|
 |
asp_databases thread: Database paging
Message #1 by "Frode Strømme" <fstroemm@o...> on Tue, 29 May 2001 21:55:15
|
|
Searching through www.aspin.com for articles on paging I found one at
http://www.smashco.com/pages.asp
However I can't get it to work correctly with my Request.Form
Code part I want included:
Dim searchStr
searchStr = Replace(Request.Form("strSearch"), "'", "''")
' Generate SQL string based on user input
strSQL ="SELECT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
[Skuespiller]" & _
" FROM khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON
[khitskue].[SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].
[FilmID]" & _
" WHERE " & Request("choice") & " LIKE '%" & Request
("strSearch") & "%' ORDER BY " & Request("choice")
Set RS = MyConn.Execute(SQL)
If searchStr <> "" Then
If RS.BOF AND RS.EOF Then
Response.Write "<center> No Records found.</center>"
Else
Response.Write ...
Error msg I get:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'LIKE '%%''.
/wwwroot/index.asp, line 35
----------
Working code (not including the support.inc file)
<%
Dim rst, strSQL
set rst = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM khitfilm ORDER BY khitfilm.FilmID"
rst.Open strSQL, connect, 3, 1, 1
Dim IntPageSize, PageIndex, TotalPages, TotalRecords
IntPageSize = 5
PageIndex = Request("PageIndex")
'If pageIndex is empty, set it to one.
if PageIndex = "" then PageIndex = 1
rst.PageSize = intPageSize ' Set the number of records per page
rst.AbsolutePage = PageIndex 'Define what page number on which the current
record resides.
TotalPages = rst.PageCount
dim intPrev, intNext
intPrev = PageIndex - 1
intNext = PageIndex + 1 'same as rst.AbsolutePage
'Build table header
%>
<table border="1" cellpadding="5" cellspacing="0">
<tr bgcolor="#c0c0c0">
<td>ID</td>
<td>Film</td>
</tr>
<%
Dim Count 'We'll use this to limit the number of records displayed on a
page
Count = 1
do while NOT rst.EOF AND Count <= IntPageSize
%>
<tr>
<td><%=rst("FilmID")%></td>
<td><%=rst("Tittel")%></td>
</tr>
<%
count = count + 1
rst.MoveNext
loop
%>
<tr>
<td align="center" colspan="8" bgcolor="#c0c0c0">
<%
response.write BuildNav (intPrev,IntNext,TotalPages)
rst.close
%>
Message #2 by "Ken Schaefer" <ken@a...> on Wed, 30 May 2001 11:36:05 +1000
|
|
Do this
Response.Write(Request("strSearch"))
and make sure that there is something *in* Request("strSearch") first.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Frode StrXmme" <fstroemm@o...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, May 29, 2001 9:55 PM
Subject: [asp_databases] Database paging
: Searching through www.aspin.com for articles on paging I found one at
: http://www.smashco.com/pages.asp
:
: However I can't get it to work correctly with my Request.Form
:
: Code part I want included:
:
: Dim searchStr
:
: searchStr = Replace(Request.Form("strSearch"), "'", "''")
:
: ' Generate SQL string based on user input
:
: strSQL ="SELECT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
: [Skuespiller]" & _
: " FROM khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON
: [khitskue].[SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].
: [FilmID]" & _
: " WHERE " & Request("choice") & " LIKE '%" & Request
: ("strSearch") & "%' ORDER BY " & Request("choice")
:
: Set RS = MyConn.Execute(SQL)
:
: If searchStr <> "" Then
: If RS.BOF AND RS.EOF Then
: Response.Write "<center> No Records found.</center>"
: Else
: Response.Write ...
:
: Error msg I get:
:
: Error Type:
: Microsoft JET Database Engine (0x80040E14)
: Syntax error (missing operator) in query expression 'LIKE '%%''.
: /wwwroot/index.asp, line 35
:
Message #3 by "Tom Hodder \(Global Gold Network Ltd\)" <t.hodder@g...> on Wed, 30 May 2001 01:02:47 +0100
|
|
By the look of it, the form input field name does not match the
Request("choice") in the SQL. And you are therefore passing no value to that
part of the SQL string,
-----Original Message-----
From: Frode Strømme [mailto:fstroemm@o...]
Sent: 29 May 2001 21:55
To: ASP Databases
Subject: [asp_databases] Database paging
Searching through www.aspin.com for articles on paging I found one at
http://www.smashco.com/pages.asp
However I can't get it to work correctly with my Request.Form
Code part I want included:
Dim searchStr
searchStr = Replace(Request.Form("strSearch"), "'", "''")
' Generate SQL string based on user input
strSQL ="SELECT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
[Skuespiller]" & _
" FROM khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON
[khitskue].[SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].
[FilmID]" & _
" WHERE " & Request("choice") & " LIKE '%" & Request
("strSearch") & "%' ORDER BY " & Request("choice")
Set RS = MyConn.Execute(SQL)
If searchStr <> "" Then
If RS.BOF AND RS.EOF Then
Response.Write "<center> No Records found.</center>"
Else
Response.Write ...
Error msg I get:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'LIKE '%%''.
/wwwroot/index.asp, line 35
----------
Working code (not including the support.inc file)
<%
Dim rst, strSQL
set rst = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM khitfilm ORDER BY khitfilm.FilmID"
rst.Open strSQL, connect, 3, 1, 1
Dim IntPageSize, PageIndex, TotalPages, TotalRecords
IntPageSize = 5
PageIndex = Request("PageIndex")
'If pageIndex is empty, set it to one.
if PageIndex = "" then PageIndex = 1
rst.PageSize = intPageSize ' Set the number of records per page
rst.AbsolutePage = PageIndex 'Define what page number on which the current
record resides.
TotalPages = rst.PageCount
dim intPrev, intNext
intPrev = PageIndex - 1
intNext = PageIndex + 1 'same as rst.AbsolutePage
'Build table header
%>
<table border="1" cellpadding="5" cellspacing="0">
<tr bgcolor="#c0c0c0">
<td>ID</td>
<td>Film</td>
</tr>
<%
Dim Count 'We'll use this to limit the number of records displayed on a
page
Count = 1
do while NOT rst.EOF AND Count <= IntPageSize
%>
<tr>
<td><%=rst("FilmID")%></td>
<td><%=rst("Tittel")%></td>
</tr>
<%
count = count + 1
rst.MoveNext
loop
%>
<tr>
<td align="center" colspan="8" bgcolor="#c0c0c0">
<%
response.write BuildNav (intPrev,IntNext,TotalPages)
rst.close
%>
Message #4 by "Frode Strømme" <fstroemm@o...> on Wed, 30 May 2001 03:44:53
|
|
Not sure if I'm too dumb to get it, or you misunderstood my question.
Can't see how a Response.Write could help me with this one.
I show the first page as normal but when I click on next page/any other
page number, I get a error msg:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'LIKE '%%''.
/wwwroot/default.asp, line 19
The paging works just fine if I remove the WHERE line in my SQL query.
But then I don't get the user input from my form.
My code:
<%option explicit%>
<!-- #include file="support.inc" -->
<html>
<body>
<%
Dim rst, strSQL
set rst = Server.CreateObject("ADODB.Recordset")
Dim searchStr
searchStr = Replace(Request.Form("strSearch"), "'", "''")
strSQL ="SELECT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
[Skuespiller]" & _
" FROM khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON
[khitskue].[SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].
[FilmID]" & _
" WHERE " & Request("choice") & " LIKE '%" & Request
("strSearch") & "%' ORDER BY " & Request("choice")
rst.Open strSQL, connect, 3, 1, 1
Dim IntPageSize, PageIndex, TotalPages, TotalRecords
IntPageSize = 5
PageIndex = Request("PageIndex")
'PageIndex = Request("strSearch")
'If pageIndex is empty, set it to one.
if PageIndex = "" then PageIndex = 1
rst.PageSize = intPageSize ' Set the number of records per page
rst.AbsolutePage = PageIndex 'Define what page number on which the current
record resides.
TotalPages = rst.PageCount
dim intPrev, intNext
intPrev = PageIndex - 1
intNext = PageIndex + 1 'same as rst.AbsolutePage
'Build table header
%>
<table border="1" cellpadding="5" cellspacing="0">
<tr bgcolor="#c0c0c0"><td>ID</td><td>Film</td></tr>
<%
Dim Count 'We'll use this to limit the number of records displayed on a
page
Count = 1
do while NOT rst.EOF AND Count <= IntPageSize
%>
<tr>
<td><%=rst("FilmID")%></td>
<td><%=rst("Tittel")%></td>
</tr>
<%
count = count + 1
rst.MoveNext
loop
%>
<tr>
<td align="center" colspan="8" bgcolor="#c0c0c0">
<%
response.write BuildNav (intPrev,IntNext,TotalPages)
rst.close
%>
</td></tr>
</table>
</body>
</html>
Support.inc:
<%
Function BuildNav (intPrev,IntNext,TotalPages)
Dim counter
BuildNav = "<font face=verdana size=1><b>"
' If the previous page id is not 0, then let's add the 'prev' link.
if intPrev <> 0 then
BuildNav = BuildNav & "<a href=kpage.asp?PageIndex="
BuildNav = BuildNav & intPrev & "><< previous</a>"
BuildNav = BuildNav & NBSP(5)
end if
'This section displays the list of page numbers as links,
separated with the pipe ( | )
counter = 1
BuildNav = BuildNav & "jump to page: "
do while counter <= TotalPages
' If the current page is the same as the counter, then
write the page number with no hyperlink
if cint(counter) = cint(PageIndex) then
BuildNav = BuildNav & counter
Else
' Else, let's write the page number as a hyperlink
to that page
BuildNav = BuildNav & "<a href=kpage.asp?
PageIndex="
BuildNav = BuildNav & counter & ">" & Counter
& "</a>"
End if
' As long as we're not at the last page number in this
loop, let's add a pipe to the string.
if cInt(counter) <> TotalPages then
BuildNav = BuildNav & " | "
end if
counter = counter + 1
Loop
if (rst.AbsolutePage <> -3) then
BuildNav = BuildNav & NBSP(5)
BuildNav = BuildNav & "<a href=kpage.asp?PageIndex="
BuildNav = BuildNav & intNext & ">next >></a>"
end if
BuildNav = BuildNav & "</b></font>"
end function
%>
<%
function Connect()
dim strCon
set Connect=Server.CreateObject("ADODB.connection")
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\datastores\movies.mdb"
Connect.ConnectionString = strCon
Connect.open
end function
%>
<%
function NBSP(count)
dim i
for i = 1 to count
NBSP = NBSP + " "
next
end function
%>
Thanks.
- Frode Strømme.
> Do this
>
> Response.Write(Request("strSearch"))
>
> and make sure that there is something *in* Request("strSearch") first.
>
> Cheers
> Ken
Message #5 by "Ken Schaefer" <ken@a...> on Wed, 30 May 2001 22:48:18 +1000
|
|
Frode,
You might not think that a Response.Write() will help you - but I think it
will. Indulge me please. Here's why. Please do:
<% Response.Write(Request("strSearch"))%>
on each of the pages (ie the subsequent loads) where you are getting the
error. As I've pointed out, and Tom has pointed out, the problem seems to be
THAT THERE IS NOTHING IN Request("strSearch"), and so when you concantenate
your SQL string together, you get:
LIKE %%
Why? Because there is nothing between the %% because there is nothing in
Request("strSearch").
That's why we are asking you to Response.Write("strSearch"). If there *is*
something in Request("strSearch"), then you are not writing it into your SQL
statement properly.
My guess is that you are getting it properly on the first page of results,
but then you are failing to pass it properly to the second page of results,
and when you execute the query, it bombs.
Maybe I'm too dumb to understand the problem...but it seems to me, where I'm
sitting, that this is the problem...
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Frode StrXmme" <fstroemm@o...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, May 30, 2001 3:44 AM
Subject: [asp_databases] Re: Database paging
: Not sure if I'm too dumb to get it, or you misunderstood my question.
: Can't see how a Response.Write could help me with this one.
:
: I show the first page as normal but when I click on next page/any other
: page number, I get a error msg:
:
: Error Type:
: Microsoft JET Database Engine (0x80040E14)
: Syntax error (missing operator) in query expression 'LIKE '%%''.
: /wwwroot/default.asp, line 19
:
: The paging works just fine if I remove the WHERE line in my SQL query.
: But then I don't get the user input from my form.
:
:
Message #6 by "Tomm Matthis" <matthis@b...> on Wed, 30 May 2001 08:57:57 -0400
|
|
Frode... it appears that your Request("choice") is not being passed into
the second page..
hence the 'Like '%%' statement.... which SQL doesn't like.
-- Tomm
> -----Original Message-----
> From: Frode Str=F8mme [mailto:fstroemm@o...]
> Sent: Wednesday, May 30, 2001 3:45 AM
> To: ASP Databases
> Subject: [asp_databases] Re: Database paging
>
>
> Not sure if I'm too dumb to get it, or you misunderstood my question.
> Can't see how a Response.Write could help me with this one.
>
> I show the first page as normal but when I click on next page/any
other
> page number, I get a error msg:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error (missing operator) in query expression 'LIKE '%%''.
> /wwwroot/default.asp, line 19
>
> The paging works just fine if I remove the WHERE line in my SQL query.
> But then I don't get the user input from my form.
>
>
> My code:
>
> <%option explicit%>
> <!-- #include file=3D"support.inc" -->
> <html>
> <body>
>
> <%
>
> Dim rst, strSQL
> set rst =3D Server.CreateObject("ADODB.Recordset")
>
> Dim searchStr
>
> searchStr =3D Replace(Request.Form("strSearch"), "'", "''")
>
> strSQL =3D"SELECT [khitfilm].[FilmID], [khitfilm].[Tittel],
[khitskue].
> [Skuespiller]" & _
> " FROM khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON
> [khitskue].[SkueID] =3D[khitfs].[SkueID]) ON [khitfilm].[FilmID]
=3D[khitfs].
> [FilmID]" & _
> " WHERE " & Request("choice") & " LIKE '%" & Request
> ("strSearch") & "%' ORDER BY " & Request("choice")
>
> rst.Open strSQL, connect, 3, 1, 1
>
> Dim IntPageSize, PageIndex, TotalPages, TotalRecords
>
> IntPageSize =3D 5
>
> PageIndex =3D Request("PageIndex")
> 'PageIndex =3D Request("strSearch")
>
>
> 'If pageIndex is empty, set it to one.
> if PageIndex =3D "" then PageIndex =3D 1=09
>
> rst.PageSize =3D intPageSize ' Set the number of records per page
> rst.AbsolutePage =3D PageIndex 'Define what page number on which
> the current
> record resides.
>
> TotalPages =3D rst.PageCount
>
> dim intPrev, intNext
> intPrev =3D PageIndex - 1
> intNext =3D PageIndex + 1 'same as rst.AbsolutePage
>
> 'Build table header
> %>
> <table border=3D"1" cellpadding=3D"5" cellspacing=3D"0">
> <tr bgcolor=3D"#c0c0c0"><td>ID</td><td>Film</td></tr>
>
> <%
> Dim Count 'We'll use this to limit the number of records displayed on
a
> page
> Count =3D 1
> do while NOT rst.EOF AND Count <=3D IntPageSize
> %>
> <tr>
> <td><%=3Drst("FilmID")%></td>
> <td><%=3Drst("Tittel")%></td>=09
> </tr>
> <%
> count =3D count + 1
> rst.MoveNext
> loop
> %>
> <tr>
> <td align=3D"center" colspan=3D"8" bgcolor=3D"#c0c0c0">
> <%
> response.write BuildNav (intPrev,IntNext,TotalPages)
> rst.close
> %>
> </td></tr>=09
> </table>
> </body>
> </html>
>
>
> Support.inc:
>
> <%
> Function BuildNav (intPrev,IntNext,TotalPages)
> Dim counter
> BuildNav =3D "<font face=3Dverdana size=3D1><b>"
> =09
> ' If the previous page id is not 0, then let's add the 'prev' link.
> if intPrev <> 0 then
> BuildNav =3D BuildNav & "<a href=3Dkpage.asp?PageIndex=3D"
> BuildNav =3D BuildNav & intPrev & "><< previous</a>"
> BuildNav =3D BuildNav & NBSP(5)
> end if
>
> 'This section displays the list of page numbers as links,
> separated with the pipe ( | )
> counter =3D 1
> BuildNav =3D BuildNav & "jump to page: "
> do while counter <=3D TotalPages
> ' If the current page is the same as the counter, then
> write the page number with no hyperlink
> if cint(counter) =3D cint(PageIndex) then
> BuildNav =3D BuildNav & counter
> Else
> ' Else, let's write the page number as a hyperlink
> to that page
> BuildNav =3D BuildNav & "<a href=3Dkpage.asp?
> PageIndex=3D"
> BuildNav =3D BuildNav & counter & ">" & Counter
> & "</a>"
> End if
> =09
> ' As long as we're not at the last page number in this
> loop, let's add a pipe to the string.
> if cInt(counter) <> TotalPages then
> BuildNav =3D BuildNav & " | "
> end if
> counter =3D counter + 1
> Loop
>
> if (rst.AbsolutePage <> -3) then
> BuildNav =3D BuildNav & NBSP(5)
> BuildNav =3D BuildNav & "<a href=3Dkpage.asp?PageIndex=3D"
> BuildNav =3D BuildNav & intNext & ">next >></a>"
> end if
>
> BuildNav =3D BuildNav & "</b></font>"
> end function
> %>
>
> <%
> function Connect()
> dim strCon
> set Connect=3DServer.CreateObject("ADODB.connection")
> strCon =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;Data
> Source=3Dc:\datastores\movies.mdb"
> Connect.ConnectionString =3D strCon
> Connect.open
> end function
> %>
>
> <%
> function NBSP(count)
> dim i
> for i =3D 1 to count
> NBSP =3D NBSP + " "
> next
> end function
> %>
>
>
> Thanks.
>
> - Frode Str=F8mme.
>
> > Do this
> >
> > Response.Write(Request("strSearch"))
> >
> > and make sure that there is something *in* Request("strSearch")
first.
> >
> > Cheers
> > Ken
>=20
Message #7 by "Frode Strømme" <fstroemm@o...> on Wed, 30 May 2001 22:07:16
|
|
Thanks all.
Using a response.write told me I indeed have a problem passing the
information on to the next page.
I'm trying...
searchStr = Request.Form("formSearch")
choice = Request.Form("formChoice")
...
" WHERE " & choice & " LIKE '%" & searchStr & "%' ORDER BY " & choice
Shouldn't my form input be stored in searchStr and choice this way?
I also have problems passing information from a Request.Querystring over
to the next page with paging. But I guess this is related to the same
problem.
- Frode.
> Searching through www.aspin.com for articles on paging I found one at
> http://www.smashco.com/pages.asp
>
> However I can't get it to work correctly with my Request.Form
>
> Code part I want included:
>
> Dim searchStr
>
> searchStr = Replace(Request.Form("strSearch"), "'", "''")
>
> ' Generate SQL string based on user input
>
> strSQL ="SELECT [khitfilm].[FilmID], [khitfilm].[Tittel], [khitskue].
> [Skuespiller]" & _
> " FROM khitfilm INNER JOIN (khitskue INNER JOIN khitfs ON
> [khitskue].[SkueID] =[khitfs].[SkueID]) ON [khitfilm].[FilmID] =[khitfs].
> [FilmID]" & _
> " WHERE " & Request("choice") & " LIKE '%" & Request
> ("strSearch") & "%' ORDER BY " & Request("choice")
>
> Set RS = MyConn.Execute(SQL)
>
> If searchStr <> "" Then
> If RS.BOF AND RS.EOF Then
> Response.Write "<center> No Records found.</center>"
> Else
> Response.Write ...
>
> Error msg I get:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error (missing operator) in query expression 'LIKE '%%''.
> /wwwroot/index.asp, line 35
>
> ----------
>
> Working code (not including the support.inc file)
>
> <%
>
> Dim rst, strSQL
> set rst = Server.CreateObject("ADODB.Recordset")
>
>
> strSQL = "SELECT * FROM khitfilm ORDER BY khitfilm.FilmID"
>
>
> rst.Open strSQL, connect, 3, 1, 1
>
>
> Dim IntPageSize, PageIndex, TotalPages, TotalRecords
>
> IntPageSize = 5
> PageIndex = Request("PageIndex")
> 'If pageIndex is empty, set it to one.
> if PageIndex = "" then PageIndex = 1
>
> rst.PageSize = intPageSize ' Set the number of records per page
> rst.AbsolutePage = PageIndex 'Define what page number on which the
current
> record resides.
>
> TotalPages = rst.PageCount
>
> dim intPrev, intNext
> intPrev = PageIndex - 1
> intNext = PageIndex + 1 'same as rst.AbsolutePage
>
> 'Build table header
> %>
> <table border="1" cellpadding="5" cellspacing="0">
> <tr bgcolor="#c0c0c0">
> <td>ID</td>
> <td>Film</td>
>
>
> </tr>
>
>
> <%
> Dim Count 'We'll use this to limit the number of records displayed on a
> page
> Count = 1
> do while NOT rst.EOF AND Count <= IntPageSize
> %>
> <tr>
> <td><%=rst("FilmID")%></td>
> <td><%=rst("Tittel")%></td>
>
>
> </tr>
> <%
> count = count + 1
> rst.MoveNext
> loop
> %>
> <tr>
> <td align="center" colspan="8" bgcolor="#c0c0c0">
> <%
> response.write BuildNav (intPrev,IntNext,TotalPages)
> rst.close
> %>
>
>
|
|
|
View Cart
