Help Questions | Solutions | Experts

Select Categories Below

.net [

]

Bluetooth [

]

browsers [

]

C# [

]

computer [

]

Errors [

]

forms [

]

HTML [

]

information tec... [

]

internet [

]

Start Your Own Q&A Site

Create your own Q&A site easily, allowing you to quickly grow a new community around any subject matter or generate new organic traffic for your existing website.

Question

Status: Closed Points: 250 Time: 14:34 - Jan 07, 2009

san

Binding a dropdownlsit to a button to determine datagrid rows

I am using ASP (not ASP.net) and SQL Server
I have a datagrid that I want filtered data to appear in. To do that, I created a dropdownlist which is populated from the same table as the datagrid, however now I'm stuck on how to update the datagrid with the selection that's been made in the dropdownlist. I'd like to use a button. Can anyone help me with this?
Here's my existing code:

<%
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

%>

<FORM name="formDepartment">
<td width="40"><label id="lbldept" for="select1">Sort by Department:</label></td>
<SELECT NAME="qryDepartment" onChange="getFilter(this.form.qryDepartment)">
<OPTION selected>Select A Department:
<% Do While Not rs.EOF
Response.Write ("<OPTION value='" & rs("DepartmentName") & "'>" & rs("DepartmentName"))
  rs.MoveNext
Loop
rs.Close
Set rs=Nothing
%>
</OPTION>

</SELECT>

<BR>
</FORM>

<SCRIPT language="JavaScript">

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);
}
</SCRIPT>
<%

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 Where (UPPER(a.ProblemStatus) = 'OPEN') and (e.DepartmentName = 'strsql') ORDER BY e.DepartmentName"

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("<H3>Items Found for Department: " & Request.QueryString("qryDepartment") & "</H3>")

' Build a table here
Response.Write("<TABLE CELLSPACING=2 CELLPADDING=0 COLS=3 WIDTH=500 BGCOLOR=#F0F0FF border=3 bordercolor=#666666>")
Response.Write("<TR>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15% BGCOLOR=#C8C8FF><B>Problem ID</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=65% BGCOLOR=#C8C8FF><B>Team Member Name</B></TD>")
  Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=20% BGCOLOR=#C8C8FF><B>Title</B></TD>")

Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15% BGCOLOR=#C8C8FF><B>Departments</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15% BGCOLOR=#C8C8FF><B>Status</B></TD>")

Response.Write("</TR>")

Do While Not rs.EOF
  Response.Write("<TR>")
  Response.Write("<TD>" & rs("ProblemStatusID") & "</TD>")
    Response.Write("<TD>" & rs("TeamMemberName") & "</TD>")
  Response.Write("<TD>" & rs("ProblemTitle") & "</TD>")

  Response.Write("<TD>" & rs("DepartmentName") & "</TD>")
Response.Write("<TD>" & rs("ProblemStatus") & "</TD>")

  Response.Write("</TR>")
  rs.MoveNext
Loop

rs.Close

Response.Write("</TABLE>")

End If

End If

Set rs=Nothing

conn.Close
Set conn=Nothing

%>

Categories:

asp

Answer Discussion

Tutorials

san

Date:: Jan 07, 2009

Time:: 15:30

<%
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

%>

<FORM name="formDepartment">
<td width="40"><label id="lbldept" for="select1">Sort by Department:</label></td>
<SELECT NAME="qryDepartment" onChange="getFilter(this.form.qryDepartment)">
<OPTION selected>Select A Department:
<% Do While Not rs.EOF
Response.Write ("<OPTION value='" & rs("DepartmentName") & "'>" & rs("DepartmentName"))
  rs.MoveNext
Loop
rs.Close
Set rs=Nothing
%>
</OPTION>

</SELECT>

<BR>
</FORM>

<SCRIPT language="JavaScript">

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);
}
</SCRIPT>
<%

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("<H3>Items Found for Department: " & Request.QueryString("qryDepartment") & "</H3>")

' Build a table here
Response.Write("<TABLE CELLSPACING=2 CELLPADDING=0 COLS=3 WIDTH=500 BGCOLOR=#F0F0FF border=3 bordercolor=#666666>")
Response.Write("<TR>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15% BGCOLOR=#C8C8FF><B>Problem ID</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=65% BGCOLOR=#C8C8FF><B>Team Member Name</B></TD>")
  Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=20% BGCOLOR=#C8C8FF><B>Title</B></TD>")

Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15% BGCOLOR=#C8C8FF><B>Departments</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=15% BGCOLOR=#C8C8FF><B>Status</B></TD>")

