Solved

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

Posted on 2009-04-03
9
921 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

636 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