CODEWITHSUNDEEP
c#mysqlloops
Need2learn
Need2learn

Reputation: 41

Looping parameterized query

I've been looking for its solution for some time now, I've seen similar questions here on SO but the answers there didn't solve my problem. In my situation, I would like to loop through the rows of datagridview values to insert it to MySql database. The number of rows in the datagridview will depend on the user. When I try to input 2 or more rows of datagridview values and try to insert it to the database, only the first row of data is inserted successfully.

Here is my code: 

MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = SecurityMod.dbconn();
                for (int i = 0; i < dgv_ctrl.Rows.Count; i++)
                {
                    string sql = "INSERT INTO delivery(DeliveryNumber, CreationDate, Client, Product, Price, Quantity, Total) " +
                             "VALUES(@num_Delivery, " + "@dgv_datevalue" + ", " +
                             "@name_Client" + ", " +
                             "@name_Product" + ", " +
                             "@priceof_Produt" + ", " +
                             "@quan_Product" + ", " +
                             "@price_Total" + ");";

                    cmd.Parameters.AddWithValue("@num_Delivery", num_Delivery);
                    cmd.Parameters.AddWithValue("@dgv_datevalue", DateTime.Parse(dgv_ctrl.Rows[i].Cells["Creation_Date"].Value.ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
                    cmd.Parameters.AddWithValue("@name_Client", dgv_ctrl.Rows[i].Cells["Client_name"].Value);
                    cmd.Parameters.AddWithValue("@name_Product", dgv_ctrl.Rows[i].Cells["Product_name"].Value);
                    cmd.Parameters.AddWithValue("@priceof_Produt", dgv_ctrl.Rows[i].Cells["Price_ofProduct"].Value);
                    cmd.Parameters.AddWithValue("@quan_Product", dgv_ctrl.Rows[i].Cells["Quantity_ofProduct"].Value);
                    cmd.Parameters.AddWithValue("@price_Total", dgv_ctrl.Rows[i].Cells["Total_Price"].Value);
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();

The error: Parameter '@num_Delivery' has already been defined. I'm new at using C# language. Any ideas and suggestions would be welcomed.

Upvotes: 3

Views: 859

Answers (2)

Emanuele
Emanuele

Reputation: 664

Move

MySqlCommand cmd = new MySqlCommand();

in the for loop:

string sql = "INSERT INTO delivery(DeliveryNumber, CreationDate, Client, Product, Price, Quantity, Total) " +
             "VALUES(@num_Delivery, " + "@dgv_datevalue" + ", " +
             "@name_Client" + ", " +
             "@name_Product" + ", " +
             "@priceof_Produt" + ", " +
             "@quan_Product" + ", " +
             "@price_Total" + ");";
for (int i = 0; i < dgv_ctrl.Rows.Count; i++)
{
    MySqlCommand cmd = new MySqlCommand();
    cmd.Connection = SecurityMod.dbconn();
    cmd.Parameters.AddWithValue("@num_Delivery", num_Delivery);
    cmd.Parameters.AddWithValue("@dgv_datevalue", DateTime.Parse(dgv_ctrl.Rows[i].Cells["Creation_Date"].Value.ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
    cmd.Parameters.AddWithValue("@name_Client", dgv_ctrl.Rows[i].Cells["Client_name"].Value);
    cmd.Parameters.AddWithValue("@name_Product", dgv_ctrl.Rows[i].Cells["Product_name"].Value);
    cmd.Parameters.AddWithValue("@priceof_Produt", dgv_ctrl.Rows[i].Cells["Price_ofProduct"].Value);
    cmd.Parameters.AddWithValue("@quan_Product", dgv_ctrl.Rows[i].Cells["Quantity_ofProduct"].Value);
    cmd.Parameters.AddWithValue("@price_Total", dgv_ctrl.Rows[i].Cells["Total_Price"].Value);
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
}

or clear its Parameters collection:

string sql = "INSERT INTO delivery(DeliveryNumber, CreationDate, Client, Product, Price, Quantity, Total) " +
             "VALUES(@num_Delivery, " + "@dgv_datevalue" + ", " +
             "@name_Client" + ", " +
             "@name_Product" + ", " +
             "@priceof_Produt" + ", " +
             "@quan_Product" + ", " +
             "@price_Total" + ");";
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = SecurityMod.dbconn();
for (int i = 0; i < dgv_ctrl.Rows.Count; i++)
{
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("@num_Delivery", num_Delivery);
    cmd.Parameters.AddWithValue("@dgv_datevalue", DateTime.Parse(dgv_ctrl.Rows[i].Cells["Creation_Date"].Value.ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
    cmd.Parameters.AddWithValue("@name_Client", dgv_ctrl.Rows[i].Cells["Client_name"].Value);
    cmd.Parameters.AddWithValue("@name_Product", dgv_ctrl.Rows[i].Cells["Product_name"].Value);
    cmd.Parameters.AddWithValue("@priceof_Produt", dgv_ctrl.Rows[i].Cells["Price_ofProduct"].Value);
    cmd.Parameters.AddWithValue("@quan_Product", dgv_ctrl.Rows[i].Cells["Quantity_ofProduct"].Value);
    cmd.Parameters.AddWithValue("@price_Total", dgv_ctrl.Rows[i].Cells["Total_Price"].Value);
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
}

Sql string can be out of loop.

Upvotes: 0

Keyur Ramoliya
Keyur Ramoliya

Reputation: 1924

You can do it like this. It should work.

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = SecurityMod.dbconn();
for (int i = 0; i < dgv_ctrl.Rows.Count; i++)
{
    string sql = "INSERT INTO delivery(DeliveryNumber, CreationDate, Client, Product, Price, Quantity, Total) " +
             "VALUES(@num_Delivery, " + "@dgv_datevalue" + ", " +
             "@name_Client" + ", " +
             "@name_Product" + ", " +
             "@priceof_Produt" + ", " +
             "@quan_Product" + ", " +
             "@price_Total" + ");";
    cmd.Parameter.Clear();
    cmd.Parameters.AddWithValue("@num_Delivery", num_Delivery);
    cmd.Parameters.AddWithValue("@dgv_datevalue", DateTime.Parse(dgv_ctrl.Rows[i].Cells["Creation_Date"].Value.ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
    cmd.Parameters.AddWithValue("@name_Client", dgv_ctrl.Rows[i].Cells["Client_name"].Value);
    cmd.Parameters.AddWithValue("@name_Product", dgv_ctrl.Rows[i].Cells["Product_name"].Value);
    cmd.Parameters.AddWithValue("@priceof_Produt", dgv_ctrl.Rows[i].Cells["Price_ofProduct"].Value);
    cmd.Parameters.AddWithValue("@quan_Product", dgv_ctrl.Rows[i].Cells["Quantity_ofProduct"].Value);
    cmd.Parameters.AddWithValue("@price_Total", dgv_ctrl.Rows[i].Cells["Total_Price"].Value);
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
}

Upvotes: 2

Related Questions