can't update table

[tolisss]

i m using the following code without errors but the data in the table remains the same what i m doing wrong

da = New OleDbDataAdapter("SELECT * FROM t", cnNew)
        ds = New DataSet("t")
        da.Fill(ds, "t")
        For Each drRow In ds.Tables.Item("t").Rows
            drRow.Item("LastN") = "test"
        Next
        ds.AcceptChanges()
        Try
            da.Update(ds, "t")
        Catch ex As Exception
            Trace.WriteLine(ex.Message)
        End Try

[robymix]

what about the update command for the data adaptor?
you don't need to call the AcceptChanges() method of the data adaptor before the update. in order words comment out

ds.AcceptChanges()

and try again.

Hope it will help.

[tolisss]

i have used that line cuase else i m getting the following error

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

[robymix]

u need an update command to update the data store. u can either write an SQL command for update and assign it to the UpdateCommand property of the data adaptor. If new rows will be added, then u need an InsertCommand. if rows will be deleted, you need a DeleteCommand.

Alternatively, you can use the CommandBuilder. You pass it a data adaptor object with a valid select command and it builds Insert, Delete and Update commands for you.

TRY THIS

da = New OleDbDataAdapter("SELECT * FROM t", cnNew)
Dim cmdBdr As New OleDbCommandBuilder(da)
ds = New DataSet("t")
da.Fill(ds, "t")
For Each drRow In ds.Tables.Item("t").Rows
     drRow.Item("LastN") = "test"
Next
'ds.AcceptChanges()
Try
    da.Update(ds, "t")
Catch ex As Exception
    Trace.WriteLine(ex.Message)
End Try

Good Luck!

[tolisss]

thnks robymix but i m very unlucky my update table does not have any key and when i tried your solution i m getting an error that i key is required do u have a solution for that ?

ps:except adding a primary field key

[Carnifex]

You need to explicitly define your update command. You cannot use command builder as you do not have any primary columns. (Using command builder is bad for performance anyway)

Check out MSDN on OleDb.OleDbCommand and OleDb.OleDbParameter.

Once you have defined your command add it to the adapter

da.UpdateCommand = myUpdateCommand

[robymix]

As Carnifex rightly said, you need to define your update command.

[tolisss]

that isn't so straight forward.

"define an update command"

what do u mean how can i keep trace of how many records i have updated!!!

could u be a little more clear on that also please

[Carnifex]

1. Create a OleDB command. Look on MSDN for how to do this.
e.g. If using SqL

'Define the update command
Dim myCommand As New SqlClient.SqlCommand("Update myTable Set Field1 = @Field1 Where Field2 = @Field2", myConnection)
 
'Create the Sql Parameters
Dim prmField1 As New SqlClient.SqlParameter("@Field1, SqlDbType.Int, 4, "Field1")
Dim prmField2 As New SqlClient.SqlParameter("@Field2, SqlDbType.Int, 4, "Field2")
 
'Add the parameters to the command
myCommand.Parameters.Add(prmField1)
myCommand.Parameters.Add(prmField2)
 
'Associate the command with the data adapter
da.UpdateCommand = myCommand

2. Attach it to the DataAdapter how I showed in my example code.
3. The data adapter will still return how many records were updated when update is called. If you want to use the adapter to add or delete records you must also define the insert and delete commands.