Member Avatar for 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

  • 3 Contributors
  • 4 Replies
  • 186 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by sara

Dani AI

Generated

The root cause is that a CheckBoxList can hold multiple selections but reading SelectedItem only gives a single ListItem (or the first selected item). As hinted and suggested, the solution is to treat the master/detail design correctly (one PMO_DRUG_TEST_MASTER row per test) and insert one PMO_FIRST_TEST_DRUG row per selected drug by looping the CheckBoxList.Items and checking each ListItem.Selected. Use a single DB transaction, parameterized commands, and a reliable source for TEST_ID (Oracle sequence or INSERT ... RETURNING) instead of calculating max+1 to avoid race conditions.

Example pattern (VB.NET, simplified) — insert master, then loop selected items and execute a parameterized child INSERT inside the same transaction:

' open connection, begin transaction
' get testId from sequence (or INSERT ... RETURNING)
' insert master with parameters
Using childCmd As New OleDbCommand("INSERT INTO PMO_FIRST_TEST_DRUG (TEST_ID, STAFF_NO, STAFF_NAME, DRUG_NAME, FIRST_RESULT) VALUES (?, ?, ?, ?, ?)", conn, trans)
    childCmd.Parameters.AddWithValue("p1", testId)
    childCmd.Parameters.AddWithValue("p2", lblstaffno.Text)
    childCmd.Parameters.AddWithValue("p3", lblstaffname.Text)
    childCmd.Parameters.Add("p4", OleDbType.VarChar)
    childCmd.Parameters.Add("p5", OleDbType.VarChar)

    For Each li As ListItem In chkdrugnegative.Items
        If li.Selected Then
            childCmd.Parameters("p4").Value = li.Text
            childCmd.Parameters("p5").Value = rdbfirstresult.SelectedItem.Text
            childCmd.ExecuteNonQuery()
        End If
    Next
End Using
' commit transaction

Key cautions and tips:

  • Always use parameters (no string concatenation) to avoid SQL injection and date/format bugs.
  • Wrap master + detail inserts in one transaction so partial writes do not occur on error.
  • Prefer an Oracle data provider (OracleCommand with named parameters) for native features like RETURNING.
  • If no child rows are inserted, make sure the CheckBoxList control is the expected server control and that selections are preserved on postback.
Member Avatar for 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".....

Member Avatar for sara

i use checkboxlist. checkboxlist have selectedItem

Member Avatar for kapil.goyal

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

Member Avatar for sara

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

Can u give me sample coding the loop..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.