CODEWITHSUNDEEP
c#oledb
Дмитрий
Дмитрий

Reputation: 131

Command parameters

How to correct send parameters to oledb query?

MyCode is

cmd.CommandText = "UPDATE @target SET [@columnname] = Replace([@columnname], Chr(10), '');";

cmd.Parameters.Add(new OleDbParameter("@target", OleDbType.VarChar)).Value = tb_tablename.Text.Trim();
cmd.Parameters.Add(new OleDbParameter("@columnname", OleDbType.VarChar)).Value = column.ColumnName;

And it's not working). I need to add in query @target ( table name ) and @columnname ( column name ).

Modified to code with ?

cmd.CommandText = "UPDATE ? SET [?] = Replace([?], Chr(10), '');";

cmd.Parameters.Add(new OleDbParameter("@target", OleDbType.VarChar)).Value = tb_tablename.Text.Trim();
cmd.Parameters.Add(new OleDbParameter("@columnname", OleDbType.VarChar)).Value = column.ColumnName;
cmd.Parameters.Add(new OleDbParameter("@columnname", OleDbType.VarChar)).Value = column.ColumnName;

Got error:

syntax error in update statement

Concatenation style got error

string query = "UPDATE " + tb_tablename.Text.Trim() + " SET [" + column.ColumnName + "] = Replace([" + column.ColumnName + "], Chr(10), '');";

data type mismatch in criteria expression

Full code:

DataTable dt = new DataTable();
using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + tb_tablename.Text, conn))
{
    adapter.Fill(dt);
}

foreach (DataColumn column in dt.Columns)
{
    if (column.DataType == typeof(String))
    {
        if (column.ColumnName != "ID1" && column.ColumnName != "ID" && column.ColumnName != "Geometry" && column.ColumnName != "Geometry_SK")
        {
            string query = "UPDATE " + tb_tablename.Text.Trim() + " SET [" + column.ColumnName + "] = Replace([" + column.ColumnName + "], Chr(10), '');";

        using (OleDbCommand cmd = new OleDbCommand(query, conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}
}

Whats helped: string query = "UPDATE " + tb_tablename.Text.Trim() + " SET " + column.ColumnName + " = Replace(" + column.ColumnName + ", Chr(10), \"\") WHERE " + column.ColumnName + " <> \"\";";

Blank data + reserved column name brokes all. ColumnNames Date,Type,Note brokes all - exclude it from cycle.

Upvotes: 0

Views: 375

Answers (3)

Alexander Petrov
Alexander Petrov

Reputation: 14231

You cannot substitute table and column names with parameters.

Parameters can only be applied as follows:

UPDATE SomeTable SET SomeColumn = ?

It is best to allow the user to choose the names of tables and columns from Comboboxes/ListBoxes with ready-made values. Something like this:

var table = tablesComboBox.SelectedItem;
var column = columnsComboBox.SelectedItem;

var query = "UPDATE " + table + " SET " + column +
            " = Replace(" + column + ", Chr(10), '''');";

Note that you must escape single quote characters.

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

You indicated in a comment that you're trying to run a replace on all columns in a db with minimal effort. For this you can consider the following:

  • use the GetSchema method of your DbConnection object to get a list of the tables in the db
  • loop Over it, concatenate a string sql of the table name into "SELECT * FROM "+tablename+" WHERE 1=0" and run this sql using a DataAdapter to return an empty datatable with all the columns of the target table
  • loop over the datatable.Columns collection returned from the above select, running your REPLACE sql, subbing the table and column names in via string concat (it's safe and non hackable because you retrieved the list and are not concatting values provided by a user
  • if you have non string columns (dates,ints) then examine the datatype of the DataColumn and only run th replace if it's a string/varchar or similar

Helpful links:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/getschema-and-schema-collections

https://sizious.com/2015/05/11/how-to-load-an-access-database-into-a-dataset-object-in-c/ - goes part way- add a where clause to this guy's code so that no rows are returned (you only want the columns)

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

You're getting a syntax error because cannot make a SQL identifier (table name, column name etc) a parameter. Only values can be parameterized

Your query would hence have to look like:

cmd.CommandText = "UPDATE "+tb_tablename.Text+" SET ["+...+"] = Replace(["+...+"], Chr(10), '');";

Never concatenate values supplied by the user, into an SQL. Because you're forced in this instance to concat take and column names in you should absolutely make sure that only safe values are provided. Ideally you should take the value provided for the the table name and column name and have a lot of all table and column names (you can query the db for this) and only permit the sql to build if the values provided are in that list

This is a very unusual requirement- almost no one here seeks to parameterize table names etc. If you're trying to write some sort of mini library to make your data access life easier, I would recommend you use one that already exists like Dapper

Upvotes: 1

Related Questions