Reputation: 1916

Sql to update table

I am using the following code to update the user information but i am getting error. Can anybody point me out what is wrong. I am writing this code for web service.

public string UpdateUser(int uID, string fName,
            string lName, string password, string emailAddress)
        {
            // Create connection object
            int ix = 0;
            string rTurn = "";
            OleDbConnection oleConn = new OleDbConnection(connString);
            try
            {
                oleConn.Open();
                string sql = "UPDATE [User] SET [fName]=@fName, [lName]=@lName, [password]=@password, [emailAddress]=@emailAddress" + "WHERE [ID]=@uID";
                OleDbCommand oleComm = new OleDbCommand(sql, oleConn);

                oleComm.Parameters.Add("@fName", OleDbType.Char).Value = fName;
                oleComm.Parameters.Add("@lName", OleDbType.Char).Value = lName;
                oleComm.Parameters.Add("@password", OleDbType.Char).Value = password;
                oleComm.Parameters.Add("@emailAddress", OleDbType.Char).Value = emailAddress;
                oleComm.Parameters.Add("@uID", OleDbType.Integer).Value = uID;

                ix = oleComm.ExecuteNonQuery();
                if (ix > 0)
                    rTurn = "User Updated";
                else
                    rTurn = "Update Failed";
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                rTurn = ex.ToString();
            }
            finally
            {
                oleConn.Close();
            }
            return rTurn;
        }

Error *I am getting following error when i try to update user.*

    <string>
System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression '@emailAddressWHERE [ID]=@uID'.
       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 UserManagement.UserRegistration.UpdateUser(Int32 uID, String fName, String lName, String password, String emailAddress) in C:\Users\smartamrit\Desktop\SystemSoftware\UserManagement\UserRegistration.asmx.cs:line 97
</string>

Upvotes: 0

Answers (4)

Deepa Sinha

Reputation: 11

You need add space before your where starts.

You can write your query like this as mentioned below.

string sql = "UPDATE [User] SET [fName]=@fName, [lName]=@lName, 
    [password]=@password, [emailAddress]=@emailAddress" +" "+ "WHERE [ID]=@uID";

Upvotes: 1

John Woo

Reputation: 263733

you need to add extra space before WHERE

string sql = @"UPDATE [User] 
              SET [fName]=@fName, 
                  [lName]=@lName, [password]=@password, 
                  [emailAddress]=@emailAddress" + " WHERE [ID]=@uID";
                                                   ^ here

or I can't see any difference if you didn't concatenate it, why not do it directly

string sql = @"UPDATE [User] 
               SET [fName]=@fName, 
                   [lName]=@lName, 
                   [password]=@password, 
                   [emailAddress]=@emailAddress 
               WHERE [ID]=@uID";

Upvotes: 4