Why does the SQL show error?

[gilgalbiblewhee]

Why does the SQL show error?

The error says:

Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

/wheelofgod/kjvresp2.asp, line 87



Line 87 is:
rs.Open SQL, conn, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect

found in:
<% 'Option Explicit%>
<!--#include file="biblepagetop.txt"-->
<%
'Response.Buffer=false

' Mode contstants
Const MODE_DEFAULT = 1
Const MODE_RESULTS = 2

Const DB_NAME = "kjv.mdb" ' Name of our database file
Const SCRIPT_NAME = "kjv2.asp" ' Name of this script
Const SCRIPT_NAMES = "kjvresp2.asp"
const SCRIPT_SAVED = "saved.asp"
Const SCRIPT_FEEDBACK = "mailto.asp"
Const SCRIPT_TEXT = "bibletext.asp"

Const RECORDS_PER_PAGE = 5 ' Number of records per page

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3

Dim nMode ' Current Mode
' Find out what mode we are in
nMode = CLng(Request.QueryString("Mode"))

' Depending on our mode we will do different things
Select Case nMode

Case MODE_RESULTS
' This is where all the results will show
call ShowResults()

Case Else ' This one is for MODE_DEFAULT or invalid modes all the same
' By default display the search form
call ShowSearchForm()
End Select

Private Function GetConnectionString()
GetConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath(DB_NAME) & ";" & _
"UID=;PWD=;"
End Function

Private Sub ShowSearchForm()
%>
<!--
This form will direct user to itself with MODE_RESULTS mode
-->
<%
' This function will display the results of the search
call ShowResults()
End Sub
%>

<!--#include file="query.asp"-->
<table border="1" cellspacing="0" cellpadding="0" width="732" style="width:549.0pt;
border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt;
mso-padding-alt:0in 0in 0in 0in">
<tr style="height:12.75pt">
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">
<form align="center" method="get" action="<%=SCRIPT_SAVED%>" id=form1 name=form1>Select</font><br>
<select size="1" name="choices" id="choices">
<option SELECTED VALUE="">None</option>
<option VALUE="001">Delete</option>
<option VALUE="002">Email to:</option>
<option VALUE="003">Print</option>
<option VALUE="004">Save in:</option>
</select><input type="submit" value="OK" name=submit1><br>
<input type='checkbox' onclick='checkAll(this.form,this)' value="check all" name="ck<%CStr(id)%>">
<font face="Verdana" color="#FFFFFF">Select All</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Book Title</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Book #/<br>Book Spoke</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Chapter #/<br>Chapter Spoke</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Verse #/<br>Verse Spoke</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Text</font></th>
</tr>

<%
Set rs = Server.CreateObject("ADODB.Recordset")

rs.PageSize = 5
rs.CacheSize = 5
rs.CursorLocation = adUseClient

<b>rs.Open SQL, conn, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect</b>


If Len(Request("pagenum")) = 0 Then
rs.AbsolutePage = 1
Else
If CInt(Request("pagenum")) <= rs.PageCount Then
rs.AbsolutePage = Request("pagenum")
Else
rs.AbsolutePage = 1
End If
End If

Dim abspage, pagecnt
abspage = rs.AbsolutePage
pagecnt = rs.PageCount
If rs.EOF Then
Response.Write "No records found!"
Else
Response.Write "PageCount : " & rs.PageCount & "<br>" & vbcrlf
Response.Write "Absolute Page : " & rs.AbsolutePage & "<br>" & vbcrlf

Response.Write "Total number of records : " & rs.RecordCount & "<br><br>" & vbcrlf

Dim fldF

Response.Write "<table border=1 align=center cellpadding=3 cellspacing=0><thead><tr>"
'For Each fldF in rs.Fields
' Response.Write "<td>" & fldF.Name & "</td>"
'Next
'Response.Write "</tr></thead><tbody>"

For intRec=1 To rs.PageSize
If Not rs.EOF Then
' Response.Write "<tr>"
' For Each fldF in rs.Fields
' Response.Write "<td>" & fldF.Value & "</td>"
' Next
' Response.Write "<tr>"

rs.MoveFirst
Count = 0
Do while not rs.eof And Count < 10
%>
<%=SQL%>
<td width="135" nowrap rowspan="2" style="width:101.25pt;border:solid windowtext .5pt;
border-right:solid windowtext .75pt;padding:.75pt .75pt 0in .75pt;height:
8.0pt">

