[RESOLVED] Add row to SQL Server table with Primary Key ID field using Identity Specification

[cory_jackson]

I created a simple table with an ID field and text string field. The ID field is Primary Key integer and set the Identity specification so as to auto increment the ID. I then used the Data Source Configuration wizard to create a strongly typed DataSet in the project. But now I can't figure out how to add a row to the table. If I don't include a value in the ID column I get a null exception. How do I do this properly?

Using taTestMe As New TestMeDataSetTableAdapters.ErrorsTableAdapter, dtErrors As New TestMeDataSet.ErrorsDataTable
    Dim drNew As TestMeDataSet.ErrorsRow
    drNew = dtErrors.NewErrorsRow
    'drNew.ID = 1
    drNew.Message = "Test 3"
    dtErrors.Rows.Add(drNew)
    taTestMe.Update(dtErrors)
End Using

Thank you for taking the time to read my post.

[wes4dbt]

My guess is that your not working with the database that you think you are. If you added the database to your project and the IDE is in Debug mode, check the database in the "bin\debug" folder. Because if the "ID" field is an Identity field and autonumber, then your code should work fine.

[cory_jackson]

Roger. I'll start over from scratch. Am I creating the DataSet correctly by using that wizard?

[cory_jackson]

It wasn't that I wasn't connecting to a different database but that was the clue I needed to solve the problem. The initial table design had no identity specification and I created the DataSet using the wizard. After I change it to auto-increment I ran the wizard again but failed to create a new string. In my old DataSet I can see the AutoIncrement was false. This time I had it create a new connection string and my code works now. Thank you much for heading me in the right direction.

[wes4dbt]

When using datasets created by the Wizard from a database, when you modify the database, sometimes its just best to delete the affected datatable from the dataset and then run the wizard and add it back. There are a few bugs with the wizard and sometimes it doesn't update the dataset correctly.

[cory_jackson]

That's great advice. Thank you. Is it better not to use the Wizard? Is there a manual way that is better?

[wes4dbt]

That's great advice. Thank you. Is it better not to use the Wizard? Is there a manual way that is better?

I use the wizard all the time but you have to make sure if you modify the database that the dataset gets updated. You can manually create a type dataset, just Project->Add new item->Dataset.

[cory_jackson]

I have created DataSets that way before when I didn't rely on ouside data. I imagine then all one needs to do is create a DataAdapter. What is the easiest way to do that?

[wes4dbt]

Lots of good information Here

[cory_jackson]

Oh nice. Thanks!