DataAdapter - How do I set up create Insert, Update and Delete commands for your it?

[emaduddeen]

Hi Everyone,

Can you tell me how do I set up create Insert, Update and Delete commands for your a DataAdapter?

I'm using a DataGridView to allow the user to view, change and delete data rows and would like to be able to save any changes made in the grid back to the database.

The application is part of one I'm developing for our local school to write reports to report cards for the children.

Here is the code I used to set it up:

' Setup the Connection string.
    '-----------------------------
    Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Emad's Section\Development\Visual Studio\Projects\ISGL School Application\ISGL School Application\iap1.mdb"

    ' Declare string to hold SQL query.
    '----------------------------------
    Dim strQuery As String = _
        "SELECT attendance.AttendanceID, " & _
               "class.ClassName, " & _
               "Student.StudentId, LTrim(Student.FirstName) & ' ' & LTrim(Student.LastName) AS StudentName, " & _
               "Int((Date()-dob)/365.25) AS StudentAge, " & _
               "attendance.DateOfClass, " & _
               "attendance.Absent " & _
         "FROM Class " & _
         "INNER JOIN (Attendance INNER JOIN Student ON Attendance.StudentID = Student.StudentId) " & _
                 "ON Class.ClassId = Attendance.ClassId"

    ' Declare a database connection.
    '-------------------------------
    Dim objConnection As OleDbConnection

    ' Declare a command using the SQL query that will be run.
    '--------------------------------------------------------
    Dim objCommand As OleDbCommand

    ' Declare a DataAdapter.
    '-----------------------
    Dim objDataAdapter As OleDbDataAdapter

    ' Declare a DataSet.
    '-------------------
    Dim objDataSet As DataSet

    ' Declare a DataView. We will filter on this.
    '--------------------------------------------
    Dim objDataView As DataView

    Private Sub frmAttendance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            ' Initialize the Connection object.
            '----------------------------------
            objConnection = New OleDbConnection(strConnectionString)

            ' Initialize the Command object with the SQL query.
            '--------------------------------------------------
            objCommand = New OleDbCommand(strQuery, objConnection)

            objConnection.Open()

            ' Initialize the DataSet.
            '------------------------
            objDataSet = New DataSet()

            ' Initialize the DataAdapter.
            '----------------------------
            objDataAdapter = New OleDbDataAdapter()

            ' Initialize the DataView.
            '-------------------------
            objDataView = New DataView

            ' Set DataAdapter command properties.
            '------------------------------------
            objDataAdapter.SelectCommand = objCommand

            ' Populate the Dataset.
            '----------------------
            objDataAdapter.Fill(objDataSet, "Attendance Data")

            objDataView.Table = objDataSet.Tables("Attendance Data")

            If (objDataSet.Tables("Attendance Data").Rows.Count = 0) Then
                MessageBox.Show("There is currently no attendance data in the database.")
            Else
                DataGridViewAttendance.DataSource = objDataView
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            objConnection.Close()
        End Try
    End Sub

I'm rather new to VB 2008 and all help will be appreciated.

I can also post the project if needed.

Thanks.

Truly,
Emad

[kvprajapati]

>Can you tell me how do I set up create Insert, Update and Delete commands for your a DataAdapter?

You can configure Insert/Update/Delete commands manually or by using CommandBuilder class.

Using CommandBuilder,

