p2p.wrox.com: Archives - asp

asp_databases thread: Paging - Problem with sql or the loop

Message #1 by "Darren Woollatt" <dwoollatt@h...> on Wed, 12 Dec 2001 23:49:50

In trying to do some recordset paging I seem to have a problem with my sql 

or the loop part here is the code can anyone help.



<% @ LANGUAGE="VBSCRIPT" %>

<% Response.Expires = 0 %>

<% 

Dim objConn

Dim objRst

Dim strObject

Dim Sql

Dim intPageCount

Dim intRecordCount

Dim intPage

Dim intRecord

Dim intStart

Dim intFinish

Dim rsFiles



If Request.QueryString("NAV") = "" Then

		intPage=1

Else

		intPage = Request.QueryString("NAV")

End If



Set objConn = Server.CreateObject("ADODB.Connection")



virtualDir="recipes.mdb"



objConn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb); DBQ=" 

& Server.MapPath(VirtualDir) & DBPath & ";"



objConn.Open



If Request.Form("search") <> "" Then

	strObject = Request.Form("search")

Else

	strObject = Request.Form("VALUE")

End If



Sql = "SELECT * FROM main WHERE main.[Recipe Name] = " & strObject



Set objRst = Server.CreateObject("ADODB.Recordset")



objRst.CursorLocation = 3

objRst.CursorLocation = 3

objRst.ActiveConnection = objConn



objRst.Open Sql





objRst.PageSize = 10

objRst.CacheSize = objRst.PageSize

intPageCount = objRst.PageCount

intRecordCount = objRst.RecordCount



If intRecordCount > 0 Then

	objRst.AbsolutePage = intPage

	intStart = objRst.AbsolutePosition

	If intPage = intPageCount Then

		intFinish = intRecordCount

	Else

		intFinish = intStart + (objRst.Pagesize - 1)

	End If

End If

%>



<html>

<body>

<h1>Search results</h1>

Your search for <%=strObject%> returned <%=intRecordCount%> records.

<% If intRecordCount > 0 Then 

%>

you are now viewing records <%=intStart%> through <%=intFinish%>.

<table border=0>

<tr><td>Name</td></tr>

<% For intRecord=1 to objRst.PageSize

if instr(1,ucase(objRst("Recipe Name")),strObject) then



	Response.Write "<tr><td>" & objRst("Recipe Name") & "</td></tr>"

	objRst.MoveNext

	If objRst.EOF Then Exit for

end if

Next



%>



<tr><td align=right> 

<% 

If intPage > 1 Then

%>

<a href="search.asp?NAV=<%=intPage - 1%>&VALUE=<%=strObject%>">Prev</a>

<% End If %>

<% 

If intPage < intPageCount Or intPage = 1 Then

%>

<a href="search.asp?NAV=<%=intPage + 1%>&VALUE=<%=strObject%>">Next</a>

<% End If %>

</td></tr>

</table>

<% End If %>

Jump to page 

<% If intFinish = 1 Then 



Response.Write "This is the only page" 

End If %>

<br><a href="form.htm">NEW SEARCH</a>

</body>

</html>

Message #2 by David Cameron <dcameron@i...> on Thu, 13 Dec 2001 10:54:00 +1100

This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C18368.455C9A7E

Content-Type: text/plain



What is the problem/error?



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: Darren Woollatt [mailto:dwoollatt@h...]

Sent: Thursday, 13 December 2001 9:50 AM

To: ASP Databases

Subject: [asp_databases] Paging - Problem with sql or the loop





In trying to do some recordset paging I seem to have a problem with my sql 

or the loop part here is the code can anyone help.



<% @ LANGUAGE="VBSCRIPT" %>

<% Response.Expires = 0 %>

<% 

Dim objConn

Dim objRst

Dim strObject

Dim Sql

Dim intPageCount

Dim intRecordCount

Dim intPage

Dim intRecord

Dim intStart

Dim intFinish

Dim rsFiles



If Request.QueryString("NAV") = "" Then

		intPage=1

Else

		intPage = Request.QueryString("NAV")

End If



Set objConn = Server.CreateObject("ADODB.Connection")



virtualDir="recipes.mdb"



objConn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb); DBQ=" 

& Server.MapPath(VirtualDir) & DBPath & ";"



objConn.Open



If Request.Form("search") <> "" Then

	strObject = Request.Form("search")

