SQL Server - Duplicate Keys Error

Question

I'm running an SQL stored procedure which inserts records into a table from 2 different tables. Since there is a primary key constraint on the table that is being inserted, the exception Duplicate key was ignored raises. But when I call the SP from my C# windows form application this raises the exception and the execution stops. How do I handle this? My C# code is below:

qry = "Exec Database.dbo.spname '" + searchvalue + "'";

if (rs.State == 1) { 
   rs.Close(); 
}

rs.Open(qry, sqltbl.GetSqlConnection());

I get an exception error on the rs.Open line

I don't get you @Larnu are you asking me to edit my question or the code..?? — Karthikeyan Natarajan
– Karthikeyan Natarajan, Commented Jul 18, 2018 at 8:31
Just because it's an "example" doesn't mean you should be using poor/bad coding practices. Regardless if it is an example or not, string concatenation for a query is bad, and should never be done. — Thom A
– Thom A ♦, Commented Jul 18, 2018 at 8:59
For the last time people.. what I had posted was pretty rough code and isn't the actual one. I simply wanted to put in a context of what I was actually seeking and I DO KNOW ABOUT SQL INJECTION AND ITS CONSEQUENCES. Please read all my comments before posting further. Thanks. @Caldazar. This goes for you too sir. — Karthikeyan Natarajan
– Karthikeyan Natarajan, Commented Jul 18, 2018 at 11:21
People here are taking too much time in pointing out others mistakes rather than providing / helping with the actual solutions. Stop feeding your ego and at least let the genuine ones to help. — Karthikeyan Natarajan
– Karthikeyan Natarajan, Commented Jul 18, 2018 at 11:23

EzLo · Accepted Answer · 2018-07-18 09:04:11Z

1

Seems that the exception is being thrown from the procedure Database.dbo.spname you are executing. Exceptions aren't something you should ignore or hide under the couch.

You probably have a statement like the following:

INSERT INTO SomeTable (
    KeyColumn,
    ColumnName)
SELECT
    I.InsertingKeyColumn,
    I.ColumnName
FROM
    SomeOtherTable AS I

Make use of the NOT EXISTS to check existance of records before trying to insert and the error won't pop up.

INSERT INTO SomeTable (
    KeyColumn,
    ColumnName)
SELECT
    I.InsertingKeyColumn,
    I.ColumnName
FROM
    SomeOtherTable AS I
WHERE
    NOT EXISTS (SELECT 'not yet in SomeTable' FROM SomeTable AS S WHERE I.InsertingKeyColumn = S.KeyColumn)

answered Jul 18, 2018 at 9:04

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server - Duplicate Keys Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related