Inserting data from DatagridView error

Question

I've got

string insert_cmd_str;

using (conn_server)
{
    using (NpgsqlCommand insert_cmd = new NpgsqlCommand())
    {
        insert_cmd.Connection = conn_server;
        conn_server.Open();
        for (int i = 0; i < dataGridView1.Rows.Count; i++)
        {
            insert_cmd_str = "insert into @table values (" +
                                 "'" + dataGridView1.Rows[i].Cells[0].FormattedValue + "', " +
                                 "'" + dataGridView1.Rows[i].Cells[1].FormattedValue + "', " +
                                 "'" + dataGridView1.Rows[i].Cells[2].FormattedValue + "', " +
                                 "'" + dataGridView1.Rows[i].Cells[3].FormattedValue + "', " +
                                 "'" + dataGridView1.Rows[i].Cells[4].FormattedValue + "', " +
                                 "'" + dataGridView1.Rows[i].Cells[5].FormattedValue + "', " +
                                 "'" + dataGridView1.Rows[i].Cells[6].FormattedValue + "', " +
                                 "'" + dataGridView1.Rows[i].Cells[7].FormattedValue + "', " +
                                 "'" + dataGridView1.Rows[i].Cells[8].FormattedValue + "', " +
                                 "'" + dataGridView1.Rows[i].Cells[9].FormattedValue + "')";
            insert_cmd.CommandText = insert_cmd_str;
            insert_cmd.Parameters.AddWithValue("@table", "table");


            MessageBox.Show(insert_cmd.CommandText + Environment.NewLine +
                            insert_cmd.Parameters["@db"].Value);

            insert_cmd.ExecuteNonQuery();
        }
        conn_server.Close();
    }
}

And I get an error:

ERROR: 42601: syntax error at or near "("

I've added the MessageBox to check the command text just before it executes and it seems to be good. If I post the same text into PG admin it executes the command without the error.

The command text is:

insert into @table values ('425', '10-31-2016 00:00:00', 'False', '', 'test', 'test', 'test', 'pas_kosmetologus', 'Skambutis', '237')

The value of @table is table. I can't figure out were the error comes from. I've even tried deleting both parenthesis from the command text but it still throws the same error.

Am I missing something obvious here?

Your table is called table? thats a keyword that can cause you issues. — BugFinder, Nov 23 '16 at 08:40
Not really. I've just changed it here to simplify it. The actual name of the table it needs to insert data into is based on the user of the application. — Martynas, Nov 23 '16 at 08:45
It may be that the insert statement expects the columns first, e.g. `insert into (column1, ...) values (value1, ...)` — rbr94, Nov 23 '16 at 08:46
For now it's vizitai_test. I don't think that could cause the error. — Martynas, Nov 23 '16 at 08:47
@rbr94 I've added the names of the columns into the string. Still the same error. — Martynas, Nov 23 '16 at 08:53

score 1 · Accepted Answer · edited May 23 '17 at 12:24

1

PostgreSQL doesn't support parameterizing table or column names - you'll have to either concatenate these in into your string (but beware of SQL injection), or write a plpgsql. See https://stackoverflow.com/a/13289939/640325 for an example.

Regardless, concatenating in the values from your datagrid leaves you wide open to SQL injection, consider using parameters there.

edited May 23 '17 at 12:24

Community

1
1

answered Nov 23 '16 at 08:55

Shay Rojansky

15,357
2
40
69

I've changed the table name from a parameter to a string and it works. – Martynas Nov 23 '16 at 09:01

Inserting data from DatagridView error

1 Answers1