Else

	strObject = Request.Form("VALUE")

End If



Sql = "SELECT * FROM main WHERE main.[Recipe Name] = " & strObject



Set objRst = Server.CreateObject("ADODB.Recordset")



objRst.CursorLocation = 3

objRst.CursorLocation = 3

objRst.ActiveConnection = objConn



objRst.Open Sql





objRst.PageSize = 10

objRst.CacheSize = objRst.PageSize

intPageCount = objRst.PageCount

intRecordCount = objRst.RecordCount



If intRecordCount > 0 Then

	objRst.AbsolutePage = intPage

	intStart = objRst.AbsolutePosition

	If intPage = intPageCount Then

		intFinish = intRecordCount

	Else

		intFinish = intStart + (objRst.Pagesize - 1)

	End If

End If

%>



<html>

<body>

<h1>Search results</h1>

Your search for <%=strObject%> returned <%=intRecordCount%> records.

<% If intRecordCount > 0 Then 

%>

you are now viewing records <%=intStart%> through <%=intFinish%>.

<table border=0>

<tr><td>Name</td></tr>

<% For intRecord=1 to objRst.PageSize

if instr(1,ucase(objRst("Recipe Name")),strObject) then



	Response.Write "<tr><td>" & objRst("Recipe Name") & "</td></tr>"

	objRst.MoveNext

	If objRst.EOF Then Exit for

end if

Next



%>



<tr><td align=right> 

<% 

If intPage > 1 Then

%>

<a href="search.asp?NAV=<%=intPage - 1%>&VALUE=<%=strObject%>">Prev</a>

<% End If %>

<% 

If intPage < intPageCount Or intPage = 1 Then

%>

<a href="search.asp?NAV=<%=intPage + 1%>&VALUE=<%=strObject%>">Next</a>

<% End If %>

</td></tr>

</table>

<% End If %>

Jump to page 

<% If intFinish = 1 Then 



Response.Write "This is the only page" 

End If %>

<br><a href="form.htm">NEW SEARCH</a>

</body>

</html>




$subst('Email.Unsub').

Message #3 by "Darren Woollatt" <dwoollatt@h...> on Thu, 13 Dec 2001 00:10:22

The error is 

Microsoft OLE DB Provider for ODBC Drivers error '80040e10' 



[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. 



/asp/quallis/search.asp, line 44 



> This message is in MIME format. Since your mail reader does not 

understand

> this format, some or all of this message may not be legible.

> 

> ------_=_NextPart_001_01C18368.455C9A7E

> Content-Type: text/plain

> 

> What is the problem/error?

> 

> regards

> David Cameron

> nOw.b2b

> dcameron@i...

> 

> -----Original Message-----

> From: Darren Woollatt [mailto:dwoollatt@h...]

> Sent: Thursday, 13 December 2001 9:50 AM

> To: ASP Databases

> Subject: [asp_databases] Paging - Problem with sql or the loop

> 

> 

> In trying to do some recordset paging I seem to have a problem with my 

sql 

> or the loop part here is the code can anyone help.

> 

> <% @ LANGUAGE="VBSCRIPT" %>

> <% Response.Expires = 0 %>

> <% 

> Dim objConn

> Dim objRst

> Dim strObject

> Dim Sql

> Dim intPageCount

> Dim intRecordCount

> Dim intPage

> Dim intRecord

> Dim intStart

> Dim intFinish

> Dim rsFiles

> 

> If Request.QueryString("NAV") = "" Then

> 		intPage=1

> Else

> 		intPage = Request.QueryString("NAV")

> End If

> 

> Set objConn = Server.CreateObject("ADODB.Connection")

> 

> virtualDir="recipes.mdb"

> 

> objConn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb); 

DBQ=" 

> & Server.MapPath(VirtualDir) & DBPath & ";"

> 

> objConn.Open

> 

> If Request.Form("search") <> "" Then

> 	strObject = Request.Form("search")

> Else

> 	strObject = Request.Form("VALUE")

> End If

> 

> Sql = "SELECT * FROM main WHERE main.[Recipe Name] = " & strObject

> 

> Set objRst = Server.CreateObject("ADODB.Recordset")

> 

> objRst.CursorLocation = 3

> objRst.CursorLocation = 3

> objRst.ActiveConnection = objConn

> 

> objRst.Open Sql

