How to store multiple selection?

[sara]

Hi..
I've problem here.
I have checkbox chkdrugnegative..user can select more than one drug name.
my problem is how to insert to database. how the table. i'm using pl/sql.
here is my sample coding.

Private Function adddrugtest() As Integer

Dim oleConn As OleDb.OleDbConnection
Dim roleData As OleDb.OleDbDataAdapter
Dim oleCmd As OleDb.OleDbCommand
Dim recSet As New DataSet()
Dim connString As New ConnectionString()
Dim strCon As String
Dim strSQL As String
Dim strSQL2 As String
Dim int_row_insert As Integer
Dim nexttestIdSeq As Integer
Dim clsUtility As New Utility()

strCon = connString.connPMO

oleCmd = New OleDb.OleDbCommand("", New OleDbConnection(strCon))
oleCmd.CommandType = CommandType.Text
oleCmd.Connection.Open()

nexttestIdSeq = Me.getMaxtestID + 1

'strSQL = " INSERT INTO PMO_ALCOHOL_TEST " _
' & " ( TEST_ID, DATE_TEST, SITE_TESTING, RESULT, SUPERVISOR, REMARK, DATE_CREATED ) " _
' & " VALUES " _
' & " (" & nexttestIdSeq & ", '" & txtdate.Text & "', " & cbosite.SelectedItem.Value & ", " _
' & " '" & rdbresult.SelectedItem.Text & "', '" & txtsupervisor.Text & "', '" & txtremark.Text & "', sysdate )"

strSQL = " INSERT INTO PMO_DRUG_TEST_MASTER " _
& " ( TEST_ID, DATE_TEST, SITE_TEST, RESULT, TYPE_DRUG, " _
& " TYPE_DRUG_TEST_POST, CONFIRM_DATE_TEST, CONFIRM_RESULT, " _
& " CONFIRM_TYPE_DRUG_POST, SUPERVISOR, REMARK, staff_name, staff_no, " _
& " Department, section, position, " _
& " USER_ASSIGN_NO, USER_ASSIGN_NAME, DATE_CREATED) " _
& " VALUES " _
& " (" & nexttestIdSeq & ", '" & txtdate.Text & "', " & cbosite.SelectedItem.Value & ", '" & rdbfirstresult.SelectedItem.Text & "', " _
& " '" & chkdrugnegative.SelectedItem.Text & "', " _
& " '" & chkseconddrug.SelectedItem.Text & "', '" & txtconfirmatorydate.Text & "', " _
& " '" & rdbsecondresult.SelectedItem.Text & "', '" & chkconfdrugname.SelectedItem.Text & "', " _
& " '" & txtsupervisor.Text & "', '" & txtremark.Text & "', " _
& " '" & lblstaffname.Text & "', '" & lblstaffno.Text & "', '" & lbldepartment.Text & "', '" & lblSection.Text & "', '" & lblposition.Text & "', " _
& " '" & CStr(Session("loginstaffno")) & "', '" & clsUtility.replaceSingleQuote(CStr(Session("loginstaffname"))) & "', sysdate) "

strSQL2 = " INSERT INTO PMO_FIRST_TEST_DRUG " _
& " ( TEST_ID, STAFF_NO, STAFF_NAME, DRUG_NAME, FIRST_RESULT )" _
& " VALUES " _
& " ( " & nexttestIdSeq & ", '" & lblstaffno.Text & "', '" & lblstaffname.Text & "', " _
& " '" & chkdrugnegative.SelectedItem.Text & "', '" & rdbfirstresult.SelectedItem.Text & "')"

With oleCmd
Try
If (.Connection.State <> ConnectionState.Open) Then
.Connection.Open()
End If

.CommandText = strSQL
int_row_insert = .ExecuteNonQuery()
Catch ex As OleDb.OleDbException 'Exception
'if error occurs during transaction
txtdate.Text = ex.Message
txtsupervisor.Text = ex.Message
txtremark.Text = ex.Message

Finally
If Not .Connection Is Nothing Then
.Connection.Close()
.Connection.Dispose()
End If
.Dispose()
End Try
End With

adddrugtest = int_row_insert

End Function

[ESHbyESH]

Don't know if this is resolved but...
Can you indicate the declaration code (from " Windows Form Designer generated code ") for the chkdrugnegative?
MY checkboxes don't have the property "SelectedItem".....

[sara]

i use checkboxlist. checkboxlist have selectedItem

[kapil.goyal]

i think tat u hav to use loop to insert multiple rows into database.

[sara]

i think tat u hav to use loop to insert multiple rows into database.

Can u give me sample coding the loop..