<!--webbot bot="SaveResults" U-File="fpweb:///_private/form_results.txt"
S-Format="TEXT/CSV" S-Label-Fields="TRUE" -->
<p><input type="checkbox" name="ck<%CStr(id)%> value="<%=rs("id")%>"></p>

<p>&nbsp;
</td>
<td width="135" nowrap rowspan="2" style="width:101.25pt;border:solid windowtext .5pt;
border-right:solid windowtext .75pt;padding:.75pt .75pt 0in .75pt;height:
8.0pt">
<p class="MsoNormal" align="center" style="text-align:center"><span style="font-size:10.0pt;font-family:Verdana"><%=rs("book_title")%><o:p>
</span>
<td width="149" nowrap style="width:111.75pt;border-top:solid windowtext .5pt;
border-left:none;border-bottom:solid windowtext .75pt;border-right:solid windowtext .75pt;
mso-border-left-alt:solid windowtext .75pt;padding:.75pt .75pt 0in .75pt;
height:8.0pt">
<p class="MsoNormal" align="center" style="text-align:center"><span style="font-size:10.0pt;font-family:Verdana"><%=rs("book")%><o:p>
</o:p>
</span></p>
</td>
<td width="164" nowrap style="width:123.0pt;border-top:solid windowtext .5pt;
border-left:none;border-bottom:solid windowtext .75pt;border-right:solid windowtext .75pt;
mso-border-left-alt:solid windowtext .75pt;padding:.75pt .75pt 0in .75pt;
height:8.0pt">
<p class="MsoNormal" align="center" style="text-align:center"><span style="font-size:10.0pt;font-family:Verdana"><%=rs("chapter")%><o:p>
</o:p>
</span></p>
</td>
<td width="151" nowrap style="width:113.25pt;border-top:solid windowtext .5pt;
border-left:none;border-bottom:solid windowtext .75pt;border-right:solid windowtext .75pt;
mso-border-left-alt:solid windowtext .75pt;padding:.75pt .75pt 0in .75pt;
height:8.0pt">
<p class="MsoNormal" align="center" style="text-align:center"><span style="font-size:10.0pt;font-family:Verdana"><%=rs("verse")%><o:p>
</o:p>
</span></p>
</td>
<td width="245" nowrap rowspan="2" valign="top" style="mso-border-left-alt: solid windowtext .75pt; height: 12.75pt; border-left-style: none; border-left-width: medium; border-right: .5pt solid windowtext; border-top: .5pt solid windowtext; border-bottom: .5pt solid windowtext; padding-left: .75pt; padding-right: .75pt; padding-top: .75pt; padding-bottom: 0in">
<p class="MsoNormal"><span style="font-size:10.0pt;font-family:Verdana"><b><%=rs("book_title")%>&nbsp;&nbsp;<%=rs("chap")%>:<%=rs("vers")%></b><br><%=rs("text_data")%><o:p>
</o:p>
</span></p>
</td>
<tr style="height:8.0pt" ALIGN="middle">
<td width="10">
<span>
<A href="<!--#include file="letters.asp"-->" target="_blank">
<IMG SRC="hebrew/<%=RS("book_spoke")%>.jpg" ALT="Book&nbsp;Spoke&nbsp;&nbsp;<%=RS("book_spoke")%>">
</A>

</span>
</td>
<td width="10">
<span>
<IMG SRC="hebrew/<%=RS("chapter_spoke")%>.jpg" ALIGN="middle" ALT="Chapter&nbsp;Spoke&nbsp;&nbsp;<%=RS("chapter_spoke")%>">

</span>
</td>
<td width="10" ALIGN="middle" >
<span><IMG SRC="hebrew/<%=RS("verse_spoke")%>.jpg" ALIGN="middle" ALT="Verse&nbsp;Spoke&nbsp;&nbsp;<%=RS("verse_spoke")%>">
</span>
</td>
</tr>
<% rs.MoveNext
Loop
%>
</form>
</table>
<%
'rs.MoveNext
End If
Next
'Response.Write "</tbody></table><p>"