> 

> 

> objRst.PageSize = 10

> objRst.CacheSize = objRst.PageSize

> intPageCount = objRst.PageCount

> intRecordCount = objRst.RecordCount

> 

> If intRecordCount > 0 Then

> 	objRst.AbsolutePage = intPage

> 	intStart = objRst.AbsolutePosition

> 	If intPage = intPageCount Then

> 		intFinish = intRecordCount

> 	Else

> 		intFinish = intStart + (objRst.Pagesize - 1)

> 	End If

> End If

> %>

> 

> <html>

> <body>

> <h1>Search results</h1>

> Your search for <%=strObject%> returned <%=intRecordCount%> records.

> <% If intRecordCount > 0 Then 

> %>

> you are now viewing records <%=intStart%> through <%=intFinish%>.

> <table border=0>

> <tr><td>Name</td></tr>

> <% For intRecord=1 to objRst.PageSize

> if instr(1,ucase(objRst("Recipe Name")),strObject) then

> 

> 	Response.Write "<tr><td>" & objRst("Recipe Name") & "</td></tr>"

> 	objRst.MoveNext

> 	If objRst.EOF Then Exit for

> end if

> Next

> 

> %>

> 

> <tr><td align=right> 

> <% 

> If intPage > 1 Then

> %>

> <a href="search.asp?NAV=<%=intPage - 1%>&VALUE=<%=strObject%>">Prev</a>

> <% End If %>

> <% 

> If intPage < intPageCount Or intPage = 1 Then

> %>

> <a href="search.asp?NAV=<%=intPage + 1%>&VALUE=<%=strObject%>">Next</a>

> <% End If %>

> </td></tr>

> </table>

> <% End If %>

> Jump to page 

> <% If intFinish = 1 Then 

> 

> Response.Write "This is the only page" 

> End If %>

> <br><a href="form.htm">NEW SEARCH</a>

> </body>

> </html>




> $subst('Email.Unsub').

> 

> ------_=_NextPart_001_01C18368.455C9A7E

> Content-Type: text/html

> Content-Transfer-Encoding: quoted-printable

> 

> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">

> <HTML>

> <HEAD>

> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; 

> charset=3DUS-ASCII">

> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version 

> 5.5.2650.12">

> <TITLE>RE: [asp_databases] Paging - Problem with sql or the 

> loop</TITLE>

> </HEAD>

> <BODY>

> 

> <P><FONT SIZE=3D2>What is the problem/error?</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>regards</FONT>

> <BR><FONT SIZE=3D2>David Cameron</FONT>

> <BR><FONT SIZE=3D2>nOw.b2b</FONT>

> <BR><FONT SIZE=3D2>dcameron@i...</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>-----Original Message-----</FONT>

> <BR><FONT SIZE=3D2>From: Darren Woollatt [<A 

> HREF=3D"mailto:dwoollatt@h...">mailto:dwoollatt@h...</A>]<

> /FONT>

> <BR><FONT SIZE=3D2>Sent: Thursday, 13 December 2001 9:50 AM</FONT>

> <BR><FONT SIZE=3D2>To: ASP Databases</FONT>

> <BR><FONT SIZE=3D2>Subject: [asp_databases] Paging - Problem with sql 

> or the loop</FONT>

> </P>

> <BR>

> 

> <P><FONT SIZE=3D2>In trying to do some recordset paging I seem to have 

> a problem with my sql </FONT>

> <BR><FONT SIZE=3D2>or the loop part here is the code can anyone 

> help.</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>&lt;% @ LANGUAGE=3D&quot;VBSCRIPT&quot; %&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;% Response.Expires =3D 0 %&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;% </FONT>

> <BR><FONT SIZE=3D2>Dim objConn</FONT>

> <BR><FONT SIZE=3D2>Dim objRst</FONT>

> <BR><FONT SIZE=3D2>Dim strObject</FONT>

> <BR><FONT SIZE=3D2>Dim Sql</FONT>

> <BR><FONT SIZE=3D2>Dim intPageCount</FONT>

> <BR><FONT SIZE=3D2>Dim intRecordCount</FONT>

> <BR><FONT SIZE=3D2>Dim intPage</FONT>

> <BR><FONT SIZE=3D2>Dim intRecord</FONT>

> <BR><FONT SIZE=3D2>Dim intStart</FONT>

