How to insert records from data gridview entry to SQL database in C# windows application?

Question

I'm trying to insert records into sql database and below is my code for insertion from a button click.

I was not able to insert the records and it is throwing a error all the time when i execute the code.....I know there is something wrong in the code but I;m not sure where the issue occurs.....

The error message is "Incorrect syntax near ','.. "

    private void ADD_button_Click(object sender, EventArgs e)
    {
        try
                    {
                        using (SqlConnection con = new SqlConnection(sqlconn))
                        {
                            con.Open();

                            for (int i = 1; i < dataGridView.Rows.Count; i++)
                            {
                                string sql = @"INSERT INTO ERSBusinessLogic VALUES ("
                                     + dataGridView.Rows[i].Cells["ERSBusinessLogic_ID"].Value + ", "
                                    + dataGridView.Rows[i].Cells["ERSBusinessLogic_Formula"].Value + ", "
                                    + dataGridView.Rows[i].Cells["ERSBusinessLogic_InputsCount"].Value + ", "
                                    + dataGridView.Rows[i].Cells["ERSBusinessLogic_Inputs"].Value + ");";

                                SqlCommand cmd = new SqlCommand(sql, con);
                                cmd.ExecuteNonQuery();

                            }
                        }
        }

        catch (Exception ex)
        {
            MessageBox.Show("Error : " + ex.Message);
        }
        finally
        {
            con.Close();

        }

Beware of the database table's datatypes of all inputs in the query: + dataGridView.Rows[i].Cells["ERSBusinessLogic_ID"].Value + ", " + dataGridView.Rows[i].Cells["ERSBusinessLogic_Formula"].Value + ", " + dataGridView.Rows[i].Cells["ERSBusinessLogic_InputsCount"].Value + ", " + dataGridView.Rows[i].Cells["ERSBusinessLogic_Inputs"].Value + ", " ");"; They must match — Ian
– Ian, Commented Mar 29, 2016 at 6:13
Could you show the structure of the database table? Around each string field in the database you'll need to add a "'" in the insert statement. — diiN__________
– diiN__________, Commented Mar 29, 2016 at 6:18
@diiN_ (Hi... so you mean if the datatype is varchar(for eg: ERSbusinesslogic_inputs is varchar)... then the code should be " ' "+ dataGridView.Rows[i].Cells["ERSBusinessLogic_Inputs"].Value + " ' " — Gowtham Ramamoorthy
– Gowtham Ramamoorthy, Commented Mar 29, 2016 at 6:22

Yurii Tsurul · Accepted Answer · 2016-03-29 11:19:43Z

Try this

    private void button1_Click(object sender, EventArgs e)
    {
        // Getting data from DataGridView
        DataTable myDt = new DataTable();
        myDt = GetDTfromDGV(dataGridView1);

        // Writing to sql
        WriteToSQL(myDt);
    }

    private DataTable GetDTfromDGV(DataGridView dgv)
    {
        // Macking our DataTable
        DataTable dt = new DataTable();
        foreach (DataGridViewColumn column in dgv.Columns)
        {
            dt.Columns.Add(column.Name, typeof(string));
        }
        // Getting data
        foreach (DataGridViewRow dgvRow in dgv.Rows)
        {
            DataRow dr = dt.NewRow();
            for (int col = 0; col < dgv.Columns.Count; col++)
            {
                dr[col] = dgvRow.Cells[col].Value;
            }
            dt.Rows.Add(dr);
        }
        // removing empty rows
        for (int row = dt.Rows.Count - 1; row >= 0; row--)
        {
            bool flag = true;
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                if (dt.Rows[row][col] != DBNull.Value)
                {
                    flag = false;
                    break;
                }
            }
            if (flag == true)
            {
                dt.Rows.RemoveAt(row);
            }
        }
        return dt;
    }
    private void WriteToSQL(DataTable dt)
    {
        string connectionStringSQL = "Your connection string";
        using (SqlConnection sqlConn = new SqlConnection(connectionStringSQL))
        {
            SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConn);
            // Setting the database table name
            sqlBulkCopy.DestinationTableName = "Table_1_temp";
            // Mapping the DataTable columns with that of the database table
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[0].ColumnName, "sql_col1");
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[1].ColumnName, "sql_col2");
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[2].ColumnName, "sql_col3");
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[3].ColumnName, "sql_col4");
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[4].ColumnName, "sql_col5");
            sqlBulkCopy.ColumnMappings.Add(dt.Columns[5].ColumnName, "sql_col6");
            sqlConn.Open();
            sqlBulkCopy.WriteToServer(dt);
        }
    }

