Checking existing database record items against listbox items

Question

I have a datatable that I am adding records to in my windows forms application. this datatable only has 2 columns and the first column is the primary key and is an integer. The second column contains names and I need to automatically add a list of names from a listbox to the table. Most of these names will already have their own record in the table but there will be different names in the listbox each time. I need to check the existing record items against the listbox items to make sure no duplicates get added, but if there is a name in the listbox that does not exist in the datatable, add a new record for that name.

What I have tried so far is this:

Private m_cn As New SqlConnection()
Private m_DA As SqlDataAdapter
Private m_CB As SqlCommandBuilder
Private m_DataTable As New DataTable
Private m_IntRowPosition As Integer = 0

Private Sub btnInsertIntoDatabase_Click(sender As Object, e As EventArgs) Handles btnInsertIntoDatabase.Click
    Dim drReadRow As DataRow = m_DataTable.NewRow()
    Dim dcReadCol As DataColumn = m_DataTable.Columns.Item(1)
    Dim intLoopCounter As Integer = 0
    Dim unique As Boolean = True

    If m_DataTable.Rows.Count = 0 Then
        For m_IntRowPosition = 0 To (lstScannedNames.Items.Count() - 1)
            Dim drNewRow As DataRow = m_DataTable.NewRow()
            drNewRow("emp_id") = m_IntRowPosition
            drNewRow("emp_name") = RTrim(lstScannedNames.Items.Item(RTrim(m_IntRowPosition)))

            m_DataTable.Rows.Add(drNewRow)
        Next m_IntRowPosition
        GoTo SomeWhereElse
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ElseIf m_DataTable.Rows.Count > 0 Then

        For m_IntRowPosition = 0 To m_DataTable.Rows.Count
            For intLoopCounter = 0 To lstScannedNames.Items.Count - 1
                If RTrim(m_DataTable.Rows(m_IntRowPosition).Item(1)) = lstScannedNames.Items.Item(intLoopCounter) Then
                    unique = False

                ElseIf RTrim(m_DataTable.Rows(m_IntRowPosition).Item(1)) <> lstScannedNames.Items.Item(intLoopCounter) Then
                    unique = True
                    lstScannedNames.SelectedIndex = intLoopCounter
                    intLoopCounter = lstScannedNames.Items.Count - 1
                End If
            Next intLoopCounter
            If (unique) = True Then
                Dim drNewRow As DataRow = m_DataTable.NewRow()
                drNewRow("emp_id") = m_DataTable.Rows.Count()
                drNewRow("emp_name") = lstScannedNames.Items.Item(lstScannedNames.SelectedIndex)
                m_DataTable.Rows.Add(drNewRow)
            Else
                unique = True
            End If
        Next m_IntRowPosition
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    End If
SomeWhereElse:
    m_DA.Update(m_DataTable)
    MessageBox.Show("Operation Completed")
End Sub

The area of focus is the ElseIf statement. When I run this code and there are already records in the database, it adds the first name that is in the list and not in the datatable, to the datatable. It then adds the first name from the list to a new record, for each item in the list, which is about 20 times but it depends on the list length. I've been trying different things and I know I'm close but I've been stuck for a while.

Steve · Accepted Answer · 2016-07-20T18:50:54.300

0

Reverse your logic. Loop over the items in the listbox and check if they are present in the datatable

....
For intLoopCounter = 0 To lstScannedNames.Items.Count - 1
   Dim name = lstScannedNames.Items.Item(intLoopCounter)
   Dim rowExist = m_DataTable.Select("emp_name = '" & name & "'")
   if rowExist Is Nothing OrElse rowExist.Length = 0 Then
       Dim drNewRow As DataRow = m_DataTable.NewRow()
       drNewRow("emp_id") = m_DataTable.Rows.Count()
       drNewRow("emp_name") = name
       m_DataTable.Rows.Add(drNewRow)
   End If
Next m_IntRowPosition
...

Using the DataTable.Select method avoids to write an explicit loop to find the duplicate. And you can directly add the row when you are certain that there is no duplicate

edited Jul 20 '16 at 18:50

answered Jul 20 '16 at 18:00

Steve

213,761
22
232
286

I'm gunna try it out now, I'll mark this as the answer after I get it to work. Thank you though! – Lacuna Jul 20 '16 at 18:09
I had to change `IsNothing` to `Is Nothing` to avoid converting type `DataRow` to type `Boolean` since it was giving me an error. Not sure what the difference is between them but it works exactly as expected. Thanks a bunch – Lacuna Jul 20 '16 at 18:31
Corrected, my bad. See here the difference http://stackoverflow.com/questions/5791/vb-net-isnothing-versus-is-nothing – Steve Jul 20 '16 at 18:51

Checking existing database record items against listbox items

1 Answers1