> <BR><FONT SIZE=3D2>Dim intFinish</FONT>

> <BR><FONT SIZE=3D2>Dim rsFiles</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>If Request.QueryString(&quot;NAV&quot;) =3D 

> &quot;&quot; Then</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 

> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT 

> SIZE=3D2>intPage=3D1</FONT>

> <BR><FONT SIZE=3D2>Else</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 

> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>intPage =3D 

> Request.QueryString(&quot;NAV&quot;)</FONT>

> <BR><FONT SIZE=3D2>End If</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>Set objConn =3D 

> Server.CreateObject(&quot;ADODB.Connection&quot;)</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>virtualDir=3D&quot;recipes.mdb&quot;</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>objConn.ConnectionString =3D &quot;DRIVER=3DMicrosoft 

> Access Driver (*.mdb); DBQ=3D&quot; </FONT>

> <BR><FONT SIZE=3D2>&amp; Server.MapPath(VirtualDir) &amp; DBPath &amp; 

> &quot;;&quot;</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>objConn.Open</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>If Request.Form(&quot;search&quot;) &lt;&gt; 

> &quot;&quot; Then</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>strObject 

> =3D Request.Form(&quot;search&quot;)</FONT>

> <BR><FONT SIZE=3D2>Else</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>strObject 

> =3D Request.Form(&quot;VALUE&quot;)</FONT>

> <BR><FONT SIZE=3D2>End If</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>Sql =3D &quot;SELECT * FROM main WHERE main.[Recipe 

> Name] =3D &quot; &amp; strObject</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>Set objRst =3D 

> Server.CreateObject(&quot;ADODB.Recordset&quot;)</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>objRst.CursorLocation =3D 3</FONT>

> <BR><FONT SIZE=3D2>objRst.CursorLocation =3D 3</FONT>

> <BR><FONT SIZE=3D2>objRst.ActiveConnection =3D objConn</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>objRst.Open Sql</FONT>

> </P>

> <BR>

> 

> <P><FONT SIZE=3D2>objRst.PageSize =3D 10</FONT>

> <BR><FONT SIZE=3D2>objRst.CacheSize =3D objRst.PageSize</FONT>

> <BR><FONT SIZE=3D2>intPageCount =3D objRst.PageCount</FONT>

> <BR><FONT SIZE=3D2>intRecordCount =3D objRst.RecordCount</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>If intRecordCount &gt; 0 Then</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT 

> SIZE=3D2>objRst.AbsolutePage =3D intPage</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>intStart 

> =3D objRst.AbsolutePosition</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>If 

> intPage =3D intPageCount Then</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 

> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>intFinish =3D 

> intRecordCount</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT 

> SIZE=3D2>Else</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 

> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>intFinish =3D 

> intStart + (objRst.Pagesize - 1)</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>End 

> If</FONT>

> <BR><FONT SIZE=3D2>End If</FONT>

> <BR><FONT SIZE=3D2>%&gt;</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>&lt;html&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;body&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;h1&gt;Search results&lt;/h1&gt;</FONT>

> <BR><FONT SIZE=3D2>Your search for &lt;%=3DstrObject%&gt; returned 

> &lt;%=3DintRecordCount%&gt; records.</FONT>

> <BR><FONT SIZE=3D2>&lt;% If intRecordCount &gt; 0 Then </FONT>

> <BR><FONT SIZE=3D2>%&gt;</FONT>

> <BR><FONT SIZE=3D2>you are now viewing records &lt;%=3DintStart%&gt; 

> through &lt;%=3DintFinish%&gt;.</FONT>

> <BR><FONT SIZE=3D2>&lt;table border=3D0&gt;</FONT>

> <BR><FONT 

> SIZE=3D2>&lt;tr&gt;&lt;td&gt;Name&lt;/td&gt;&lt;/tr&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;% For intRecord=3D1 to objRst.PageSize</FONT>

> <BR><FONT SIZE=3D2>if instr(1,ucase(objRst(&quot;Recipe 

> Name&quot;)),strObject) then</FONT>

> </P>

> 

> <P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT 

> SIZE=3D2>Response.Write &quot;&lt;tr&gt;&lt;td&gt;&quot; &amp; 

> objRst(&quot;Recipe Name&quot;) &amp; 

> &quot;&lt;/td&gt;&lt;/tr&gt;&quot;</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT 

