problem displaying records

Hi,

I have a list menu where users will select a category and then the records that are in that category are displayed.

There is one table for the category names and one for the records.

It works but the problem is it only works for say the first 7 records (there are about 50 in the table at the moment...). Some categories don't show at all!

Below is the loop that is used, any help?!

Quote:

If rs.RecordCount = 0 Then
Response.Write("No records found for category " & Request.QueryString("qryCategory"))
Else
Response.Write("<font color=" & "white" & "><H3>Items Found for Category: <b><font color=" & "yellow" & ">" & Request.QueryString("qryCategory") & "</font></font></b></H3>")

If not rs.bof then RS.movefirst

' Build a table here
Response.Write("<TABLE BORDER=1 CELLSPACING=4 CELLPADDING=4 COLS=5 WIDTH=940 BGCOLOR=#F0F0FF>")
Response.Write("<TR>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=2% BGCOLOR=#C8C8FF><B>ID</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=18% BGCOLOR=#C8C8FF><B>One Line Summary</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=18% BGCOLOR=#C8C8FF><B>Problem</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=50% BGCOLOR=#C8C8FF><B>Procedure</B></TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=14% BGCOLOR=#C8C8FF><B>Notes</B></TD>")
Response.Write("</TR>")

Do While Not rs.eof

' Do

Response.Write("<TR>")
Response.Write("<TD>" & rs("Upload_Category.Category_Id") & "</TD>")
Response.Write("<TD><b><font face=" & "red" & ">" & rs("subject") & "</TD></b></font>")
Response.Write("<TD>" & rs("problem") & "</TD>")
Response.Write("<TD>" & rs("proc") & "</TD>")
Response.Write("<TD>" & rs("notes") & "</TD>")
Response.Write("</TR>")
rs.movenext
' Loop until rs.eof
Loop

rs.Close

Response.Write("</TABLE>")

End If

End If

Thanks in advance, housey

I don't think the code you posted is the problem...... where does RS get populated at? What do the queries look like?

query

This is before what I posted above:

Code:

If Request.QueryString("qryCategory") = "" Then Response.Write("<font color=" & "white" & ">Please select a category from the list." & "</font>") Else ' Build query Set rs = SERVER.CreateObject("ADODB.Recordset") ' first have to get the category records to fill the drop down.... ' Set SQL statement strSQL = "SELECT * " strSQL = strSQL & "FROM Upload_Category, Document_Category " strSQL = strSQL & "WHERE Upload_Category.Category_Id = Document_Category.Category_Id " strSQL = strSQL & "AND Upload_Category.Category_Name = '" & Request.QueryString("qryCategory") & "'" 'response.write strSQL ' Open Recordset Object rs.Open strSQL, conn, 2, 3

Thanks

hi u get data from 2 tables

Quote:

strSQL = "SELECT * "
strSQL = strSQL & "FROM Upload_Category, Document_Category "
strSQL = strSQL & "WHERE Upload_Category.Category_Id = Document_Category.Category_Id "
strSQL = strSQL & "AND Upload_Category.Category_Name = '" & Request.QueryString("qryCategory") & "'"

you may have only 7 match records between 2 tables and the category u get by querystring

check it out

Hi,

There are records that fit the query for certain categories that have ID of more than 7...

I don't think i'm understanding it too well....

btw would it matter if some of the category names have a / in them ?

any?

Hi,

Does anyone have a clue on this i'm going mad :(

I've now tried it off two different servers with the same result!

Thanks,

Housey2

Is it possible to post all of your code here?
Post it like this, type:

[code]

Your code here

[/code]

So your code will look like this:

Code:

Your code here

Thanks.

Code:

