Creating DataGrid From DataSource - Reads & Adds But No Update Or Delete

[Skittle]

I am working through a text book on VB.net VS 2010 and have found a problem I can't explain.

I have created a datasource via Data->Add Data Source and selected a table to appear in a dataset.
The datasource is SQL server 2008.

I can see the tables in the Visual Studio Data Sources window and I have dragged it on to a single windows form.
Visual Studio generates a datagrid for me automatically complete with navigation buttons to delete,
add and save changes made to the dataset.

When I run the program it reads the source data into the grid and I can add new rows to it and click the save button
to save the new rows.

However if I try deleting a row or changing a cell in an existing row, the program throws a runtime error on the
Me.MyTableTableAdapter.Fill(Me.MyDataSource.MyTable) command indicating the Update and Delete commands are not set.

So it seems the auto generated code is not created for an Update or Delete on a datagrid.
The text book I am following gives the impression the add, delete and update options should all work from auto-generated code.

I have also tried the same thing on an Iseries/AS400 dataset and get the same behaviour.

Is is always the case the data adapter update and delete code is not generated this always the case or is there something I need to do?




Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[MajP]

So it seems the auto generated code is not created for an Update or Delete on a datagrid

The datagrid does not create the code. The code is created when you make the strong typed dataset. But it does create the commands. This image shows the tableadapter associated with the datatable. On the right you see the generated commands.

 

[Skittle]

This is strange because I don't get the same results.

My Update Command and Delete Command have '(None)' in the properties box.
At first I thought the difference might be that my table had no primary key but after trying a different table with a primary key, I get the same result.



Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[Skittle]

After a day of pain and suffering trawling through sites with similar stories of frustration, I have solved it.

The table on the source database must not only have a unique key, the unique key must be sql coded as the primary key.

ALTER TABLE MyTable
ADD PRIMARY KEY (MyPrimary_Field)

When I looked at the table in Visual Studio the unique for the table was there with the little yellow key against it
so it looked like a primary key. It was however just a unique key! Even though in Visual Studio when I right clicked
the dataset table field and selected 'Set primary Key' it still did not get recognised as a true primary key at
DeleteCommand and UpdateCommand at code auto-generation point.

So there are 3 ways to sole this error:-

1) Type the Update and Delete commands in manually (Yuk!)
2) Add the Primary Key constraint to your table before bringing the table into Visual Studio
3) Add the Primary Key constraint to your table already in Visual Studio and then right clicking the table adapter, select the Configure option, select the 'advanced option' and make sure 'Refresh the Table' is ticked and click 'OK' followed by finish.

I think behind the scenes Visual Studio is merely executing a CommandBuilder but it works a lot better in the manual coded method
than via a datagrid wizard as it doesn't need a primary key to work.

Another day of my life gone.....


Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.