none
Inserting values from datagridview RRS feed

  • Question

  • Hello all!

    I have a datagridview on a form, i want to add as many rows as i want, and then insert those rows into a table.

    Now my for loop does well until it reaches the next iteration and then gives a message:

    The variable name '@ProductID' has already been declared. Variable names must be unique within a query batch or stored procedure. 

    The table does not have primaryKey constraints..

    Any ideas?? Thanks in advance!!

    Here is my code:

                  using (SqlCommand objCmd = Con.CreateCommand())
                            {
    
                                for (int i = 0; i < dgvIngredientes.Rows.Count - 1; i++)
                                {
    
                                   
                                    //objCmd.CommandType = System.Data.CommandType.StoredProcedure;
                                    //objCmd.CommandText = "[Insert_RecipeDetail]";
    
    
                                    objCmd.CommandText = "insert into RecipeDetail values (@ProductID,@RecipeID,@Quantity,@UnitMedID)";
                                    objCmd.CommandType = CommandType.Text;
                                    objCmd.Parameters.Add(new SqlParameter("@ProductID", dgvIngredientes.Rows[i].Cells["Codigo"].Value));
                                    objCmd.Parameters.Add(new SqlParameter("@RecipeID", txtRef.Text));
                                    objCmd.Parameters.Add(new SqlParameter("@Quantity", dgvIngredientes.Rows[i].Cells["Medida"].Value));
                                    objCmd.Parameters.Add(new SqlParameter("@UnitMedID", dgvIngredientes.Rows[i].Cells["Cantidad"].Value));
                                    objCmd.ExecuteNonQuery();
    
    
                                }
                                 
                            }//end using detail

     


    • Edited by lagartija75 Monday, September 14, 2015 9:21 PM
    Monday, September 14, 2015 9:21 PM

Answers

  • If you have several rows of data in your -- you should add the data you entered into the datagridview to a dataTable object.  You can loop through the rows in your datagridview to populate a dataTable object.  Then you can transfer the content of the dataTable to the sql Server dataTable with you command object the way you have it set up with your parameters.  Additionally, to use the Parameters as you are doing -- you should use an InsertCommand object (a sqlDataAdapter.InsertCommand).

    If you just want to add one row for the 4 fields -- you have to assign the values respectively like this:

    param0.Value = dgr.Rows[0][0].value;

    param1.value = dgr.Rows[0][1].Value;

    ...

    The sqlDataAdapter is a bit better way to go (of course, Entity framework is even better but a little more sophisticated).



    Rich P

    Monday, September 14, 2015 10:02 PM

All replies

  • If you have several rows of data in your -- you should add the data you entered into the datagridview to a dataTable object.  You can loop through the rows in your datagridview to populate a dataTable object.  Then you can transfer the content of the dataTable to the sql Server dataTable with you command object the way you have it set up with your parameters.  Additionally, to use the Parameters as you are doing -- you should use an InsertCommand object (a sqlDataAdapter.InsertCommand).

    If you just want to add one row for the 4 fields -- you have to assign the values respectively like this:

    param0.Value = dgr.Rows[0][0].value;

    param1.value = dgr.Rows[0][1].Value;

    ...

    The sqlDataAdapter is a bit better way to go (of course, Entity framework is even better but a little more sophisticated).



    Rich P

    Monday, September 14, 2015 10:02 PM
  • Hi, 

    Because of the first loop already added the parameter. So, next loop are not allow add the same parameter name instead of you clear the parameter.

    Please refer the code below:

     			using (SqlCommand objCmd = Con.CreateCommand())
                            {
    
                                for (int i = 0; i < dgvIngredientes.Rows.Count - 1; i++)
                                {
    
                                   
                                    //objCmd.CommandType = System.Data.CommandType.StoredProcedure;
                                    //objCmd.CommandText = "[Insert_RecipeDetail]";
    
    
                                    objCmd.CommandText = "insert into RecipeDetail values (@ProductID,@RecipeID,@Quantity,@UnitMedID)";
                                    objCmd.CommandType = CommandType.Text;
                                    objCmd.Parameters.Add(new SqlParameter("@ProductID", dgvIngredientes.Rows[i].Cells["Codigo"].Value));
                                    objCmd.Parameters.Add(new SqlParameter("@RecipeID", txtRef.Text));
                                    objCmd.Parameters.Add(new SqlParameter("@Quantity", dgvIngredientes.Rows[i].Cells["Medida"].Value));
                                    objCmd.Parameters.Add(new SqlParameter("@UnitMedID", dgvIngredientes.Rows[i].Cells["Cantidad"].Value));
                                    objCmd.ExecuteNonQuery();
    				//Need to clear parameter  before next item looping
    				objCmd.Parameters.Clear()
    				
                                }
                                 
                            }//end using detail 
    Thanks
    • Edited by stef chui Tuesday, September 15, 2015 12:54 AM Add the explaination
    Tuesday, September 15, 2015 12:52 AM
  • Try this code

    for (int i = 0; i < dgvIngredientes.Rows.Count - 1; i++) { using (SqlCommand objCmd = Con.CreateCommand()) { objCmd.CommandText = "insert into RecipeDetail values (@ProductID,@RecipeID,@Quantity,@UnitMedID)"; objCmd.CommandType = CommandType.Text; objCmd.Parameters.Add(new SqlParameter("@ProductID", dgvIngredientes.Rows[i].Cells["Codigo"].Value)); objCmd.Parameters.Add(new SqlParameter("@RecipeID", txtRef.Text)); objCmd.Parameters.Add(new SqlParameter("@Quantity", dgvIngredientes.Rows[i].Cells["Medida"].Value)); objCmd.Parameters.Add(new SqlParameter("@UnitMedID", dgvIngredientes.Rows[i].Cells["Cantidad"].Value)); objCmd.ExecuteNonQuery(); } }



    Happy Coding, RDRaja

    Tuesday, September 15, 2015 6:30 AM
  • Hi

    If you move your for loop, you won't have to make multiple connections.  Just a quick edit to your code block like below one

    string StrQuery;
    try
    {
        using (SqlConnection conn = new SqlConnection(ConnString))
        {
            using (SqlCommand comm = new SqlCommand())
            {
                comm.Connection = conn;
                conn.Open();
                for(int i=0; i< dgvIngredientes.Rows.Count;i++)
                {
                    StrQuery= @"INSERT INTO RecipeDetail values ("
                        + dataGridView1.Rows[i].Cells["Codigo"].Value +", "
          + txtRef.Text +","
          + dataGridView1.Rows[i].Cells["Codigo"].Value +", "
                        + dataGridView1.Rows[i].Cells["Cantidad"].Value +");";
                    comm.CommandText = StrQuery;
                    comm.ExecuteNonQuery();
                }
            }
        }
    }


    Harshad..... Always 4 U

    Tuesday, September 15, 2015 6:58 AM