|
 |
access_asp thread: I just can't figure this out, because I'm a idiot!!
Message #1 by "Michael Hall" <vlad027@h...> on Fri, 9 Nov 2001 15:41:54
|
|
Seriously, I need help with the following problem.
I am using an Access database that takes a user's request and creates an
ASP page. The first page is created fine
http://www.healthalliance.org/TEMP/OurProvider/GE/search.asp , however,
when the user clicks on the "next 20" I receive the following message:
[
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in query expression 'LIKE '%''.
/TEMP/OurProvider/GE/decanc_.asp, line 134
]
Here is the code:
<%
Set ObjConn = Server.CreateObject ("ADODB.Connection")
Set rsFeedback = Server.CreateObject ("ADODB.Recordset")
ObjConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("AREALDATABASE.mdb") & ";"
'Selecting the feedbacks order by latest first
SQL = "Select * from p43gedoc "
SQL = SQL & "WHERE " & SearchType & " LIKE '" & Search & "%'"
rsFeedback.Source = SQL
rsFeedback.ActiveConnection = ObjConn
rsFeedback.CursorType = 3 'adOpenStatic
rsFeedback.CursorLocation = 3 'adUseClient
rsFeedback.Open
PageNumber = 0
if not rsFeedback.EOF Then
'For navigation through records, Setting the page size and starting
position of record
rsFeedback.PageSize = RecordsPageSize
rsFeedback.AbsolutePosition = 1
if Request("PageIndex") <> "" Then
rsFeedback.AbsolutePage = CInt(Request
("PageIndex"))
Else
rsFeedback.AbsolutePage = 1
End If
PageNumber = rsFeedback.AbsolutePage
End If
Counter = 1
While (Counter <= RecordsPageSize) And (not rsFeedback.EOF)
%>
__
<%
'Writing Record Count here
iStart = 0
iEnd = 0
iStart = (((PageNumber - 1) * RecordsPageSize) + 1)
if PageNumber * RecordsPageSize < rsFeedback.RecordCount
Then
iEnd = PageNumber * RecordsPageSize
Else
iEnd = rsFeedback.RecordCount
End If
Response.Write("<B>Displaying " & iStart & " - " & iEnd
& " of Total " & rsFeedback.RecordCount & "</I> Records</B>")
%>
___
<%
'writing Prev and Next Links
if (Request("PageIndex") <> "" )and (PageNumber > 1) Then
Response.Write("<a href=decanc_.asp?PageIndex=" & Int(Request
("PageIndex")) - 1 & ">Prev " & RecordsPageSize & "</a> | ")
Elseif Request("PageIndex") <> "" Then
Response.Write("Prev " & RecordsPageSize & " | ")
End If
If (Request("PageIndex") <> "" )and (PageNumber * RecordsPageSize <
rsFeedback.RecordCount) Then
Response.Write("<a href=decanc_.asp?PageIndex=" & Int(Request
("PageIndex")) + 1 & "> Next " & RecordsPageSize & " </a>")
Elseif Request("PageIndex") <> "" Then
Response.Write("Next " & RecordsPageSize)
End If
If Request("PageIndex") = "" Then
Response.Write("Prev " & RecordsPageSize & " | ")
If ( RecordsPageSize < rsFeedback.RecordCount) Then
Response.Write("<a href=decanc_.asp?PageIndex=2" & ">Next " &
RecordsPageSize & "</a>")
Else
Response.Write("Next 20")
End if
End If
%>
How do I hold the user's request search info and transfer to the next page
(s). Is the answer "cookies" "session variables"?
Thanks for your time and trouble. Your input is greatly appreciated.
I can pay you if you want or give you access to my ftp site.
Message #2 by "Zee Computer Consulting" <zee@t...> on Fri, 9 Nov 2001 10:56:47 -0800
|
|
Pass the search string back to the page in the hyperlink near the bottom of
your code:
Response.Write("<a href=decanc_.asp" )
Response.Write( "?PageIndex=2" )
' Put the search string in the hyperlink
Response.Write( "&Search=" & Search )
Response.Write( ">Next " & RecordsPageSize & "</a>")
Then at the top of the page:
Search = Request.QueryString( "Search" )
' Check for empty passed search string
IF len(Search) = 0 THEN
' Add code here to prompt for search string
END IF
----- Original Message -----
From: "Michael Hall" <vlad027@h...>
To: "Access ASP" <access_asp@p...>
Sent: Friday, November 09, 2001 3:41 PM
Subject: [access_asp] I just can't figure this out, because I'm a idiot!!
> Seriously, I need help with the following problem.
> I am using an Access database that takes a user's request and creates an
> ASP page. The first page is created fine
> http://www.healthalliance.org/TEMP/OurProvider/GE/search.asp , however,
> when the user clicks on the "next 20" I receive the following message:
> [
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
> in query expression 'LIKE '%''.
> /TEMP/OurProvider/GE/decanc_.asp, line 134
> ]
>
> Here is the code:
> <%
>
> Set ObjConn = Server.CreateObject ("ADODB.Connection")
> Set rsFeedback = Server.CreateObject ("ADODB.Recordset")
> ObjConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("AREALDATABASE.mdb") & ";"
>
>
> 'Selecting the feedbacks order by latest first
> SQL = "Select * from p43gedoc "
> SQL = SQL & "WHERE " & SearchType & " LIKE '" & Search & "%'"
>
> rsFeedback.Source = SQL
> rsFeedback.ActiveConnection = ObjConn
> rsFeedback.CursorType = 3 'adOpenStatic
> rsFeedback.CursorLocation = 3 'adUseClient
> rsFeedback.Open
> PageNumber = 0
> if not rsFeedback.EOF Then
>
> 'For navigation through records, Setting the page size and starting
> position of record
> rsFeedback.PageSize = RecordsPageSize
> rsFeedback.AbsolutePosition = 1
>
> if Request("PageIndex") <> "" Then
> rsFeedback.AbsolutePage = CInt(Request
> ("PageIndex"))
> Else
> rsFeedback.AbsolutePage = 1
> End If
> PageNumber = rsFeedback.AbsolutePage
> End If
> Counter = 1
> While (Counter <= RecordsPageSize) And (not rsFeedback.EOF)
> %>
>
> __
>
> <%
> 'Writing Record Count here
> iStart = 0
> iEnd = 0
>
> iStart = (((PageNumber - 1) * RecordsPageSize) + 1)
> if PageNumber * RecordsPageSize < rsFeedback.RecordCount
> Then
> iEnd = PageNumber * RecordsPageSize
> Else
> iEnd = rsFeedback.RecordCount
> End If
> Response.Write("<B>Displaying " & iStart & " - " & iEnd
> & " of Total " & rsFeedback.RecordCount & "</I> Records</B>")
> %>
>
> ___
>
> <%
> 'writing Prev and Next Links
> if (Request("PageIndex") <> "" )and (PageNumber > 1) Then
> Response.Write("<a href=decanc_.asp?PageIndex=" & Int(Request
> ("PageIndex")) - 1 & ">Prev " & RecordsPageSize & "</a> | ")
> Elseif Request("PageIndex") <> "" Then
> Response.Write("Prev " & RecordsPageSize & " | ")
> End If
>
> If (Request("PageIndex") <> "" )and (PageNumber * RecordsPageSize <
> rsFeedback.RecordCount) Then
> Response.Write("<a href=decanc_.asp?PageIndex=" & Int(Request
> ("PageIndex")) + 1 & "> Next " & RecordsPageSize & " </a>")
> Elseif Request("PageIndex") <> "" Then
> Response.Write("Next " & RecordsPageSize)
> End If
>
> If Request("PageIndex") = "" Then
> Response.Write("Prev " & RecordsPageSize & " | ")
> If ( RecordsPageSize < rsFeedback.RecordCount) Then
> Response.Write("<a href=decanc_.asp?PageIndex=2" & ">Next " &
> RecordsPageSize & "</a>")
> Else
> Response.Write("Next 20")
> End if
> End If
>
> %>
>
> How do I hold the user's request search info and transfer to the next page
> (s). Is the answer "cookies" "session variables"?
>
> Thanks for your time and trouble. Your input is greatly appreciated.
> I can pay you if you want or give you access to my ftp site.
>
>
Message #3 by "Samuel Fullman" <sam@c...> on Sun, 18 Nov 2001 02:17:48
|
|
I had a few comments on what you are doing (and I've felt like an idiot
before too so I can empathize):
1. while debugging, if you have a SQL string that is variable generated,
put a write line below it so you can see what a given search produces,
like this:
> rsFeedback.Source = SQL
> response.write rsFeedback.Source & "<BR>"
2. You need to control use of single quotes, so instead of the variable
search, you should put in replace(search,"'", "''") -- otherwise the user
will screw your code up when they write something like "don't"
3. You may use cookies to store the search results from page to page, but
I suggest you do it as a "where clause", i.e. response.cookies
("whereClause") = " where someField LIKE ' & replace(search,"'","''")
& "' "
This way, if cookies aren't read, the entire where clause gets left out
and the sql string still makes sense without it (although you may get a
lot of records that way). For dynamic SQL statements I always try to put
them into the form "Select " & selectFieldsClause & " from tableName " &
whereClause & orderClause
Hope that give some good strategy.
> Seriously, I need help with the following problem.
> I am using an Access database that takes a user's request and creates an
> ASP page. The first page is created fine
> http://www.healthalliance.org/TEMP/OurProvider/GE/search.asp , however,
> when the user clicks on the "next 20" I receive the following message:
> [
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator)
> in query expression 'LIKE '%''.
> /TEMP/OurProvider/GE/decanc_.asp, line 134
> ]
>
> Here is the code:
> <%
>
> Set ObjConn = Server.CreateObject ("ADODB.Connection")
> Set rsFeedback = Server.CreateObject ("ADODB.Recordset")
> ObjConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("AREALDATABASE.mdb") & ";"
>
>
> 'Selecting the feedbacks order by latest first
> SQL = "Select * from p43gedoc "
> SQL = SQL & "WHERE " & SearchType & " LIKE '" & Search & "%'"
>
> rsFeedback.Source = SQL
> rsFeedback.ActiveConnection = ObjConn
> rsFeedback.CursorType = 3 'adOpenStatic
> rsFeedback.CursorLocation = 3 'adUseClient
> rsFeedback.Open
> PageNumber = 0
> if not rsFeedback.EOF Then
>
|
|
|
View Cart
