473,834 Members | 1,874 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

auto-increment identity column

93 New Member
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
13 37578
ck9663
2,878 Recognized Expert Specialist
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
BobLewiston
93 New Member
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
2,878 Recognized Expert Specialist
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
BobLewiston
93 New Member
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 ExternalExcepti on 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 ExternalExcepti on. 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
2,878 Recognized Expert Specialist
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
BobLewiston
93 New Member
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
2,878 Recognized Expert Specialist
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
BobLewiston
93 New Member
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
2,878 Recognized Expert Specialist
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

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

Similar topics

2
2611
by: Manlio Perillo | last post by:
Hi. This post follows "does python have useless destructors". I'm not an expert, so I hope what I will write is meaningfull and clear. Actually in Python there is no possibility to write code that follows C++ RAII pattern. Of course Python objects are not statics like in C++, but in C++ the auto_ptr class is used for enforcing this pattern for dynamical
1
34324
by: Lew | last post by:
Hi all, I'm trying to create a page that has a user-selectable page auto-refresh option (IE 5.5). Essentially, it's a page that contains a checkbox, when the user checks the checkbox, I'd like the page to auto-refresh every 4 seconds....if the user un-checks the checkbox, I'd like to turn off the auto refresh. The page is as follows:
1
13965
by: Glabbeek | last post by:
I'm changing the layout of my site. Instead of using tables, I will use DIVs. It's working fine, except for 1 thing: In IE6 some DIVs are not the correct width. Mozilla and Opera are showing the page the way I want. Does anybody know a solution for this? First of all, the code I am using: CSS ------- body {
5
6130
by: Robert Downes | last post by:
I'm using the following in a page that I'm testing in Mozilla: p.actionLinkBlock {border: 1px #000000 dashed; padding: 0.2cm; width: auto} But the dashed border is extending to the right-edge of the screen. I want it to only extend as far as it needs to to nicely contain the content within (a couple of links). Is width: auto the wrong property to do this? Is Mozilla rendering the style wrong?
20
2884
by: Vijay Kumar R. Zanvar | last post by:
Hello, Unlike register, auto keyword can not be used to declare formal parameter(s). Is there any specific reason for this? Kind regards, Vijay Kumar R. Zanvar
5
5053
by: Samuel | last post by:
Hi, I am running into a problem of mixing UICulture = auto and allowing users to select culture using a dropdown list. I am detecting a querystring, "setlang", and when found, setting the CurrentUICulture to what's specified in the querystring. Since I want to allow UICulture auto detecting, I add UICulture = "auto" to page directive on each page.
5
3284
by: maya | last post by:
at work they decided to center divs thus: body {text-align:center} #content {width: 612px; text-align:left; margin: 0 auto 0 auto; } this works fine in IE & FF, EXCEPT in FF it doesn't work if I change 'auto' to 0 for left and right margin values; I have to leave those at 'auto'.. so I would like to know what exactly means 'auto' -- what value it represents exactly (and does it apply for all elements/values you might apply 'auto' to?)
22
3097
by: nospam_news | last post by:
I currently get asked about my usage of "auto". What is it for? The keyword is clearly superflous here. In contrast to the huge majority of C/C++ developers I write definitions very explicitly like that: int main(char argc, char *argv, char *env) { try { auto Exception mainException(1); mainException.setErrNo(42);
2
3083
by: Piotr K | last post by:
Hi, I've encountered a strange problem with Firefox which I don't have any idea how to resolve. To the point: I've <divelement with a style "height: auto" and I want to retrieve this value ("auto") in JavaScript - however instead of getting "auto" value, I get calculated height. In IE and Opera it simply returns "auto". Any ideas how to check in Firefox if element height was set to "auto" ? I'll be grateful for any help.
21
6367
by: JOYCE | last post by:
Look the subject,that's my problem! I hope someone can help me, thanks
0
9799
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10799
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10515
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9338
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6960
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5629
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5799
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3985
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3084
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.