How to get rid of this comma!!?!

gilgalbiblewheel · August 2nd, 2005, 02:22 PM

THe code is this:

Code:

If Trim(Request.QueryString("id")) <> "" Then
        myarray = split(id," ")
        whereclause=" id = " & myarray(0)
        for counter=1 to ubound(myarray)
            whereclause = whereclause & " OR id = " & myarray(counter)
        next
     SQL = SQL & whereclause
     iCounter = iCounter + 1
     End If

It gives this error:
[QUOTE]
SELECT * FROM bible WHERE id = 1, OR id = 2

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression 'id = 1, OR id = 2'.
[\QUOTE]

How do you get rid of this comma?

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm

ChrisScott · August 3rd, 2005, 03:20 AM

I guess the comma must be in the querystring value you are splitting to create the array, try splitting by comma:

Code:

myarray = split(id,",")

HTH,

Chris

gilgalbiblewheel · August 3rd, 2005, 12:21 PM

To give more details, I believe that the reason that it didn't pick up the 2nd id was because it didn't recognize the 2nd selected checkbox. Here is the question/response's question page. It picked up the first checkbox but not the 2nd.

Code:

<%
Do until RS.eof
Dim strText, strFind
%>
<input type="checkbox" value="<%=RS("id")%>" name="id"><br/>
<%
Response.Write("<textarea ")
response.write("name=" & chr(34) & "Keywordh" & RS("id") & chr(34))
response.write(" rows=" & chr(34) &  "3" & chr(34))
response.write(" cols=" & chr(34) & "64" & chr(34) & ">")
response.write("</textarea>")
rs.movenext

-------
This is what I did:

Code:

    For each x in myarray

SQL= "SELECT * "
SQL = SQL & " FROM bible WHERE "

If Trim(Request.QueryString("id")) <> "" Then
        myarray = split(id," ")
        whereclause=" id = " & x
        for counter=1 to ubound(myarray)
'            whereclause = whereclause & " OR id = " & myarray(counter)
'        whereclause = left(whereclause,len(whereclause)-1) & " OR id = " & myarray(counter)
        next
     SQL = SQL & whereclause
     iCounter = iCounter + 1
     End If
next

This is what I got:

Quote:

quote:Microsoft VBScript runtime error '800a01c3'

Object not a collection

and points to
For each x in myarray

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm

rstelma · August 4th, 2005, 11:33 AM

Hi,

This works:

<%
    id = "223, 224, 225"

    SQL= "SELECT * "
    SQL = SQL & " FROM bible WHERE "

    If id <> "" Then
            myarray = split(id,",")
            for i=0 to ubound(myarray)
                Response.Write Trim(myarray(i)) & "<br>"
                whereclause = whereclause + " id = " & Trim(myarray(i)) & " Or "
            next
         SQL = SQL & whereclause
    End If

    SQL = SQL & " 1 = 1;"

    Response.Write "This is the SQL statement: " & SQL
%>

gilgalbiblewheel · August 4th, 2005, 03:54 PM

This is what I got which raises up a few questions:

Quote:

quote:
223
224
225
This is the SQL statement: SELECT * FROM bible WHERE id = 223 Or id = 224 Or id = 225 Or 1 = 1;
31102 verses have been selected. .

In the beginning God created the heaven and the earth.

The last phrase is id = 1 but not the id I selected, which explains because your code is not meant to search it. It serves as an example.

But it says 31102 verses have been selected. Maybe the code for that phrase is wrong too.

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm

gilgalbiblewheel · August 4th, 2005, 03:59 PM

Ok if I replace

Code:

'    id = "23, 24, 25"

with

Code:

    id = Request.QueryString("id")

I get my selection (2) and what is automatically in there ( id =1 which I need to get rid of which creates the issue that if I select multiple records will it show all the selected records in the response page? That's what I need which didn't happen in this case):

Quote:

quote:2

Quote:

This is the SQL statement: SELECT * FROM bible WHERE id = 2 Or 1 = 1;
31102 verses have been selected. .

In the beginning God created the heaven and the earth.

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm

gilgalbiblewheel · August 4th, 2005, 04:09 PM

