Solved

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

Posted on 2009-04-03
9
916 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 29

Accepted Solution

by:
Nightman earned 500 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

697 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