CODEWITHSUNDEEP
sqlvb.netsql-server-ceincrement
BenAdamson
BenAdamson

Reputation: 675

Auto Increment in SQL INSERT Query?

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

Answers (2)

James Johnson
James Johnson

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

Mark Byers
Mark Byers

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

Related Questions