VB.NET SQL Update Table Using Listbox As Identifier

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

Answers


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

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

Need Your Help

Howto make join in DQL

php sql symfony dql

I'm trying to get a User and his associated Role using DQL queries. In order to check why I can't get the user roles using symfony2 tutorial.