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
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):
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
<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):