auto-increment identity column

[BobLewiston]

Using Visual C# 2008 Express and SQL Server 2008 Express, I would like to insert new records into database "AdventureWorks", table "Person.Contact".

To my surprise, this table's int-value identity column "ContactID" does not appear to be auto-increment. I don't know how to confirm for sure that this is so. (I have installed SQL Server Management Studio, if that helps.)

How can I confirm this, and how can I make ContactID auto-increment?

And incidentally, will auto-increment just start incrementing from the highest existing ContactID, or will it first use lesser ContactID values that are absent from the table (presumably due to deletions)?

 

[Daeva]

All you should need to do is set it to identity and set the seed (1 for +1 ever time). Also, in your code, don't insert the ContactID column as one of your parameters to insert. See if that helps.

 

[BobLewiston]

Daeva:

...set it to identity and set the seed (1 for +1 ever time).

In SQL Server 2008 Management Studio Express, I have accessed Identity Specification by the following series of steps: expand table, expand Columns or Keys, right-click identity column, click Modify, in lower right frame scroll down to Identity Specification, expand it.

Three options appear: (Is Identity), Identity Increment and Identity Seed, which are set to Yes, 1 and 1, respectively.

So Identity Seed is already set to 1. Nonetheless, I don't seeContactID being incremented.

Maybe I'm going about this wrong. Here's how I'm adding the new record:

DataRow row = dataTable.NewRow (); // create new row (new record)
dataTable.Rows.Add (row);          // add new row to table

row.BeginEdit ();

row ["Title"] = txtTitle.Text;
row ["FirstName"] = txtFirstName.Text;
row ["MiddleName"] = txtMiddleName.Text;
row ["LastName"] = txtLastName.Text;
row ["Suffix"] = txtSuffix.Text;
row ["Phone"] = txtPhone.Text;
row ["EmailAddress"] = txtEmailAddress.Text;

row.EndEdit ();

At what point is row ["ContactID"] supposed to be incremented?

...don't insert the ContactID column as one of your parameters to insert.

The ContactID column is already not one of the parameters being inserted.

 

[Daeva]

And incidentally, will auto-increment just start incrementing from the highest existing ContactID, or will it first use lesser ContactID values that are absent from the table (presumably due to deletions)?

Also, it will not start at the lowest value (if records are deleted). It will pick up from where it left off, there is no way to "fill in" holes left by deleted records.

Your method for setting the identity is correct.

Are you using a dataset object, or just data tables as an intermediary?

For example, I don't use dataset objects because I make frequent changes to my databases, which require frequent changes to the datasets and can result in much frustration.

I write directly to the database (whereas the dataset requires you to commit the changes made to the dataset before it writes to the database). Could you post a more complete listing of the code?

Manual data insertion (in VB, sorry):

Dim val1 As String = "test"
Dim val2 As String = "test2"
Dim val3 As String = "test3"
Dim connStr As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("myConnectionStringName").ConnectionString()
Dim conn As New SqlConnection(connStr)
Dim cmd As New SqlCommand("INSERT INTO [TABLENAME] (NAME1,NAME2,ETC) VALUES('" & val1 & "','" & val2 & "', '" & val3 & "')",conn)
conn.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
conn.Close()
if rowsAffected > 0 then
  ' successfull
Else
  'not successfull
End if

I am leaving work right now, otherwise I would have shown that in c#. Also note that the way I retreive the connection string is consistent with a website, you may or may not be using a website. If not, you connection string is in the application config.

Let me know if that helps, or makes it worse.

Try setting the values for row before adding it to the datatable.