Trying to spot the syntax error

-1

Getting this error when attempting to update user info in my code...

ErrorSystem.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 clsDataLayer.UpdateUser(String userName, String favProgram, String City, String leastFav, String State, DateTime Date, Int32 userID1) in c:\Users\John Vinson\Documents\Visual Studio 2015\WebSites\ProgramAnonymous\App_Code\clsDataLayer.cs:line 56 at useraccount_details.updateinfo_Click(Object sender, EventArgs e) in c:\Users\John Vinson\Documents\Visual Studio 2015\WebSites\ProgramAnonymous\useraccount_details.aspx.cs:line 57

and here is the code...

public void UpdateUser(string userName, string favProgram, string City, string leastFav,
        string State, DateTime Date, int userID1)
    {
        dbConnection.Open();

        string sqlStmt = "UPDATE users SET userName = @username1, " +
            "favProgram = @favprogram1, " +
            "city = @city1, " +
            "state = @state1, " +
            "leastFavProgram = @leastfavprogram, " +
            "dateComplete = @datecomplete, " +
            "WHERE (users.userID = @id)";

        OleDbCommand dbCommand = new OleDbCommand(sqlStmt, dbConnection);

        OleDbParameter param = new OleDbParameter("@username1", userName);
        dbCommand.Parameters.Add(param);

        dbCommand.Parameters.Add(new OleDbParameter("@favprogram1", favProgram));
        dbCommand.Parameters.Add(new OleDbParameter("@city1", City));
        dbCommand.Parameters.Add(new OleDbParameter("@state1", State));
        dbCommand.Parameters.Add(new OleDbParameter("@leastfavprogram", leastFav));
        dbCommand.Parameters.Add(new OleDbParameter("@datecomplete", Date));
        dbCommand.Parameters.Add(new OleDbParameter("@id", userID1));

        dbCommand.ExecuteNonQuery();

        dbConnection.Close();

    }
c#
sql
asked on Stack Overflow May 29, 2017 by John Vinson

1 Answer

1

In order to avoid such pesky syntax errors, do not build (concatenate) queries from chunks, use verbatim strings to put query as a single, readable string:

    string sqlStmt = 
      @"UPDATE users
           SET userName = @username1,
               favProgram = @favprogram1,
               city = @city1,
               leastFavProgram = @leastfavprogram,
               dateComplete = @datecomplete -- <- no comma ',' should be here
         WHERE (users.userID = @id)"; 

There's a simple developing procedure: open SQL editor, write a correct query then put it (copy + paste) into @""

answered on Stack Overflow May 29, 2017 by Dmitry Bychenko

User contributions licensed under CC BY-SA 3.0