Update Problem

[melkanzi]

Hi All,
I cant update my database. Im using Access as the db. I retrieve the data and put it in a datagridview. I want to be able to edit the data in the datagridview and then store it into the db, but it just doesnt want to. in the gridview the change is there but when i check the db it hasnt changed
I cant insert into the table either

Me.DAsrch.Fill(Me.DsSrch1, "Staff")
        If Not newtext Is Nothing Then
            Me.DsSrch1.Tables(0).Rows(e.RowIndex).Item(e.ColumnIndex) = newtext

        End If

        MessageBox.Show(Me.DsSrch1.Staff.Rows(e.RowIndex).Item(e.ColumnIndex))
        Me.Com.Connection = Me.DBCon
        Dim field As String = Me.GVStaff.Item(e.ColumnIndex, e.RowIndex).OwningColumn.DataPropertyName.ToString
        Dim value As String = Me.GVStaff.Item(e.ColumnIndex, e.RowIndex).EditedFormattedValue

        Dim value2 As String = newtext
        Dim cprv As String = Me.GVStaff.Item(0, e.RowIndex).Value
        Me.Com.CommandText = "Update Staff Set " & field & " = " & value2 & " WHERE CPR = " & cprv & ""
        Me.DAsrch.UpdateCommand = Com
        Try
            Me.DBCon.Open()
            Dim r As Integer = Me.Com.ExecuteNonQuery()
            Me.DAsrch.Update(Me.DsSrch1, "Staff")

            Me.DsSrch1.AcceptChanges()
            Me.DBCon.Close()
            Me.DAsrch.Fill(Me.DsSrch1, "Staff")

Whats wrong?? Please reply ASAP..... Thanks

 

[PlausiblyDamp]

You will need to configure the DataAdapter with a valid InsertCommand, UpdateCommand and DeleteCommand and then call the .Update(...) method to push the changes back.

 

[melkanzi]

Thanks for the reply but it still doesnt want to write to the db.
I have assigned a statement for each command but still its not writing to the db.
What am i missing here?

 

[PlausiblyDamp]

The update command should be a parameterised string that the DataAdapter will call with the relevant parameters, you dont need to build it yoursefl (and if you do using string concatenation is a bad way to do it).
If you are happy to build the update command yourself then you can just execute it directly and not bother with the DataAdapter.Update.

 

[melkanzi]

Thanks for the reply but when i use com.executenonquery() and i store the number of affected rows i get the correct number but the DB doesnt get updated....
I think the problem is in the OleDB connection itself because I can read from the db but i cant write to it.
What could be wrong??

 

[melkanzi]

Please doesnt anyone know what is wrong?
Its urgent i need a solution before next week
Thanks

 

[PlausiblyDamp]

How are you building and executing the string? If you run the string directly against the db (Query Analyzer) what happens?

 

[melkanzi]

I found where the problem was.... the problem is that there were two copies of the db file in the project folder and everytime i updated one i see the old unupdated file....
Thanks alot for your help