Answered How to transfer updated rows of datagridview to database ?

[rajdh75]

Hello,
I have a windows form in which it has a datagridview which shows data from Access database table named TblTest by entering Invoice number in Text box named as TxtInvoiceNo.
The code for this is -

Dim cmd As New OleDbCommand("SELECT * from TblTest WHERE InvoiceID = @InvoiceID", con)

        With cmd.Parameters

            .Add("@InvoiceID", OleDbType.VarChar, 50).Value = TxtInvoiceID.Text


            Dim da As New OleDbDataAdapter
            Dim dt As New DataTable
            da.SelectCommand = cmd
            dt.Clear()
            da.Fill(dt)

            DataGridView1.AutoGenerateColumns = False

            DataGridView1.Columns(0).DataPropertyName = "TestID"
            DataGridView1.Columns(1).DataPropertyName = "InvoiceID"
            DataGridView1.Columns(2).DataPropertyName = "ProductID"
            DataGridView1.Columns(3).DataPropertyName = "ProductName"
            DataGridView1.Columns(4).DataPropertyName = "HSNCode"
            DataGridView1.Columns(5).DataPropertyName = "Unit"
            DataGridView1.Columns(6).DataPropertyName = "Qty"
            DataGridView1.Columns(7).DataPropertyName = "InclRate"
            DataGridView1.Columns(8).DataPropertyName = "BaseRate"
            DataGridView1.Columns(9).DataPropertyName = "Amount"
            DataGridView1.Columns(10).DataPropertyName = "GST"
            DataGridView1.Columns(11).DataPropertyName = "GSTAmt"
            DataGridView1.Columns(12).DataPropertyName = "Total"


            DataGridView1.DataSource = dt


        End With

    End Sub

This works well.
I have another code for editing rows of DataGridView from entering from Textboxes for above columns with DataGridView1_CellClick event as

Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Dim index As Integer
        index = e.RowIndex
        Dim selectedrow As DataGridViewRow
        selectedrow = DataGridView1.Rows(index)

        TxtTestID.Text = selectedrow.Cells(0).Value.ToString
        TxtInvoiceID.Text = selectedrow.Cells(1).Value.ToString
        TxtProductID.Text = selectedrow.Cells(2).Value.ToString
        CmbProductName.Text = selectedrow.Cells(3).Value.ToString
        TxtHSN.Text = selectedrow.Cells(4).Value.ToString
        TxtUnit.Text = selectedrow.Cells(5).Value.ToString
        TxtQty.Text = selectedrow.Cells(6).Value.ToString
        TxtInclRate.Text = selectedrow.Cells(7).Value.ToString
        TxtBaseRate.Text = selectedrow.Cells(8).Value.ToString
        TxtAmount.Text = selectedrow.Cells(9).Value.ToString
        TxtGST.Text = selectedrow.Cells(10).Value.ToString
        TxtGSTAmt.Text = selectedrow.Cells(11).Value.ToString
        TxtTotal.Text = selectedrow.Cells(12).Value.ToString

    End Sub

This also works well.
Now I am trying to transfer all rows from this DataGridView to database by a button named update. I have tried following code but it gives error as
An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll
Additional information: Conversion from string "Extra ) in query expression 'Tes" to type 'Integer' is not valid.
the code is

Try
            con.Close()
            con.Open()
            For i As Integer = 0 To DataGridView1.Rows.Count - 1

                con.Close()
                con.Open()
                cmd = New OleDbCommand("UPDATE TblTest set InvoiceID=@InvoiceID,ProductID=@ProductID,ProductName=@ProductName,HSNCode=@HSNCode,Unit=@Unit,Qty=@Qty,InclRate=@InclRate,BaseRate=@BaseRate,Amount=@Amount,GST=@GST,GSTAmt=@GSTAmt,Total=@Total where TestID=@CID)", con)

                cmd.Parameters.AddWithValue("@InvoiceID", DataGridView1.Rows(i).Cells(1).Value.ToString())
                cmd.Parameters.AddWithValue("@ProductID", DataGridView1.Rows(i).Cells(2).Value.ToString())
                cmd.Parameters.AddWithValue("@ProductName", DataGridView1.Rows(i).Cells(3).Value.ToString())
                cmd.Parameters.AddWithValue("@HSNCode", DataGridView1.Rows(i).Cells(4).Value.ToString())
                cmd.Parameters.AddWithValue("@Unit", DataGridView1.Rows(i).Cells(5).Value.ToString())
                cmd.Parameters.AddWithValue("@Qty", DataGridView1.Rows(i).Cells(6).Value.ToString())
                cmd.Parameters.AddWithValue("@InclRate", DataGridView1.Rows(i).Cells(7).Value.ToString())
                cmd.Parameters.AddWithValue("@BaseRate", DataGridView1.Rows(i).Cells(8).Value.ToString())
                cmd.Parameters.AddWithValue("@Amount", DataGridView1.Rows(i).Cells(9).Value.ToString())
                cmd.Parameters.AddWithValue("@GST", DataGridView1.Rows(i).Cells(10).Value.ToString())
                cmd.Parameters.AddWithValue("@GSTAmt", DataGridView1.Rows(i).Cells(11).Value.ToString())
                cmd.Parameters.AddWithValue("@Total", DataGridView1.Rows(i).Cells(12).Value.ToString())
                cmd.Parameters.AddWithValue("@CID", DataGridView1.Rows(i).Cells(0).Value.ToString())
                cmd.ExecuteNonQuery()
            Next
            con.Close()
            MsgBox("Record updated successfully.", MsgBoxStyle.Information, MsgBoxStyle.OkOnly)
        Catch ex As Exception
            MsgBox("", ex.Message)
        Finally
            con.Close()

        End Try

how can I remove this error ? or is there another code for this ?

Thanks

 

[jmcilhinney]

I think that you're doing it all wrong. Just use a data adapter to populate a DataTable, bind that to a BindingSource and then bind that to your controls, e.g.

myDataAdapter.Fill(myDataTable)
myBindingSource.DataSource = myDataTable
myDataGridView.DataSource = myBindingSource
myTextBox.DataBindings.Add("Text", myBindingSource, "ColumnName")

If you do that, there's no code to move data from the grid to the TextBox and vice versa. Data-binding does the work. You select a row in the grid and the TextBox automatically displays the data for that row. You make changes in the TextBox and that is automatically pushed to the grid. When you want to add a new row, you call AddNew on the BindingSource and a new row is added for you to edit. When it comes time to save, you do this:

If Validate() Then
    myBindingSource.EndEdit()
    myDataAdapter.Update(myDataTable)
End If

The Validate and EndEdit methods ensure that any pending changes are pushed from the UI to the DataTable before saving back to the database. If you're not handling any Validating events, you can ignore the value returned by Validate and omit the If statement.

Hopefully it goes without saying but, just in case, you still need to configure your data adapter. That means providing a connection string and query at least. The data adapter can create the connection object for you or you can create it yourself. You can also use a command builder to generate the action commands for you or you can create them yourself.

 

[rajdh75]

Thanks
jmcilhinney
I will try this.