Ok let me update:

Code:

    id = Request.QueryString("id")
    SQL= "SELECT * "
    SQL = SQL & " FROM bible WHERE "

    If id <> "" Then
            myarray = split(id,",")
                whereclause = whereclause + " id = " & Trim(myarray(i)) '& " Or "
                i=i+1
            for i=1 to ubound(myarray)
                Response.Write Trim(myarray(i)) & "<br>"
                whereclause = whereclause + " OR id = " & Trim(myarray(i)) '& " Or "
            next
         SQL = SQL & whereclause
    End If

'    SQL = SQL & " 1 = 1;"

    Response.Write "This is the SQL statement: " & SQL
response.Write "<br/>"

If I make 1 selection I get it right:

Quote:

quote:This is the SQL statement: SELECT * FROM bible WHERE id = 3

Quote:

1 verses have been selected. .

And God said, Let there be light: and there was light.

But if I make a multiple selection I get the first selection on the list:

Quote:

quote:

Quote:

3
5
7
This is the SQL statement: SELECT * FROM bible WHERE id = 1 OR id = 3 OR id = 5 OR id = 7
4 verses have been selected. .

In the beginning God created the heaven and the earth.

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm

rstelma · August 4th, 2005, 05:53 PM

Can you post the code for your form?

And also write the value from your Request.QueryString so we can see exactly what's being posted.

gilgalbiblewheel · August 4th, 2005, 07:42 PM

arrayresp.asp

Code:

<% 'Option Explicit
Response.Buffer=false%>
<%
Const DB_NAME           = "kjv.mdb" ' Name of our database file

Const RECORDS_PER_PAGE  = 10            ' Number of records per page

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

Private Function GetConnectionString()
    GetConnectionString =   "Driver={Microsoft Access Driver (*.mdb)};" & _
                "DBQ=" & Server.MapPath(DB_NAME) & ";" & _
                "UID=;PWD=;"
End Function
Set Conn = server.createobject("ADODB.Connection")
Conn.open GetConnectionString

'http://www.access-programmers.co.uk/forums/showthread.php?p=403692#post403692
    Dim strConn     ' Database connection string
    Dim SQL         ' String that will have our SQL statments
    Dim RS          ' Recordset object

'pageing
    Dim nRecCount   ' Number of records found
    Dim nPageCount  ' Number of pages of records we have
    Dim nPage       ' Current page number


Dim myarray, whereclause


Dim id
Dim Keywordh

    ' Let's see what user wants to search for today :)

    id = Request.QueryString("id")
    Keywordh = Trim(Request.QueryString("Keywordh"))

'If instr(request("id"),",") then myarray = split(request("id"),",")


'    id = "23, 24, 25"

    SQL= "SELECT * "
    SQL = SQL & " FROM bible WHERE "

    If id <> "" Then
            myarray = split(id,",")
                whereclause = whereclause + " id = " & Trim(myarray(i)) '& " Or "
                i=i+1
            for i=1 to ubound(myarray)
                Response.Write Trim(myarray(i)) & "<br>"
                whereclause = whereclause + " OR id = " & Trim(myarray(i)) '& " Or "
            next
         SQL = SQL & whereclause
    End If

'    SQL = SQL & " 1 = 1;"

    Response.Write "This is the SQL statement: " & SQL


response.Write "<br/>"


set RS = Server.CreateObject("ADODB.Recordset")
rs.PageSize= RECORDS_PER_PAGE
rs.CursorLocation = adUseClient
rs.CacheSize = 20
RS.Open SQL, Conn, adOpenForwardOnly, adLockReadOnly


rscount=rs.RecordCount
rspage=rs.PageCount

if request.querystring("page")="" then 
   page=1
else
   page=cint((request.querystring("page")))
end if

If Not rs.EOF Then
Response.Write "<b>" & rs.RecordCount &_
"</b> verses have been selected. "



Response.Write ".</p>"
End if%>

<%response.Write rs("text_data") & "</br>"%>
<% rs.Close()%>

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm

gilgalbiblewheel · August 4th, 2005, 07:53 PM

I think the red section would concern
<%
    OPTION EXPLICIT
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>show an individual verse</title>
</head>
<body>

<%

'This function checks to see that numerical values are valid
Function ValidateInt(SomeInt)
    if IsNumeric(SomeInt) = False or SomeInt = "" then
        SomeInt = 1
    end if

    ValidateInt = SomeInt
End Function

Function Highlight(strText, strFind, strBefore, strAfter)
  Dim nPos
  Dim nLen
  Dim nLenAll

  nLen = Len(strFind)
  nLenAll = nLen + Len(strBefore) + Len(strAfter) + 1

  Highlight = strText

  If nLen > 0 And Len(Highlight) > 0 Then
    nPos = InStr(1, Highlight, strFind, 1)
    Do While nPos > 0
      Highlight = Left(Highlight, nPos - 1) & _
        strBefore & Mid(Highlight, nPos, nLen) & strAfter &_
         Mid(Highlight, nPos + nLen)

      nPos = InStr(nPos + nLenAll, Highlight, strFind, 1)
    Loop
  End If
End Function

'Setting up search variables
Dim Book
Dim Chapter
Dim Verse
Dim myVerses
'Dim TextData
Dim Keyword
Dim Keywordb
Dim Keywordc
Dim Keywordd
Dim Keyworde
Dim Keywordf

Book = ValidateInt(request("Book"))
Chapter = ValidateInt(request("Chapter"))
Verse = request("Verse")
    if Verse = "" or IsNumeric(replace(Verse,", ","")) = False then
        myVerses = ""
    else
        myVerses = replace(verse,", ", " OR Verse = ")
        myVerses = "AND (Verse = " & myVerses & ")"
    end if

Keyword = request("Keyword")
Keywordb = request("Keywordb")
Keywordc = request("Keywordc")
Keywordd = request("Keywordd")
Keyworde = request("Keyworde")
Keywordf = request("Keywordf")

'Setting up my database variables
Dim DSNName
DSNName = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
DSNName = DSNName & Server.MapPath("kjv.mdb")
DSNName = DSNName & ";PWD=" & "mypass"

Dim Conn
Dim RS
Dim SQL1

'Opening Database connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open DSNName

set RS = Server.CreateObject("ADODB.recordset")

            'This SQL statement creates a list of books
            SQL1 = "Select * FROM Bible WHERE Book = " & Book & " AND Chapter = " & Chapter & " " & myVerses & "ORDER BY Verse ASC"
            rs.Open sql1,conn, 1
                if not rs.eof then
%>

<%

If Keyword <> "" then
Response.Write "Looking for <b>" & request("Keyword") & "</b> <b>" & request("Keywordb") & "</b> <b>" & request("Keywordc") & "</b> <b>" & request("Keywordd") & "</b> <b>" & request("Keyworde") & "</b> <b>" & request("Keywordf") & "</b>"
End If
                %>
                <form name="kjbible" action="arrayresp.asp" method="get">
                              <h2><%=rs("Book_Title")%> <%=RS("Chapter")% ></h2>
<input type="submit" value=" Search " size="100" title="Search everything selected">
                <%
                    Do until RS.eof

Dim strText, strFind
                        %>
                        <p><sup><%=RS("Verse")%></sup>

<input type="checkbox" value="<%=RS("id")%>" name="id"><br/>
<%
Response.Write("<textarea name='" & "Keywordh" & RS("id") & "' rows='3' cols='64'>")
strFind=Request("Keyword")
strText=rs("text_data")
'some things taken out to avoid confusion but I like to keep the strText
Response.Write strText
response.write("</textarea>")

                        rs.movenext
                        if rs.eof then
                            exit do
                        end if
                    Loop
                else
                    response.write "No verses found"
                end if
            rs.close
            conn.close
'response.write "Verse = " & Verse
'response.write "<br>myVerses = " & myVerses
%>
</form>
</body>
</html>

Martial Law 9/11 Rise of the Police State is now available! Visit our Martial Law movie section for complete info (click here), or order now by clicking the button below or by calling 888-253-3139
http://www.infowars.com/martial_law_911.htm