Go Back   Computer Forums > General Computing > Programming
Click Here to Login
Join Computer forums Today


Reply
 
Thread Tools Search this Thread Display Modes
 
Old 03-24-2009, 07:09 PM   #1
Baseband Member
 
Join Date: Nov 2008
Posts: 91
Default auto-increment identity column

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

BobLewiston is offline   Reply With Quote
Old 03-24-2009, 07:47 PM   #2
In Runtime
 
Daeva's Avatar
 
Join Date: Dec 2005
Posts: 407
Send a message via AIM to Daeva Send a message via MSN to Daeva Send a message via Yahoo to Daeva
Default Re: auto-increment identity column

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

__________________
**Official Self-proclaimed glorified excessive (insert additional adjectives here) post editor/modifier.
Edit = Best feature ever
http://www.twitter.com/xDaevax
Daeva is offline   Reply With Quote
Old 03-25-2009, 05:32 PM   #3
Baseband Member
 
Join Date: Nov 2008
Posts: 91
Default Re: auto-increment identity column

Daeva:

Quote:
...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:
Code:
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?
Quote:
...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.
BobLewiston is offline   Reply With Quote
Old 03-25-2009, 05:54 PM   #4
In Runtime
 
Daeva's Avatar
 
Join Date: Dec 2005
Posts: 407
Send a message via AIM to Daeva Send a message via MSN to Daeva Send a message via Yahoo to Daeva
Default Re: auto-increment identity column

Quote:
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):
Code:
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.
__________________
**Official Self-proclaimed glorified excessive (insert additional adjectives here) post editor/modifier.
Edit = Best feature ever
http://www.twitter.com/xDaevax
Daeva is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



All times are GMT -5. The time now is 12:37 PM.


Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.0