Multiple Selection

[jue]

HI..
I've problem here.
I have chkdrugname where is user can select this drug name more than one. my problem is how i insert into database. And how the database look like. i use PL/SQL as my database.

here 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

please everyone outhere help me