I created a DataSet on a table that does not have a primary key defined, but will have a column that I can depend on to be unique. Is there a way to generate the update commands without having to recreate the dataSet? I generally have been delting the DataSet from ym project, adding a primary key to the table, recreating the dataset on the table, and then removign the primary key from the actual table.

Also, when I do this I have a problem with the fact that I will always have one record that has a null or blank in my unique column. But it is always null or blank, so when changes to that row are made, I can use UPDATE WHERE columnName IS NULL OR columnName = ''

But I don't quite understand how to add and then use my own update queries to the strongly typed DataSet. I've tried searches, but I just get tutorials where people are walking me through the add DataSet wizard.

Can anyone provide some links to more advanced usage of the strongly typed dataset in C# desktop applications? I'm sure there is an intended way to do this kind of stuff, because I know I shouldn't go mucking around in the designer generated code. I tried that already when I was trying to modify the conenctions tring such that it is set at runtime, and the settings editor got completely screwed up.

EDIT: Also this is an Access table. I can't depend on stored procedures.

I don't understand why you can not use wizard, but maybe you have a course.

If you want to customise strongly-typed dataset, you can extend generated classes by using "partial" keyword. (All dataset classes aregenerated with this keyword). With this technique you can add additional methods and properties, and get access to dataset internals. But be carefull, if you modify internal data of dateset.

The wizard doesn't generate update statements because there is no primary key, and it doesn't let me specify the column to use as a unique column during the wizard. I do an add new, choose DataSet, and it takes me through the wizard. However, if the table I choose does not have a primary key, it does not let me specify a primary key, and therefore it does not generate update statements.

What I ended up doing is setting the pkey on the table, generating the dataset, then removing the pkey from the table in the database.

In the dataset editor, I selected my unique column, ands set the AllowDBNull to True and made sure Unique was set to true. This resolveed the issue I was having with my one blank record.

I had not reallized I could do this, but I found that if I click on the Adapter header in the designer that is named <TableName>Adapter, then it display the commands in the Properties windows. I can expand the Update command and edit the parameters and CommandText.

I even tried setting the primary key in the designer(but not the database), and right clicking the data table header in the designer, and clicking Configure. This would take me through the wizard again, but still Update statements were not genertated. If I set the primary key in the database, then it would generate them when I did this.

I just wish the DataSet designer would trust me when I tell it that the column is unique, and use that in the udpate and delete statements. Instead it examines the constraints in the database.

I got it working, though.

Edit: I thought I had posted this to the Data Access section, but somehow I ended up with it in Data Binding. Sorry about that.

Well I tried to go a step further and add:
RTRIM(CodeValue) as RTrim_CodeValue
to my Select query. Now it's all foobared, as it added duplicate CodeValue columns in my Insert and Update statements. When I try to fix the statements in the Properties window of the TableAdapter by editing the CommandText, I get a message box that says "editing is not allowed for this dataset". I've probably had to delete my data set a couple dozen times due to problems like this.

I think this is a bug in visual studio 2008. We work a lot with datasets and we get this now and then and it is a pain in the *** to fix, because you have to rollback (in source control) to the previous working version, then you have to figure out what the differences were and then apply them again. this is nothing to do with using a wizard or not.

This is why I hate graphical editors in VS. They are great for quickly prototyping something and learning because you can see what code is generated, but really the guides and documentation should be more about how to create well structured strongly typed classes via code editing rather than using some frustrating GUI.

Just in case anyone is still getting this "editing is not allowed for this dataset" message, I've solved it by opening the .xsd with Visual Studio XmlEditor and just saving it. When I open the xsd in design mode again, the problem dissapears and editing is allowed.

Pedro Chico...many thanks for that helpful tip.