Response.Write("</TR>")

Do While Not rs.EOF
  Response.Write("<TR>")
  Response.Write("<TD>" & rs("ProblemStatusID") & "</TD>")
    Response.Write("<TD>" & rs("TeamMemberName") & "</TD>")
  Response.Write("<TD>" & rs("ProblemTitle") & "</TD>")

  Response.Write("<TD>" & rs("DepartmentName") & "</TD>")
Response.Write("<TD>" & rs("ProblemStatus") & "</TD>")

  Response.Write("</TR>")
  rs.MoveNext
Loop

rs.Close

Response.Write("</TABLE>")

End If

End If

Set rs=Nothing

conn.Close
Set conn=Nothing

%>

san

Date:: Jan 12, 2009

Time:: 17:42

---View Problem List---
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-t...
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<%
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: <br />")
Else
%>
<h3 align="center">
<%Response.Write("The following Tickets are opened today")%></h3>
<table width="98%" height="140" border="1" align="center" cellpadding="3" cellspacing="3">
<tr bgcolor="#CCCCCC" bordercolor="#999999"><th width="9%">Problem ID</th>
<th width="24%">Team Member Name</th>
<th width="14%">Subject</th>
<th width="29%">Component</th>
<th width="13%">Status </th>
<th width="11%">Edit</th>
</tr>
<%Do Until result.EOF%>
<tr><td><%=result("ProblemStatusID")%></td>
<td><%=result("TeamMemberName")%></td>
<td><%=result("ProblemTitle")%></td>
<td><%=result("DepartmentName")%></td>
<td><%=result("ProblemStatus")%></td>
<%
Dim ProblemStatusID
ProblemStatusID=Request.QueryString("ProblemStatusId") %>
<td><a href="problemdetails.asp?ProblemStatusID=<% = result("ProblemStatusId") %> ">Edit</a></td></tr>
<%
result.MoveNext
loop
End If
'Close Database
'
objconn.Close
%>
</table>
</body>
</html>

---Problem Details.asp----
<%@ LANGUAGE="VBScript" %>
<%
Option Explicit
'Buffer the response, so Response.Expires can be used
Response.Buffer = TRUE
%>
<?xml version="1.0"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-t...
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<title>ProblemDetails</title>
<%
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 & "<HR>"
'Execute the SQL and obtain resultset
Set result = objConn.Execute(strsql)

'Check for Errors
If objconn.Errors.Count > 0 then
Response.Write("Error Occurred: <br />")
Else
%>
<h3 align="center">
Edit Problem <%=result("ProblemStatusId")%>
</h3>
</head>

<body>

<form id="CloseProblem" action="closeproblem.asp" method="post" name="ProblemDetails">

<table>
<tr>
<td colspan="2" align="right"></td>
</tr>
<tr>
<td colspan="2"> </td>
</tr>
<tr>
<td colspan="2"><table border="0" cellspacing="0">
<tr>
<td><b>ProblemStatusId</b> </td>
<td><%=result("ProblemStatusId")%> </td>
</tr>
<tr>
<td><b>UserName</b> </td>
<td><%=result("UserName")%> </td>
</tr>
<tr>
<td><b>EMail:</b> </td>
<td><%=result("UserEmail")%> </td>
</tr>
   <tr>
<td><b>Location:</b> </td>
<td><%=result("UserLocation")%> </td>
</tr>
   <tr>
<td><b>Phone:</b> </td>
<td><%=result("UserPhone")%> </td>
</tr>
   <tr>
   
<td><b>Component:</b> </td>
<td><%=result("DepartmentName")%> </td>
</tr>
   <tr>
<td><b>NH:</b> </td>
<td><%=result("UserNH")%> </td>
</tr>

   <tr>
<td><b>SSN:</b> </td>
<td><%=result("UserSSN")%> </td>
</tr>
  <tr>
<td><b>IsRepresentativeInvolved:</b> </td>
<td><%=result("IsRepresentativeInvolved")%> </td>
</tr>

   <tr>
<td><b>RepresentativeSSN:</b> </td>
<td><%=result("RepresentativeSSN")%> </td>
</tr>

  <tr>
<td><b>RepresentativeZipCode:</b> </td>
<td><%=result("RepresentativeZipCode")%> </td>
</tr>

   <tr>
<td><b>OrganizationName:</b> </td>
<td><%=result("OrganizationName")%> </td>
</tr>
  <tr>
