Tutorial Request - DataAdapter commands to update your database from a DataGridView.

[emaduddeen]

Hi Everyone,

Can you reply with a tutorial and maybe a sample project that can be downloaded that shows how to update a database such as MS Access from DataAdapter commands in which changes are made in a DataGridView?

For example, the DataAdapter contains 2 tables called FirstTable and the other table would be called SecondTable. The DataGridView is based on a query that shows rows from both tables. The actual table that contains the rows to be changed, inserted or deleted is FirstTable.

Hopefully this description is clear and I'm sure this tutorial would be helpful to many people such as myself learning VB 2008 .NET.

Truly,
Emad

[kvprajapati]

Take a look at sample.

[emaduddeen]

Hi adatapost,

Thanks for the sample. It's greatly appreciated. We will use that when we need to process a single table in a DataViewGrid.

What we currently have is a DataGridView based on a query that has 3 other tables joined to the table we want to update, insert and delete from. We also researched what the CommandBuilder is and found that it can only have a query with a single table.

Using your example, if we were to change:

Dim t2 As DataTable = GetTable("select * from empsalary")

To this:

Dim t2 As DataTable = GetTable("select emp.ename, empsalary.salid, empsalary.amount from empsalary, emp " & _
                                       "where emp.eno = empsalary.eno")

What additional coding do we need to include or change so updates, inserts, and deletes are handled using:

Adp.Update(dt)

Truly,
Emad

[kvprajapati]

Here is another example,

Public Class Form2

    Dim CnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\updateApp\TestDB.mdb;Persist Security Info=True"

    Dim Cn As New OleDbConnection(CnStr)
    Dim Adp As New OleDbDataAdapter("select emp.eno, emp.ename, empsalary.salid, empsalary.amount from empsalary, emp where emp.eno = empsalary.eno", Cn)

    Dim dt As New DataTable
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Adp.Update(dt)
    End Sub

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Adp.UpdateCommand = New OleDbCommand("update empsalary set amount=@p1 where salid=@p2", Cn)
        Adp.UpdateCommand.Parameters.Add("@p1", OleDbType.Integer, 4, "amount")
        Adp.UpdateCommand.Parameters.Add("@p2", OleDbType.Integer, 4, "salid")

        Adp.DeleteCommand = New OleDbCommand("delete from empsalary where salid=@p1", Cn)
        Adp.UpdateCommand.Parameters.Add("@p1", OleDbType.Integer, 4, "salid")


        Adp.InsertCommand = New OleDbCommand("insert into empsalary (salid,eno,amount) values (@p1,@p2,@p3)", Cn)
        Adp.InsertCommand.Parameters.Add("@p1", OleDbType.Integer, 4, "salid")
        Adp.InsertCommand.Parameters.Add("@p2", OleDbType.Integer, 4, "eno")
        Adp.InsertCommand.Parameters.Add("@p3", OleDbType.Integer, 4, "amount")

        Adp.Fill(dt)

        DataGridView1.DataSource = dt
    End Sub
End Class

[kvprajapati]

Here is another example,

Public Class Form2

    Dim CnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\updateApp\TestDB.mdb;Persist Security Info=True"

    Dim Cn As New OleDbConnection(CnStr)
    Dim Adp As New OleDbDataAdapter("select emp.eno, emp.ename, empsalary.salid, empsalary.amount from empsalary, emp where emp.eno = empsalary.eno", Cn)

    Dim dt As New DataTable
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Adp.Update(dt)
    End Sub

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Adp.UpdateCommand = New OleDbCommand("update empsalary set amount=@p1 where salid=@p2", Cn)
        Adp.UpdateCommand.Parameters.Add("@p1", OleDbType.Integer, 4, "amount")
        Adp.UpdateCommand.Parameters.Add("@p2", OleDbType.Integer, 4, "salid")

        Adp.DeleteCommand = New OleDbCommand("delete from empsalary where salid=@p1", Cn)
        Adp.UpdateCommand.Parameters.Add("@p1", OleDbType.Integer, 4, "salid")


        Adp.InsertCommand = New OleDbCommand("insert into empsalary (salid,eno,amount) values (@p1,@p2,@p3)", Cn)
        Adp.InsertCommand.Parameters.Add("@p1", OleDbType.Integer, 4, "salid")
        Adp.InsertCommand.Parameters.Add("@p2", OleDbType.Integer, 4, "eno")
        Adp.InsertCommand.Parameters.Add("@p3", OleDbType.Integer, 4, "amount")

        Adp.Fill(dt)

        DataGridView1.DataSource = dt
    End Sub
End Class

[emaduddeen]

Hi adatapost,

Thanks so much for the help.

We will apply your logic.

Truly,
Emad

[docjwm]

Thanks for helping to teach and old dog new tricks.