By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,422 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,422 IT Pros & Developers. It's quick & easy.

auto-increment identity column

P: 93
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)?
Mar 24 '09 #1
Share this Question
Share on Google+
13 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Mar 25 '09 #2

P: 93
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
  1. DataRow row = dataTable.NewRow (); // create new row (new record)
  2. 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?
Mar 26 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Try to do an insert on the table itself. Or maybe that it's a sample DB so microsoft only allow read-only.


-- CK
Mar 26 '09 #4

P: 93
ck9663:

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
  1. DataRow row = dataTable.Rows [currRec];
  2. row.BeginEdit ();
  3.  
  4. // try { row ["ContactID"] = txtContactID.Text; }
  5. // catch (Exception exc) { MessageBox.Show (exc.Message); }
  6.  
  7. row ["Title"] = txtTitle.Text;
  8. row ["FirstName"] = txtFirstName.Text;
  9. row ["MiddleName"] = txtMiddleName.Text;
  10. row ["LastName"] = txtLastName.Text;
  11. row ["Suffix"] = txtSuffix.Text;
  12. row ["Phone"] = txtPhone.Text;
  13. row ["EmailAddress"] = txtEmailAddress.Text;
  14.  
  15. row.EndEdit ();
  16.  
  17. try { dataAdapter.Update (dataSet, "Person.Contact"); }
  18. catch (System.Runtime.InteropServices.ExternalException exc)
  19. {
  20.     MessageBox.Show (exc.Message + "\n\nSystem.Runtime.InteropServices.ExternalException");
  21. }
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
  1. try { row ["ContactID"] = txtContactID.Text; }
  2. 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.

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.
Mar 26 '09 #5

ck9663
Expert 2.5K+
P: 2,878
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
Mar 26 '09 #6

P: 93
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?
Mar 26 '09 #7

ck9663
Expert 2.5K+
P: 2,878
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
Mar 26 '09 #8

P: 93
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?
Mar 27 '09 #9

ck9663
Expert 2.5K+
P: 2,878
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
Mar 27 '09 #10

P: 93
ck9663:

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?

Although you can capture this inside a trigger...
Do you mean inside try and catch blocks for handling exceptions?

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.

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.
Mar 27 '09 #11

P: 3
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.
Jul 1 '09 #12

P: 1
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.
Jul 1 '09 #13

MrMancunian
Expert 100+
P: 569
@owofeminew
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
Jul 15 '09 #14

Post your reply

Sign in to post your reply or Sign up for a free account.