Programmers Resource - No records found

Vimal

06-20-2003, 12:49 AM

Hi can anyone please help me out. I have the following codes:

<%
Response.Buffer = True
%>

<%
Dim Connrcs
Dim Conn
Dim StrConn
Dim strSQL
Dim intPageCount
Dim intRecordCount
Dim intPage ' The current page that we are on.
Dim intRecord ' Counter used to iterate through the recordset.
Dim intStart ' The record that we are starting on.
Dim intFinish ' The record that we are finishing on.

if Session("username") = "" then
response.redirect "login.asp"
end if

If Request.QueryString("NAV") = "" Then
intPage = 1
Else
intPage = Request.QueryString("NAV")
End If

sPath = Server.MapPath("reservedb.mdb")
strConn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & sPath
set conn = server.createobject("adodb.connection")
conn.open strConn

set connrcs = server.createobject("adodb.recordset")

If Request.form("D1") = "Fiction" Then
strSql = "select * from books where Fiction= ON"

ElseIf Request.form("D1") = "Romance" Then
strSql = "select * from books where Romance= ON"

Elseif Request.form("D1") = "Suspense" Then
strSql = "select * from books where Suspense= ON"

Else
strSql = "select * from books where Autobiography= ON"
End if

'strSql = "select * from books"

Connrcs.CursorLocation = 3 'adUseClient
Connrcs.CursorType = 3 'adOpenStatic
Connrcs.ActiveConnection = Conn

Connrcs.Open strSQL

Connrcs.PageSize = 1
Connrcs.CacheSize = Connrcs.PageSize
intPageCount = Connrcs.PageCount
intRecordCount = Connrcs.RecordCount

If Cint(intPage) > CInt(intPageCount) Then intPage = intPageCount
if Cint(intPage) <= 0 then intPage = 1

If intRecordCount > 0 Then
Connrcs.AbsolutePage = intPage
intStart = Connrcs.AbsolutePosition
If CInt(intPage) = CInt(intPageCount) Then
intFinish = intRecordCount
Else
intFinish = intStart + (Connrcs.PageSize - 1)
End if
End If
%>

