Updating items in ComboBoxes from an Microsoft Access database

Question

I am making a furniture rental system in VB.NET to update a ComboBox from a Microsoft Access Database. This code to update the ComboBox is not working; it only works for one item, for others it shows 'NO RECORD FOUND'.

I'm using Microsoft Access for my databases.

Public Class Form8
    Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jeeva\Desktop\VB Project\18HU5A1015.accdb")
    Private Sub update_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        cn.Open()
        Dim cm As New OleDb.OleDbCommand("select * from customerinfo", cn)
        Dim dr As OleDb.OleDbDataReader = cm.ExecuteReader
        While dr.Read
            ComboBox1.Items.Add(dr(0).ToString)
            ComboBox2.Items.Add(dr(1).ToString)
            ComboBox3.Items.Add(dr(2).ToString)
            ComboBox4.Items.Add(dr(3).ToString)
        End While
        dr.Close()
        cn.Close()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim customername = TextBox1.Text
        Dim customerid = TextBox2.Text
        Dim customeraddress = TextBox3.Text
        Dim customeraadharno = TextBox4.Text
        Try
            cn.Open()
            Dim cmd As New OleDb.OleDbCommand()
            cmd.CommandText = "Update customerinfo set customername='" + customername + "' where customername='" + ComboBox1.SelectedItem() + "'"
            cmd.CommandText = "Update customerinfo set customerid='" + customerid + "' where customername='" + ComboBox2.SelectedItem() + "'"
            cmd.CommandText = "Update customerinfo set customeraddress='" + customeraddress + "' where customername='" + ComboBox3.SelectedItem() + "'"
            cmd.CommandText = "Update customerinfo set customeraadharno='" + customeraadharno + "' where customername='" + ComboBox4.SelectedItem() + "'"
            cmd.Connection = cn

            Dim i = cmd.ExecuteNonQuery
            If i > 0 Then
                MsgBox("Record is updated successfully")
            Else
                MsgBox("No record found")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cn.Close()
        End Try
    End Sub

    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click

    End Sub

You keep replacing `cmd.CommandText`. Only the last one is being called. Use parameters to avoid sql injection and formatting errors. — LarsTech, Mar 25 '21 at 16:15
Your code is a bit weird, especially your four ComboBoxes. Not sure how they all relate to `customername` in your query. Google sql updating multiple columns in a table. — LarsTech, Mar 25 '21 at 16:39
Customername is one table in MS Access, inside that I have customername, customerid,customeraddress and customeraadharno as fields. — jeevan kurian, Mar 25 '21 at 17:06
But in your four queries, you are calling `where customername='" + ComboBox1.SelectedItem() + "'"` and ComboBox2, etc. Why do your four ComboBoxes all have a CustomerName in them when they got populated by four different columns in your load? Avoid using `SELECT *` and specify the actual columns: `SELECT ColumnName1, ColumnName2, etc` Also see [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/719186) — LarsTech, Mar 25 '21 at 17:28

score 0 · Answer 1 · answered Mar 26 '21 at 08:38

Don't change customerid, you need to use the unique customerid to update the table. Take a look at the following example:

Dim cn As New OleDb.OleDbConnection("Provider=...;")
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    cn.Open()
    Dim adapter As New OleDb.OleDbDataAdapter("select customerid,customername from customerinfo", cn)
    Dim dt As DataTable = New DataTable
    adapter.Fill(dt)
    ComboBox1.DataSource = dt
    ComboBox1.DisplayMember = "customername"
    ComboBox1.ValueMember = "customerid"
    cn.Close()

End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try

        Dim cmd As New OleDb.OleDbCommand()
        cmd.Connection = cn

        cmd.CommandText = "Update customerinfo set customername = @customername, customeraddress = @customeraddress, customeraadharno = @aadharno where customerid=@customerid"
        cmd.Parameters.AddWithValue("customername", TextBox1.Text)
        cmd.Parameters.AddWithValue("customeraddress", TextBox2.Text)
        cmd.Parameters.AddWithValue("aadharno", TextBox3.Text)
        cmd.Parameters.AddWithValue("customerid", ComboBox1.SelectedValue)
        cn.Open()
        cmd.ExecuteNonQuery()
        MsgBox("Successfully update customerinfo")
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        cn.Close()
    End Try
End Sub

Updating items in ComboBoxes from an Microsoft Access database

1 Answers1