VB.NET SQL Update Table Using Listbox As Identifier

Question

Hi I'm just learning how handle SQL statements using vb.net. My problem is how do I update my table using the items in listbox as an identifier. My SQL query is working. update tblBillingSched set Status = 'paid' where BillNum = 'MA5'

Here's the revised code:

    Private Sub btnPostAdvancedPayment_Click(sender As Object, e As EventArgs) Handles btnPostAdvancedPayment.Click

    Dim connection_string As String = "Data Source=.\sqlexpress;Initial Catalog=CreditAndCollection;Integrated Security=True"
    Dim connection As New SqlConnection(connection_string)
    connection.Open()
    Dim SQLCmd As SqlCommand
    Dim sSQL As String = "UPDATE tblBillingSched SET Status = 'Paid' WHERE BillNum = "
    If MessageBox.Show("Continue to Save?", " ", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = DialogResult.OK Then
        Dim firstTime As Boolean = True
        For Each item In lstBillNum.Items
            If (item IsNot Nothing AndAlso item.ToString().Trim().Length > 0) Then
                If (firstTime) Then
                    firstTime = False
                Else
                    sSQL = sSQL & " OR BillNum = "
                End If
                sSQL = sSQL & "'" & item.ToString() & "'"
            End If
        Next
        SQLCmd = New SqlCommand(sSQL, Connection)
        SQLCmd.ExecuteNonQuery()

        MessageBox.Show("Client Record Successfully Saved!", " ", MessageBoxButtons.OK, MessageBoxIcon.Information)

        connection.Close()
        SQLCmd.Dispose()
    ElseIf DialogResult.Cancel Then
        MessageBox.Show("Saving Cancelled!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If
End Sub

Answer 1

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim dt as date = CDate(DateTime.Today).ToString("MM/dd/yyyy")
        Try
            Dim cn As New OleDb.OleDbConnection
            cn.ConnectionString = GetConnectionStringByName("FD_project.My.MySettings.fixedepoConnectionString")
             Dim sSQL As String = "UPDATE fixdeporeg SET approval = 'A', a_user = 'CFO', " & _
                                                        "app_date = # " & dt & "# WHERE slno = "
            If MessageBox.Show("Continue to Save?", " ", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = DialogResult.OK Then
                Dim firstTime As Boolean = True
                For x As Integer = 0 To CheckedListBox1.CheckedItems.Count - 1
                    If (firstTime) Then
                        firstTime = False
                    Else
                        sSQL = sSQL & " OR slno = "
                    End If
                    'sSQL = sSQL & "'" & item.ToString() & "'"
                    sSQL = sSQL & CheckedListBox1.CheckedItems(x).item("slno")
                Next
                Dim SQLCmd As OleDbCommand = New OleDbCommand(sSQL, cn)
                MessageBox.Show(sSQL)
                cn.Open()
                'dt = cmd1.ExecuteScalar()
                SQLCmd.ExecuteNonQuery()
                cn.Close()
            ElseIf DialogResult.Cancel Then
                MessageBox.Show("Saving Cancelled!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

Answer 2

I understand that you are after a loop on these lines:

Dim sSQL As String = "UPDATE tblBillingSched SET Status = 'paid' WHERE BillNum = "

Dim firstTime As Boolean = True
For Each item In lstBillNum.Items
    If (item IsNot Nothing AndAlso item.ToString().Trim().Length > 0) Then
        If (firstTime) Then
            firstTime = False
        Else
            sSQL = sSQL & " OR BillNum = "
        End If
        sSQL = sSQL & "'" & item.ToString() & "'"
    End If
Next