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
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