' Now showing first, next, back, last buttons.
Response.Write "<div align=""center"">" & vbcrlf
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=1"">First Page</a>"
Response.Write "&nbsp;|&nbsp;"

If abspage = 1 Then
Response.Write "<span style=""color:silver;"">Previous Page</span>"
Else
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage - 1 & """>Previous Page</a>"
End If
Response.Write "&nbsp;|&nbsp;"

If abspage < pagecnt Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage + 1 & """>Next Page</a>"
Else
Response.Write "<span style=""color:silver;"">Next Page</span>"
End If
Response.Write "&nbsp;|&nbsp;"
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & pagecnt & """>Last Page</a>"
Response.Write "</div>" & vbcrlf

rs.Close
Set rs = Nothing
End If
End Sub
%>
<!--#include file=biblepagebottom.txt-->

I tried shortening the length of the code by putting the SQL statement as an Include File:

<!--#include file="query.asp"-->

[lmf232s]

theirs a problem with your SQL statement .
Post your SQL statement so we can look at that.

[gilgalbiblewhee]

SELECT * FROM bible WHERE text_data LIKE '%artaxerxes%' AND text_data LIKE '%%' AND text_data LIKE '%%'

[lmf232s]

im not sure what is wrong. I would try to narrow it down.
I would try something like this.
See if this works
SELECT * FROM BIBLE

If that works then try
SELECT * FROM BIBLE WHERE text_date LIKE '%artaxerxes%'

then if that works add then next on then the next on.
You should be able to narrow it down like that.
It might be one of your where clause but not sure.
Try to narrow it down and see what works.
Post back and let us know.

[gilgalbiblewhee]

I'm trying to fit this code in the example from
http://www.stardeveloper.com/articles/display.html?article=2000071001&page=1

Because I liked the "pageing".

<% 'Option Explicit
Response.Buffer=false%>
<!--#include file="biblepagetop.txt"-->
<%
' Mode contstants
Const MODE_DEFAULT = 1
Const MODE_RESULTS = 2

Const DB_NAME = "kjv.mdb" ' Name of our database file
Const SCRIPT_NAME = "kjv2.asp" ' Name of this script
Const SCRIPT_NAMES = "kjvresp2.asp"
const SCRIPT_SAVED = "saved.asp"
Const SCRIPT_FEEDBACK = "mailto.asp"
Const SCRIPT_TEXT = "bibletext.asp"

Const RECORDS_PER_PAGE = 5 ' Number of records per page

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3

Dim nMode ' Current Mode
' Find out what mode we are in
nMode = CLng(Request.QueryString("Mode"))

' Depending on our mode we will do different things
Select Case nMode

Case MODE_RESULTS
' This is where all the results will show
call ShowResults()

Case Else ' This one is for MODE_DEFAULT or invalid modes all the same
' By default display the search form
call ShowSearchForm()
End Select

Private Function GetConnectionString()
GetConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath(DB_NAME) & ";" & _
"UID=;PWD=;"
End Function

Private Sub ShowSearchForm()
%>
<!--
This form will direct user to itself with MODE_RESULTS mode
-->
<%
' This function will display the results of the search
call ShowResults()
End Sub

Sub ShowResults()
Dim strConn ' Database connection string
Dim SQL ' String that will have our SQL statments
Dim RS ' Recordset object
Dim Keyword ' Keyword for search
'pageing
Dim nRecCount ' Number of records found
Dim nPageCount ' Number of pages of records we have
Dim nPage ' Current page number
'query
Dim iCounter
Dim iLoopCount
Dim aRecTypes
Dim spoke ' For dropdown
Dim number
Dim Keywordb
Dim Keywordc
Dim intRec


' define our SQL statement
' we will be looking for all the records in tblItem table
' where ItemName contains our Keyword
' do not forget to fix tick marks (single quotes) in our Keyword
SQL = "SELECT * FROM bible WHERE "
strConn = GetConnectionString()

' Let's see what page are we looking at right now
nPage = CLng(Request.QueryString("Page"))

' Let's see what user wants to search for today :)
Keyword = Trim(Request.QueryString("Keyword"))
spoke = Request.Querystring("spoke")
number = Request.QueryString("number")
Keywordb = Request.QueryString("Keywordb")
Keywordc = Request.QueryString("Keywordc")

'Set conn = Server.CreateObject("ADODB.Connection")
'conn.Open(GetConnectionString)

iCounter = 0

If request.QueryString("text_data")="yes" then

SQL = SQL & "text_data LIKE '%" & Keyword & "%' AND "
SQL = SQL & "text_data LIKE '%" & Keywordb & "%' AND "
SQL = SQL & "text_data LIKE '%" & Keywordc & "%'"

iCounter = iCounter + 1

end if


If request.QueryString("book")="yes" then

If iCounter > 0 Then
SQL = SQL & " AND "
End If

SQL = SQL & "book LIKE '" & number & "'"

iCounter = iCounter + 1

end if


If request.QueryString("book_title")="yes" then

If iCounter > 0 Then
SQL = SQL & " AND "
End If

SQL = SQL & "book_title LIKE '%" & number & "%'"

iCounter = iCounter + 1

end if

If request.QueryString("chapter")="yes" then

If iCounter > 0 Then
SQL = SQL & " AND "
End If

SQL = SQL & "chapter LIKE '%" & number & "%'"

iCounter = iCounter + 1

end if

If request.QueryString("verse")="yes" then

If iCounter > 0 Then
SQL = SQL & " AND "
End If

SQL = SQL & "verse LIKE '%" & number & "%'"

iCounter = iCounter + 1

end if

If request.QueryString("book_spoke")="Book_Spoke" then

If iCounter > 0 Then
SQL = SQL & " AND "
End If

SQL = SQL & "book_spoke = '" & spoke & "'"

iCounter = iCounter + 1

end if

If request.QueryString("chapter_spoke")="Chapter_Spoke" then

If iCounter > 0 Then
SQL = SQL & " AND "
End If

SQL = SQL & "chapter_spoke = '" & spoke & "'"

iCounter = iCounter + 1

end if

If request.QueryString("verse_spoke")="Verse_Spoke" then

If iCounter > 0 Then
SQL = SQL & " AND "
End If

SQL = SQL & "verse_spoke = '" & spoke & "'"

iCounter = iCounter + 1

end if

If Trim(Request.QueryString("recordType")) <> "" Then


aRecTypes = Split(Request.QueryString("recordType"), ",")

If IsArray(aRecTypes) Then 'This is a bit redundant, but it can't hurt
SQL = SQL & " AND ("

For iLoopCount = 0 To UBound(aRecTypes)
If iLoopCount <> 0 Then
SQL = SQL & " OR "
End If

SQL = SQL & " recordType = '" & trim(aRecTypes(iLoopCount)) & "'"
Next
End If
SQL = SQL & ")"
End If

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

rs.PageSize = 5
rs.CacheSize = 5
rs.CursorLocation = adUseClient
Response.Write SQL
rs.Open SQL, StrConn, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect


If Len(Request("pagenum")) = 0 Then
rs.AbsolutePage = 1
Else
If CInt(Request("pagenum")) <= rs.PageCount Then
rs.AbsolutePage = Request("pagenum")
Else
rs.AbsolutePage = 1
End If
End If

Dim abspage, pagecnt
abspage = rs.AbsolutePage
pagecnt = rs.PageCount

If rs.BOF and rs.EOF Then
Response.Write "No records found!"
Else
Response.Write "PageCount : " & rs.PageCount & "<br>" & vbcrlf
Response.Write "Absolute Page : " & rs.AbsolutePage & "<br>" & vbcrlf
Response.Write "Total number of records : " & rs.RecordCount & "<br><br>" & vbcrlf
%>
<table>
<tr>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">
<form align="center" method="get" action="<%=SCRIPT_SAVED%>" id=form1 name=form1>Select</font><br>
<select size="1" name="choices" id="choices">
<option SELECTED VALUE="">None</option>
<option VALUE="001">Delete</option>
<option VALUE="002">Email to:</option>
<option VALUE="003">Print</option>
<option VALUE="004">Save in:</option>
</select><input type="submit" value="OK" name=submit1><br>
<input type='checkbox' onclick='checkAll(this.form,this)' value="check all" name="ck<%CStr(id)%>">
<font face="Verdana" color="#FFFFFF">Select All</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Book Title</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Book #/<br>Book Spoke</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Chapter #/<br>Chapter Spoke</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Verse #/<br>Verse Spoke</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Text</font></th>
</tr>
<%
rs.MoveFirst
Count = 0
Do while not rs.eof And Count < 10
%>
<td>

<p><input type="checkbox" name="ck<%CStr(id)%> value="<%=rs("id")%>"></p>

<p>
</td>
<td>
<p><span><%=rs("book_title")%><o:p>
</span>
<td>
<p><span><%=rs("book")%><o:p>
</o:p>
</span></p>
</td>
<td>
<p><span><%=rs("chapter")%><o:p>
</o:p>
</span></p>
</td>
<td>
<p><span><%=rs("verse")%><o:p>
</o:p>
</span></p>
</td>
<td>
<p><span><b><%=rs("book_title")%> <%=rs("chap")%>:<%=rs("vers")%></b><br><%=rs("text_data")%><o:p>
</o:p>
</span></p>
</td>
<tr>
<td width="10">
<span>
<A href="<!--#include file="letters.asp"-->" target="_blank">
<IMG SRC="hebrew/<%=RS("book_spoke")%>.jpg" ALT="Book Spoke <%=RS("book_spoke")%>">
</A>
</span>
</td>
<td>
<span>
<IMG SRC="hebrew/<%=RS("chapter_spoke")%>.jpg" ALIGN="middle" ALT="Chapter Spoke <%=RS("chapter_spoke")%>">

</span>
</td>
<td>
<span><IMG SRC="hebrew/<%=RS("verse_spoke")%>.jpg" ALIGN="middle" ALT="Verse Spoke <%=RS("verse_spoke")%>">
</span>
</td>
</tr>
<% rs.MoveNext
Loop
%>
</form>
</table>
<%
' Now showing first, next, back, last buttons.
Response.Write "<div align=""center"">" & vbcrlf
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAMES") & "?pagenum=1"">First Page</a>"
Response.Write " | "

If abspage = 1 Then
Response.Write "<span style=""color:silver;"">Previous Page</span>"
Else
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAMES") & "?pagenum=" & abspage - 1 & """>Previous Page</a>"
End If
Response.Write " | "

