Updating Data from a DataGridView

[polar]

Hi,
I have a datagridview and the datasource is a database query. This query takes data from multiple tables within the database.

When I run the project the data loads to the datagridview fine. However, the system needs to allow the user to update data shown in the datagrid and save it.

I've been messing with this for ages now and it is still not saving correctly. Here is the code behind the save button:

 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim strUpdate As String = "Update tblAttendance set Reasonforabsence = @Reason For Absence"
        Dim strInsert As String = "Insert Into TodaysAbsentees values (@Student, @Class Name, @AttendanceDate, @Reason For Absence)"

        Dim sqlUpdate, sqlInsert As OleDb.OleDbCommand

        sqlInsert = New OleDb.OleDbCommand(strInsert, objConnection)
        sqlUpdate = New OleDb.OleDbCommand(strUpdate, objConnection)


        sqlUpdate.Parameters.Add("@Reason For Absence", Data.OleDb.OleDbType.Char, 255, "Reasonforabsence")
        sqlInsert.Parameters.Add("@Student", Data.OleDb.OleDbType.Char, 255, "Student")
        sqlInsert.Parameters.Add("@Class Name", Data.OleDb.OleDbType.Char, 255, "ClassName")
        sqlInsert.Parameters.Add("@AttendanceDate", Data.OleDb.OleDbType.Char, 255, "AttendanceDate")
        sqlInsert.Parameters.Add("@Reason For Absence", Data.OleDb.OleDbType.Char, 255, "Reasonforabsence")

        TodaysAbsentessTableAdapter = New OleDb.OleDbDataAdapter()
        TodaysAbsentessTableAdapter.UpdateCommand = sqlUpdate
        TodaysAbsentessTableAdapter.InsertCommand = sqlInsert

        If Me.DataSet2.HasChanges Then
            Try
                DataSet2.TodaysAbsentees.GetChanges()
                TodaysAbsentessTableAdapter.Update(DataSet2, "tblAttendance")
                TodaysAbsentessTableAdapter.AcceptChangesDuringUpdate = True
                DataSet2.AcceptChanges()
                MsgBox("Update Successful")
            Catch ex As Exception
                MsgBox("Update Failed")
            End Try
        End If

When I run the project and hit the save button, the "update successful" msgbox will actually appear but changes are not actually saved.

I'm not even sure I need all that code regarding the InsertCommand because it's just updating that needs to be done, no new rows can be created.
Another thing I did was add some code behind the dataset;

Partial Class DataSet2
End Class
Namespace DataSet2TableAdapters
    
    Partial Public Class TodaysAbsenteesTableAdapter
        Public Sub AssignConnection(ByVal conn As OleDb.OleDbConnection, _
                                    Optional ByVal trans As OleDb.OleDbTransaction = Nothing)
            Me.Connection = conn
            If trans IsNot Nothing Then
                Me.Adapter.InsertCommand.Transaction = trans
                Me.Adapter.UpdateCommand.Transaction = trans
            End If
            Me.Adapter.AcceptChangesDuringUpdate = True
            Me.Adapter.ContinueUpdateOnError = False

            trans.Commit()
        End Sub
    End Class
End Namespace

Again, I'm not sure if this is needed, I just saw it when I was googling this problem and said I'd give it a go.

Your help would be greatly appreciated as this functionality is crucial to my project and if I can just get it working here, I'll be able to apply it to other areas of the project.

Thanks,
Laura

 

[cjard]

Hi,
This query takes data from multiple tables within the database.

the system needs to allow the user to update data shown in the datagrid and save it

Youre going to struggle. Join views are not normally updateable unless you have ensured that the primary keys of all tables are preserved. Key preservation is a pain in the arse to arrange and get working

You'd be far better learning how to Display Related Data (see the DW2 link in my signature for a section named thus) and then how to Save Related Data (again, see DW2)

I've been messing with this for ages now and it is still not saving correctly.

Indeed, it wont, because when you join data from multiple tables, one table ends up with repeated values if the relationship is 1:Many. Thus you can ask the user to give two different values to one row; this conflict cannot be resolved

-

As an aside, you may be thinking about things in completely the wrong way. If you want a list of all the absentees and their reason for absence, this would be inserted into a table, and pulled for the current day. THere is no need to UPDATE any table to list someone as having a reason for absence, you simply look up todays record in the absentees table and there ought to be the reason for absence

When I run the project and hit the save button, the "update successful" msgbox will actually appear but changes are not actually saved.

Read the DNU linki n my sig to check youre not falling intoa common trap

I'm not even sure I need all that code regarding the InsertCommand because it's just updating that needs to be done, no new rows can be created.

Er.. i'd say its the insert that is the critical one; it gives you a historical log, and can provide the current info. UPDATEing a table all the time give your current but no history; i.e. it offers less of a solution

Again, I'm not sure if this is needed, I just saw it when I was googling this problem and said I'd give it a go.

SImplify the problem and it will go away - often what is needed is a thinking shift rather than a coding one

Note that the initial advice from me doesnt necessarily apply to your problem; your first few sentences might have been something of a red herring!

Can you post the MDB youre using?