> SIZE=3D2>objRst.MoveNext</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>If 

> objRst.EOF Then Exit for</FONT>

> <BR><FONT SIZE=3D2>end if</FONT>

> <BR><FONT SIZE=3D2>Next</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>%&gt;</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>&lt;tr&gt;&lt;td align=3Dright&gt; </FONT>

> <BR><FONT SIZE=3D2>&lt;% </FONT>

> <BR><FONT SIZE=3D2>If intPage &gt; 1 Then</FONT>

> <BR><FONT SIZE=3D2>%&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;a href=3D&quot;search.asp?NAV=3D&lt;%=3DintPage 

> - 

> 1%&gt;&amp;VALUE=3D&lt;%=3DstrObject%&gt;&quot;&gt;Prev&lt;/a&gt;</FONT>

> 

> <BR><FONT SIZE=3D2>&lt;% End If %&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;% </FONT>

> <BR><FONT SIZE=3D2>If intPage &lt; intPageCount Or intPage =3D 1 

> Then</FONT>

> <BR><FONT SIZE=3D2>%&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;a href=3D&quot;search.asp?NAV=3D&lt;%=3DintPage 

> + 

> 1%&gt;&amp;VALUE=3D&lt;%=3DstrObject%&gt;&quot;&gt;Next&lt;/a&gt;</FONT>

> 

> <BR><FONT SIZE=3D2>&lt;% End If %&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;/td&gt;&lt;/tr&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;/table&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;% End If %&gt;</FONT>

> <BR><FONT SIZE=3D2>Jump to page </FONT>

> <BR><FONT SIZE=3D2>&lt;% If intFinish =3D 1 Then </FONT>

> </P>

> 

> <P><FONT SIZE=3D2>Response.Write &quot;This is the only page&quot; 

> </FONT>

> <BR><FONT SIZE=3D2>End If %&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;br&gt;&lt;a href=3D&quot;form.htm&quot;&gt;NEW 

> SEARCH&lt;/a&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;/body&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;/html&gt;</FONT>

> <BR><FONT SIZE=3D2>---</FONT>

> <BR><FONT SIZE=3D2>Change your mail options at <A 

> HREF=3D"http://p2p.wrox.com/manager.asp" 

> TARGET=3D"_blank">http://p2p.wrox.com/manager.asp</A> or </FONT>

> <BR><FONT SIZE=3D2>

> $subst('Email.Unsub').</FONT>

> </P>

> 

> </BODY>

> </HTML>

> ------_=_NextPart_001_01C18368.455C9A7E--

Message #4 by David Cameron <dcameron@i...> on Thu, 13 Dec 2001 11:50:45 +1100

This message is in MIME format. Since your mail reader does not understand

this format, some or all of this message may not be legible.



------_=_NextPart_001_01C18370.34B4E732

Content-Type: text/plain



Add the following code just after you set the SQL string:



Response.Write Sql

Response.End



The error is likely to be that strObject is an empty string or that it holds

a value that is causing an error.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: Darren Woollatt [mailto:dwoollatt@h...]

Sent: Thursday, 13 December 2001 10:10 AM

To: ASP Databases

Subject: [asp_databases] RE: Paging - Problem with sql or the loop





The error is 

Microsoft OLE DB Provider for ODBC Drivers error '80040e10' 



[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. 



/asp/quallis/search.asp, line 44 



> This message is in MIME format. Since your mail reader does not 

understand

> this format, some or all of this message may not be legible.

> 

> ------_=_NextPart_001_01C18368.455C9A7E

> Content-Type: text/plain

> 

> What is the problem/error?

> 

> regards

> David Cameron

> nOw.b2b

> dcameron@i...

> 

> -----Original Message-----

> From: Darren Woollatt [mailto:dwoollatt@h...]

> Sent: Thursday, 13 December 2001 9:50 AM

> To: ASP Databases

> Subject: [asp_databases] Paging - Problem with sql or the loop

> 

> 

> In trying to do some recordset paging I seem to have a problem with my 

sql 

> or the loop part here is the code can anyone help.

> 

> <% @ LANGUAGE="VBSCRIPT" %>

> <% Response.Expires = 0 %>

> <% 

> Dim objConn

> Dim objRst

> Dim strObject

> Dim Sql

> Dim intPageCount

> Dim intRecordCount

> Dim intPage

> Dim intRecord

> Dim intStart

> Dim intFinish

> Dim rsFiles

> 

> If Request.QueryString("NAV") = "" Then

> 		intPage=1

> Else

> 		intPage = Request.QueryString("NAV")

> End If

> 

> Set objConn = Server.CreateObject("ADODB.Connection")

> 

> virtualDir="recipes.mdb"

> 

> objConn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb); 