If abspage < pagecnt Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAMES") & "?pagenum=" & abspage + 1 & """>Next Page</a>"
Else
Response.Write "<span style=""color:silver;"">Next Page</span>"
End If
Response.Write " | "
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAMES") & "?pagenum=" & pagecnt & """>Last Page</a>"
Response.Write "</div>" & vbcrlf
%>
<!--#include file=biblepagebottom.txt-->
<%
rs.Close
Set rs = Nothing
End If
End Sub
%>

[lmf232s]

Man i just dont know. I noticed that you build the sql statment
on the fly based on user specified data.
I also see that your doing a response.write sql right before
you execute it.

Is this sql statement exaclty the same as the post before.
SELECT * FROM bible WHERE text_data LIKE '%artaxerxes%' AND text_data LIKE '%%' AND text_data LIKE '%%'

or is it something different.

are you still getting the same error.
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

if so its the sql statment and something is wrong with it. Im not sure what it could be. but if thats still the error, i dont believe its the code, as much as i think its the sql statment.

post the sql statment if its different from the las post.

[buntine]

Make sure you look at the less-obvious things also. The generated SQL statement looks fine, but mayby your executing another SQL query that you hae forgotten about (there is alot of code there!) or you have misspelled it somewhere.

Error messages can often be misleading.

