|
-
Jan 23rd, 2003, 12:48 PM
#1
Thread Starter
Lively Member
 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?!
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
-
Jan 23rd, 2003, 01:03 PM
#2
I don't think the code you posted is the problem...... where does RS get populated at? What do the queries look like?
-
Jan 23rd, 2003, 01:10 PM
#3
Thread Starter
Lively Member
 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
-
Jan 24th, 2003, 12:08 AM
#4
Fanatic Member
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
-
Jan 24th, 2003, 10:38 AM
#5
Thread Starter
Lively Member
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 ?
-
Jan 28th, 2003, 09:47 AM
#6
Thread Starter
Lively Member
 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
-
Jan 28th, 2003, 10:59 AM
#7
Frenzied Member
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:
Thanks.
-
Jan 28th, 2003, 05:26 PM
#8
Thread Starter
Lively Member
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
-
Jan 28th, 2003, 06:06 PM
#9
Frenzied Member
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") & "'"
Last edited by andreys; Jan 28th, 2003 at 06:14 PM.
-
Jan 29th, 2003, 10:53 AM
#10
Thread Starter
Lively Member
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..
Last edited by housey2; Jan 29th, 2003 at 11:08 AM.
-
Jan 29th, 2003, 11:21 AM
#11
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
