Having trouble with AND, iCounter in SQL

[gilgalbiblewhee]

I think it the iCounter that's not placed properly. I often get and extra "AND". I erase the "AND" and search something else and then there is a lack of "AND".

rs.PageSize = RECORDS_PER_PAGE
rs.CacheSize = 5
rs.CursorLocation = adUseClient
Dim SQL
Dim number
Dim hInput
Dim hOutput
Dim iCounter
Dim Keyword ' Keyword for search
Dim Keywordb
Dim Keywordc
Dim Keywordd
Dim Keyworde
Dim Keywordf
Dim spoke
Dim aRecTypes
Dim iLoopCount
Dim recordType

' Let's see what user wants to search for today :)
Keyword = Trim(Request.QueryString("Keyword"))
Keywordb = Trim(Request.QueryString("Keywordb"))
Keywordc = Trim(Request.QueryString("Keywordc"))
Keywordd = Trim(Request.QueryString("Keywordd"))
Keyworde = Trim(Request.QueryString("Keyworde"))
Keywordf = Trim(Request.QueryString("Keywordf"))
spoke = Request.Querystring("spoke")
number = Request.QueryString("number")
hInput = Request.QueryString("hInput")
hOutput = Request.QueryString("hOutput")
recordType = Request.QueryString("hebrewbibletable.recordType")
iCounter = 0

SQL= "SELECT * "
SQL = SQL & " FROM hebbooktable INNER JOIN hebrewbibletable "
SQL = SQL & " ON hebbooktable.book = hebrewbibletable.book "
SQL = SQL & " WHERE "

' SQL = "SELECT * FROM 2 WHERE "

If Keyword <> "" Then
If iCounter > 0 Then
SQL = SQL & " AND "
End If
SQL = SQL & "text_data LIKE '%" & Keyword & "%' "
iCounter = iCounter + 1
End If
If Keywordb <> "" Then
If iCounter > 0 Then
SQL = SQL & " AND "
End If
SQL = SQL & "text_data LIKE '%" & Keywordb & "%' "
iCounter = iCounter + 1
End If
If Keywordc <> "" Then
If iCounter > 0 Then
SQL = SQL & " AND "
End If
SQL = SQL & "text_data LIKE '%" & Keywordc & "%' "
iCounter = iCounter + 1
End If
If Keywordd <> "" Then
If iCounter > 0 Then
SQL = SQL & " AND "
End If
SQL = SQL & "text_data LIKE '%" & Keywordd & "%' "
iCounter = iCounter + 1
End If
If Keyworde <> "" Then
If iCounter > 0 Then
SQL = SQL & " AND "
End If
SQL = SQL & "text_data LIKE '%" & Keyworde & "%' "
iCounter = iCounter + 1
End If
If Keywordf <> "" Then
If iCounter > 0 Then
SQL = SQL & " AND "
End If
SQL = SQL & "text_data LIKE '%" & Keywordf & "%' "
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 LIKE '" & 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 LIKE '" & 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 LIKE '" & spoke & "'"

iCounter = iCounter + 1

end if


'If number <> "" Then

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

If iCounter > 0 Then
SQL = SQL & " AND "
End If
SQL = SQL & "hebbooktable.book 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("bookchapter")="yes" then

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

SQL = SQL & "hebrewbibletable.book & chapter LIKE '%" & number & "%'"

iCounter = iCounter + 1

end if

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

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

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

iCounter = iCounter + 1

end if
If request.QueryString("bookchapterverse")="yes" then

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

SQL = SQL & "hebrewbibletable.book & chapter & verse LIKE '%" & number & "%'"

iCounter = iCounter + 1

end if

If Trim(Request.QueryString("recordType")) <> "" Then
aRecTypes = Split(Request.QueryString("recordType"), ",")
If IsArray(aRecTypes) Then
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 & ")"
iCounter = iCounter + 1
End If

'End If
If hInPut <> "" Then
If iCounter > 0 Then
SQL = SQL & " AND "
End If

SQL = SQL & "text_data LIKE '%" & hInPut & "%'"

iCounter = iCounter + 1

end if

If hOutPut <> "" Then

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

SQL = SQL & "book & chapter & verse LIKE '%" & hOutPut & "%'"

' iCounter = iCounter + 1

end if

SQL = SQL & " ORDER BY id ASC "

[lmf232s]

before you execute your SQL statment do a
respone.write SQL
and see what it displays. then its should be easier to see where the extra AND is coming from and will help you narrow down the problem.

[gilgalbiblewhee]

i always keep that on to see where i go wrong. But I added 1=1 after where and this is what i got:
SELECT * FROM hebbooktable INNER JOIN hebrewbibletable ON hebbooktable.book = hebrewbibletable.book WHERE 1=1 text_data LIKE '%bra%' ORDER BY id ASC

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '1=1 text_data LIKE '%bra%''.

/gemetria/kjvresp5.asp, line 333