' Setup the Connection string.
    '-----------------------------
    Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Emad's Section\Development\Visual Studio\Projects\ISGL School Application\ISGL School Application\iap1.mdb"

    ' Declare string to hold SQL query.
    '----------------------------------
    Dim strQuery As String = _
        "SELECT attendance.AttendanceID, " & _
               "class.ClassName, " & _
               "Student.StudentId, LTrim(Student.FirstName) & ' ' & LTrim(Student.LastName) AS StudentName, " & _
               "Int((Date()-dob)/365.25) AS StudentAge, " & _
               "attendance.DateOfClass, " & _
               "attendance.Absent " & _
         "FROM Class " & _
         "INNER JOIN (Attendance INNER JOIN Student ON Attendance.StudentID = Student.StudentId) " & _
                 "ON Class.ClassId = Attendance.ClassId"

    ' Declare a database connection.
    '-------------------------------
    Dim objConnection As OleDbConnection

    ' Declare a command using the SQL query that will be run.
    '--------------------------------------------------------
    Dim objCommand As OleDbCommand

    ' Declare a DataAdapter.
    '-----------------------
    Dim objDataAdapter As OleDbDataAdapter

    Dim cmb as OleDbCommandBuilder
    ' Declare a DataSet.
    '-------------------
    Dim objDataSet As DataSet

    ' Declare a DataView. We will filter on this.
    '--------------------------------------------
    Dim objDataView As DataView

    Private Sub frmAttendance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            ' Initialize the Connection object.
            '----------------------------------
            objConnection = New OleDbConnection(strConnectionString)

            ' Initialize the Command object with the SQL query.
            '--------------------------------------------------
            objCommand = New OleDbCommand(strQuery, objConnection)

            objConnection.Open()

            ' Initialize the DataSet.
            '------------------------
            objDataSet = New DataSet()

            ' Initialize the DataAdapter.
            '----------------------------
            objDataAdapter = New OleDbDataAdapter()

            ' Initialize the DataView.
            '-------------------------
            objDataView = New DataView

            ' Set DataAdapter command properties.
            '------------------------------------
            objDataAdapter.SelectCommand = objCommand

           cmb=new OleDbCommandBuilder(objDataAdapter)           
 
            ' Populate the Dataset.
            '----------------------
            objDataAdapter.Fill(objDataSet, "Attendance Data")

            objDataView.Table = objDataSet.Tables("Attendance Data")

            If (objDataSet.Tables("Attendance Data").Rows.Count = 0) Then
                MessageBox.Show("There is currently no attendance data in the database.")
            Else
                DataGridViewAttendance.DataSource = objDataView
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            objConnection.Close()
        End Try
    End Sub

[emaduddeen]

Hi adatapost,

Thanks for the useful help.

I found out from an error message that it will only work for a single table. My sql statement includes several tables but I want to only update, change and delete from the Attendance table.

Could you tell me what code to use to get the CommandBuilder to work only with that table?

Thanks.

Truly,
Emad

[kvprajapati]

Here is way to write code for insert/delete/update actions.

....
         objDataAdapter.InsertCommand = New OleDbCommand("insert into Attendance (col1,col2,col3) values (@p1,@p2,@p3)", objConnection)
        objDataAdapter.InsertCommand.Parameters.Add("@p1", OleDbType.Integer, 4, "col1")
        objDataAdapter.InsertCommand.Parameters.Add("@p2", OleDbType.VarChar, 20, "col2")
        objDataAdapter.InsertCommand.Parameters.Add("@p3", OleDbType.Date, 8, "col3")


        objDataAdapter.UpdateCommand = New OleDbCommand("update Attendance set col2=@p2,col3=@p3 where col1=@p1", objConnection)
        objDataAdapter.UpdateCommand.Parameters.Add("@p1", OleDbType.Integer, 4, "col1")
        objDataAdapter.UpdateCommand.Parameters.Add("@p2", OleDbType.VarChar, 20, "col2")
        objDataAdapter.UpdateCommand.Parameters.Add("@p3", OleDbType.Date, 8, "col3")

        objDataAdapter.DeleteCommand = New OleDbCommand("delete from Attendance where col1=@p1", objConnection)
        objDataAdapter.DeleteCommand.Parameters.Add("@p1", OleDbType.Integer, 4, "col1")

[emaduddeen]

Hi adatapost,

Thanks for such a quick reply.

Just a few more questions.

In this code, "col1" the column name from the DataGrid?

If so, and my DataGrid columns are:
col1=AttendanceId
col2=DateOfClass
col3=Absent

Is the following code correct?

#
objDataAdapter.UpdateCommand = New OleDbCommand("update Attendance set DateOfClass=@pDateOfClass, Absent=@pAbsent where AttendanceID=@pAttendanceID", objConnection)
#
objDataAdapter.UpdateCommand.Parameters.Add("@pAttendanceID", OleDbType.Integer, 4, "AttendanceID")
#
objDataAdapter.UpdateCommand.Parameters.Add("@pDateOfClass", OleDbType.Date, 8, "DateOfClass")
#
objDataAdapter.UpdateCommand.Parameters.Add("@pAbsent", OleDbType.Boolean,  "Absent")

Would I also use:

objDataAdapter.Update(objDataSet, "Attendance Data")

to update the DataAdapter?

Thanks.

Truly,
Emad

[kvprajapati]

>"col1" the column name from the DataGrid?

col1, col2, and so on are names of columns of Attendance table.

>to update the DataAdapter?

objDataAdapter.Update(objDataSet, "Attendance Data")

[emaduddeen]

Hi,

I put in an update command and there were no errors but also the database was not updated so I think I'm still missing some code. I tried to change the DateOfClass in the DataGrid and click my update button. Could you tell me what additional code to use?

Here is what I did so far. This is the setup for the update command:

