0

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.

Improve this question
8
  • The OLE DB .NET Provider does not support named parameters link. The question mark (?) placeholder must be used. Commented Nov 18, 2019 at 5:45
  • Side note; I think in OLE you can write @namedParameters but they don't function like named parameters - they're still positional and you have to declare a value for each one. i.e. they behave as if they're converted to ? internally Commented Nov 18, 2019 at 6:44
  • From the full code you've posted consider that you could do the replacement in c# and then use a dataadapter to send all the values back to the db Commented Nov 18, 2019 at 7:01
  • @CaiusJard i'm using replace function in ms access. You suggest do it in c# and after that make update? Commented Nov 18, 2019 at 7:05
  • Doesn't access use " for strings? Replace with "" not ''? Commented Nov 18, 2019 at 7:05

3 Answers 3

Reset to default
1

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

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

7 Comments

How i can change it dynamical? I need to execute Replace to all columns..
Oof, it's tough because Access doesn't have anything like SQL Server's information schema to list all the columns and tables of a database. I'll make another answer
Actually, just a quick query- is this MS Access? I assumed it was but then thought I could be something else...
If i try concatenation it shows System.Data.OleDb.OleDbException: "Data type mismatch in criteria expression."
Because you can only run REPLACE on string type columns probably
|
0

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)

Improve this answer

1 Comment

My algoritm is the same. But it is going to exeption. Added full code in question.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.