More than one checkbox checked and data to be retrieved from sql database

[elizann10]

i am not getting the information from the recordset when more than one checkbox is ticked. Could someone please advise,
Thankyou


Function IsChecked(check)
Dim nChecked
If (check<> " ") Then
nChecked = True
Else
nChecked = False

End If
IsChecked = nChecked
End Function
If request("submit")="SUBMIT" then
'declarationiof variables
Dim i, check, myArray, where_clause
'obtaining values from form
check = Request("checkbox")
myArray = Split(check,", ")
where_clause = " "
For i = 0 To UBound(myArray)
if(IsChecked(check) = True) then
if i > 0 then
where_clause = "acct_code = " &"'"&myArray(i)&"'"& " "&"AND"& " "&where_clause
else
where_clause = "acct_code = " &"'"&myArray(i)&"'"
'response.write(myArray(i))
end if
else
if check = "" then
Response.Write(" was NOT checked<br>")
end if
'response.write(where_clause & "<br>")
end if
Next
qry = "Select name, email, phone, fax, acct_code from acctstable WHERE "& where_clause &""
response.Write(qry)
Set oRs= server.CreateObject("adodb.recordset")

dim pagenum, page
'pagecnt=oRs.PageCount
oRs.Pagesize=50
oRs.CursorLocation = 3
'oRs.Locktype = 3
oRs.CursorType = 3
oRs.open qry, oConn, 3, 3

[siteguru]

What are you expecting to happen? Your SQL suggests "give me results where account code equals X and account code equals Y". I suspect the AND should be OR.

[elizann10]

I have tried Or but it only brings up the results for one acct_code. I want to bring up the results for more than one acct_code if several are checked on the previous page.

Thanks

[siteguru]

Think about how the records are actually stored in your database. Can ONE record have MORE THAN ONE account code? (I suspect not, that why I say OR is probably needed).

If you do Response.End right after response.Write(qry) what do you see in the browser? (This will indicate if your SQL query is well-formed).

[itHighway]

Okay. First print Request.Form("Checkbox") values to see if all values are properly passed.
In this type of cases this is what I do:

txtCheckBox = request.Form("CheckBox")

'#remove space
txtCheckBox = replace(txtCheckBox, " ", "")

'#if database table field is a text field
txtCheckBox = "'"& replace(txtCheckBox, ",", "','") &"'"


'#Sql Query
Where_Clause = " Account_Code IN ("&txtCheckBox&") "

[elizann10]

Thanks I think i have gotten it now. I needed another for loop under the if i>0
for t= 1 to i

where_clause = where_clause = "acct_code = " &"'"&myArray(i)&"'"& " "&"OR"& " "&where_clause
Next