Insert data in Sql Server datable from Datagridview c#

Question

I have a problem in inserting data in datatable from datagridview this is my code:

SqlConnection con1 = new SqlConnection();
con1.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\GestStock.mdf;Integrated Security=True;User Instance=True";
con1.Open();
string insertcmd = "INSERT INTO [Article] (CodeArticle,LibelleFr,LibelleAr,IdCategorie,InfomationsDetaille,Quantite,StockActuel,StockMinimum,PrixAchat,DateAchat,NumeroFacture)" +
                     "VALUES(@CodeArticle1,@LibelleFr1,@LibelleAr1,@IdCategorie1 ,@InfomationsDetaille1,@Quantite1,@StockActuel1,@StockMinimum1,@PrixAchat1,@DateAchat1,@NumeroFacture1)";
using (SqlCommand cmd = new SqlCommand(insertcmd,con1))
{
  cmd.Parameters.Add("@CodeArticle1", SqlDbType.NVarChar);
  cmd.Parameters.Add("@LibelleFr1",SqlDbType.NVarChar);
  cmd.Parameters.Add("@LibelleAr1",SqlDbType.NVarChar);
  cmd.Parameters.Add("@IdCategorie1",SqlDbType.Int);
  cmd.Parameters.Add("@InfomationsDetaille1",SqlDbType.NVarChar);
  cmd.Parameters.Add("@Quantite1",SqlDbType.Int);
  cmd.Parameters.Add("@StockActuel1",SqlDbType.Int);
  cmd.Parameters.Add("@StockMinimum1",SqlDbType.Int);
  cmd.Parameters.Add("@PrixAchat1",SqlDbType.Int);
  cmd.Parameters.Add("@DateAchat1",SqlDbType.Date);
  cmd.Parameters.Add("@NumeroFacture1",SqlDbType.NVarChar);
  for(int i = 0; i < dataGridView2.Rows.Count; i++)
   {
      cmd.Parameters["@CodeArticle1"].Value= dataGridView2.Rows[i].Cells["codeArticleDataGridViewTextBoxColumn"].Value;
      cmd.Parameters["@LibelleFr1"].Value= dataGridView2.Rows[i].Cells["libelleFrDataGridViewTextBoxColumn"].Value;
      cmd.Parameters["@LibelleAr1"].Value= dataGridView2.Rows[i].Cells["libelleArDataGridViewTextBoxtColumn"].Value;
      cmd.Parameters["@IdCategorie1"].Value= dataGridView2.Rows[i].Cells["idCategorieDataGridViewTexBoxColumn"].Value;
      cmd.Parameters["@InfomationsDetaille1"].Value= dataGridView2.Rows[i].Cells["InfomationsDetailleDataGridViewTextBoxColumn"].Value;
      cmd.Parameters["@Quantite1"].Value= dataGridView2.Rows[i].Cells["QuantiteDataGridViewTextBoxColumn"].Value;
      cmd.Parameters["@StockActuel1"].Value= dataGridView2.Rows[i].Cells["StockActuelDataGridViewTextBoxColumn"].Value;
      cmd.Parameters["@StockMinimum1"].Value= dataGridView2.Rows[i].Cells["StockMinimumDataGridViewTextBoxColumn"].Value;
      cmd.Parameters["@PrixAchat1"].Value= dataGridView2.Rows[i].Cells["PrixAchatDataGridViewTextBoxColumn"].Value;
      cmd.Parameters["@DateAchat1"].Value= dataGridView2.Rows[i].Cells["DateAchatDataGridViewTextBoxColumn"].Value;
      cmd.Parameters["@NumeroFacture1"].Value= dataGridView2.Rows[i].Cells["NumeroFactureDataGridViewTextBoxColumn"].Value;
  }
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
con1.Close();
}
MessageBox.Show("données enregistrés");          

But i got this error message:

The parameterized query '(@CodeArticle1 nvarchar(4000),@LibelleFr1 nvarchar(4000),@Libell' expects the parameter '@CodeArticle1', which was not supplied.

EDIT:

i added this condition in the the for loop to all parameters :

