-2

The following code raises an exception datatype mistmach in criteria expression. Where does the exception come from ? Generated query works in Microsoft Access with no problems.

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();
            }
        }
    }
}

As example generated SQL code is UPDATE Necropolis SET NAME_MR = Replace([NAME_MR], Chr(10), ""); . After generating its execute with cmd.ExecuteNonQuery();

Parameters version shows Syntax error in query. Incomplete query clause..

using (OleDbCommand cmd = new OleDbCommand(query, conn))
                            {
                                cmd.CommandType = CommandType.Text;
                                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;

                                MessageBox.Show(cmd.CommandText);

                                cmd.ExecuteNonQuery();
                            }
Fab
  • 14,327
  • 5
  • 49
  • 68
Дмитрий
  • 131
  • 7
  • @tymtam And i like there is column ID and ID1 :) – Djuro Nov 15 '19 at 10:53
  • Welcome to Stack Overflow! Please review [how-to-ask](https://stackoverflow.com/help/how-to-ask) and [how-to-create-reproducible-example](https://stackoverflow.com/help/minimal-reproducible-example) to help you to ask a good question, and thus get a good answer – Selim Yildiz Nov 15 '19 at 10:54
  • What is the **exact** value of `query`? – mjwills Nov 15 '19 at 11:35
  • https://stackoverflow.com/questions/601300/what-is-sql-injection – mjwills Nov 15 '19 at 11:35
  • The problem comes from the fact that you mistake type stored in database like Varchar and identifiers that should be static and cannot be query parameters. Look at https://stackoverflow.com/questions/2838490/table-name-as-variable – Fab Nov 18 '19 at 09:14

1 Answers1

0

are you sure, that query gives a valid SQL statement? I am not sure, but =Replace([xyz], Chr(10), \"\"); does not look like a valid SQL statement fo me... I think, you should use two seperate strings for the columname:

string col_org = column.ColumnName;
string col_new = column.ColumnName.Replace(Chr(10), "\"");
string query = "UPDATE " + tb_tablename.Text.Trim() + " SET " + col_org + " = " + "+ col_new + ";";

Can you give us an example of the update string?

Wolfgang Roth
  • 451
  • 4
  • 18