SQLCommandBuilder question

[me_again]

Here's a question for the gurus out there.

 

I've got a DataSet bound to a DataGrid conrol. I used Robby's example code (http://www.xtremedotnettalk.com/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 won't generate commands where more than ONE table is referenced in the SELECT statement, and that's fine, because I'm 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 doens't 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 :confused:

[Robby]

The fieldnames with th @ can be named anything, it's 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).

 

I've included my test app. I've got three builder functions where I build the command strings and set up the parameters, and I've done this all from you example (thanks! :D ). The problem is that the updates still doesn't happen. The DataAdapter.Update command returns a zero everytime. If I don't 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 I'm going off the track.

 

Your help would be greatly appreciated!

 

Cheers

binding.zip

[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 you're passing to the sub.

Since it's already a member of the class, then don't pass it along, just build on it using the original.

[me_again]

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

 

I'm now comparing my generated SQL scripts agains the command builder's 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!

 

I'm sure everybody that's 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, don't call the AcceptChanges procedure on the Dataset, because then the DataAdapter's Update command doesn't save anything.

 

I'm including my sample project which will allow for inserting, updating and deleting records from any table in any SQL database. The tables doesn't 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 doesn't compare with the scripts generated by the CommandBuilder?!

binding.zip