Can't get update to work in oledb

[FOXPROG]

I am trying to update a foxpro table but the da.update(ds,"sample") fails with an error about not being valid. Can someone tell me what I am doing wrong. I am new to this programming environment.I know the dataset gets fill with the foxpro table data and the dataset changes to the new information but the update to the actual foxpro table fails.



Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim ds As DataSet = New DataSet
da.Fill(ds, "sample")
ds.Tables("sample").Rows(1).Item(0) = "frank"
ds.Tables("sample").Rows(1).Item(1) = "Scorzelli"
da.Update(ds, "sample")

 

[RiverGuy]

1. Try to say away from CommandBuilder if possible
2. I believe CommandBuilder will need a Primary Key on your table to create proper Insert, Update and Delete commands for your DataAdapter. Do you have a Primary Key defined on your table?

 

[FOXPROG]

No, I do not have a primary key. Can you give me a snippet of code that might work better?

 

[RiverGuy]

No, because that's the whole rationale for using CommandBuilder. If you use CommandBuilder, you don't have much work to do. If you don't use it, you need to hand code your Insert, Update and Delete commands. Which of course turns into writing a bit of SQL, a bit of .Net, and a lot of debugging work to do.

This is an old article, but you will see what I mean:

http://msdn.microsoft.com/en-us/library/ms971491.aspx

 

[pmegan]

DataAdapters assign a Select command when they're created, but you have to assign the update command manually. The code you have doesn't do this, so da.update isn't doing anything. If you had a primary key on your table you could assign the command generated by the command builder like this:

da.UpdateCommand = cb.GetUpdateCommand

I agree with RiverGuy about not using the command builder in your final product. It is, however, a decent tool for learning how to format SQL commands. That's how I learned how to write SQL queries in .Net. To do this, create a string variable and assign the the update command to it:

dim strUpdate as string = cb.GetUpdateCommand

Put a breakpoint on that line and start debugging. When it stops, step one line and take a look at the contents of the strUpdate variable. It shouldn't be hard to figure out how the command works and modify it enough to right your own update command.

If this bombs because there's no primary key you may want to check it out on a test table that has one. It's something you'll use all the time, so the effort will be worth it.