Regards.

[gilgalbiblewhee]

Yes I understand it can be misleading. I've used just
SELECT * FROM bible too
and it still failed. I had put ' before every continuation of the statement.

I know that in my previous code it all worked fine. The reason I was working in this code is that I liked the "pageing" (first page, last page...). But when I moved the SQL and everything else from the old page to the new, it went wrong.

Could it be:
the "MODE"? Since MODE 1 and MODE 2 were used in case there was no result.

"If BOF and EOF"?

the pageing codes? (I suspect it is)

[gilgalbiblewhee]

I have put the original paging code in this link:
http://n.1asphost.com/wheelofgod/pageing/paging.asp

Here is the original code, which was without SQL

<% Option Explicit

' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
%>
<html>
<head>
<style>
body { font-family : Verdana; font-size : 8pt; }
a { font-family : Verdana; font-size : 8pt; text-decoration : none; }
</style>
</head>

<body>
<%
Dim connStr
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("paging.mdb")

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

rs.PageSize = 5
rs.CacheSize = 5
rs.CursorLocation = adUseClient

rs.Open "Names", connStr, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect


If Len(Request("pagenum")) = 0 Then
rs.AbsolutePage = 1
Else
If CInt(Request("pagenum")) <= rs.PageCount Then
rs.AbsolutePage = Request("pagenum")
Else
rs.AbsolutePage = 1
End If
End If

