1

I'm writing a program with C# to automatically insert data in a Navision database via ODBC. I am filling a DataTable-object by calling the OdbcDataAdapter.Fill() method. After changing some values, I want to update the original DB using OdbcDataAdapter.Update(). However this always results in:

ERROR [42000] [Simba][SimbaEngine ODBC Driver]UPDATE Item SET Location = ? WHERE ((No. << ??? >>= ?) AND ((? = 1 AND Location IS NULL) OR (Location = ?))) ERROR [42000] [Simba][SimbaEngine ODBC Driver]Syntax Error.

This is the code:

    static void Main(string[] args)
    {
        DataTable Item = new DataTable();

        OdbcConnection connection = new OdbcConnection("DSN=Navision Frank-Backup");

        connection.Open();
        OdbcDataAdapter adapter = new OdbcDataAdapter();
        adapter.SelectCommand = new OdbcCommand("select \"No.\", \"Location\" from Item where \"No.\" = 'AA0182'", connection);
        OdbcCommandBuilder builder = new OdbcCommandBuilder(adapter);

        adapter.Fill(Item);

        Item.Rows[0].BeginEdit();
        Item.Rows[0]["Location"] = "SW";
        Item.Rows[0].EndEdit();

        adapter.UpdateCommand = builder.GetUpdateCommand();
        adapter.Update(Item);
    }

I'm hoping someone can help me out with that.

Edit: I just found out that if I set the update-command manually, using

adapter.UpdateCommand = new OdbcCommand("update Item set Location = 'SW' where \"No.\" = 'AA0182'");

It works perfectly fine. However I can't use that method because I need to change multiple records.

tubu
  • 11
  • 2
  • Failed to find the specified query `UPDATE Item SET ...` in the supplied code! are you sure you have added the correct code with the question? – sujith karivelil Jul 19 '17 at 11:20
  • Yes that's exactly the code I just used. – tubu Jul 19 '17 at 11:22
  • You have trigger created on the table? Are you sure the error is because of this code only? – Chetan Jul 19 '17 at 11:33
  • No I didn't create a trigger. – tubu Jul 19 '17 at 11:38
  • You really have a dot (.) in the column name ("No.")? Can you try to leave that column out and see if it works then? – PrfctByDsgn Jul 19 '17 at 13:35
  • I can't leave that column out because it's the primary key which I need to update a row. However, a select command with the exact same column name ("No.") works perfeclty fine. So the dot doesn't cause this error. – tubu Jul 19 '17 at 13:38

1 Answers1

0

I found the solution by myself. The error occured because of some of the column names are wrapped in quotation marks. I needed to set the QuotePrefix and QuoteSuffix properties of the command builder.

The following code works:

        static void Main(string[] args)
    {
        DataTable Item = new DataTable();

        OdbcConnection connection = new OdbcConnection("DSN=Navision Frank-Backup");

        connection.Open();
        OdbcDataAdapter adapter = new OdbcDataAdapter();
        adapter.SelectCommand = new OdbcCommand("select \"No.\", Location from Item where \"No.\" = 'AA0182'", connection);
        OdbcCommandBuilder builder = new OdbcCommandBuilder(adapter);
        builder.QuotePrefix = builder.QuoteSuffix = "\"";

        adapter.Fill(Item);

        Item.Rows[0].BeginEdit();
        Item.Rows[0]["Location"] = "TC";
        Item.Rows[0].EndEdit();

        adapter.UpdateCommand = builder.GetUpdateCommand();
        adapter.Update(Item);
        connection.Close();
    }
tubu
  • 11
  • 2