viewing records

sinner · March 19th, 2004, 03:21 PM

Hey Guys,
Below is the code i am using to view all of the records where state is = to OPEN. It list all of the duplicates but when i select it, it always shows the same record.. I hope i am being clear in what i am trying to do. Any idea????

<html>

<head>
<title>View Tickets</title>
<meta name="GENERATOR" content="Microsoft FrontPage 3.0">
</head>

<body>

<p align="center"><br>
</p>
<%
ConnString = "DSN=ticketbackup"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open ConnString,,,adOpenForwardOnly
Set rs = SERVER.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM ticket WHERE Ticket_state = 'OPEN' ORDER BY PSAP "

rs.Open strSQL,conn, adOpenStatic

%>

<form name="openedticket">
  <p><select name="open" size="1">
    <option value="NULL">LIST OF OPEN TICKETS BY PSAP</option>
<% Do While Not rs.EOF
                                    Response.Write ("<OPTION value='"& rs("PSAP") & "' >" & rs("PSAP") & "</option> ")
                                    rs.MoveNext
                                Loop
                                rs.Close
                                Set rs=Nothing %> </select><input type="submit" value="View">    </p>
</form>

<p> </p>
<%
Set rs = SERVER.CreateObject("ADODB.Recordset")

     strST=Request.QueryString("open")
     strSQL = "SELECT * FROM ticket WHERE PSAP = '" & strST & "'"

    ' Open Recordset Object
    rs.Open strSQL,conn,adOpenStatic

%>

<table border="1" width="100%">
  <tr>
    <td width="17%"><strong>PSAP NAME:</strong></td>
    <td width="83%"><%= rs.fields("PSAP") %>
</td>
  </tr>
  <tr>
    <td width="17%"><strong>Ticket ID:</strong></td>
    <td width="83%"><%= rs.fields("Ticket_ID") %>
</td>
  </tr>
  <tr>
    <td width="17%"><strong>Contact Number:</strong></td>
    <td width="83%"><%= rs.fields("Contact") %>
</td>
  </tr>
  <tr>
    <td width="17%"><strong>Badge/Name:</strong></td>
    <td width="83%"><%= rs.fields("Badge") %>
</td>
  </tr>
  <tr>
    <td width="17%"><strong>Trunk Number:</strong></td>
    <td width="83%"><%= rs.fields("Trunk") %>
</td>
  </tr>
  <tr>
    <td width="17%"><strong>Reason for Request:</strong></td>
    <td width="83%"><%= rs.fields("Reason") %>
</td>
  </tr>
  <tr>
    <td width="17%"><strong>Comments:</strong></td>
    <td width="83%"><%= rs.fields("Comments") %>
</td>
  </tr>
  <tr>
    <td width="17%"> </td>
    <td width="83%"> </td>
  </tr>
</table>
</body>
</html>

rajanikrishna · March 19th, 2004, 09:28 PM

use looping

while not rs.eof
response.write("<td>Name</td><td>" & rs("name") & "</td>")
rs.moveNext
wend

Imar · March 20th, 2004, 06:08 AM

Hi there,

If I understand your problem correctly, it's caused by the fact you are not submitting your page to the server.

Your second recordset tries to retrieve information using the drop-down as a filter. However, Request.QueryString("open") runs at the server, and not at the client.
If the page loads the first time, it will write out the drop down, and then immediately proceed with the second recordset.

Here's what you need to do to fix it:

1. Add an onchange handler to the drop down. Add some code to it that automatically submits the form to the server using something like: document.openedticket.submit();

2. On the server, check whether the QueryString has a value. If it does, it means the form has been submitted to the server, so you can construct your second recordset, like this:

Code:

<%
If Request.QueryString("open") & "" <> "" Then
  ' There is a QueryString, so get the record
  Set rs = Server.CreateObject("ADODB.Recordset")
  strST = Request.QueryString("open")
  strSQL = "SELECT * FROM ticket WHERE PSAP = '" & strST & "'"
  ' Open Recordset Object
  rs.Open strSQL, conn, adOpenStatic
%>

This way, the second code block will only run when the form has been posted back to the server.

Does this help?

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.