<td><b>IsProblem/Question:</b> </td>
<td><%=result("IsProblemQuestion")%> </td>
</tr>
  <tr>
<td><b>IsComment:</b> </td>
<td><%=result("IsComment")%> </td>
</tr>

   <tr>
<td><b>ProblemTitle:</b> </td>
<td><%=result("ProblemTitle")%> </td>
</tr>

   <tr>
<td><b>ProblemDescription:</b> </td>
<td><%=result("ProblemDescription")%> </td>
</tr>
   <tr>
<td><b>Attachment :</b> </td>
<td><%=result("Attachment")%> </td>
</tr>

   <tr>
<td><b>Solution :</b> </td>
<td><textarea cols="80" rows="12" id="txtBox6" name="Solution"></textarea></td>
</tr>


</table>
<tr>

  <td><div align="center"><a href="closeproblem.asp?ProblemStatusID=<% = result("ProblemStatusId") %> ">Close the Problem</a></div></td>


</td>
</tr>
<%Do Until result.EOF%>
<%
result.MoveNext
loop
End If
'Close Database
'
objconn.Close
%>
</table>
</form>
</body>
</html>

--Close the Problem--

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-t...
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Problem is Closed</title>


<%
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 & "<HR>"
'Execute the SQL and obtain resultset
Set result = objConn.Execute(strsql)

'Check for Errors
If objconn.Errors.Count > 0 then
Response.Write("Error Occurred: <br />")
Else
%>
<h3 align="center">
Problem is Closed
</h3>
<%
End If
'Close Database
'
objconn.Close
%>
</h3>
</head>
</head>

<body>
<table>
<tr>
<td colspan="2" align="right"></td>
</tr>
<tr>
<td colspan="2"> </td>
</tr>
<tr>
<td colspan="2"><table border="0" cellspacing="0">
<tr>
<td><b>ProblemStatusId</b> </td>
<td><%=result("ProblemStatusId")%> </td>
</tr>
<tr>
<td><b>UserName</b> </td>
<td><%=result("UserName")%> </td>
</tr>
<tr>
<td><b>EMail:</b> </td>
<td><%=result("UserEmail")%> </td>
</tr>
   <tr>
<td><b>Location:</b> </td>
<td><%=result("UserLocation")%> </td>
</tr>
   <tr>
<td><b>Phone:</b> </td>
<td><%=result("UserPhone")%> </td>
</tr>
   <tr>
   
<td><b>Component:</b> </td>
<td><%=result("DepartmentName")%> </td>
</tr>
   <tr>
<td><b>NH:</b> </td>
<td><%=result("UserNH")%> </td>
</tr>

   <tr>
<td><b>SSN:</b> </td>
<td><%=result("UserSSN")%> </td>
</tr>
  <tr>
<td><b>IsRepresentativeInvolved:</b> </td>
<td><%=result("IsRepresentativeInvolved")%> </td>
</tr>

   <tr>
<td><b>RepresentativeSSN:</b> </td>
<td><%=result("RepresentativeSSN")%> </td>
</tr>

  <tr>
<td><b>RepresentativeZipCode:</b> </td>
<td><%=result("RepresentativeZipCode")%> </td>
</tr>

   <tr>
<td><b>OrganizationName:</b> </td>
<td><%=result("OrganizationName")%> </td>
</tr>
  <tr>
<td><b>IsProblem/Question:</b> </td>
<td><%=result("IsProblemQuestion")%> </td>
</tr>
  <tr>
<td><b>IsComment:</b> </td>
<td><%=result("IsComment")%> </td>
</tr>

   <tr>
<td><b>ProblemTitle:</b> </td>
<td><%=result("ProblemTitle")%> </td>
</tr>

   <tr>
<td><b>ProblemDescription:</b> </td>
<td><%=result("ProblemDescription")%> </td>
</tr>
   <tr>
<td><b>Attachment :</b> </td>
<td><%=result("Attachment")%> </td>
</tr>

   <tr>
<td><b>Solution :</b> </td>

</tr>
  </table>

</table>

</body>
</html>

/****** 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

Question Answered

This question has been closed, and points have been rewarded to the following experts:

san:

250

You're welcome however to comment or give additional information or if you wish, you have the ability to write a Tutorial in the Tutorial Area.

Answer this Question

New User

Email:
Upon submission of this form, you will automatically be registered as a Quomon user and we will send your login information to this address

Registered User

Username:

Password:

Forgot Your Password?

No tutorials have been submitted yet. Want to be the first?