SQL statement

[gilgalbiblewhee]

The code of <!--#include file="query.asp"--> is:

<%Sub ShowResults()
Dim conn
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 "
Conn = GetConnectionString()

Keyword = Trim(Request.QueryString("Keyword"))
spoke = Request.Querystring("spoke")
number = Request.QueryString("number")
Keywordb = Request.QueryString("Keywordb")
Keywordc = Request.QueryString("Keywordc")

Counter = 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%>


I hope you have the patience to read it through or show me if there is any way to simplify it further.

Is it a good idea to leave the SQL as a separate file?

[russell]

Do a response.write sql and response.end just before the rs.open statement. see what the query actually is. if that soesn't make the problem obvious then post the query as written out by the response.write.

[gilgalbiblewhee]

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


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

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

/wheelofgod/kjvresp2.asp, line 218


<% '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
%>