update database from data grid view in vb.net

Question

i have a data grid view, filled by data from database now i need to change the data and update database.

 Private daVD As New OracleDataAdapter
    Private cmdJV As New OracleCommandBuilder
    Private dtVD As New DataTable()



 Dim cmd As New OracleCommand("Select JV_CODE,JV_ACC_NAME,DEBIT,CREDIT From VOUCHER_DETAIL where VOUCHERNO =:Vno", sgcnn)
            cmd.Parameters.Add("@Vno", OracleDbType.NVarchar2).Value = txtJVNo.Text.ToString.Trim


            dtVD.Clear()
            daVD = New OracleDataAdapter(cmd)
            daVD.Fill(dtVD)
            dgvAccDetail.AutoGenerateColumns = False
            dgvAccDetail.DataSource = dtVD



            If dtVD.Rows.Count > 0 Then
                For i As Integer = 0 To dtVD.Rows.Count - 1
                    ''Dim DataType() As String = myTableData.Rows(i).Item(1)
                    dgvAccDetail.Rows(i).Cells(0).Value = dtVD.Rows(i).Item("JV_CODE").ToString.Trim
                    dgvAccDetail.Rows(i).Cells(1).Value = dtVD.Rows(i).Item("JV_ACC_NAME").ToString.Trim
                    dgvAccDetail.Rows(i).Cells(2).Value = dtVD.Rows(i).Item("DEBIT").ToString.Trim
                    dgvAccDetail.Rows(i).Cells(3).Value = dtVD.Rows(i).Item("CREDIT").ToString.Trim
                Next
            End If

i tried many different ways to update the database table but not working

      Try
        cmdJV = New OracleCommandBuilder(da)
       daJV.Update(ds, "VJ_Details")
        ds.AcceptChanges()
        MessageBox.Show(" The record has been updated.")
    Catch ex As Exception

        MsgBox(ex.Message)

    End Try

Is an exception thrown? If not, what value is returned by that call to Update? — jmcilhinney
– jmcilhinney, Commented Jul 16, 2018 at 10:33
Um, get rid of the loop from that first code snippet. You don't set the DataSource of the grid AND add rows manually too. — jmcilhinney
– jmcilhinney, Commented Jul 16, 2018 at 10:34
What exactly happens (when you do Update)? and is your DataGridVeiw bound to database or not? — CruleD
– CruleD, Commented Jul 16, 2018 at 12:59
i tried many different ways... Instead of blind trial and error have you tried reading any of the many great articles on databinding and DB Ops at MSDN? — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jul 16, 2018 at 12:59
@CruleD A DataGridView is not bound to a database. It is bound to a DataTable. The DataTable is not bound to the database either. The DataTable is able to keep track of the RowState and RowVersion and send the appropriate Update, Insert and Delete commands to the database through a DataAdapter. — Mary
– Mary, Commented Jul 16, 2018 at 21:06

ASH · Accepted Answer · 2018-07-16 20:22:51Z

I have never used Oracle, but this is how you could do it using SQL Server (there are actually several ways to push data from a DatGridView to a table in SQL Server).

Here is one option for you to try.

Imports System.Data.SqlClient
Public Class Form1
    Dim connetionString As String
    Dim connection As SqlConnection
    Dim adapter As SqlDataAdapter
    Dim cmdBuilder As SqlCommandBuilder
    Dim ds As New DataSet
    Dim changes As DataSet
    Dim sql As String
    Dim i As Int32

    Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        connetionString = "Data Source=Server_Name\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True;"
        connection = New SqlConnection(connetionString)
        sql = "Select * from Orders"
        Try
            connection.Open()
            adapter = New SqlDataAdapter(Sql, connection)
            adapter.Fill(ds)
            DataGridView1.DataSource = ds.Tables(0)
            connection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'NOTE:  for this code to work, there must be a PK on the Table
        Try
            cmdBuilder = New SqlCommandBuilder(adapter)
            changes = ds.GetChanges()
            If changes IsNot Nothing Then
                adapter.Update(changes)
            End If
            MsgBox("Changes Done")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub DataGridView1_Click(sender As Object, e As EventArgs) Handles DataGridView1.Click
        DataGridView1.DefaultCellStyle.SelectionBackColor = Color.Orange
    End Sub
End Class

Here is one more option for you to consider.

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim tblReadCSV As New DataTable()

tblReadCSV.Columns.Add("FName")
tblReadCSV.Columns.Add("LName")
tblReadCSV.Columns.Add("Department")

Dim csvParser As New TextFieldParser("C:\Users\Excel\Desktop\Employee.txt")

csvParser.Delimiters = New String() {","}
csvParser.TrimWhiteSpace = True
csvParser.ReadLine()

While Not (csvParser.EndOfData = True)
    tblReadCSV.Rows.Add(csvParser.ReadFields())
End While

Dim con As New SqlConnection("Server=Server_Name\SQLEXPRESS;Database=Database_Name;Trusted_Connection=True;")
Dim strSql As String = "Insert into Employee(FName,LName,Department) values(@Fname,@Lname,@Department)"
'Dim con As New SqlConnection(strCon)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = strSql
cmd.Connection = con
cmd.Parameters.Add("@Fname", SqlDbType.VarChar, 50, "FName")
cmd.Parameters.Add("@Lname", SqlDbType.VarChar, 50, "LName")
cmd.Parameters.Add("@Department", SqlDbType.VarChar, 50, "Department")

Dim dAdapter As New SqlDataAdapter()
dAdapter.InsertCommand = cmd
Dim result As Integer = dAdapter.Update(tblReadCSV)

End Sub

Collectives™ on Stack Overflow

update database from data grid view in vb.net

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related