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 statementstrSQL = "SELECT * From Departments WHERE DepartmentID > 0 ORDER BY DepartmentName ASC"' Open Recordset Objectrs.Open strSQL,conn, adOpenStatic%>
!<% If Request.QueryString("qryDepartment") = "" ThenResponse.Write("Please select a Department from the list.")Else' Build querySet rs = SERVER.CreateObject("ADODB.Recordset")' first have to get the Department records to fill the drop down....' Set SQL statementstrSQL = "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 Objectrs.Open strSQL,conn,adOpenStaticIf rs.RecordCount = 0 ThenResponse.Write("No records found for Department " & Request.QueryString("qryDepartment"))ElseResponse.Write(" Items Found for Department: " & Request.QueryString("qryDepartment") & " ")' Build a table hereResponse.Write("")Response.Write("
")End IfEnd IfSet rs=Nothingconn.CloseSet conn=Nothing%>
!<% If Request.QueryString("qryDepartment") = "" ThenResponse.Write("Please select a Department from the list.")Else' Build querySet rs = SERVER.CreateObject("ADODB.Recordset")' first have to get the Department records to fill the drop down....' Set SQL statementstrSQL = "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 Objectrs.Open strSQL,conn,adOpenStaticIf rs.RecordCount = 0 ThenResponse.Write("No records found for Department " & Request.QueryString("qryDepartment"))ElseResponse.Write(" Items Found for Department: " & Request.QueryString("qryDepartment") & " ")' Build a table hereResponse.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 statementstrSQL = "GetAllOpenProblems"'Execute teh SQL and obtain resultsetSet result = objconn.Execute(strsql)'Check for ErrorsIf objconn.Errors.Count > 0 thenResponse.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]GOCREATE 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])GOALTER 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 ONGOSET QUOTED_IDENTIFIER ONGO--This stored Procedure updates the status from Open to Closed--Use this to update the status once team member is ready to close the ticketALTER Proc [dbo].[UpdateProblemStatusIdToClose](@ProblemStatusId int,@Solution nvarchar)As Declare @errorMsg nvarchar(128)-- Insert Solution into the ProblemSolutions TableINSERT 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