Reputation: 675
I am connected to a SQL Server Compact Edition Database. One of the tables involved has 3 columns - CompanyID, CompanyName and CompanyNotes. I have created a form in Visual Basic to add a new company to the database, and of course I don't want the user to have to manually put in the company ID.
In the dataset.xsd I have created an INSERT SQL query that simply inserts a new row with the CompanyID, CompanyName and CompanyNotes. I have set the CompanyID to auto-increment. However, when I tried inserting the name and notes it didn't like it because I set the ID field to be mandatory. How will I insert the new company data into a new row with a new ID?
The very basic code I currently use for adding a new company:
Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
Me.CompanyTableAdapter1.AddCompany(??????????, txtCompanyName.Text, txtCompanyNotes.Text)
Me.Close()
End Sub
What should I put instead of the '?????', or should I be doing something completely different?
Thanks!
Upvotes: 1
Views: 9030
Reputation: 46077
This should be done at the database level with an auto-incrementing identity specification:
ALTER TABLE TableName ALTER COLUMN ColumnName IDENTITY (/*seed*/1, /*increment*/1)
Once the identity column is in place, you can omit the identity column during inserts, as it will be assigned automatically on insert.
Upvotes: 0
Reputation: 839264
Simply omit the ID field in your SQL query.
INSERT INTO company (CompanyName, CompanyNotes) VALUES (?, ?)
Then your method can be simplified by removing the ID parameter:
Me.CompanyTableAdapter1.AddCompany(txtCompanyName.Text, txtCompanyNotes.Text)
Upvotes: 3