[(HTML TAGS]

<TABLE width="632" height="382">
<TBODY>
<TR>
<TD vAlign=top width="218" height="378">
<form method="POST" action="result1.asp">
<TABLE height="106">
<TBODY>
<TR>
<TD
style="background-color: #336699; text-align: center; border-style: outset; border-width: 1px"
colSpan=2 height="21"><A name=Search><FONT
style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ffffff">Search</FONT></A></TD></TR>
<TR>
<TD
style="background-color: #ffeac5; border-style: inset; border-width: 0px" height="22"><FONT
style="FONT-SIZE: 10pt; COLOR: #000000">Category</FONT></TD>
<TD
style="background-color: #ffffff; border-width: 1px" height="22"><select size="1" name="D1">
<option selected>Choose a Category</option>
<option>Fiction</option>
<option>Romance</option>
<option>Suspense</option>
<option>Autobiography</option>
</select></TD></TR>
<TR>
<TD align=right
colSpan=2 height="27"><INPUT type=submit value=Search name="B1"></TD></TR></TBODY></TABLE></FORM>
<TABLE>
<TBODY>
<TR>
<TD
style="BORDER-RIGHT: 1px outset; BORDER-TOP: 1px outset; BORDER-LEFT: 1px outset; BORDER-BOTTOM: 1px outset; BACKGROUND-COLOR: #336699; TEXT-ALIGN: center"><FONT
style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ffffff">More
Search Options</FONT></TD></TR>
<TR>
<TD
style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BACKGROUND-COLOR: #fffff; BORDER-RIGHT-WIDTH: 1px"><a href="advsearch.asp"><font color="#FF0000">Advanced
Search</font></a></TD></TR></TBODY></TABLE>
<TABLE height="117">
<TBODY>
<TR>
<TD
style="background-color: #336699; text-align: center; border-style: outset; border-width: 1px" height="21"><A
name=Categories><FONT
style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ffffff">Categories</FONT></A></TD></TR>
<TR>
<TD
style="background-color: #ffffff; border-style: inset; border-width: 0px" height="18"><FONT
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #ce7e00"></FONT></TD></TR>
<TR>
<TD
style="background-color: #ffffff; border-width: 1px" height="18">Fiction</TD></TR>
<TR>
<TD
style="background-color: #ffffff; border-width: 1px" height="20">Suspense</TD></TR>
<TR>
<TD
style="background-color: #ffffff; border-width: 1px" height="20">Autobiography</TD></TR>
<TR>
<TD
style="background-color: #ffffff; border-width: 1px" height="20">Romance</TD></TR></TBODY></TABLE>
<TABLE>
<TBODY>
<TR>
<TD
style="BORDER-RIGHT: 0px inset; BORDER-TOP: 0px inset; BORDER-LEFT: 0px inset; BORDER-BOTTOM: 0px inset; BACKGROUND-COLOR: #ffffff"><FONT
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #ce7e00"></FONT></TD></TR>
<TR>
<TD
style="BORDER-RIGHT: 0px inset; BORDER-TOP: 0px inset; BORDER-LEFT: 0px inset; BORDER-BOTTOM: 0px inset; BACKGROUND-COLOR: #ffffff"><FONT
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #ce7e00"></FONT></TD></TR>
</TBODY></TABLE></TD>
<TD vAlign=top width="400" height="378">
<TABLE width=385>
<TBODY>
<TR>
<TD width="377"></TD></TR></TBODY></TABLE>

<TABLE height="257" width="384">
<TBODY>
<TD
style="background-color: #ffffff; border-style: inset; border-width: 0px" height="19" width="378">
<p align="center"><FONT
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #ce7e00"></FONT>Your search returned <b><%=intRecordCount%></b> records</p>
</TD>

<TR>
<TD
style="background-color: #ffffff; border-style: inset; border-width: 0px" height="19" width="378">
<p align="center"><font size="2"><i>You are viewing <%=intStart%> through
</font> <%=intFinish%></p>
</i></TD>
<TR>
<TD
style="background-color: #336699; text-align: center; border-style: outset; border-width: 1px" height="21" width="376"><A
name=Recommended><FONT style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ffffff"><%=Request.form("D1")%></FONT></A></TD></TR>

<%
If intRecordCount > 0 Then
For intRecord = 1 to Connrcs.PageSize
%>

<TR>
<TD
style="background-color: #ffffff; border-style: inset; border-width: 0px" height="19" width="378"><FONT
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #ce7e00"></FONT><b>Title</b></TD></TR>
<TR>

<TD
style="background-color: #ffffff; border-width: 1px" height="20" width="376"><%=Connrcs(1)%></a></TD></TR>
<TR>

<TD
style="background-color: #ffffff; border-style: inset; border-width: 0px" height="19" width="378"><FONT
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: #ce7e00"></FONT><b>Author</b></TD></TR>
<TR>
<TD
style="background-color: #ffffff; border-width: 1px" height="20" width="376"><%=Connrcs(2)%></TD></TR>

<TR>
<TD
style="background-color: #ffffff; border-width: 1px" height="20" width="376"><%=Connrcs(10)%></TD></TR>
<TR>
<TD
style="background-color: #ffffff; border-style: inset; border-width: 0px" height="19" width="378"><b>Price                                             Stock</b></TD></TR>
<TR>
<TD
style="background-color: #ffffff; border-width: 1px" height="20" width="376">Rs. <%=Connrcs(6)%>                                        <%=Connrcs(5)%></TD></TR>
<%
Response.write "<tr><th><font size=2><p align=left><font color=blue><a href=""bag.asp?bookid=" & connrcs(0) & """><br>Reserve"
%>

<TR>
<TD
style="background-color: #ffffff; border-width: 1px" height="20" width="376"></TD></TR>
<TR>
<TD
style="background-color: #ffffff; border-style: inset; border-width: 0px" height="17" width="378">

<%
connrcs.MoveNext

If Connrcs.EOF Then Exit For
Next
%>

</TD></TR></TBODY></TABLE>

<%If cInt(intPage) > 1 Then%>
<p align="center"><a href="result1.asp?NAV=<%=intPage - 1%>"> << Prev</a>
<%End IF %>

<%If cInt(intPage) < cInt(intPageCount) Then%>
<a href="result1.asp?NAV=<%=intPage + 1%>"> << Next</a></p>
<%End if%>

</table>
<%End if%>
</BODY>
</HTML>

My problem lies within the SQL Query. As it is, I am getting a Zero record found, though I know there are records that satisfy the above conditions, and the request.form("D1") contain the necessary value.

However, I changed my SQL query into

strSql = "select * from books", for a test, and I am getting all the required results, and my paging through recordsets work perfectly.

I cannot understand why my initial SQL query statement is finding no records when it should have found some.

Can anyone help me plz?

Thanks

Kodo

06-20-2003, 08:35 AM

if your field is a boolean type you need to search for

0=false or no
-1 =true or yes

If Request.form("D1") = "Fiction" Then
strSql = "select * from books where Fiction= -1"

Vimal

06-23-2003, 12:42 AM

Hi Kodo,

Thnks for ur help. I replaced the "ON" by "-1" and it worked out fine. In fact, it even works out when I put "1". Is there any difference with the "-" (minus)?

Now I am getting another problem though.

According to my codes, when I search for a particular category, the first, lets say 2 records should be displayed, and then when I click on 'Next', it should display me the next 2 and so on.

However, if for example I click on category "Fiction", I get the Record1 and Record2 of Fiction being displayed correctly, but when I click on 'Next', it gives me Record3 and Record4 of Autobiography, and when I click on Next again, it still gives me the next records in the category Autobiography. If I click on 'Previous', still records of category Autobiography is being displayed.

Whichever category I search for, when I click on 'Next', it gives me the next records of category Autobiography.

I feel its the SQL query in the last Else statement that is influencing this result.

If Request.form("D1") = "Fiction" Then
strSql = "select * from books where Fiction= -1"

ElseIf Request.form("D1") = "Romance" Then
strSql = "select * from books where Romance= -1"

Elseif Request.form("D1") = "Suspense" Then
strSql = "select * from books where Suspense= -1"

Else
strSql = "select * from books where Autobiography= -1"
End if

because if I change the condition into, lets say "Else strSql = "select * from books where Suspense=-1", then it will display me the next records of Suspense when I click on 'Next".

I cant figure the problem out. can u help me plz?

Thnks