Adding data in sql Table

Question

I have this basic WinForms application user interface:

enter image description here

And I want to add the data both to the DataGridView and the sql table, when the "Gem" button is clicked. I have this following code:

private void Form2_Load(object sender, EventArgs e)
    {
        try
        {
            con = new SqlConnection();
            con.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Produkt.mdf;Integrated Security=True";
            con.Open();
            //adap = new SqlDataAdapter("select SN, FName as 'Navn', MName as 'Vare nr', LName as 'Antal', Age from Produkt", con);
            string sql = "SELECT Navn, Varenr, Antal, Enhed, Priseksklmoms, Konto FROM ProduktTable";

            adap = new SqlDataAdapter(sql, con);
            ds = new System.Data.DataSet();
            adap.Fill(ds, "ProduktTable");
            dataGridView1.DataSource = ds.Tables["ProduktTable"];

        }

        catch (Exception ex)
        {
            MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

    }

private void button1_Click(object sender, EventArgs e)
    {
        string navn = textBox2.Text;
        int varenr = int.Parse(textBox3.Text);
        float antal = (float)Convert.ToDouble(textBox4.Text);
        string enhed = textBox5.Text;
        string konto = comboBox2.Text;
        float pris = (float)Convert.ToDouble(textBox6.Text);

        dataGridView1.Rows[0].Cells[0].Value = navn;
        dataGridView1.Rows[0].Cells[1].Value = varenr;

        string StrQuery;

        try
        {

            SqlCommand comm = new SqlCommand();
            comm.Connection = con;

           for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                StrQuery = @"INSERT INTO ProduktTable VALUES ('"
                + dataGridView1.Rows[i].Cells[0].Value + "',' "
                + dataGridView1.Rows[i].Cells[1].Value + "');";
                comm.CommandText = StrQuery;                  
                comm.ExecuteNonQuery();                    
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show("Error\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

This is just an example with the purpose for storing the string "navn" and the integer "Varenr" in the DataGridView and the sql. When Im running the application and clicking on the button, following error occurs:

enter image description here

My column names for my ProduktTable table are exactly the same as the column names for the dataGridView.

Thanks in advance

have you tried to set a break point on your for statement to check whether the loop is not out of the range of your database fields ? — Ali
– Ali, Commented Jun 6, 2014 at 0:14
Yes. It throws the error in the first time it reaches the comm.ExecuteNonQuery() line. — Osman Esen
– Osman Esen, Commented Jun 6, 2014 at 0:31
Please STOP concatenating together your SQL statements! This is a huge security risk and opens the doors for SQL injection attacks! Just don't do it - EVER. Instead, use parametrized queries - ALWAYS, no exception — marc_s
– marc_s, Commented Jun 6, 2014 at 6:54

dbugger · Accepted Answer · 2014-06-06 00:39:35Z

1

Explicitly set your column names in the insert statement...

StrQuery = @"INSERT INTO ProduktTable (Navn, Varenr) VALUES ('"
                + dataGridView1.Rows[i].Cells[0].Value + "',' "
                + dataGridView1.Rows[i].Cells[1].Value + "');";

answered Jun 6, 2014 at 0:39

dbugger

16.6k10 gold badges34 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding data in sql Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related