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
