在使用两个表之间的外键和主要关系时插入记录 [英] inserting records in using foreign key and primary relationship between both tables

问题描述

我使用基于c＃window的应用程序将记录插入到sql表中。

都有主键和外键关系。



一旦我插入记录我收到此错误：

违反PRIMARY KEY约束'PK__Configur__1CDF71AC5AB5B85B'。无法在对象'dbo.ConfigurationDetails'中插入重复键。重复的键值是（XCD213123）。

我的代码是打击的。请使用与此代码相同的链接参考一些



我尝试过：

 SqlConnection con =  new  SqlConnection（  Data Source = .; Initial Catalog = DCID; Integrated Security = True）; 
 
  string  query =  插入ConfigurationDetails（SystemserialNo，SystemPartsDetails，manufactured_Details）值（' 
 + 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]。行）
 {
  if （row == ds2.Tables [  SystemDetails]。行[ 0 ] [  SystemserialNo]）
 {
 MessageBox.Show（  yes）; 
} 
  else  
 {
 MessageBox.Show（ 否）; 
} 
 
} 

解决方案

因为 SystemserialNo 是主要的你的表 ConfigurationDetails 的密钥，你让用户输入它的值（在textBox1中）它真的很简单：你输入了一个已经存在于该表中的主键。



如果您第二次输入，因为实际上有第二个项目/部分具有相同的 SystemserialNo ，那么您选择 SystemserialNo 因为主键不对。



如果没有第二个项目/部分具有相同的 SystemserialNo 那么你需要在尝试插入用户输入的内容之前先检查它，如果它已经存在，请通知用户而不将其插入数据库。



除此之外：

- 不要对SQL语句使用字符串连接。您的代码容易受到SQL注入的影响，并且不易读取/维护。使用 SQL参数 [< a href =https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter%28v=vs.110%29.aspxtarget =_ blanktitle =New Window>相反，取而代之。

- 给你的控件（textBox1 - 3这里）有意义的名字（一般所有设计师生成的标识符）。使用Visual Studio的F2重构很容易。

- 在依赖其有效性之前验证用户输入（除非你在这里没有显示，TextBox输入可能是空的或者无效） 。

- 使用使用 -statements和Sql ***** - 对象，以便安全地释放分配的系统资源。

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:

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");
    }
    
}

解决方案

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.

这篇关于在使用两个表之间的外键和主要关系时插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！