Connecting Tech Pros Worldwide Help | Site Map

auto-increment identity column

  #1  
Old March 25th, 2009, 12:06 AM
Member
 
Join Date: Feb 2009
Posts: 68
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)?
  #2  
Old March 25th, 2009, 06:10 AM
ck9663's Avatar
Expert
 
Join Date: Jun 2007
Posts: 1,914
Provided Answers: 1

re: auto-increment identity column


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
  #3  
Old March 26th, 2009, 01:57 AM
Member
 
Join Date: Feb 2009
Posts: 68

re: auto-increment identity column


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?
  #4  
Old March 26th, 2009, 03:30 AM
ck9663's Avatar
Expert
 
Join Date: Jun 2007
Posts: 1,914
Provided Answers: 1

re: auto-increment identity column


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


-- CK
  #5  
Old March 26th, 2009, 08:07 PM
Member
 
Join Date: Feb 2009
Posts: 68

re: auto-increment identity column


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
  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.

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.
  #6  
Old March 26th, 2009, 09:31 PM
ck9663's Avatar
Expert
 
Join Date: Jun 2007
Posts: 1,914
Provided Answers: 1

re: auto-increment identity column


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
  #7  
Old March 26th, 2009, 11:17 PM
Member
 
Join Date: Feb 2009
Posts: 68

re: auto-increment identity column


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?
  #8  
Old March 26th, 2009, 11:46 PM
ck9663's Avatar
Expert
 
Join Date: Jun 2007
Posts: 1,914
Provided Answers: 1

re: auto-increment identity column


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
  #9  
Old March 27th, 2009, 01:55 AM
Member
 
Join Date: Feb 2009
Posts: 68

re: auto-increment identity column


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?
  #10  
Old March 27th, 2009, 06:37 AM
ck9663's Avatar
Expert
 
Join Date: Jun 2007
Posts: 1,914
Provided Answers: 1

re: auto-increment identity column


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
  #11  
Old March 27th, 2009, 11:04 PM
Member
 
Join Date: Feb 2009
Posts: 68

re: auto-increment identity column


ck9663:

Quote:
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.
  #12  
Old July 1st, 2009, 01:23 PM
Newbie
 
Join Date: Jul 2009
Posts: 3

re: auto-increment identity column


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.
  #13  
Old July 1st, 2009, 01:32 PM
Newbie
 
Join Date: Jul 2009
Posts: 1

re: auto-increment identity column


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.
  #14  
Old July 15th, 2009, 12:18 PM
MrMancunian's Avatar
Expert
 
Join Date: Jul 2008
Location: Utrecht, The Netherlands
Posts: 264

re: auto-increment identity column


Quote:
Originally Posted by owofeminew View Post
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
Reply


Similar Threads
Thread Thread Starter Forum Replies Last Post
[C#] Getting the value of an identity column after an insert insertAlias answers 6 September 26th, 2008 09:15 PM
DB2 Express UDB 9 with db2jcc JDBC driver- Issues with Auto generatedfield Zunil answers 2 July 14th, 2008 08:45 PM
Insert value in an auto-identity column Bharat answers 1 July 23rd, 2005 09:38 AM
auto-number/Identity column Larry Rekow answers 2 July 20th, 2005 05:42 AM
Using @@IDENTITY in manual commit mode Deepak answers 1 July 20th, 2005 02:50 AM