Dim abspage, pagecnt
abspage = rs.AbsolutePage
pagecnt = rs.PageCount

If Not rs.EOF Then
Response.Write "PageCount : " & rs.PageCount & "<br>" & vbcrlf
Response.Write "Absolute Page : " & rs.AbsolutePage & "<br>" & vbcrlf

Response.Write "Total number of records : " & rs.RecordCount & "<br><br>" & vbcrlf

Dim fldF, intRec

Response.Write "<table border=1 align=center cellpadding=3 cellspacing=0><thead><tr>"
For Each fldF in rs.Fields
Response.Write "<td>" & fldF.Name & "</td>"
Next
Response.Write "</tr></thead><tbody>"

For intRec=1 To rs.PageSize
If Not rs.EOF Then
Response.Write "<tr>"
For Each fldF in rs.Fields
Response.Write "<td>" & fldF.Value & "</td>"
Next
Response.Write "<tr>"
rs.MoveNext
End If
Next
Response.Write "</tbody></table><p>"

' Now showing first, next, back, last buttons.
Response.Write "<div align=""center"">" & vbcrlf
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=1"">First Page</a>"
Response.Write "&nbsp;|&nbsp;"

If abspage = 1 Then
Response.Write "<span style=""color:silver;"">Previous Page</span>"
Else
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage - 1 & """>Previous Page</a>"
End If
Response.Write "&nbsp;|&nbsp;"

If abspage < pagecnt Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & abspage + 1 & """>Next Page</a>"
Else
Response.Write "<span style=""color:silver;"">Next Page</span>"
End If
Response.Write "&nbsp;|&nbsp;"
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?pagenum=" & pagecnt & """>Last Page</a>"
Response.Write "</div>" & vbcrlf

Else
Response.Write "No records found!"
End If

rs.Close
Set rs = Nothing
%>
</body>
</html>

[gilgalbiblewhee]

I had changed


Dim connStr
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("paging.mdb")


to

Private Function GetConnectionString()
GetConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath(DB_NAME) & ";" & _
"UID=;PWD=;"
End Function

Dim strConn ' Database connection string
strConn = GetConnectionString()

[lmf232s]

are you still getting the same error.

ok i posted to soon without looking at the page.
So its working when you take out the sql statement and open the
DB by Name?

But when you add in the sql statement it breaks?

I see your opening the table "Names" what happens when you replace it with the table "bibles"? Have you tried to open this table by
its name only and not with the sql statement?

[gilgalbiblewhee]

As I was beginning to make changes from the example, by adding things, I was testing them. I changed the DB and the table name first. The table name was as rs.open Names. SQL didn't exist there. When I replaced the DB and table it worked.

One of the changes, I don't know if this was a problem, was the replacement of the asp table construction codes and the looping through the fields (I don't know what it's called) because I wanted designed in a certain way. So I used HTML.

[lmf232s]

can you post this page up where you have the other page.
NO reason but to take a look at it, see the error and then
view the output if any output is present

Dude im stumped

[gilgalbiblewhee]

<% Option Explicit

' ADO constants used in this page
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3
%>
<html>
<head>
<style>
body { font-family : Verdana; font-size : 8pt; }
a { font-family : Verdana; font-size : 8pt;
text-decoration : none; }
</style>
</head>

<body>
<%
Dim connStr
connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("paging.mdb")

Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")

rs.PageSize = 5
rs.CacheSize = 5
rs.CursorLocation = adUseClient

rs.Open "Names", connStr, adOpenForwardOnly, adLockReadOnly, _
adCmdTableDirect

If Len(Request("pagenum")) = 0 Then
rs.AbsolutePage = 1
Else
If CInt(Request("pagenum")) <= rs.PageCount Then
rs.AbsolutePage = Request("pagenum")
Else
rs.AbsolutePage = 1
End If
End If

Dim abspage, pagecnt
abspage = rs.AbsolutePage
pagecnt = rs.PageCount

