inserting records in using foreign key and primary relationship between both tables

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I am inserting records into sql table using c# window based application.
both have primary and foreign key relation.

once I insert the records I am getting this error:

Violation of PRIMARY KEY constraint 'PK__Configur__1CDF71AC5AB5B85B'. Cannot insert duplicate key in object 'dbo.ConfigurationDetails'. The duplicate key value is (XCD213123).

my codes are blow. please refer some using links same as this codes

What I have tried:

C#

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DCID;Integrated Security=True");

string query = "insert into ConfigurationDetails(SystemserialNo,SystemPartsDetails,manufactured_Details) values('"
    + textBox1.Text
    + "','"
    + textBox2.Text
    + "','"
    + textBox3.Text
    + "')";
    
string query1 = "select * from SystemDetails";

SqlCommand cmd1 = new SqlCommand(query1,con);
SqlCommand cmd  = new SqlCommand(query, con);

SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
SqlDataAdapter da  = new SqlDataAdapter(cmd);

DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();

da.Fill(ds1, "ConfigurationDetails");
da1.Fill(ds2, "SystemDetails");

foreach (DataRow row in ds1.Tables["ConfigurationDetails"].Rows)
{
    if (row == ds2.Tables["SystemDetails"].Rows[0]["SystemserialNo"])
    {
        MessageBox.Show("yes");
    }
    else
    {
        MessageBox.Show("No");
    }
    
}

Posted 4-Feb-16 8:22am

faizyab 2009

Updated 4-Feb-16 9:20am

StM0n

v3

Add a Solution

Comments

Sascha Lefèvre 4-Feb-16 14:52pm

What is the primary key of your table ConfigurationDetails?

faizyab 2009 4-Feb-16 14:58pm

SystemserialNo is the primary key

Richard Deeming 4-Feb-16 15:10pm

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

StM0n 4-Feb-16 15:22pm

my guess, you're trying to insert a primary key a second time...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Lefèvre · Answer 1 · 2016-02-04T09:10:00

As SystemserialNo is the primary key of your table ConfigurationDetails and you're letting the user enter the value for it (in textBox1) it's really trivial: You entered a primary key which already existed in that table.

If you entered it a second time because there actually is a second item/part that has the same SystemserialNo then your choice of SystemserialNo as the primary key isn't right.

If there can't be a second item/part with the same SystemserialNo then you need to check that first before attempting to insert what the user entered and if it already exists, notify the user without inserting it into the database.

Apart from that:
- Don't use string-concatenation for SQL-statements. Your code becomes susceptible to SQL-injection and less readable/maintainable. Use SQL-parameters[^] instead.
- Give your controls (textBox1 - 3 here) meaningful names (all designer generated identifiers in general). That's easy with the F2-refactoring of Visual Studio.
- Validate user input before relying on its validity (unless you've just not shown it here, the TextBox-inputs could be empty or otherwise invalid).
- Use using-statements with your Sql*****-objects in order to safely free the allocated system resources.