[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to stop insertion of duplicate Primary Key

Posted on 2010-08-16
12
Medium Priority
?
517 Views
Last Modified: 2013-12-17
Experts
I want to know how to stop the user entering the primary key as duplicate key.
I am not using the Identity Specification as YES , rather it is NO.
The PK value is coming from another table where the same attribute is PK .
For more information please see the image attached.
How to use the try catch block for that purpose .
Please tell me.
Thanking you,
Anindya
using (SqlConnection con1 = new SqlConnection(Connectionstring.Connection))
            {


                Int32 Opportunuty_ID = _Opportunuty_ID;
                string query1 = "insert into Table_OpportunityTechnology(Opportunuty_ID,TechID,SubTechID) values(@paramOpportunuty_ID,@paramTechID,@paramSubTechID)";
                using (SqlCommand cmd1 = new SqlCommand(query1, con1))
                {
                    cmd1.Parameters.AddWithValue("@paramOpportunuty_ID", Opportunuty_ID);
                    cmd1.Parameters.AddWithValue("@paramTechID", TechID);
                    cmd1.Parameters.AddWithValue("@paramSubTechID", SubTechID);
                    con1.Open();
                    SqlDataReader reader1 = cmd1.ExecuteReader();
                }
            }
        }

Open in new window

1.JPG
error.JPG
0
Comment
Question by:ANINDYA
[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
  • 4
  • 2
  • +1
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33444156
first of all: for a INSERT, don't use ExecuteReader, but ExecuteNonQuery function

apart from that, the Table_OpportunityTechnology should not have SubTechID as PK, but as FK, from what I understand?!
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33444161
Try to catch it the first time, to see what exception number you get
using (SqlConnection con1 = new SqlConnection(Connectionstring.Connection))
            {


                Int32 Opportunuty_ID = _Opportunuty_ID;
                string query1 = "insert into Table_OpportunityTechnology(Opportunuty_ID,TechID,SubTechID) values(@paramOpportunuty_ID,@paramTechID,@paramSubTechID)";
                using (SqlCommand cmd1 = new SqlCommand(query1, con1))
                {
                    cmd1.Parameters.AddWithValue("@paramOpportunuty_ID", Opportunuty_ID);
                    cmd1.Parameters.AddWithValue("@paramTechID", TechID);
                    cmd1.Parameters.AddWithValue("@paramSubTechID", SubTechID);
                    con1.Open();
                    try
                    {
                         SqlDataReader reader1 = cmd1.ExecuteReader();
                    catch(SqlException ex)
                     {
                      if (!ex.ErrorCode == 0x80131904)
                              throw;
                      }
                }
            }
        }

Open in new window

0
 

Author Comment

by:ANINDYA
ID: 33444188
Expert Dhaest
please see the error in the attached image .
I have used your code sir.
Thanking you,
Take regards.
Anindya
error.JPG
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 53

Expert Comment

by:Dhaest
ID: 33444195
You need to place the errorcode here: ex.ErrorCode == 0x80131904

If you have message above, take a look at the value of ex.ErrorCode and use that one in the code
0
 
LVL 2

Expert Comment

by:soniethomas
ID: 33444200
Why don't you check if the row is there and insert if not present?
0
 

Author Comment

by:ANINDYA
ID: 33444215
Sage angelIII
I do not know why the database designer is using that primary key there also.
As per him it is necessary.
I can not tell you anything more than that.

error.JPG
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33444279
>As per him it is necessary.
I presume the designed requests "a" primary key.
please make sure you change that, I presume that the combination of several of the columns (aka the 3 FK columns) make up the PK.
if that is the case, select the 3 columns, and hit the PK icon, so all 3 columns get the icon of being primary key.
0
 

Author Comment

by:ANINDYA
ID: 33444352
Expert Dhaest
Will you please tell me what is the meaning of the statement of yours
"take a look at the value of ex.ErrorCode and use that one in the code"
Does it means that it is system dependent of something else.
please explain sir.
Thanking you
Take regards
Anindya
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33444396
I just gave an example, but I don't know which version of sql-server, sqllite, ... you are using
0
 

Author Comment

by:ANINDYA
ID: 33444403
Expert Dhaest
I am using sql server 2005
Thanking you
Anindya
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 2000 total points
ID: 33444471
You can easily find the exception-number (or errorcode) by yourself by examining the ex-object in your immeadiatly or watch-window

Can you try with this:

try
                    {
                         SqlDataReader reader1 = cmd1.ExecuteReader();
                     }
                    catch(SqlException ex)
                     {
                      if (!ex.Number.Equals(2627))
                              throw;
                      }
0
 

Author Closing Comment

by:ANINDYA
ID: 33444606
Expert Dhaest
Thank you very much for the reply and the correct answer .
Sir I have a small request that is if you have a bit of time please glance upon the below question.
The url is
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_26400189.html?cid=239#a33426981

Anyway thanking you,
Hope to get your assistance in future too.
Thanking you
Anindya Chatterjee
Bangalore
India
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

656 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