If Not rs.EOF Then
Response.Write "PageCount : " & rs.PageCount & "<br>" & vbcrlf
Response.Write "Absolute Page : " & rs.AbsolutePage & "<br>" & vbcrlf

Response.Write "Total number of records : " & rs.RecordCount
Response.Write "<br><br>" & vbcrlf

Dim fldF, intRec

Response.Write "<table border=1 align=center cellpadding=3 "
Response.Write "cellspacing=0><thead><tr>"
For Each fldF in rs.Fields
Response.Write "<td>" & fldF.Name & "</td>"
Next
Response.Write "</tr></thead><tbody>"

For intRec=1 To rs.PageSize
If Not rs.EOF Then
Response.Write "<tr>"
For Each fldF in rs.Fields
Response.Write "<td>" & fldF.Value & "</td>"
Next
Response.Write "<tr>"
rs.MoveNext
End If
Next
Response.Write "</tbody></table><p>"
' Now showing first, next, back, last buttons.

Response.Write "<div align=""center"">" & vbcrlf
Response.Write "<a href="""
Response.Write Request.ServerVariables("SCRIPT_NAME")
Response.Write "?pagenum=1""><b>First Page</b></a>"
Response.Write " | "

If abspage = 1 Then
Response.Write "<span style=""color:silver;"">Previous Page</span>"
Else
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME")
Response.Write "?pagenum=" & abspage - 1 & """><b>Previous Page</b></a>"
End If

Response.Write " | "
If abspage < pagecnt Then
Response.Write "<a href=""" & _
Request.ServerVariables("SCRIPT_NAME")
Response.Write "?pagenum=" & abspage + 1 & """>Next Page</a>"
Else
Response.Write "<span style=""color:silver;""" & _
"><b>Next Page</b></span>"
End If

