Martynas Martynas - 16 days ago 7
SQL Question

Inserting data from DatagridView error

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?

Answer

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 http://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.