auto-increment identity column

BobLewiston · #1 March 25th, 2009, 12:06 AM

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

ck9663 · #2 March 25th, 2009, 06:10 AM

Open the Management Studio. Right click on the table then choose modify. On the Identity Specification, check if the property is set to YES.

-- CK

BobLewiston · #3 March 26th, 2009, 01:57 AM

ck9663:

In SQL Server 2008 Management Studio Express, only three options appear under Identity Specification: (Is Identity), Identity Increment and Identity Seed, which are set to Yes, 1 and 1, respectively. Identity Increment = 1 would seem to imply that auto-increment is on, but it doesn't seem to be working.

But maybe I just don't know what I'm doing. I create the new DataRow as follows:

Expand|Select|Wrap|Line Numbers

 DataRow row = dataTable.NewRow (); // create new row (new record)

dataTable.Rows.Add (row);          // add new row to table

But at this point (and ever after, unless I assign a value in my code, which I shouldn't be doing if ContactID is supposed to auto-increment), row ["ContactID"].ToString () is just null. What else should I be doing to implement auto-increment?

ck9663 · #4 March 26th, 2009, 03:30 AM

Try to do an insert on the table itself. Or maybe that it's a sample DB so microsoft only allow read-only.

-- CK

BobLewiston · #5 March 26th, 2009, 08:07 PM

ck9663:

Quote:

Try to do an insert on the table itself.

I'm not sure what you mean by "the table itself". I'm trying to modify a DataRow, but that row has already been added to the table (see code below). Here are the problems I've been having:

When I try to save a new (inserted) record via the following code:

Expand|Select|Wrap|Line Numbers

 DataRow row = dataTable.Rows [currRec];

row.BeginEdit ();
 
// try { row ["ContactID"] = txtContactID.Text; }

// catch (Exception exc) { MessageBox.Show (exc.Message); }
 
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 ();
 
try { dataAdapter.Update (dataSet, "Person.Contact"); }

catch (System.Runtime.InteropServices.ExternalException exc)

{

    MessageBox.Show (exc.Message + "\n\nSystem.Runtime.InteropServices.ExternalException");

}

I get the following ExternalException message:

Cannot insert the value NULL into column 'PasswordHash', table 'AdventureWorks.Person.Contact'; column does not allow nulls. INSERT fails. The statement has been terminated.

When I uncomment

Expand|Select|Wrap|Line Numbers

 try { row ["ContactID"] = txtContactID.Text; }

catch (Exception exc) { MessageBox.Show (exc.Message); }

and enter ContactID in my WinForm, I get the same ExternalException. With the above uncomment made, when I DON'T enter a ContactID, I get the following exception message:

Input string was not in correct format. Couldn't store <> in ContactID Column. Expected type is int32.

Quote:

Or maybe that it's a sample DB so microsoft only allow read-only.

How can I tell? I looked at the table in SQL Server 2008 Management Studio Express, but I couldn't see anything pertinent.

ck9663 · #6 March 26th, 2009, 09:31 PM

All your errors has nothing to do with ContactID being an IDENTITY.

Your first error happens because you're saving a NULL into a column that does not allow nulls. Your second error looks like a problem on your sql string.

--- CK

BobLewiston · #7 March 26th, 2009, 11:17 PM

ck9663:

What I mean is: when the value of the highest-numbered ContactID is auto-incremented, where do I copy that newly-incremented value from to put it into the ContactID column of the new record that is being inserted?

ck9663 · #8 March 26th, 2009, 11:46 PM

When you do an insert into a table with an IDENTITY column, you must not include that column on the column that you're trying to populate. SQL Server will handle that increment for you and will populate the IDENTITY column even if you did not specify it.

--- CK

BobLewiston · #9 March 27th, 2009, 01:55 AM

ck9663:

OK then, when the value of the highest-numbered ContactID is auto-incremented, where do I find that newly-incremented value so I can display it onscreen? And is it not incremented until the record is actually saved to the DataSet? Because if that's so, and if I want to display it onscreen before the new record is saved, I would have to increment it myself so I can display it. Then what would be the point of auto-incrementing?

ck9663 · #10 March 27th, 2009, 06:37 AM

Quote:

And is it not incremented until the record is actually saved to the DataSet?

YES. Although you can capture this inside a trigger, the actual value is not incremented until it's written. You can not just handle the increment. What if your table is accessed by multiple users? Then you'll have problem who has the next value of your IDENTITY.

What are you trying to do? Maybe there are other ways of doing it.

--- CK

BobLewiston · #11 March 27th, 2009, 11:04 PM

ck9663:

Quote:

And is it not incremented until the record is actually saved to the DataSet?

YES.

Is it actually incremented when saved to the DataSet or when the app actually reconnects to the database and saves it there?

Quote:

Although you can capture this inside a trigger...

Do you mean inside try and catch blocks for handling exceptions?

Quote:

You can not just handle the increment. What if your table is accessed by multiple users? Then you'll have problem who has the next value of your IDENTITY.

Point taken.

Quote:

What are you trying to do?

I would have liked to display the ContactID (the indentity column) for the new record as the new record is being entered by being typed in. But perhaps that will not be possible.

Aceswild · #12 July 1st, 2009, 01:23 PM

I'm doing a insert of a dat file into a SQL table and if the ID column is auto-incremented then I will turn this off and insert the dat file then turn back on. How would have the program check if the ID column is auto-incremented from my program. I'm doing a insert of a dat file into a SQL table and if the ID column is auto-incremented then I will turn this off and insert the dat file then turn back on.

owofeminew · #13 July 1st, 2009, 01:32 PM

My opinion:

Is it actually incremented when saved to the DataSet or when the app actually reconnects to the database and saves it there?

Yes. The increment is done only when new data is created or inserted.

I would have liked to display the ContactID (the indentity column) for the new record as the new record is being entered by being typed in. But perhaps that will not be possible.

I suggest you try this

connect to table and get last ContactID
increment last ContactID by 1 eg lastContactID +=1
insert new record immediately with the values typed
display your new ContactID as incrementedContactID
After record typing is complete, Update new record with complete Record where ContactID = incrementedContactID

Hope this helps.

MrMancunian · #14 July 15th, 2009, 12:18 PM

Quote:

Originally Posted by owofeminew

I suggest you try this

connect to table and get last ContactID
increment last ContactID by 1 eg lastContactID +=1
insert new record immediately with the values typed
display your new ContactID as incrementedContactID
After record typing is complete, Update new record with complete Record where ContactID = incrementedContactID

I'd advise you to be very careful with this method, as you can't verify if lastContactID +=1 is the same as the new Identity. I think I would create a new record with null values the moment someone starts typing and return the Identity Seed from the Insert query. After that, you simply update the row by using the new identity.

Steven