Display multiple records with checkbox

[leeny]

i get an error when I'm trying to display multiple records from a database by selecting from the checkbox.

If Request("Go")="Go" then 

i = request.form("item")

Dim strSelection,arrSelection
strSelection = request.form("opt")

'convert strSelection to String
strSelection = CStr(strSelection)
arrSelection = Split(strSelection, ", ")'return array of sub strings

Dim i, strQuery
strQuery = "Select * from ABSSubGrade WHERE"

For i = 0 to UBOUND(arrSelection)
If i > 0 Then
strQuery = StrQuery & " OR Item = '" & arrSelection(i) & "' ORDER by Item,ABSID"
Else
strQuery = strQuery  & " Item = '" & arrSelection(i) & "'"
End if
Next
 
sql31 = "Select * from ABS order by ABSID"
Set Rs31 = Server.CreateObject("ADODB.RecordSet")
Rs31.open sql31,conn

Set Rs32 = Server.CreateObject("ADODB.RecordSet")
'Rs32.open strQuery,conn
Response.write strQuery
%>

This works fine when i select two checkboxes, however when i select more than two, error occurs. please assis me.

i printed my sql statement and this is how it looks:

Select * from ABSSubGrade WHERE Item = 'S501 G10' OR Item = 'S501 G30' ORDER by Item,ABSID OR Item = 'S505 G10' ORDER by Item,ABSID

[russell]

strSelection = request.form("opt")

strQuery = "Select * from ABSSubGrade WHERE item in(" & strSelection & ")"

[leeny]

i still get an error

this is how my sql statement looks like

Select * from ABSSubGrade WHERE item in(S501 G10, S501 G30) Item = 'S501 G10' OR Item = 'S501 G30' ORDER by Item,ABSID

[russell]

Dim strCriteria
Dim strSelection
Dim ar

strSelection = request.form("opt")
ar = Split(strSelection, ",")

For i = 0 to ubound(ar)
  strCriteria = "'" & ar(i) & "',"
Next

'' strip off the last comma
strCriteria = left(strCriteria, len(strCriteria) -1)

strQuery = "Select * from ABSSubGrade WHERE item in(" & strCriteria & ")"

[leeny]

i followed your instructions but there's a new error now that says

ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/nz/ABS.asp, line 0

Dim strCriteria
Dim strSelection
Dim ar

strSelection = request.form("opt")
ar = Split(strSelection, ",")

For i = 0 to ubound(ar)
  strCriteria = "'" & ar(i) & "',"
Next

'' strip off the last comma
strCriteria = left(strCriteria, len(strCriteria) -1)

strQuery = "Select * from ABSSubGrade WHERE item in(" & strCriteria & ")"

 
sql31 = "Select * from ABS order by ABSID"
Set Rs31 = Server.CreateObject("ADODB.RecordSet")
Rs31.open sql31,conn

Set Rs32 = Server.CreateObject("ADODB.RecordSet")
Rs32.open strQuery,conn
Response.write strQuery

my sql statement looks like this

Select * from ABSSubGrade WHERE item in(' S521 G30')

[russell]

there's more to it than what u posted. but 1st, try chnaging this:

For i = 0 to ubound(ar)
  strCriteria = "'" & ar(i) & "',"
Next

to

For i = 0 to ubound(ar)
  strCriteria = "'" & trim(ar(i)) & "',"
Next

the error means that u are trying to access the records in the recordset, but none were returned.

what happens when u directly execute the query against the db? what do u want the query to look like. when doing something like this, figure out what the query should be first, then use asp to build that query.