?
Solved

my first sql insert statement via c# csharp .net sql server 2005

Posted on 2009-04-03
9
Medium Priority
?
922 Views
Last Modified: 2012-05-06
I have five columns, the first is an int identity auto-incrementing field, and the next four are varchar(xx).

I am simply trying to do the first insert into the table, and I get an error message that says:

Cannot insert duplicate key row in object 'dbo.tblFormsOfTests' with unique index 'IX_col2to5tblFormsOfTests'.
The statement has been terminated.

So I am thinking maybe I'm not inserting in regards to the identity field because I don't include it as the first field?

I thought if I just insert the values for fields 2 through 5, that SQL Server 2005 will automatically insert the incremented value for the first field.

What should I do??

By the way there isn't any record like the one below already in the table.
sqlCmd.CommandText =
                        "INSERT INTO " +
                        "tblFormsOfTests(multiplicity, instrument, test_type, resides_in_assembly) " +
                        "VALUES('xx', 'yy', 'zz', 'ff');";

Open in new window

0
Comment
Question by:yaronusa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 24061667
can you script the table (create script ) and post it for us?
0
 
LVL 29

Expert Comment

by:Nightman
ID: 24061781
The error you are getting means that a record already exists with the values "xx,yy,zz,ff'. This is NOT a primary key violation, and it is not an error with the identity value - there is a unique index that has been created on the table, and this is preventing you from inserting a duplicate row.
0
 

Author Comment

by:yaronusa
ID: 24061820
I don't really know how to do that, I've designed the table via Visual Studio 2005. It has one record in it. I took some images below that kind of describe the table.

The primary key is the first field (see image below), and the unique index includes fields 2 - 4.

Do the images below help?
 


// SQL Insert Statement
.
.
.
sqlCmd.CommandText = "INSERT INTO " +
                     "tblFormsOfTests(multiplicity, instrument, test_type, resides_in_assembly) " +
                     "VALUES('xx', 'yy', 'zz', 'ff');";
 
recordsAffected = sqlCmd.ExecuteNonQuery(); // Error is generated here.
.
.
.

Open in new window

SP32-20090403-122436.png
SP32-20090403-122456.png
SP32-20090403-122521.png
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 29

Accepted Solution

by:
Nightman earned 2000 total points
ID: 24061945
Check that the values in your insert statement are the ones that are actually being executed (step through the code). SQL will not throw that error if the data differs.
0
 

Author Comment

by:yaronusa
ID: 24061999
I did step through the code, and those are the values that are actually being executed. Below is an image of a debug session and the exception thrown.



SP32-20090403-125201.png
0
 

Author Comment

by:yaronusa
ID: 24062087
Egghh... YOU are CORRECT!

...

It was already in there... didn't refresh the table properly or something... thank you!
0
 

Author Closing Comment

by:yaronusa
ID: 31566320
Egghh... YOU are CORRECT!

...

It was already in there... didn't refresh the table properly or something... thank you!
0
 

Author Comment

by:yaronusa
ID: 24062116
For whatever reason, in MS SQL Server Management Studio Express, if I right-click on the table, and select refresh from the context menu that pops, it does not show the most recent table. However, from VS 2005, doing the same thing properly refreshes the table. Go figure. :)
0
 
LVL 29

Expert Comment

by:Nightman
ID: 24062326
Glad you found it. You have to re-run the "open table" statement by clicking the red exclamation mark button above the query window. Right-click, refresh from the tree view refreshes the objects in the tree view only.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question