Help with invalid SQL query statement

allranger
12-19-2003, 07:38 PM
O.k. Here is my code. I am sorry that it looks so crappy. I am rather new to this all and have had a friend helping me.

Public Function ChapterOutput(bCount As Integer, cCount As Integer, vCount As Integer)
Dim x As Integer
Dim y As Integer
Dim z As Integer

Dim rsDisplay As ADODB.Recordset

Set rsDisplay = New ADODB.Recordset

Open AppPath & BookMap(bCount) & align(cCount, 3, "RIGHT", 0) & ".html" For Output As #1

Print #1, "<html>"
Print #1, "<head>"
Print #1, "<title>" & BookMap(bCount) & " - Chapter " & cCount & "</title>"
Print #1, "</head>"
Print #1, "<body>"
Print #1,

strSQL = "Select * from Bible where trim$(BookID) = '" & Trim$(bCount) & _
"' and Trim$(Chapter)= '" & Trim$(cCount) & "' order by Verse"

rsDisplay.Open strSQL, conTree, adOpenStatic, adLockOptimistic

If rsDisplay.RecordCount <> vCount Then
lstErrors.AddItem("Error may have occured in book " & bCount & _
" chapter " & cCount & ". There are " & rsDisplay.RecordCount & _
" verses present out of " & vCount & " verses.")
End If

If rsDisplay.RecordCount = 0 Then
Set rsDisplay = Nothing
Close #1
Kill (AppPath & BookMap(bCount) & align(cCount, 3, "RIGHT", 0) & ".html")
Exit Function
End If

If rsDisplay.RecordCount <> 0 Then
While Not rsDisplay.EOF
Print #1, "<p>" & Chr(60) + Chr(97) + Chr(32) + Chr(110) + Chr(97) + Chr(109) + _
Chr(101) + Chr(61) + Chr(34) & rsDisplay.Fields("Verse") & Chr(34) + _
Chr(62) + Chr(60) + Chr(47) + Chr(97) + Chr(62) & rsDisplay.Fields("Chapter") & _
":" & rsDisplay.Fields("Verse") & " " & rsDisplay.Fields("Scripture") & "</p>"
rsDisplay.MoveNext
Wend
End If

Set rsDisplay = Nothing

Print #1, "</body>"
Print #1, "</html>"
Close #1

End Function

The Function is part of an html program that I am making out put a bible in a microsoft database to web page. The problem is with this statement:

strSQL = "Select * from Bible where trim$(Book ID) = '" & Trim$(bCount) & _
"' and Trim$(Chapter)= '" & Trim$(cCount) & "' order by Verse"

The Table name is "Bible" and the Field Name is "Book ID" I have tried using [Book ID] but that does not work. I have tried searching for some info but don't really know what I am asking for so I'm sorry if I am asking a repeat question.

tia

reboot
12-19-2003, 08:21 PM
You don't say what database this is. SQL Server has no Trim function, only RTrim and LTrim. If you're using Access, it may be the same way. Try thisstrSQL = "Select * from Bible where LTrim(RTrim([Book ID])) = '" & Trim$(bCount) & _
"' and LTrim(RTrim(Chapter)) = '" & Trim$(cCount) & "' order by Verse"

allranger
12-19-2003, 08:31 PM
Thank you very much. I knew it was just some stupid thing that I didn't understand. It worked perfectly after I added the ltrim and rtrim.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum