How to get rid of this comma!!?!

THe code is this:

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

Code (markup):

It gives this error:

 

could you post all of your code in question together as one chunk? I'm a little confused by the post. I think i follow what you're saying... but i'm not clear.

Also, if you would, include your full error message. thanks

 

Yea, you lost me after the second quote...

 

Part of the question page. THis has a checkbox next to every textarea. WIthin the textarea there is a rs("text_data"). My first step is to write down the selected rs("text_data") after on the response page. I'm having difficulty doing that.

<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) & ">")

strText=rs("text_data")

Response.Write strText
response.write("</textarea>")
rs.movenext

Code (markup):

Response page:

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

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

    Dim strConn     ' Database connection string
    Dim SQL         ' String that will have our SQL statments
    Dim RS          ' Recordset object

Dim myarray, whereclause

Dim id
Dim Keywordh

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


myarray = split(id," ")
If Trim(Request.QueryString("id")) <> "" Then
For each x in myarray

SQL= "SELECT * "
SQL = SQL & " FROM bible WHERE "
        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 & left(whereclause,len(whereclause)-1)
     iCounter = iCounter + 1

response.write sql
response.Write "<br/>"
next
End If

'response.Write rs("text_data")

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"
End If
Response.Write ".</p>"
 
'Response.Write "There are " & rs.PageCount & " page(s) of result(s).<br>"
'Response.Write "The current page is " & Page & ".<p>"
End if%>
<%'<!--#include file=pagingrecordsets.asp-->
'If rs.BOF and rs.EOF Then%>
<%'response.Write rs("text_data")%>
<%'<!--#include file=pagingrecordsets.asp-->
'End If%>
<%response.Write rs("text_data") & "</br>"%>
<% rs.Close()%>

Code (markup):

 

First, let me suggest getting rid of the chr(34) and just using a single quote ' . It makes the code much easier to read, easer to write and the browser will understand just fine.

Ok, now, in looking at the rest of your code. The split on the query string should be based on a comma:

myarray = split(id,",")

But first you should test for the presence of any commas to determine if you even have an array.

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

Now, the sql string is a mess. What is this supposed to be?:

whereclause=" id = " & x

----------------

Let's start with this and see where we need to go from here.

 

i think i follow you, try splitting your array as mopacfan suggests, and try something like this to build your sql statement:

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

dim newcounter

newcounter = 0
For each x in myarray
if newcounter =  0 then 
whereclause=" id = " & x
else
whereclause = whereclause & " OR id = " & x
end if

     
     newcounter = 1
next
SQL = SQL & whereclause

Code (markup):

In your code, you're essentially re-defining your whereclause with this line

whereclause=" id = " & x

Code (markup):

each iteration through the for...next loop. you need something to tell it to either start the where clause, or concatenate add'l info onto the end of it.


VG

 

Oh, no you don't have to do that. Try this for example

response.write "<input type=""hidden"" value=""MyVal"">"

Code (markup):

Which will produce:

<input type="hidden" value="MyVal">

Code (markup):

This only gets confusing when using it in the beginning or the end or when putting one between some other text.

string = "this is """ & "really" & """ confusing, kinda like" & """" & "this" & """" 

Code (markup):

 

i """"hate""""" quotes. ugh.

 

THis thing seems too complicated. All I want to do at this point is to select from the question page certain checkboxes and have them rewritten and what you guys suggested didn't work. I don't know if I did it properly or not though.

Perrow what's the hidden-type supposed to do?

 

Serve as an example of how to write quotes from response.write, nothing to do with your original problem.

You said that you actually got a query that worked (ie no runtime error) but did not return the correct result, what I do when that happens is open up access or SQL servers administration console and run the query from there (print the query that gets created and copy paste it into the DB program). You usually get much better feedback and its much faster to change parts of the query to see how it should look. Then you can go back and change your program 'til it produces a matching query

 

Sorry man, we're trying can you post your entire form tag, and your querystring that it generates?

 

<input type="checkbox" value="<%=RS("id")%>" name="id"><br/>
<%
Response.Write("<textarea name='" & "Keywordh" & RS("id") & "' rows='3' cols='64'>")
Response.Write rs("text_data")
response.write("</textarea>")

Code (markup):

Response page:

...myarray = split(id," ")
If Trim(Request.QueryString("id")) <> "" Then
For each x in myarray

SQL= "SELECT * "
SQL = SQL & " FROM bible WHERE "
        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 & left(whereclause,len(whereclause)-1)
     iCounter = iCounter + 1

response.write sql
response.Write "<br/>"
next
End If...

Code (markup):

 

You should be able to create your whereclause using the join function which does the reverse of what the split function does.

whereclause = "WHERE id=" & join(myarray, " or id=")

Code (markup):

 

Ok let me update:

    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/>"

Code (markup):

If I make 1 selection I get it right:

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

 

Ok now, what's wrong with this?

<td>
<%
TheString = rs("text_data")
ArrayTemp = split(TheString, " ")
NumberOfWords = UBound(ArrayTemp) + 1

Response.Write "<P><i>Number of words:</i>" & NumberOfWords & "</br>"
i=0

   For Each Word In ArrayTemp
   Response.Write "<input type=""checkbox"" value=""" & Trim(ArrayTemp(i)) & """ name=""Keywordh""><font size=""2"" face=""Verdana"">" & word & "</font></br>"
i=i+1
  next
  Response.Write "</br>"

%>
</td>

Code (markup):

response page:

     If Keywordh <> "" Then
            ArrayTemp = split(Keywordh," ")
                whereclause = whereclause + " text_data LIKE '" & Trim(ArrayTemp(0)) & "'"
                i=i+1
            for i=1 to ubound(ArrayTemp)
                whereclause = whereclause + " AND text_data LIKE '" & Trim(ArrayTemp(i)) & "'"
            next
         SQL = SQL & whereclause
    End If

Code (markup):

It's able to search:

but says

 

I think the like clauses needs to be "... like '%after%' and ..." to match text before and after the search phrase, again searching directly in the DBA should tell you more .

 

try this and see if it works.

     If Keywordh <> "" Then
            ArrayTemp = split(Keywordh," ")
ArrayTemp = replace(ArrayTemp(0), ",", "")

                whereclause = whereclause + " text_data LIKE '%" & ArrayTemp  & "%'"
                i=i+1
            for i=1 to ubound(ArrayTemp)
ArrayTemp = replace(ArrayTemp(i), ",", "")
                whereclause = whereclause + " AND text_data LIKE '%" & ArrayTemp & "%'"
            next
         SQL = SQL & whereclause
    End If

Code (markup):