Update in access with vb.net

Question

I'm making an invoice making software where I have 2 tables 1 for invoice and another for invoice items. Now problem is if user wants to edit previous invoice and wanted to add some more items or remove some items I wrote query of update set but it needs to be insert also my update query updates all data to same as it is.

here is my update items query

Public Sub update_items()
    ds.Clear()
    str = ""

    If cn.State = ConnectionState.Open Then
        cn.Close()
    End If

    DBConfig.conn()
    cn.Open()

    For i As Integer = 0 To DataGridView1.Rows.Count - 2
        cmd.Parameters.Clear()
        cmd.Connection = cn
        cmd.CommandText = "update invoice_items set [item_name]=@item_name,[qty]=@qty,[rate]=@rate where [invoice_id]=@invoice_id and [item_code]=@itemcode"
        cmd.Parameters.AddWithValue("@item_name", DataGridView1.Rows(i).Cells(2).Value)
        cmd.Parameters.AddWithValue("@qty", DataGridView1.Rows(i).Cells(3).Value)
        cmd.Parameters.AddWithValue("@rate", DataGridView1.Rows(i).Cells(4).Value)
        cmd.Parameters.AddWithValue("@invoice_id", invoice_id.Text.ToString)
        cmd.Parameters.AddWithValue("@item_code", DataGridView1.Rows(i).Cells(1).Value)

        cmd.ExecuteNonQuery()


    Next
End Sub

my data is in datagridview which has one combobox too.Here is how my vb form looks like Here when page loads in side dgv data loads from table called item where item name is combobox and other calculation happens on bases of price and quantity. When it saves all of the data stores in to two diffrent tables one is invoice and second is invoice_items where I store data with item name, quantity, price, invoice no. Rest data is stored in invoice table. Now where user wants to edit data he has to search with invoice no on search click I get data from invoice table as well as invoice_items and display as it is now I want to update all the data on update click data updates in invoice table perfectly but it starts problem with invoice_items table. which I had written before.

Answer 1

Golly, I suggest you consider using the dataset desinger. (or Entity frame work desinger). Dataset designer works much like EF, but it will auto matic bind and wire up for you automatic all of the data operations.

The results are VERY much like data bound ms-access forms.

so, if I use the data set designer, then I can drag + drop fields on to the form. and you even get a navigation bar with record movement, and save, and add and delete features ALL automatic done for you.

So, you get say a form like this:

Same goes for datagrids - you can edit, add or even delete.

All of above works with without you having to write code.

Same goes for say a datagrid.

Say we drop in a button (to save), and then a data grid.

We have this:

Total code for this - inclding the save button code is now this:

Private Sub HotelGridEdit_Load(sender As Object, e As EventArgs) Handles Me.Load

    LoadGrid()

End Sub

Sub LoadGrid()

    Using conn As New OleDbConnection(My.Settings.AccessDB)

        Dim strSQL As String =
            "SELECT ID, FirstName, LastName, City, HotelName, Description, Active FROM tblHotelsA 
            ORDER BY HotelName"
        Using cmdSQL As New OleDbCommand(strSQL, conn)

            conn.Open()
            Dim rstData As New DataTable
            rstData.Load(cmdSQL.ExecuteReader)
            DataGridView1.DataSource = rstData
        End Using
    End Using


End Sub

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

    ' save all edits, and new rows, and deletes
    Using conn As New OleDbConnection(My.Settings.AccessDB)

        Dim strSQL As String =
            "SELECT ID, FirstName, LastName, City, HotelName, Description, Active FROM tblHotelsA 
            ORDER BY HotelName"
        Using cmdSQL As New OleDbCommand(strSQL, conn)
            conn.Open()
            Dim da As New OleDbDataAdapter(cmdSQL)
            Dim daC As New OleDbCommandBuilder(da)
            da.Update(DataGridView1.DataSource)

        End Using
    End Using
End Sub

Note the save code. We send all edits, deletes, and even addtions in one shot.

So, you don't need (nor want) to write code just for a insert, just for edit, and just for adding). You can get the .net system and the form to do all of this work for you.