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

Did you debug your code? Are you sure your code goes into for loop? By the way, since your ExecuteNonQuery outside on your loop, this will execute your last values. — Soner Gönül
– Soner Gönül, Commented Dec 9, 2014 at 9:24
Some of the column value is passing Empty or NULL to procedure in for loop.That's the reason it's throwing an exception. — shankar.parshimoni
– shankar.parshimoni, Commented Dec 9, 2014 at 9:27

user3309231 · Accepted Answer · 2014-12-09 14:22:06Z

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.

Uwe Hafner · Accepted Answer · 2014-12-09 09:58:39Z

1

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.

edited Dec 9, 2014 at 9:58

answered Dec 9, 2014 at 9:25

Uwe Hafner

5,0083 gold badges30 silver badges47 bronze badges

9 Comments

user4340666 Over a year ago

i have entered not null values

Uwe Hafner Over a year ago

Did you debug and see that it enters the loop?

user4340666 Over a year ago

Yes it stops in cmd.ExecuteNonQuery(); i edited my code please can you answer me about the new error

user4340666 Over a year ago

i changed the loop condition to for(int i = 0; i < dataGridView2.Rows.Count - 1; i++) and i deleted the if conditions that i added to the parameters, i executed the code with no errors but when i opened the article table i found it empty so it does'nt add the values, where is the problem ( i tried with just one row)

Uwe Hafner Over a year ago

ExecuteNonQuery gives you a return value (int). It states how many rows were affected. What do you get?

|

Collectives™ on Stack Overflow

Insert data in Sql Server datable from Datagridview c#

2 Answers 2

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related