none
Syntax Error in Update Statement

    Question

  • Dear All,

    I had encountered Syntax Error in Update Statement when I execute the following code.

     

    private void UpdateNewPwd(string EmpID, string NewPwd)

    {

    string strUpdate = "Update Users SET Password = '" + NewPwd + "'" +

    " where EmployeeID = '" + EmpID + "'";

    string connectionString1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Windows.Forms.Application.StartupPath + "\\Local_Database\\DryEtch.mdb;User Id=admin;Password=;";

    OleDbDataAdapter dbAdapter = new OleDbDataAdapter();

    OleDbCommand dbCom = new OleDbCommand(strUpdate, conn);

    int x = dbCom.ExecuteNonQuery();

     

    conn.Close();

    MessageBox.Show("Password had been changed");

    }

    Does anyone know what is the problem? I had check through the tables, EmployeeID and Password is set as text in Access database.

    Thanks

     

    Wednesday, February 15, 2006 10:22 AM

Answers

  • You'll need to escape the single quotes:

    "UPDATE table SET field = \'" + aValue + "\'"

    Or, you can also use the @ operator:


    string s = @"update table set field = 'value'";

    But, the way you're creating your queries, is not safe.   It is better if you use parametrized queries.

    See here:

    http://fgheysels.blogspot.com/2005/12/avoiding-sql-injection-and-date.html

    Wednesday, February 15, 2006 10:52 AM
  • Yes, take a look at the code i posted here.

    Here is a little modification to use it with OleDb.


    // TODO: Set date variable.
    DateTime date = DateTime.Now;

    // Set query and parameters.
    const string query = "SELECT * FROM Table1 WHERE MyDate = @MyDate";
    OleDbDataParameter pMyDate = new OleDbDataParameter("@MyDate", SqlDbType.DateTime);
    pMyDate.Value = date;

    // Create connection and open it.
    OleDbConnection dbConn = new OleDbConnection("ConnectingString");
    dbConn.Open();

    try
    {
     using(OlDbCommand dbCommand = new OlDbCommand(query, dbConn))
     {
      // Add paramter to Command.
      dbCommand.Parameters.Add( pMyDate );

      // Execute the query and get results.
      OleDbDataReader reader = dbCommand.ExecuteReader();

      try
      {
       // Walkthrough results.
       while(reader.Read())
       {
        // TODO: Do something with the data.
       }
      }
      finally
      {
       // Close reader.
       reader.Close();
      }
     }
    }
    finally
    {
     // Close connection.
     dbConn.Close();
    }

     

    Friday, February 17, 2006 7:44 AM

All replies

  • You'll need to escape the single quotes:

    "UPDATE table SET field = \'" + aValue + "\'"

    Or, you can also use the @ operator:


    string s = @"update table set field = 'value'";

    But, the way you're creating your queries, is not safe.   It is better if you use parametrized queries.

    See here:

    http://fgheysels.blogspot.com/2005/12/avoiding-sql-injection-and-date.html

    Wednesday, February 15, 2006 10:52 AM
  • Use paramitrimized queries. Then you never have to worry about format's or SQL Injection.
    It's olso better for the preformance, because you don't need to have to concatenate a string for example:


    string query = "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" + "txtName.Text + "\"";
     


    No escape characters needed, you doesn't have to think about using a " or not etc.

    Parameters are like placeholders, you use them in Stored Procedures as well.

    A little example:


    // TODO: Set date variable.
    DateTime date = DateTime.Now;

    // Set query and parameters.
    const string query = "SELECT * FROM Table1 WHERE MyDate = @MyDate";
    SqlParameter pMyDate = new SqlParameter("@MyDate", SqlDbType.DateTime);
    pMyDate.Value = date;

    // Create connection and open it.
    SqlConnection dbConn = new SqlConnection("ConnectingString");
    dbConn.Open();

    try
    {
     using(SqlCommand dbCommand = new SqlCommand(query, dbConn))
     {
      // Add paramter to Command.
      dbCommand.Parameters.Add( pMyDate );

      // Execute the query and get results.
      SqlDataReader reader = dbCommand.ExecuteReader();

      try
      {
       // Walkthrough results.
       while(reader.Read())
       {
        // TODO: Do something with the data.
       }
      }
      finally
      {
       // Close reader.
       reader.Close();
      }
     }
    }
    finally
    {
     // Close connection.
     dbConn.Close();
    }

     

    Wednesday, February 15, 2006 1:42 PM
  •  

    Hi all,

     

    I had changed to the following string, but it still give me Syntax Error.

    Please help again. Thanks.

     

     

    string strUpdate = "Update Users SET Password = \'" + NewPwd + "'" +

    " where EmployeeID = \'" + EmpID + "'";

    Thursday, February 16, 2006 1:29 AM
  • If EmployeeId is numeric, you should not use quotes.
    But, once again, use parametrized queries.
    Thursday, February 16, 2006 7:54 AM
  • But is it possible to use a parametrized querie with a MDB access file?

    how?

    tnx

    Friday, February 17, 2006 1:27 AM
  • Yes, take a look at the code i posted here.

    Here is a little modification to use it with OleDb.


    // TODO: Set date variable.
    DateTime date = DateTime.Now;

    // Set query and parameters.
    const string query = "SELECT * FROM Table1 WHERE MyDate = @MyDate";
    OleDbDataParameter pMyDate = new OleDbDataParameter("@MyDate", SqlDbType.DateTime);
    pMyDate.Value = date;

    // Create connection and open it.
    OleDbConnection dbConn = new OleDbConnection("ConnectingString");
    dbConn.Open();

    try
    {
     using(OlDbCommand dbCommand = new OlDbCommand(query, dbConn))
     {
      // Add paramter to Command.
      dbCommand.Parameters.Add( pMyDate );

      // Execute the query and get results.
      OleDbDataReader reader = dbCommand.ExecuteReader();

      try
      {
       // Walkthrough results.
       while(reader.Read())
       {
        // TODO: Do something with the data.
       }
      }
      finally
      {
       // Close reader.
       reader.Close();
      }
     }
    }
    finally
    {
     // Close connection.
     dbConn.Close();
    }

     

    Friday, February 17, 2006 7:44 AM
  • You're still getting a syntax error because you need to escape ALL single quotes in strings:



    string strUpdate = "Update Users SET Password = \'" + NewPwd + "\'" +
    " where EmployeeID = \'" + EmpID + "\'";

     

    But you should still used parameterised SQL instead.

    Friday, February 17, 2006 10:33 AM
  • string str = "update users set password=' " + variable1 +" ' where employeeid=' " + EmpID + " '" ;

    if empid is string

     

    if it is numeric

    then

    string str = "update users set password=' " + variable1 +" ' where employeeid= " + EmpID + " " ;

    Friday, February 17, 2006 1:31 PM
  • The word "password" is a reserved words, it is required that it be enclosed in brackets; (i.e. [password] )
    Thursday, May 03, 2007 3:45 PM