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
%>
! <% 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.ProblemStatus 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("
")
End If
End If
Set rs=Nothing
conn.Close
Set conn=Nothing
%>
! <% 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.ProblemStatus 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("
| Problem ID | ") Response.Write("Team Member Name | ") Response.Write("Title | ") Response.Write("Departments | ") Response.Write("Status | ") Response.Write("
| " & rs("ProblemStatusID") & " | ") Response.Write("" & rs("TeamMemberName") & " | ") Response.Write("" & rs("ProblemTitle") & " | ") Response.Write("" & rs("DepartmentName") & " | ") Response.Write("" & rs("ProblemStatus") & " | ") Response.Write("
Jan 12, 2009 - 01:42 PM
---View Problem List---
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
!
<%
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 %> <%Response.Write("The following Tickets are opened today")%>
---Problem Details.asp----
<%@ LANGUAGE="VBScript" %>
<%
Option Explicit
'Buffer the response, so Response.Expires can be used
Response.Buffer = TRUE
%>
!
--Close the Problem--
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
!
/****** 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")%>
| Problem ID | Team Member Name | Subject | Component | Status | Edit |
|---|---|---|---|---|---|
| <%=result("ProblemStatusID")%> | <%=result("TeamMemberName")%> | <%=result("ProblemTitle")%> | <%=result("DepartmentName")%> | <%=result("ProblemStatus")%> | <% Dim ProblemStatusID ProblemStatusID=Request.QueryString("ProblemStatusId") %>">Edit |
| |||||||||||||||||||||||||||||||||||||
| ">Close the Problem | |||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||
Add New Comment