<html> <head> </head> <body bgcolor="#21517B">  <title>ADSL Procs</title> <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Provider = "Microsoft.Jet.OLEDB.4.0" Conn.ConnectionString = "Data Source=" & Server.MapPath ("db/db.mdb") Conn.Open Set Rs = Server.CreateObject("ADODB.Recordset") Rs.Open "SELECT * from document_category", Conn, 1,3 %> <FORM name="formCategory"> <SELECT NAME="qryCategory" onChange="getFilter(this.form.qryCategory)"> <OPTION selected>Select A Category: <% Do While Not rs.EOF Response.Write ("<OPTION value='" & rs("Category_Name") & "'>" & rs("Category_Name")) rs.MoveNext Loop rs.Close Set rs=Nothing %> </OPTION> </SELECT></FORM> <SCRIPT language="JavaScript"> function getFilter(listitem){ var object = ""; var listValue = getListValue(listitem); document.formCategory.submit(listValue); } function getListValue(list){ var listValue=""; if (list.selectedIndex != -1) { listValue = list.options[list.selectedIndex].value; } return (listValue); } </SCRIPT> <% If Request.QueryString("qryCategory") = "" Then Response.Write("<font color=" & "white" & ">Please select a category from the list." & "</font>") Else ' Build query Set rs = SERVER.CreateObject("ADODB.Recordset") ' Set SQL statement strSQL = "SELECT * " strSQL = strSQL & "FROM Upload_Category, Document_Category " strSQL = strSQL & "WHERE Upload_Category.Category_Id = Document_Category.Category_Id " strSQL = strSQL & "AND Upload_Category.Category_Name = '" & Request.QueryString("qryCategory") & "'" 'response.write strSQL ' Open Recordset Object rs.Open strSQL, conn, 2, 3 If rs.RecordCount = 0 Then Response.Write("No records found for category " & Request.QueryString("qryCategory")) Else Response.Write("<font color=" & "white" & "><H3>Items Found for Category: <b><font color=" & "yellow" & ">" & Request.QueryString("qryCategory") & "</font></font></b></H3>") If not rs.bof then RS.movefirst ' Build a table here Response.Write("<TABLE BORDER=1 CELLSPACING=4 CELLPADDING=4 COLS=5 WIDTH=940 BGCOLOR=#F0F0FF>") Response.Write("<TR>") Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=2% BGCOLOR=#C8C8FF><B>ID</B></TD>") Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=18% BGCOLOR=#C8C8FF><B>One Line Summary</B></TD>") Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=18% BGCOLOR=#C8C8FF><B>Problem</B></TD>") Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=50% BGCOLOR=#C8C8FF><B>Procedure</B></TD>") Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=14% BGCOLOR=#C8C8FF><B>Notes</B></TD>") Response.Write("</TR>") Do While Not rs.eof ' Do Response.Write("<TR>") Response.Write("<TD>" & rs("Upload_Category.Category_Id") & "</TD>") Response.Write("<TD><b><font face=" & "red" & ">" & rs("subject") & "</TD></b></font>") Response.Write("<TD>" & rs("problem") & "</TD>") Response.Write("<TD>" & rs("proc") & "</TD>") Response.Write("<TD>" & rs("notes") & "</TD>") Response.Write("</TR>") rs.movenext ' Loop until rs.eof Loop rs.Close Response.Write("</TABLE>") End If End If Set rs=Nothing conn.Close Set conn=Nothing %> </body> </html>

I tried to make it readable but I think I failed...

Thanks for any help... its probably something simple after all this :p

Cheers
Housey

change to this first:

Code:

<FORM name="formCategory"> <SELECT NAME="qryCategory" onChange="getFilter(this.form.qryCategory)"> <OPTION selected>Select A Category: <% Do While Not rs.EOF Response.Write ("<OPTION value='" & rs("Category_Name") & "'>" & rs("Category_Name") & "</OPTION>") rs.MoveNext Loop rs.Close Set rs=Nothing %> </SELECT></FORM>

your sql should be:

Code:

strSQL = "SELECT Upload_Category.*, Document_Category .* " strSQL = strSQL & "FROM Upload_Category INNER JOIN Document_Category ON Upload_Category.Category_Id = Document_Category.Category_Id " strSQL = strSQL & "WHERE Upload_Category.Category_Name LIKE '" & Request.QueryString("qryCategory") & "'"

Sadly I get the same result......... If I write the sql to the page I get this:

Quote:

SELECT Upload_Category.*, Document_Category.* FROM Upload_Category INNER JOIN Document_Category ON Upload_Category.Category_Id = Document_Category.Category_Id WHERE Upload_Category.Category_Name LIKE 'Logon / VPN Problems'

I have amended as you have said and the SQL code now looks like this:

Code:

strSQL = "SELECT Upload_Category.*, Document_Category.* " strSQL = strSQL & "FROM Upload_Category INNER JOIN Document_Category ON Upload_Category.Category_Id = Document_Category.Category_Id " strSQL = strSQL & "WHERE Upload_Category.Category_Name LIKE '" & Request.QueryString("qryCategory") & "'" 'strSQL = strSQL & "AND Upload_Category.Category_Name = '" & Request.QueryString("qryCategory") & "'"

Cheers
Housey

p.s.

I can't see anything wrong with the database..

I think I see the prob now:
WHERE Upload_Category.Category_Name LIKE 'Logon / VPN Problems'

has the following results:

"Logon / VPN Problems" matches
"My Logon / VPN Problems Too" doesn't match
"Logon Name / VPN Problems Too" doesn't match

BUT, if the LIKE clause is changed like so:
WHERE Upload_Category.Category_Name LIKE '%Logon / VPN Problems%'

You get the following results
"Logon / VPN Problems" matches
"My Logon / VPN Problems Too" matches
"Logon Name / VPN Problems Too" doesn't match