Multiple gridview Rows Insert in Database at time

Question

In my below code give me this error

error is my gridview textbox column value not inserting in my database parameter missing error

try {
    da=DateTime.Now;

    foreach(DataGridViewRow row in dataGridView1.Rows) {
        string SelectedText=Convert.ToString((row.Cells["article_name"] as DataGridViewComboBoxCell).FormattedValue.ToString());
        string SelectedText1=Convert.ToString((row.Cells["size_name"] as DataGridViewComboBoxCell).FormattedValue.ToString());
        string SelectedText2=Convert.ToString((row.Cells["color_name"] as DataGridViewComboBoxCell).FormattedValue.ToString());
        string SelectedText3=Convert.ToString((row.Cells["unit_name"] as DataGridViewComboBoxCell).FormattedValue.ToString());

        SqlCommand comm=new SqlCommand("abc", con);
        con.Open();
        comm.Parameters.Add("@order_no", SqlDbType.NVarChar).Value="abc123";
        comm.Parameters.Add("@article_name", SqlDbType.NVarChar).Value=SelectedText;

        //dataGridView1.Rows[i].Cells["article_name"].Value;
        comm.Parameters.Add("@size_name", SqlDbType.NVarChar).Value=SelectedText1;
        comm.Parameters.Add("@color_name", SqlDbType.NVarChar).Value=SelectedText2;
        comm.Parameters.Add("@quantity", SqlDbType.Int).Value=row.Cells["quantity"].Value;
        comm.Parameters.Add("@piece_carton", SqlDbType.Int).Value=row.Cells["pcs_carton"].Value;
        comm.Parameters.Add("@no_of_carton", SqlDbType.Int).Value=row.Cells["no_carton"].Value;
        comm.Parameters.Add("@unit_name", SqlDbType.NVarChar).Value=SelectedText3;
        comm.Parameters.Add("@rate", SqlDbType.NVarChar).Value=row.Cells["rate"].Value;
        comm.Parameters.Add("@ship_qty", SqlDbType.Int).Value=row.Cells["ship_qty"].Value;
        comm.Parameters.Add("@date1", SqlDbType.DateTime).Value=da;
        comm.Parameters.Add("@amount", SqlDbType.NVarChar).Value=row.Cells["amount"].Value;

        //comm.CommandText = StrQuery;
        db.insertprocedure(comm);
    }
}
catch(SqlException ex) {
    MessageBox.Show(ex.Message);
}
finally {
    con.Close();
}

Here Is My Query

create proc abc
@order_no varchar(50),
@article_name varchar(500),
@size_name varchar(100),
@color_name varchar(100),
@quantity int,
@piece_carton int,
@no_of_carton int,
@unit_name varchar(50),
@rate varchar(50),
@ship_qty int,
@date1 date,
@amount varchar(50)
as
begin
set @date1=CONVERT(varchar,@date1,1)
insert into transaction_order(order_id,article_id,size_id,color_id,quantity,piece_carton,no_of_carton,unit_id,rate,ship_qty,date1,amount)
values(@order_no,(select article_id from article_order where article_name=@article_name),(select size_id from size where size_name=@size_name),(select color_id from color where color_name=@color_name),@quantity,@piece_carton,@no_of_carton,(select unit_id from unit where unit_name=@unit_name),@rate,@ship_qty,@date1,@amount)
end

Here Is MY Function insertprocedure this function is in database class

     public void insertprocedure(SqlCommand txt)
        {
            DateTime da = new DateTime();

            try
            {
                da = DateTime.Now;
                con.Open();
                txt.CommandType = CommandType.StoredProcedure;
                txt.ExecuteNonQuery();

                con.Close();
            }
            catch (Exception ex) { throw ex; }
            finally { con.Close(); }
        }

