How to save data grid values to sql database whole values to one primary key i tried but it doesnt work

Question

I get a exception "index was out of range. Must be no-negative and less than the size of the collection. parameter name : index

    private void button3_Click(object sender, EventArgs e)
    {
        try
        {
            if (txtSubtotal.Text == "")
            {
                MessageBox.Show("The Sub Total cannot be Empty", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtSubtotal.Focus();
                return;
            }
            if (txtamountdue.Text == "")
            {
                MessageBox.Show("The Amountdue cannot be Empty", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtamountdue.Focus();
                return;
            }
            if (txtcashgiven.Text == "")
            {
                MessageBox.Show("Please enter the cash given", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtcashgiven.Focus();
                return;
            }
            if (txtchange.Text == "")
            {
                MessageBox.Show("The Cahange cannot be Empty", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtchange.Focus();
                return;
            }



            con = new SqlConnection(cs.DBConn);
            con.Open();


            string cb = "insert Into invoiceTB(InvoiceNo,Date,BillTo,CustomerID,SalesBy,Warranty,ShipTo,SubTotal,AmountDue,CashGiven,Change) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11)";
            cmd = new SqlCommand(cb);
            cmd.Connection = con;

            cmd.Parameters.AddWithValue("@d1",txtInvoice.Text);
            cmd.Parameters.AddWithValue("@d2",dateTimePicker1.Text);
            cmd.Parameters.AddWithValue("@d3",txtBillTo.Text);
            cmd.Parameters.AddWithValue("@d4", cmbcustomerID.Text);
            cmd.Parameters.AddWithValue("@d5",txtSalesBy.Text);
            cmd.Parameters.AddWithValue("@d6",txtwarranty.Text);
            cmd.Parameters.AddWithValue("@d7",txtshipto.Text);
            cmd.Parameters.AddWithValue("@d8",txtSubtotal.Text);
            cmd.Parameters.AddWithValue("@d9",txtamountdue.Text);
            cmd.Parameters.AddWithValue("@d10",txtcashgiven.Text);
            cmd.Parameters.AddWithValue("@d11",txtchange.Text);

            cmd.ExecuteReader();
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
            con.Close();


            for (int i = 0; i <= dataGridView1.Rows.Count - 1; i++)
            {
                con = new SqlConnection(cs.DBConn);

                string cd = "insert Into invoiceTB(ItemCode,Description,Quantity,PriceEach,Amount) VALUES (@d1,@d2,@d3,@d4,@d5)";
                cmd = new SqlCommand(cd);
                cmd.Connection = con;
                cmd.Parameters.AddWithValue("d1", dataGridView1.Rows[i].Cells[1].Value);
                cmd.Parameters.AddWithValue("d2", dataGridView1.Rows[i].Cells[2].Value);
                cmd.Parameters.AddWithValue("d3", dataGridView1.Rows[i].Cells[4].Value);
                cmd.Parameters.AddWithValue("d4", dataGridView1.Rows[i].Cells[3].Value);
                cmd.Parameters.AddWithValue("d5", dataGridView1.Rows[i].Cells[5].Value);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }

            btnSave.Enabled = false;
            btnprint.Enabled = true;
            MessageBox.Show("Successfully Placed", "Order", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

Answer 1

It is not clear what you asking but I see a few things;

• You need to use ExecuteNonQuery instead of ExecuteReader in your first insert statement. That's just execute your code. ExecuteReader method returns data. Since you just inserting data, pointless to use it.

• You need to use @ in your second insert command parameter names in your AddWithValue method.

• Use using statement to dispose your database connections and objects.

• DATE can be a reserved keyword future releases of SQL Server. You might need to avoid to use it.

• As a best practice, don't use AddWithValue method. It might generates unexpected and surprising results. Use .Add() method instead. Read Can we stop using AddWithValue() already?

For your error message, read What is an "index out of range" exception, and how do I fix it?