Answers
Jan 07, 2009 - 11:30 AM
<%
ConnString = "DSN=LocalUpload;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open("Provider=SQLOLEDB.1;Password=helpdesk;Persist Security Info=True;User ID=HelpDesk;Initial Catalog=HelpDesk;Data Source=s9DCA3E")
Set rs = SERVER.CreateObject("ADODB.Recordset")
' first have to get the Department records to fill the drop down....
' Set SQL statement
strSQL = "SELECT * From Departments WHERE DepartmentID > 0 ORDER BY DepartmentName ASC"
' Open Recordset Object
rs.Open strSQL,conn, adOpenStatic
%>
Sort by Department:
Select A Department:
<% Do While Not rs.EOF
Response.Write ("" & rs("DepartmentName"))
rs.MoveNext
Loop
rs.Close
Set rs=Nothing
%>
function getFilter(listitem){ var object = ""; var listValue = getListValue(listitem); document.formDepartment.submit(listValue); } function getListValue(list){ var listValue=""; if (list.selectedIndex != -1) { listValue = list.options[list.selectedIndex].value; } return (listValue); } <% If Request.QueryString("qryDepartment") = "" Then Response.Write("Please select a Department from the list.") Else ' Build query Set rs = SERVER.CreateObject("ADODB.Recordset") ' first have to get the Department records to fill the drop down.... ' Set SQL statement strSQL = "Select a.ProblemStatusId,b.TeamMemberName,c.problemTitle,e.DepartmentName,a.ProblemSta
tus From(((ProblemsUsersDeptsReps d Inner Join Departments e on d.DepartmentID = e.DepartmentID) Inner Join Problems c on d.ProblemID = c.ProblemID) Inner Join ProblemStatus a on d.ProblemsUsersDeptsRepsID = a.ProblemsUsersDeptsRepsID) Inner Join TeamMembers b on a.TeamMemberId = b.TeamMemberId" strSSL = strSQL & "Where (((UPPER(a.ProblemStatus) = 'OPEN') = '" & Request.QueryString("qryDepartment") & "'))" ' Open Recordset Object rs.Open strSQL,conn,adOpenStatic If rs.RecordCount = 0 Then Response.Write("No records found for Department " & Request.QueryString("qryDepartment")) Else Response.Write("
")
End If
End If
Set rs=Nothing
conn.Close
Set conn=Nothing
%>
function getFilter(listitem){ var object = ""; var listValue = getListValue(listitem); document.formDepartment.submit(listValue); } function getListValue(list){ var listValue=""; if (list.selectedIndex != -1) { listValue = list.options[list.selectedIndex].value; } return (listValue); } <% If Request.QueryString("qryDepartment") = "" Then Response.Write("Please select a Department from the list.") Else ' Build query Set rs = SERVER.CreateObject("ADODB.Recordset") ' first have to get the Department records to fill the drop down.... ' Set SQL statement strSQL = "Select a.ProblemStatusId,b.TeamMemberName,c.problemTitle,e.DepartmentName,a.ProblemSta
tus From(((ProblemsUsersDeptsReps d Inner Join Departments e on d.DepartmentID = e.DepartmentID) Inner Join Problems c on d.ProblemID = c.ProblemID) Inner Join ProblemStatus a on d.ProblemsUsersDeptsRepsID = a.ProblemsUsersDeptsRepsID) Inner Join TeamMembers b on a.TeamMemberId = b.TeamMemberId" strSSL = strSQL & "Where (((UPPER(a.ProblemStatus) = 'OPEN') = '" & Request.QueryString("qryDepartment") & "'))" ' Open Recordset Object rs.Open strSQL,conn,adOpenStatic If rs.RecordCount = 0 Then Response.Write("No records found for Department " & Request.QueryString("qryDepartment")) Else Response.Write("
Items Found for Department: " & Request.QueryString("qryDepartment") & "
") ' Build a table here Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Do While Not rs.EOF Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") Response.Write("") rs.MoveNext Loop rs.Close Response.Write("| Problem ID | Team Member Name | Title | Departments | Status |
| " & rs("ProblemStatusID") & " | " & rs("TeamMemberName") & " | " & rs("ProblemTitle") & " | " & rs("DepartmentName") & " | " & rs("ProblemStatus") & " |
Jan 12, 2009 - 01:42 PM
---View Problem List---
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
Untitled Document
<%
Dim strSQL, result
' Set SQL statement
strSQL = "GetAllOpenProblems"
'Execute teh SQL and obtain resultset
Set result = objconn.Execute(strsql)
'Check for Errors
If objconn.Errors.Count > 0 then
Response.Write("Error Occurred:
") Else %>
---Problem Details.asp----
<%@ LANGUAGE="VBScript" %>
<%
Option Explicit
'Buffer the response, so Response.Expires can be used
Response.Buffer = TRUE
%>
ProblemDetails
<%
Dim ProblemStatusID
ProblemStatusID=Request.QueryString("ProblemStatusID") %>
<%
Dim strSQL, result
ProblemStatusID = CLNG("0" & Request.QueryString("ProblemStatusId") )
If ProblemStatusID <= 0 Then
Response.Write "BOGUS!"
Response.End
End If
' Set SQL statement
strSQL = "GetInformationForProblemStatusId " & ProblemStatusID
' For Debug
'Response.Write "DEBUG: " & strSQL & ""
'Execute the SQL and obtain resultset
Set result = objConn.Execute(strsql)
'Check for Errors
If objconn.Errors.Count > 0 then
Response.Write("Error Occurred:
") Else %>
--Close the Problem--
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
Problem is Closed
<%
Dim ProblemStatusID
ProblemStatusID=Request.QueryString("ProblemStatusID") %>
<%
Dim strSQL, result
ProblemStatusID = CLNG("0" & Request.QueryString("ProblemStatusId") )
If ProblemStatusID <= 0 Then
Response.Write "BOGUS!"
Response.End
End If
' Set SQL statement
strSQL = "UpdateProblemStatusIdToClose " & ProblemStatusID
' For Debug
'Response.Write "DEBUG: " & strSQL & ""
'Execute the SQL and obtain resultset
Set result = objConn.Execute(strsql)
'Check for Errors
If objconn.Errors.Count > 0 then
Response.Write("Error Occurred:
") Else %>
/****** Object: Table [dbo].[ProblemSolution] Script Date: 01/12/2009 17:47:10 ******/
CREATE TABLE [dbo].[ProblemSolution](
[ProblemSolutionId] [int] IDENTITY(1,1) NOT NULL,
[ProblemStatusId] [int] NOT NULL,
[Solution] [nvarchar] (1000) NOT NULL,
CONSTRAINT [PK_ProblemSolution] PRIMARY KEY CLUSTERED
(
[ProblemSolutionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ProblemSolution_ProblemStatusId] ON [dbo].[ProblemSolution]
(
[ProblemStatusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: ForeignKey [FK_ProblemStatus_ProblemSolution] Script Date: 1/08/2009 17:47:10 ******/
ALTER TABLE [dbo].[ProblemSolution] WITH CHECK ADD CONSTRAINT [FK_ProblemStatus_ProblemSolution] FOREIGN KEY([ProblemStatusID])
REFERENCES [dbo].[ProblemStatus] ([ProblemStatusId])
GO
ALTER TABLE [dbo].[ProblemSolution] CHECK CONSTRAINT [FK_ProblemStatus_ProblemSolution]
GO
----------------
USE [HelpDesk]
GO
/****** Object: StoredProcedure [dbo].[UpdateProblemStatusIdToClose] Script Date: 01/12/2009 13:18:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--This stored Procedure updates the status from Open to Closed
--Use this to update the status once team member is ready to close the ticket
ALTER Proc [dbo].[UpdateProblemStatusIdToClose]
(
@ProblemStatusId int,
@Solution nvarchar
)
As
Declare
@errorMsg nvarchar(128)
-- Insert Solution into the ProblemSolutions Table
INSERT INTO ProblemSolution
(@ProblemStatusID,@Solution)
-- Next, Update existing records
Update ProblemStatus
Set ProblemStatus = 'CLOSED'
Where ProblemStatusId = @problemStatusId
If @@error <> 0
Begin
select @errorMsg = 'Update Problem Status to Close failed for ProblemStatusId: ' + cast(@problemStatusId as varchar)
End
--------------------------
Problem is Closed
ProblemStatusId ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/helpdesk/test/closeproblem.asp, line 57
") Else %>
<%Response.Write("The following Tickets are opened today")%>
<%Do Until result.EOF%> <% Dim ProblemStatusID ProblemStatusID=Request.QueryString("ProblemStatusId") %> <% result.MoveNext loop End If 'Close Database ' objconn.Close %>| Problem ID | Team Member Name | Subject | Component | Status | Edit |
|---|---|---|---|---|---|
| <%=result("ProblemStatusID")%> | <%=result("TeamMemberName")%> | <%=result("ProblemTitle")%> | <%=result("DepartmentName")%> | <%=result("ProblemStatus")%> | ">Edit |
") Else %>
Edit Problem <%=result("ProblemStatusId")%>
<%Do Until result.EOF%> <% result.MoveNext loop End If 'Close Database ' objconn.Close %>
| ||||||||||||||||||||||||||||||||||||
") Else %>
Problem is Closed
<% End If 'Close Database ' objconn.Close %>
|
Add New Comment