DBQ=" 

> & Server.MapPath(VirtualDir) & DBPath & ";"

> 

> objConn.Open

> 

> If Request.Form("search") <> "" Then

> 	strObject = Request.Form("search")

> Else

> 	strObject = Request.Form("VALUE")

> End If

> 

> Sql = "SELECT * FROM main WHERE main.[Recipe Name] = " & strObject

> 

> Set objRst = Server.CreateObject("ADODB.Recordset")

> 

> objRst.CursorLocation = 3

> objRst.CursorLocation = 3

> objRst.ActiveConnection = objConn

> 

> objRst.Open Sql

> 

> 

> objRst.PageSize = 10

> objRst.CacheSize = objRst.PageSize

> intPageCount = objRst.PageCount

> intRecordCount = objRst.RecordCount

> 

> If intRecordCount > 0 Then

> 	objRst.AbsolutePage = intPage

> 	intStart = objRst.AbsolutePosition

> 	If intPage = intPageCount Then

> 		intFinish = intRecordCount

> 	Else

> 		intFinish = intStart + (objRst.Pagesize - 1)

> 	End If

> End If

> %>

> 

> <html>

> <body>

> <h1>Search results</h1>

> Your search for <%=strObject%> returned <%=intRecordCount%> records.

> <% If intRecordCount > 0 Then 

> %>

> you are now viewing records <%=intStart%> through <%=intFinish%>.

> <table border=0>

> <tr><td>Name</td></tr>

> <% For intRecord=1 to objRst.PageSize

> if instr(1,ucase(objRst("Recipe Name")),strObject) then

> 

> 	Response.Write "<tr><td>" & objRst("Recipe Name") & "</td></tr>"

> 	objRst.MoveNext

> 	If objRst.EOF Then Exit for

> end if

> Next

> 

> %>

> 

> <tr><td align=right> 

> <% 

> If intPage > 1 Then

> %>

> <a href="search.asp?NAV=<%=intPage - 1%>&VALUE=<%=strObject%>">Prev</a>

> <% End If %>

> <% 

> If intPage < intPageCount Or intPage = 1 Then

> %>

> <a href="search.asp?NAV=<%=intPage + 1%>&VALUE=<%=strObject%>">Next</a>

> <% End If %>

> </td></tr>

> </table>

> <% End If %>

> Jump to page 

> <% If intFinish = 1 Then 

> 

> Response.Write "This is the only page" 

> End If %>

> <br><a href="form.htm">NEW SEARCH</a>

> 

---<BR>

Change your mail options at http://p2p.wrox.com/manager.asp or <BR>


$subst('Email.Unsub').

</BODY>

> </html>




> $subst('Email.Unsub').

> 

> ------_=_NextPart_001_01C18368.455C9A7E

> Content-Type: text/html

> Content-Transfer-Encoding: quoted-printable

> 

> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">

> <HTML>

> <HEAD>

> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; 

> charset=3DUS-ASCII">

> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version 

> 5.5.2650.12">

> <TITLE>RE: [asp_databases] Paging - Problem with sql or the 

> loop</TITLE>

> </HEAD>

> <BODY>

> 

> <P><FONT SIZE=3D2>What is the problem/error?</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>regards</FONT>

> <BR><FONT SIZE=3D2>David Cameron</FONT>

> <BR><FONT SIZE=3D2>nOw.b2b</FONT>

> <BR><FONT SIZE=3D2>dcameron@i...</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>-----Original Message-----</FONT>

> <BR><FONT SIZE=3D2>From: Darren Woollatt [<A 

> HREF=3D"mailto:dwoollatt@h...">mailto:dwoollatt@h...</A>]<

> /FONT>

> <BR><FONT SIZE=3D2>Sent: Thursday, 13 December 2001 9:50 AM</FONT>

> <BR><FONT SIZE=3D2>To: ASP Databases</FONT>

> <BR><FONT SIZE=3D2>Subject: [asp_databases] Paging - Problem with sql 

> or the loop</FONT>