> Here Is My Gridview TextBox Columns Added Code
 private void Form1_Load(object sender, EventArgs e)
        {
            string query = "SELECT article_name FROM article_order";
            DataTable dt = db.select_command(query);
            BindingSource bi = new BindingSource();
            bi.DataSource = dt;


            string query1 = "SELECT size_name FROM size";
            DataTable dt1 = db.select_command(query1);
            BindingSource bi1 = new BindingSource();
            bi1.DataSource = dt1;

            string query2 = "SELECT color_name FROM color";
            DataTable dt2 = db.select_command(query2);
            BindingSource bi2 = new BindingSource();
            bi2.DataSource = dt2;


            string query3 = "SELECT unit_name FROM unit";
            DataTable dt3 = db.select_command(query3);
            BindingSource bi3 = new BindingSource();
            bi3.DataSource = dt3;


            //string query4 = "SELECT order_no FROM master_order";
            //DataTable dt4 = db.select_command(query4);
            //BindingSource bi4 = new BindingSource();
            //bi4.DataSource = dt4;








            #region Gridview Columns
            //Transaction Id
            DataGridViewTextBoxColumn trans_id = new DataGridViewTextBoxColumn();
            trans_id.HeaderText = "ID";
            trans_id.Width = 50;
            trans_id.DataPropertyName = "ID";
            trans_id.Name = "trans_id";
            dataGridView1.Columns.Add(trans_id);

            //Order No.

            //DataGridViewTextBoxColumn order_no = new DataGridViewTextBoxColumn();
            //order_no.HeaderText = "Order No";
            //order_no.Width = 100;
            ////order_no.DataPropertyName = "yarn_name";
            //order_no.Name = "order_no";
            //dataGridView1.Columns.Add(order_no);

            //query = "select yarn_name from yarn";
            //auto.autocomplete1(dataGridView1., textBox9.AutoCompleteCustomSource = namesCollection, query);
            //DataGridViewTextBoxColumn order_no = new DataGridViewTextBoxColumn();
            //order_no.HeaderText = "Order No.";
            //order_no.Width = 50;
            //order_no.DataPropertyName = "order_no";
            //order_no.Name = "order_no";
            //dataGridView1.Columns.Add(order_no);





            //Article Name
            DataGridViewComboBoxColumn article_name = new DataGridViewComboBoxColumn();
            article_name.DataPropertyName = "Article Name";
            article_name.HeaderText = "Article Name";
            article_name.Name = "article_name";
            article_name.Width = 120;

            article_name.DataSource = bi;
            article_name.ValueMember = "article_name";
            //article_name.DisplayMember = "article_name";

            dataGridView1.Columns.Add(article_name);
            //Size Name
            DataGridViewComboBoxColumn size_name = new DataGridViewComboBoxColumn();
            size_name.DataPropertyName = "Size Name";
            size_name.HeaderText = "Size Name";
            size_name.Name = "size_name";
            size_name.Width = 120;

            size_name.DataSource = bi1;
            size_name.ValueMember = "size_name";
            ////size_name1.DisplayMember = "size_name";

            dataGridView1.Columns.Add(size_name);

            //Color Name
            DataGridViewComboBoxColumn color_name = new DataGridViewComboBoxColumn();
            color_name.DataPropertyName = "Color Name";
            color_name.HeaderText = "Color Name";
            color_name.Name = "color_name";
            color_name.Width = 120;

            color_name.DataSource = bi2;
            color_name.ValueMember = "color_name";
            //ColumnItem.DisplayMember = "ItemText";

            dataGridView1.Columns.Add(color_name);

            //quantity
            DataGridViewTextBoxColumn quantity = new DataGridViewTextBoxColumn();
            quantity.HeaderText = "Quantity";
            quantity.Width = 100;
            quantity.DataPropertyName = "Quantity";
            quantity.Name = "quantity";
            dataGridView1.Columns.Add(quantity);

            //Pcs/Carton
            DataGridViewTextBoxColumn pcs_carton = new DataGridViewTextBoxColumn();
            pcs_carton.HeaderText = "Piece/Carton";
            pcs_carton.Width = 100;
            pcs_carton.DataPropertyName = "Piece/Carton";
            pcs_carton.Name = "pcs_carton";
            dataGridView1.Columns.Add(pcs_carton);

            //No Of Carton
            DataGridViewTextBoxColumn no_carton = new DataGridViewTextBoxColumn();
            no_carton.HeaderText = "No Of Carton";
            no_carton.Width = 70;
            no_carton.DataPropertyName = "No Of Carton";
            no_carton.Name = "no_carton";
            dataGridView1.Columns.Add(no_carton);

            //unit name
            DataGridViewComboBoxColumn unit_name = new DataGridViewComboBoxColumn();
            unit_name.DataPropertyName = "Unit Name";
            unit_name.HeaderText = "Unit Name";
            unit_name.Name = "unit_name";
            unit_name.Width = 120;

            unit_name.DataSource = bi3;
            unit_name.ValueMember = "unit_name";
            //ColumnItem.DisplayMember = "ItemText";

            dataGridView1.Columns.Add(unit_name);
            //rate
            DataGridViewTextBoxColumn rate = new DataGridViewTextBoxColumn();
            rate.HeaderText = "Rate";
            rate.Width = 70;
            rate.DataPropertyName = "Rate";
            rate.Name = "rate";
            dataGridView1.Columns.Add(rate);

            //amount
            DataGridViewTextBoxColumn amount = new DataGridViewTextBoxColumn();
            amount.HeaderText = "Amount";
            amount.Width = 70;
            amount.DataPropertyName = "Amount";
            amount.Name = "amount";
            dataGridView1.Columns.Add(amount);

            //Ship quantity
            DataGridViewTextBoxColumn ship_qty = new DataGridViewTextBoxColumn();
            ship_qty.HeaderText = "Ship Quantity";
            ship_qty.Width = 70;
            ship_qty.DataPropertyName = "Ship Quantity";
            ship_qty.Name = "ship_qty";
            dataGridView1.Columns.Add(ship_qty);
            #endregion
        }

Answer 1

Run this query:

select article_name,COUNT(*) from article_order GROUP BY article_name HAVING COUNT(*)>1

If it returns ANY rows, then your problem is that you have multiple articles in your table with the same name.

Alternatively, make sure that you have a UNIQUE INDEX on each of article_name, color_name, unit_name, and size_name. If you get an error while trying to create that index (because one will definitely give you an error), then you need to fix that in the appropriate table, then create the index.

Answer 2

values(@order_no,
(select article_id from article_order where article_name=@article_name),
(select size_id from size where size_name=@size_name),
(select color_id from color where color_name=@color_name), @quantity,@piece_carton,@no_of_carton,
(select unit_id from unit where unit_name=@unit_name),
@rate,@ship_qty,@date1,@amount)

The error message would show up if any of the marked subselects in your procedure returns more than one value.

Answer 3

Can we see your select query? it seems that there is a subquery that returns more then 1 value