Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-04-03
9
Medium Priority
?
924 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

571 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