Response.Write " | "
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME")
Response.Write "?pagenum=" & pagecnt & """><b>Last Page</b></a>"
Response.Write "</div>" & vbcrlf

Else
Response.Write "No records found!"
End If

rs.Close
Set rs = Nothing

%>
</body>
</html>

[gilgalbiblewhee]

There is no errors but the paging doesn't work on the HTML which I have inserted along. If I had left asp's table constructor it would display all the fields (I only want but a few). But it would separate into 5 records per page as I have requested.

<% 'Option Explicit
Response.Buffer=false%>
<!--#include file="biblepagetop.txt"-->
<%
' Mode contstants
Const MODE_DEFAULT = 1
Const MODE_RESULTS = 2

Const DB_NAME = "kjv.mdb" ' Name of our database file
Const SCRIPT_NAME = "kjv.asp" ' Name of this script
Const SCRIPT_NAMES = "kjvresp1.asp"
const SCRIPT_SAVED = "saved.asp"
Const SCRIPT_FEEDBACK = "mailto.asp"
Const SCRIPT_TEXT = "bibletext.asp"

Const RECORDS_PER_PAGE = 5 ' Number of records per page

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdTableDirect = &H0200
Const adUseClient = 3

Dim nMode ' Current Mode
' Find out what mode we are in
nMode = CLng(Request.QueryString("Mode"))

' Depending on our mode we will do different things
Select Case nMode

Case MODE_RESULTS
' This is where all the results will show
call ShowResults()

Case Else ' This one is for MODE_DEFAULT or invalid modes all the same
' By default display the search form
call ShowSearchForm()
End Select

Private Function GetConnectionString()
GetConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Server.MapPath(DB_NAME) & ";" & _
"UID=;PWD=;"
End Function

Private Sub ShowSearchForm()
%>
<!--
This form will direct user to itself with MODE_RESULTS mode
-->
<%
' This function will display the results of the search
call ShowResults()
End Sub

Sub ShowResults()
Dim strConn ' Database connection string
Dim SQL ' String that will have our SQL statments
Dim RS ' Recordset object
Dim Keyword ' Keyword for search
'pageing
Dim nRecCount ' Number of records found
Dim nPageCount ' Number of pages of records we have
Dim nPage ' Current page number
'query
Dim iCounter
Dim iLoopCount
Dim aRecTypes
Dim spoke ' For dropdown
Dim number
Dim Keywordb
Dim Keywordc
Dim intRec


' define our SQL statement
' we will be looking for all the records in tblItem table
' where ItemName contains our Keyword
' do not forget to fix tick marks (single quotes) in our Keyword
SQL = "SELECT * FROM bible WHERE "
strConn = GetConnectionString()


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

rs.PageSize = 5
rs.CacheSize = 5
rs.CursorLocation = adUseClient

rs.Open "bible", strConn, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect


If Len(Request("pagenum")) = 0 Then
rs.AbsolutePage = 1
Else
If CInt(Request("pagenum")) <= rs.PageCount Then
rs.AbsolutePage = Request("pagenum")
Else
rs.AbsolutePage = 1
End If
End If

Dim abspage, pagecnt
abspage = rs.AbsolutePage
pagecnt = rs.PageCount

If rs.BOF and rs.EOF Then
Response.Write "No records found!"
Else
Response.Write "PageCount : " & rs.PageCount & "<br>" & vbcrlf
Response.Write "Absolute Page : " & rs.AbsolutePage & "<br>" & vbcrlf
Response.Write "Total number of records : " & rs.RecordCount & "<br><br>" & vbcrlf
%>
<table>
<tr>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">
<form align="center" method="get" action="<%=SCRIPT_SAVED%>" id=form1 name=form1>Select</font><br>
<select size="1" name="choices" id="choices">
<option SELECTED VALUE="">None</option>
<option VALUE="001">Delete</option>
<option VALUE="002">Email to:</option>
<option VALUE="003">Print</option>
<option VALUE="004">Save in:</option>
</select><input type="submit" value="OK" name=submit1><br>
<input type='checkbox' onclick='checkAll(this.form,this)' value="check all" name="ck<%CStr(id)%>">
<font face="Verdana" color="#FFFFFF">Select All</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Book Title</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Book #/<br>Book Spoke</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Chapter #/<br>Chapter Spoke</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Verse #/<br>Verse Spoke</font></th>
<th bgcolor="#800000"><font face="Verdana" color="#FFFFFF">Text</font></th>
</tr>
<%
rs.MoveFirst
Count = 0
Do while not rs.eof And Count < 10
%>
<td>

<p><input type="checkbox" name="ck<%CStr(id)%> value="<%=rs("id")%>"></p>

<p>
</td>
<td>
<p><span><%=rs("book_title")%><o:p>
</span>
<td>
<p><span><%=rs("book")%><o:p>
</o:p>
</span></p>
</td>
<td>
<p><span><%=rs("chapter")%><o:p>
</o:p>
</span></p>
</td>
<td>
<p><span><%=rs("verse")%><o:p>
</o:p>
</span></p>
</td>
<td>
<p><span><b><%=rs("book_title")%> <%=rs("chap")%>:<%=rs("vers")%></b><br><%=rs("text_data")%><o:p>
</o:p>
</span></p>
</td>
<tr>
<td width="10">
<span>

</span>
</td>
<td>
<span>
<IMG SRC="hebrew/<%=RS("chapter_spoke")%>.jpg" ALIGN="middle" ALT="Chapter Spoke <%=RS("chapter_spoke")%>">

</span>
</td>
<td>
<span><IMG SRC="hebrew/<%=RS("verse_spoke")%>.jpg" ALIGN="middle" ALT="Verse Spoke <%=RS("verse_spoke")%>">
</span>
</td>
</tr>
<% rs.MoveNext
Loop
%>
</form>
</table>
<%
' Now showing first, next, back, last buttons.
Response.Write "<div align=""center"">" & vbcrlf
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAMES") & "?pagenum=1"">First Page</a>"
Response.Write " | "

If abspage = 1 Then
Response.Write "<span style=""color:silver;"">Previous Page</span>"
Else
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAMES") & "?pagenum=" & abspage - 1 & """>Previous Page</a>"
End If
Response.Write " | "

If abspage < pagecnt Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAMES") & "?pagenum=" & abspage + 1 & """>Next Page</a>"
Else
Response.Write "<span style=""color:silver;"">Next Page</span>"
End If
Response.Write " | "
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAMES") & "?pagenum=" & pagecnt & """>Last Page</a>"
Response.Write "</div>" & vbcrlf
%>
<!--#include file=biblepagebottom.txt-->
<%
rs.Close
Set rs = Nothing
End If
End Sub
%>