SQLCommandBuilder question

[me_again]

Heres a question for the gurus out there.

Ive got a DataSet bound to a DataGrid conrol. I used Robbys example code (http://www.computerhelp.forum/showthread.php?s=&threadid=71636), and built my own Insert, Update and Delete commands. These work fine when I make changes to the data in the grid, ie. they insert, update & delete fine.

My question is this: Why does the Update, Insert and Delete commands generated by the SQLCommandBuilder not work?

I notice that when Robby set up the parameters for each command, he names the parameters using the fieldname, with the @ appended to the fieldname.
The SQLCommandBuilder, on the other hand, generates parameter names of the form @p1, @p2, @p3 etc.
Other than that, there seems to be no major diffirence between the two commands.

I read up on the SQLCommandBuilder, and I understand that it wont generate commands where more than ONE table is referenced in the SELECT statement, and thats fine, because Im using the most commonest of common SQL statements SELECT * FROM TableName. But not even the Update etc. commands generated by the SQLCommandBuilder for this simple statement works.

If it doenst even work for a statement as simple as the above, why on earth would anybody want to use it?

ADO might have been more complicated, but at least it worked.

Cheers

 

[Robby]

The fieldnames with th @ can be named anything, its merely for readability.

 

[me_again]

Robby,

Can you please help me out with this one. I have now gone one further and duplicated what you did in your example in my test app. I ultimately need to be able to view and possibly edit data in tables from any SQL database (just like the SQL Enterprise manager).

Ive included my test app. Ive got three builder functions where I build the command strings and set up the parameters, and Ive done this all from you example (thanks! ). The problem is that the updates still doesnt happen. The DataAdapter.Update command returns a zero everytime. If I dont call the AcceptChanges on the DataSet, I get a There is no Proposed data to access.

Would you please be so kind as to take a look at the code and tell me where Im going off the track.

Your help would be greatly appreciated!

Cheers

 

[Robby]

I looked at your code using Notepad, (no .NET today) the thing that stick out is the scope and use of your DataAdapter, you have one that is a member and youre passing to the sub.
Since its already a member of the class, then dont pass it along, just build on it using the original.

 

[me_again]

I did that because Im planning to use the three builder functions as is in my production app (if I can get the whole lot working off course)

Im now comparing my generated SQL scripts agains the command builders scripts.

I would still appreciate it though if you can have a look at the test app.

B.T.W., my database contains tables, both with, and without primary keys.

 

[me_again]

Problem Solved!!!

SUCCESS!!!! At last!

Im sure everybody thats read this thread will be glad to hear that I have solved my problem, so the nagging, and moaning is at last at an end.

It turns out that I had the Input parameters set to Proposed instead of Current.
Also, dont call the AcceptChanges procedure on the Dataset, because then the DataAdapters Update command doesnt save anything.

Im including my sample project which will allow for inserting, updating and deleting records from any table in any SQL database. The tables doesnt have to have a primary key columns as the update and delete commands will find the relavant record based on the values of the existing fields. (You will only run into trouble when you have more than one row where the field values are identical in all fields)

I am not at all good with documenting stuff so please note that the code contains the absolute minimum of comments. The best way to figure out how it all works is to step through the code.

Cheers all!!

PS: i also noted that my SQL scripts doesnt compare with the scripts generated by the CommandBuilder?!