Search code examples
sqlvb.netsql-server-ceincrement

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!

Solution

  • 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)