Solved

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

Posted on 2009-04-03
9
912 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

774 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