problem displaying records

**housey2** · Jan 23rd, 2003, 12:48 PM

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?!

If rs.RecordCount = 0 Then
Response.Write("No records found for category " & Request.QueryString("qryCategory"))
Else
Response.Write("<H3>Items Found for Category: " & Request.QueryString("qryCategory") & "</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>ID</TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=18% BGCOLOR=#C8C8FF>One Line Summary</TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=18% BGCOLOR=#C8C8FF>Problem</TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=50% BGCOLOR=#C8C8FF>Procedure</TD>")
Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=14% BGCOLOR=#C8C8FF>Notes</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>" & rs("subject") & "</TD>")
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

**techgnome** · Jan 23rd, 2003, 01:03 PM

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

**housey2** · Jan 23rd, 2003, 01:10 PM

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

**shahid** · Jan 24th, 2003, 12:08 AM

hi u get data from 2 tables

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

**housey2** · Jan 24th, 2003, 10:38 AM

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 ?

**housey2** · Jan 28th, 2003, 09:47 AM

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

**andreys** · Jan 28th, 2003, 10:59 AM

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.

**housey2** · Jan 28th, 2003, 05:26 PM

Code:

<html>

<head>


</head>

<body bgcolor="#21517B">

<!--
a{text-decoration:none}
a:hover {text-decoration: underline}
a:hover {color: orange;bold)
</style> -->

<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

Cheers
Housey

**andreys** · Jan 28th, 2003, 06:06 PM

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") & "'"

**housey2** · Jan 29th, 2003, 10:53 AM

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

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

**techgnome** · Jan 29th, 2003, 11:21 AM

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

Thread: problem displaying records

Thread Tools

Display

problem displaying records

query

any?

Posting Permissions