> </P>

> <BR>

> 

> <P><FONT SIZE=3D2>In trying to do some recordset paging I seem to have 

> a problem with my sql </FONT>

> <BR><FONT SIZE=3D2>or the loop part here is the code can anyone 

> help.</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>&lt;% @ LANGUAGE=3D&quot;VBSCRIPT&quot; %&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;% Response.Expires =3D 0 %&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;% </FONT>

> <BR><FONT SIZE=3D2>Dim objConn</FONT>

> <BR><FONT SIZE=3D2>Dim objRst</FONT>

> <BR><FONT SIZE=3D2>Dim strObject</FONT>

> <BR><FONT SIZE=3D2>Dim Sql</FONT>

> <BR><FONT SIZE=3D2>Dim intPageCount</FONT>

> <BR><FONT SIZE=3D2>Dim intRecordCount</FONT>

> <BR><FONT SIZE=3D2>Dim intPage</FONT>

> <BR><FONT SIZE=3D2>Dim intRecord</FONT>

> <BR><FONT SIZE=3D2>Dim intStart</FONT>

> <BR><FONT SIZE=3D2>Dim intFinish</FONT>

> <BR><FONT SIZE=3D2>Dim rsFiles</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>If Request.QueryString(&quot;NAV&quot;) =3D 

> &quot;&quot; Then</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 

> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT 

> SIZE=3D2>intPage=3D1</FONT>

> <BR><FONT SIZE=3D2>Else</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 

> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>intPage =3D 

> Request.QueryString(&quot;NAV&quot;)</FONT>

> <BR><FONT SIZE=3D2>End If</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>Set objConn =3D 

> Server.CreateObject(&quot;ADODB.Connection&quot;)</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>virtualDir=3D&quot;recipes.mdb&quot;</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>objConn.ConnectionString =3D &quot;DRIVER=3DMicrosoft 

> Access Driver (*.mdb); DBQ=3D&quot; </FONT>

> <BR><FONT SIZE=3D2>&amp; Server.MapPath(VirtualDir) &amp; DBPath &amp; 

> &quot;;&quot;</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>objConn.Open</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>If Request.Form(&quot;search&quot;) &lt;&gt; 

> &quot;&quot; Then</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>strObject 

> =3D Request.Form(&quot;search&quot;)</FONT>

> <BR><FONT SIZE=3D2>Else</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>strObject 

> =3D Request.Form(&quot;VALUE&quot;)</FONT>

> <BR><FONT SIZE=3D2>End If</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>Sql =3D &quot;SELECT * FROM main WHERE main.[Recipe 

> Name] =3D &quot; &amp; strObject</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>Set objRst =3D 

> Server.CreateObject(&quot;ADODB.Recordset&quot;)</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>objRst.CursorLocation =3D 3</FONT>

> <BR><FONT SIZE=3D2>objRst.CursorLocation =3D 3</FONT>

> <BR><FONT SIZE=3D2>objRst.ActiveConnection =3D objConn</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>objRst.Open Sql</FONT>

> </P>

> <BR>

> 

> <P><FONT SIZE=3D2>objRst.PageSize =3D 10</FONT>

> <BR><FONT SIZE=3D2>objRst.CacheSize =3D objRst.PageSize</FONT>

> <BR><FONT SIZE=3D2>intPageCount =3D objRst.PageCount</FONT>

> <BR><FONT SIZE=3D2>intRecordCount =3D objRst.RecordCount</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>If intRecordCount &gt; 0 Then</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT 

> SIZE=3D2>objRst.AbsolutePage =3D intPage</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>intStart 

> =3D objRst.AbsolutePosition</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>If 

> intPage =3D intPageCount Then</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 

> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>intFinish =3D 

> intRecordCount</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT 

> SIZE=3D2>Else</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 

> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>intFinish =3D 

> intStart + (objRst.Pagesize - 1)</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>End 

> If</FONT>

> <BR><FONT SIZE=3D2>End If</FONT>

> <BR><FONT SIZE=3D2>%&gt;</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>&lt;html&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;body&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;h1&gt;Search results&lt;/h1&gt;</FONT>

> <BR><FONT SIZE=3D2>Your search for &lt;%=3DstrObject%&gt; returned 

> &lt;%=3DintRecordCount%&gt; records.</FONT>

