DataGridView - How do I update an MS Access with grid changes?

[emaduddeen]

Hi Everyone,

I'm using oledb to populate a DataGridView with data from an MS Access database.

Can you tell me how to update the database with all changes the user makes in the DataGridView which includes inserts, changes and deletes?

The DataGridView is also using a DataView.

Thanks.

Truly,
Emad

 

[RiverGuy]

You need to create Insert, Update and Delete commands for your DataAdapter. When you call the .Update method of your DataAdapter, it will fire off the appropriate command to update your database.

 

[emaduddeen]

Hi riverguy

I never did that before.

Could you give instructions on how to do it?

Truly,
emad

 

[mansii]

In addition to what RiverGuy mentioned, perhaps you want to look into DataBindings too.
faq796-3820 is a good start, while you can Google out for more codes.

Regards.

 

[emaduddeen]

Hi,

I found out how to do the DataAdapter commands but can't save changes to the database.

Can you help fix my code?

Sorry for this changed post.

This new code is based on some code from msdn. 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.

If I can get this to work, I will try to put in the insert and delete commands.

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


Thanks.

Truly,
Emad