Sarvjeet Verma · Accepted Answer · 2016-03-29 10:34:55Z

2

It seems a data type mismatch issue. Check your data table if table column is of numeric type(for eg: ERSBusinessLogic_ID is integer) then code should be like "+Convert.ToInt32(dataGridView.Rows[i].Cells["ERSBusinessLogic_ID"].Value) + ",

And if it is var-char type then value should be in single quote('') like '"+Convert.ToString(dataGridView.Rows[i].Cells["ERSBusinessLogic_Formula"].Value) + "',

answered Mar 29, 2016 at 10:34

Sarvjeet Verma

1067 bronze badges

Comments

Andreea Dumitru · Accepted Answer · 2016-03-29 09:41:11Z

0

    try
        {
         using (SqlConnection con = new SqlConnection(sqlconn))
         {
           using (SqlCommand cmd = new SqlCommand())
           {
               cmd.Connection = con;
               con.Open();
               for (int i = 1; i < dataGridView.Rows.Count; i++)
               {         
                string sql = @"INSERT INTO ERSBusinessLogic VALUES ("
                             +dataGridView.Rows[i].Cells["ERSBusinessLogic_ID"].Value + ", "
                                + dataGridView.Rows[i].Cells["ERSBusinessLogic_Formula"].Value + ", "
                                + dataGridView.Rows[i].Cells["ERSBusinessLogic_InputsCount"].Value + ", "
                                + dataGridView.Rows[i].Cells["ERSBusinessLogic_Inputs"].Value + ");";

                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();

              }
         }
    }

    catch (Exception ex)
    {
        MessageBox.Show("Error : " + ex.Message);
    }
    finally
    {
        con.Close();

    }

If still doesn't work, put a breakpoint right after the string, and copy the string value and test it on your database. Maybe your values are the problem, different types.

answered Mar 29, 2016 at 9:41

Andreea Dumitru

961 silver badge11 bronze badges

2 Comments

Gowtham Ramamoorthy Over a year ago

Hi you are right...... I have null values that needs to be inserted too from the grid view... but the column is not accepting null key word from the grid view..... How to do it ?

Andreea Dumitru Over a year ago

Maybe you should not allow to add empty values in your grid, if in your table are requested. If those values, can be null, you should change db columns to accept null. Other way, i don;t know

samithagun · Accepted Answer · 2016-03-29 09:48:23Z

Try adding '' to each data column. As below,

for (int i = 1; i < dataGridView.Rows.Count; i++)
                        {
                            string sql = @"INSERT INTO ERSBusinessLogic VALUES ('"
                                 + dataGridView.Rows[i].Cells["ERSBusinessLogic_ID"].Value + "', '"
                                + dataGridView.Rows[i].Cells["ERSBusinessLogic_Formula"].Value + "', '"
                                + dataGridView.Rows[i].Cells["ERSBusinessLogic_InputsCount"].Value + "', '"
                                + dataGridView.Rows[i].Cells["ERSBusinessLogic_Inputs"].Value + "');";

                            SqlCommand cmd = new SqlCommand(sql, con);
                            cmd.ExecuteNonQuery();

                        }

yusufgltc · Accepted Answer · 2020-04-13 13:18:46Z

0

Have a good day. You can also try to do this option. Be careful to write your records of variable.

using (SqlCommand cmd = new SqlCommand("INSERT INTO MyTable(Column1,Column2) VALUES (@Value1 @Value2)",con))
cmd.Parameters.Add(newSqlParameter("@Value1",SqlDbType.VarChar));
cmd.Parameters.Add(newSqlParameter("@Value2",SqlDbType.VarChar));
con.Open();
    foreach (DataGridViewRow row in myDataGridView.Rows)
        {
        if (!row.IsNewRow)
            {
            cmd.Parameters["@C1"].Value = row.Cells[0].Value;
            cmd.Parameters["@C2"].Value = row.Cells[1].Value;
            cmd.ExecuteNonQuery();
            }
        }
    }
}

answered Apr 13, 2020 at 13:18

yusufgltc

615 bronze badges

Collectives™ on Stack Overflow

How to insert records from data gridview entry to SQL database in C# windows application?

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related