> <BR><FONT SIZE=3D2>&lt;% If intRecordCount &gt; 0 Then </FONT>

> <BR><FONT SIZE=3D2>%&gt;</FONT>

> <BR><FONT SIZE=3D2>you are now viewing records &lt;%=3DintStart%&gt; 

> through &lt;%=3DintFinish%&gt;.</FONT>

> <BR><FONT SIZE=3D2>&lt;table border=3D0&gt;</FONT>

> <BR><FONT 

> SIZE=3D2>&lt;tr&gt;&lt;td&gt;Name&lt;/td&gt;&lt;/tr&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;% For intRecord=3D1 to objRst.PageSize</FONT>

> <BR><FONT SIZE=3D2>if instr(1,ucase(objRst(&quot;Recipe 

> Name&quot;)),strObject) then</FONT>

> </P>

> 

> <P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT 

> SIZE=3D2>Response.Write &quot;&lt;tr&gt;&lt;td&gt;&quot; &amp; 

> objRst(&quot;Recipe Name&quot;) &amp; 

> &quot;&lt;/td&gt;&lt;/tr&gt;&quot;</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT 

> SIZE=3D2>objRst.MoveNext</FONT>

> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2>If 

> objRst.EOF Then Exit for</FONT>

> <BR><FONT SIZE=3D2>end if</FONT>

> <BR><FONT SIZE=3D2>Next</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>%&gt;</FONT>

> </P>

> 

> <P><FONT SIZE=3D2>&lt;tr&gt;&lt;td align=3Dright&gt; </FONT>

> <BR><FONT SIZE=3D2>&lt;% </FONT>

> <BR><FONT SIZE=3D2>If intPage &gt; 1 Then</FONT>

> <BR><FONT SIZE=3D2>%&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;a href=3D&quot;search.asp?NAV=3D&lt;%=3DintPage 

> - 

> 1%&gt;&amp;VALUE=3D&lt;%=3DstrObject%&gt;&quot;&gt;Prev&lt;/a&gt;</FONT>

> 

> <BR><FONT SIZE=3D2>&lt;% End If %&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;% </FONT>

> <BR><FONT SIZE=3D2>If intPage &lt; intPageCount Or intPage =3D 1 

> Then</FONT>

> <BR><FONT SIZE=3D2>%&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;a href=3D&quot;search.asp?NAV=3D&lt;%=3DintPage 

> + 

> 1%&gt;&amp;VALUE=3D&lt;%=3DstrObject%&gt;&quot;&gt;Next&lt;/a&gt;</FONT>

> 

> <BR><FONT SIZE=3D2>&lt;% End If %&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;/td&gt;&lt;/tr&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;/table&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;% End If %&gt;</FONT>

> <BR><FONT SIZE=3D2>Jump to page </FONT>

> <BR><FONT SIZE=3D2>&lt;% If intFinish =3D 1 Then </FONT>

> </P>

> 

> <P><FONT SIZE=3D2>Response.Write &quot;This is the only page&quot; 

> </FONT>

> <BR><FONT SIZE=3D2>End If %&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;br&gt;&lt;a href=3D&quot;form.htm&quot;&gt;NEW 

> SEARCH&lt;/a&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;/body&gt;</FONT>

> <BR><FONT SIZE=3D2>&lt;/html&gt;</FONT>

> <BR><FONT SIZE=3D2>---</FONT>

> <BR><FONT SIZE=3D2>Change your mail options at <A 

> HREF=3D"http://p2p.wrox.com/manager.asp" 

> TARGET=3D"_blank">http://p2p.wrox.com/manager.asp</A> or </FONT>

> <BR><FONT SIZE=3D2>

> $subst('Email.Unsub').</FONT>

> </P>

> 

> 

---<BR>

Change your mail options at http://p2p.wrox.com/manager.asp or <BR>


$subst('Email.Unsub').

</BODY>

> </HTML>

> ------_=_NextPart_001_01C18368.455C9A7E--

Message #5 by "Ken Schaefer" <ken@a...> on Thu, 13 Dec 2001 14:13:29 +1100

Try: www.adopenstatic.com/faq/80040e10.asp



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Darren Woollatt" <dwoollatt@h...>

Subject: [asp_databases] RE: Paging - Problem with sql or the loop





: The error is

: Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

:

: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

:

: /asp/quallis/search.asp, line 44



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Return to Index

P2P Archives