if (dataGridView2.Rows[i].Cells["codeArticleDataGridViewTextBoxColumn"].Value == null)
                        {
                            cmd.Parameters["@CodeArticle1"].Value = DBNull.Value;
                        }
                        else
                        {
                            cmd.Parameters["@CodeArticle1"].Value = dataGridView2.Rows[i].Cells["codeArticleDataGridViewTextBoxColumn"].Value;
                        }

and i got this error message:

Cannot insert the value NULL into column 'CodeArticle', table 'C:\DOCUMENTS AND SETTINGS\ADMINISTRATEUR\MES DOCUMENTS\GESTIONSTOCK\GESTIONSTOCK\BIN\DEBUG\GESTSTOCK.MDF.dbo.Article'; column does not allow nulls. INSERT fails. The statement has been terminated

Can someone solve this??

thank you

Answer 1

try this code:

SqlConnection con1 = new SqlConnection();
                con1.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\GestStock.mdf;Integrated Security=True;User Instance=True";


                string insertcmd = "INSERT INTO [Article] (CodeArticle,LibelleFr,LibelleAr,IdCategorie,InfomationsDetaille,Quantite,StockActuel,StockMinimum,PrixAchat,DateAchat,NumeroFacture)" +
                     "VALUES(@CodeArticle1,@LibelleFr1,@LibelleAr1,@IdCategorie1 ,@InfomationsDetaille1,@Quantite1,@StockActuel1,@StockMinimum1,@PrixAchat1,@DateAchat1,@NumeroFacture1)";
                SqlCommand cmd=new SqlCommand(insertcmd,con1);
                con1.Open();
                for (int i = 0; i < dataGridView2.Rows.Count - 1; i++)
                {
                    cmd.Parameters.AddWithValue("@CodeArticle1", dataGridView2.Rows[i].Cells["codeArticleDataGridViewTextBoxColumn"].Value);
                    cmd.Parameters.AddWithValue("@LibelleFr1",dataGridView2.Rows[i].Cells["libelleFrDataGridViewTextBoxColumn"].Value);
                    cmd.Parameters.AddWithValue("@LibelleAr1",dataGridView2.Rows[i].Cells["libelleArDataGridViewTextBoxColumn"].Value);
                    cmd.Parameters.AddWithValue("@IdCategorie1",dataGridView2.Rows[i].Cells["idCategorieDataGridViewTextBoxColumn"].Value);
                    cmd.Parameters.AddWithValue("@InfomationsDetaille1",dataGridView2.Rows[i].Cells["InfomationsDetailleDataGridViewTextBoxColumn"].Value);
                    cmd.Parameters.AddWithValue("@Quantite1",dataGridView2.Rows[i].Cells["QuantiteDataGridViewTextBoxColumn"].Value);
                    cmd.Parameters.AddWithValue("@StockActuel1",dataGridView2.Rows[i].Cells["StockActuelDataGridViewTextBoxColumn"].Value);
                    cmd.Parameters.AddWithValue("@StockMinimum1",dataGridView2.Rows[i].Cells["StockMinimumDataGridViewTextBoxColumn"].Value);
                    cmd.Parameters.AddWithValue("@PrixAchat1",dataGridView2.Rows[i].Cells["PrixAchatDataGridViewTextBoxColumn"].Value);
                    cmd.Parameters.AddWithValue("@DateAchat1",dataGridView2.Rows[i].Cells["DateAchatDataGridViewTextBoxColumn"].Value);
                    cmd.Parameters.AddWithValue("@NumeroFacture1",dataGridView2.Rows[i].Cells["NumeroFactureDataGridViewTextBoxColumn"].Value);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                MessageBox.Show("données enregistrés");
                con1.Close();

And make sure when you check the database that you are in database located in DataDirectory path not in the project folder path.

Answer 2

Your code probably does not enter the for-loop because dataGridView2 could be empty.
And your follow-up problem will likely be that only your last entry in dataGridView2 will be saved because you only execute the last row.

UPDATE:
You should not change your question. It is better to post a new one.

But your new problem is a database problem. You declared you columns as not nullable and try to insert a null value. Change your database table definition for your columns.

And it shows the follow-up problem I mentioned. Your dataGridView has one more row than you think. It is the "Add new" row (the one with the star in front of the row). This one is also a row and gives you all null values. It is the last row so it is the last value that is written to your parameter.