none
OleDB Command UPDATE Syntax Error

    Question

  • I have the following code block and its giving me more problems than it should. It is a simple update command to an access database. For whatever reason it keeps throwing an exception on my DataAccessLayer. Any thoughts would be greatly appreciated:

    Code:

    public static Boolean SubmitLocalDB(long TestCounter, double YLoad, double YStrength, double Elongation, int BadData, int Ignore, string Direction, int Attempt)
            {
                string SqlQuery = ("UPDATE Results SET [Yield Load] = ?, [Yield Strength] = ?, [ElongationPercent] = ?, BadData = ?, Ignore = ?, Direction = ?, Complete = 1 WHERE TestCounter = ?");
                Boolean results = false;
                OleDbConnection conn = new OleDbConnection();
                conn.ConnectionString = ConfigurationManager.AppSettings.Get("LocalDBConnectionString");
                try
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(SqlQuery, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("[Yield Load]", YLoad);
                        cmd.Parameters.AddWithValue("[Yield Strength]", YStrength);
                        cmd.Parameters.AddWithValue("ElongationPercent", Elongation);
                        cmd.Parameters.AddWithValue("BadData", BadData);
                        cmd.Parameters.AddWithValue("Ignore", Ignore);
                        cmd.Parameters.AddWithValue("Direction", Direction);
                        cmd.Parameters.AddWithValue("TestCounter", TestCounter);
                        
                        cmd.ExecuteNonQuery();
                        results = true;
                    }
                }
                catch (Exception ex) { if (Attempt == 1) { log = new WriteToLog("DAL: " + ex.ToString()); } results = false; }
                finally { conn.Dispose(); conn.Close(); }
                return results;
            }

    Exception:
    System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement.
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at DataAccess.DAL.SubmitLocalDB(Int64 TestCounter, Double YLoad, Double YStrength, Double Elongation, Int32 BadData, Int32 Ignore, String Direction, Int32 Attempt) in C:\Documents and Settings\ctz2165\Desktop\Local Projects\TensileTesterDataSync\v2.0.0\uss.mto.operations.TensileTester\DataAccess\DAL.cs:line 183

    Monday, March 05, 2012 8:32 PM

Answers

  • It seems the issue was with my Ignore field... I changed it to [Ignore] and it worked... Thanks for your help anyway.
    • Marked as answer by mkruluts Monday, March 05, 2012 10:01 PM
    Monday, March 05, 2012 10:01 PM

All replies

  • string SqlQuery = ("UPDATE Results SET [Yield Load] = ?, [Yield Strength] = ?, [ElongationPercent] = ?, BadData = ?, Ignore = ?, Direction = ?, Complete = ? WHERE TestCounter = ?");
    //rest of code...
    //and..
     cmd.CommandType = CommandType.Text;
     cmd.Parameters.AddWithValue("[Yield Load]", YLoad);
     cmd.Parameters.AddWithValue("[Yield Strength]", YStrength);
     cmd.Parameters.AddWithValue("ElongationPercent", Elongation);
     cmd.Parameters.AddWithValue("BadData", BadData);
     cmd.Parameters.AddWithValue("Ignore", Ignore);
     cmd.Parameters.AddWithValue("Complete", "VALUE HERE TOO");
     cmd.Parameters.AddWithValue("TestCounter", TestCounter);


    Mitja

    Monday, March 05, 2012 9:17 PM
  • Sorry... I tried that and no dice... Before I came to the forum, I also tried removing the 'Complete = 1" part and got the same error. I would appreciate hearing any other ideas you might have.
    Monday, March 05, 2012 9:24 PM
  • It seems the issue was with my Ignore field... I changed it to [Ignore] and it worked... Thanks for your help anyway.
    • Marked as answer by mkruluts Monday, March 05, 2012 10:01 PM
    Monday, March 05, 2012 10:01 PM