' Initialize the DataAdapter.
            '----------------------------
            objDataAdapter = New OleDbDataAdapter()

            objDataAdapter.UpdateCommand = New OleDbCommand(strUpdateStatement, objConnection)

            objDataAdapter.UpdateCommand.Parameters.Add("@pAttendanceID", OleDbType.Integer, 4, "AttendanceID")
            objDataAdapter.UpdateCommand.Parameters.Add("@pDateOfClass", OleDbType.Date, 8, "DateOfClass")
            objDataAdapter.UpdateCommand.Parameters.Add("@pAbsent", OleDbType.Boolean, 1, "Absent")

This is the code in my update button:

Private Sub RibbonButtonUpdateAttendance_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonButtonUpdateAttendance.Click
        Try
            objDataAdapter.Update(objDataSet, "Attendance Data")

            MessageBox.Show("Updated")

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Sub

[emaduddeen]

Sorry for this changed post.

I changed the program with this new code based on some code from msdn but still I don't get the changes to the database and also if I change more then 1 row in the DataGrid I get an error that says:
concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

Here is the new code:

Public Class frmAttendance
    ' Setup the Connection string.
    '-----------------------------
    Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Emad's Section\Development\Visual Studio\Projects\ISGL School Application\ISGL School Application\iap1.mdb"

    ' Declare string to hold SQL query.
    '----------------------------------
    Dim strSelectStatement As String = _
        "SELECT attendance.AttendanceID, " & _
               "class.ClassName, " & _
               "Student.StudentId, LTrim(Student.FirstName) & ' ' & LTrim(Student.LastName) AS StudentName, " & _
               "Int((Date()-dob)/365.25) AS StudentAge, " & _
               "attendance.DateOfClass, " & _
               "attendance.Absent " & _
         "FROM Class " & _
         "INNER JOIN (Attendance INNER JOIN Student ON Attendance.StudentID = Student.StudentId) " & _
                 "ON Class.ClassId = Attendance.ClassId"

    Dim strUpdateStatement As String = _
        "UPDATE Attendance " & _
           "SET DateOfClass = @pDateOfClass, " & _
               "Absent = @pAbsent " & _
        "WHERE AttendanceID = @pAttendanceID"

    ' Declare a database connection.
    '-------------------------------
    Dim objConnection As OleDbConnection

    ' Declare a commands to hold the SQL statements.
    '-----------------------------------------------
    Dim objSelectCommand As OleDbCommand
    Dim objUpdateCommand As OleDbCommand

    ' Declare a DataAdapter.
    '-----------------------
    Dim objDataAdapter As OleDbDataAdapter

    ' Declare a DataSet.
    '-------------------
    Dim objDataSet As DataSet

    ' Declare a DataView. We will filter on this.
    '--------------------------------------------
    Dim objDataView As DataView

    Private Sub frmAttendance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            ' Initialize the Connection object.
            '----------------------------------
            objConnection = New OleDbConnection(strConnectionString)

            objConnection.Open()

            ' Initialize the DataAdapter.
            '----------------------------
            objDataAdapter = New OleDbDataAdapter()

            ' Initialize Select Command object with the SQL Select statement.
            '----------------------------------------------------------------
            objSelectCommand = New OleDbCommand(strSelectStatement, objConnection)
            objDataAdapter.SelectCommand = objSelectCommand

            ' Initialize Update Command object with the SQL Update statement and parameters.
            '-------------------------------------------------------------------------------
            objUpdateCommand = New OleDbCommand(strUpdateStatement, objConnection)

            With objUpdateCommand
                .Parameters.Add("@pAttendanceID", OleDbType.Integer, 10, "AttendanceID")
                .Parameters.Add("@pDateOfClass", OleDbType.Date, 8, "DateOfClass")
                .Parameters.Add("@pAbsent", OleDbType.Boolean, 10, "Absent")
            End With

            objDataAdapter.UpdateCommand = objUpdateCommand

            ' Initialize the DataView.
            '-------------------------
            objDataView = New DataView

            ' Initialize the DataSet.
            '------------------------
            objDataSet = New DataSet()

            ' Populate the Dataset.
            '----------------------
            objDataAdapter.Fill(objDataSet, "Attendance Data")

            objDataView.Table = objDataSet.Tables("Attendance Data")

            If (objDataSet.Tables("Attendance Data").Rows.Count = 0) Then
                MessageBox.Show("There is currently no attendance data in the database.")
            Else
                DataGridViewAttendance.DataSource = objDataView
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            objConnection.Close()
        End Try

    Private Sub RibbonButtonUpdateAttendance_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RibbonButtonUpdateAttendance.Click
        Try
            objDataAdapter.Update(objDataSet, "Attendance Data")

            MessageBox.Show("Updated")

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Sub
End Class

Truly,
Emad