How to insert data in a table with foreign key

Question

I want to insert data in table Article which has a foreign key to table Categorie.

I created a form with TextBoxes and I put the foreign key in a bounded ComboBox to Categorie table. This is the code I use:

private void button15_Click(object sender, EventArgs e)
{
    SqlConnection con3 = new SqlConnection();
    con3.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\GestStock.mdf;Integrated Security=True;User Instance=True";
    con3.Open();

    SqlCommand insertCommande1 = new SqlCommand("INSERT INTO [Article] (CodeArticle, LibArticle, IdCategorie, Quantite, StockActuel, StockMinimum, PrixAchat, DateAchat, NumeroFacture)" +
              "VALUES(@CodeArticle, @LibArticle, @IdCategorie, @Quantite, @StockActuel, @StockMinimum, @PrixAchat, @DateAchat, @NumeroFacture)", con3);

    insertCommande1.Parameters.AddWithValue("@CodeArticle", codeArticleTextBox1.Text);
    insertCommande1.Parameters.AddWithValue("@LibArticle", libArticleTextBox.Text);
    insertCommande1.Parameters.AddWithValue("@IdCategorie",idCategorieComboBox.Text);

    insertCommande1.Parameters.AddWithValue("@Quantite", Convert.ToInt32(quantiteTextBox1.Text));
    insertCommande1.Parameters.AddWithValue("@StockActuel", Convert.ToInt32(stockActuelTextBox1.Text));
    insertCommande1.Parameters.AddWithValue("@StockMinimum", Convert.ToInt32(stockMinimumTextBox1.Text));
    insertCommande1.Parameters.AddWithValue("@PrixAchat", Convert.ToInt32(prixAchatTextBox1.Text));
    insertCommande1.Parameters.AddWithValue("@DateAchat", Convert.ToDateTime(dateAchatDateTimePicker1.Text));
    insertCommande1.Parameters.AddWithValue("@NumeroFacture", numeroFactureTextBox.Text);

    insertCommande1.ExecuteNonQuery();

    MessageBox.Show("L'article est ajouté avec succées");
    con3.Close();
}

But when I execute this code the Article is added successfully but when I exit the application and re-run it I get this error message:

Failed to enable constraints. One or more rows contain values that violate type constraints not null, unique, or foreign key.

I know that it is caused by this

insertCommande1.Parameters.AddWithValue("@IdCategorie", idCategorieComboBox.Text);

So my question is how to do insert query in a correct way??

Thanks in advance.

Failed to enable constraints. One or more rows contain values that violate type constraints not null, unique, or foreign key. — user4374239, Dec 24 '14 at 09:52
You have to insert such value to `IdCategorie` that exists in table to which FK is built. — Andrey Korneyev, Dec 24 '14 at 09:54
Don't write query in page, try to use strored procedure, SP provide the solution for you.. — bgs, Dec 24 '14 at 09:55
Does idCategorieComboBox.Text containt the ID? Maybe you mean idCategorieComboBox.Value? — mxix, Dec 24 '14 at 10:34
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Dec 24 '14 at 10:39
http://stackoverflow.com/questions/20051986/data-insert-using-input-output-stored-procedure — bgs, Dec 24 '14 at 10:43
idCategorieComboBox.Text contains LibCategorie because it is the DisplayMember of the bounded combobox (idCategorie is the ValueMember), there is no preperty idCategorieComboBox.Value. — user4374239, Dec 24 '14 at 11:32

user4340666 · Accepted Answer · 2014-12-24T14:34:53.450

0

Try this :

insertCommande1.Parameters.AddWithValue("@IdCategorie",  idCategorieComboBox.SelectedValue.ToString());

edited Dec 24 '14 at 14:34

answered Dec 24 '14 at 13:14

user4340666

1,453
15
36

How to insert data in a table with foreign key

1 Answers1