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 :confused:

I cant insert into the table either :o

 

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")

 

What's wrong?? Please reply ASAP..... Thanks

Edited July 29, 2006 by melkanzi

[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 don't 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