none
update statement problem RRS feed

  • Question

  • hi guys...want to know what's wrong with this piece of code...been debuging yet couldnt find any answeers to it..
    the error is: syntax error in update statement
    im using C#
    please help me...if you have the solution it will be better Smile

    [code]
    string takeNumber = doNumberLabel.Text;
    string eID = idDatabaseLabel.Text;
    int pas;
    pas = int.Parse(eID);

    string connection = "provider=Microsoft.jet.OLEDB.4.0;Data source=" + Application.StartupPath + "\\..\\..\\Stock.mdb; Jet OLEDBBig Smileatabase Password=" + databasePassword;
    OleDbConnection myConnection = new OleDbConnection(connection);
    myConnection.Open();

    string query = @"update doNumber set Number = '" + takeNumber + "' where ID =" +pas;

    myCommand = new OleDbCommand();
    myCommand.CommandText = query;
    myCommand.Connection = myConnection;
    myCommand.ExecuteNonQuery();

    myConnection.Close();
    [/code]


    Friday, January 4, 2008 9:44 AM

Answers

  • First, you should never formulate SQL with string operations.  Type "SQL injection" into your favorite search engine to find out why.

     

    Try this instead:

     

    Code Block

    string cmdText = "update donumber set number = ? where id = ?";

    OleDbCommand c = new OleDbCommand(cmdText);

    c.Parameters.AddWithValue("number", n);

    c.Parameters.AddWithValue("id", id);

     

    OLEDB doesn't use named parameters in text queries, so the names you provide in AddWithValue aren't relevant, just the order.

    Friday, January 4, 2008 10:26 AM
  • Hi,

    Building a SQL statement using string concatenation can be risky from a security point of view (think SQL injection attacks). The preferred way to build command text is using a parameterized query. Here I will show you an example:

    Code Snippet

    public void UpdateCarPetName(int id, string newPetName)
    { sqlconnection conection=new sqlconection(connnectionstring);
    // Get ID of car to modify and new pet name.
    string sql =
    string.Format("Update Inventory Set PetName = '{0}' Where CarID = '{1}'",
    newPetName, id);
    using(SqlCommand cmd = new SqlCommand(sql, connection))
    {
    cmd.ExecuteNonQuery();
    }

    connection.Close();
    }

     

    Regards

    Jing

    Wednesday, July 2, 2008 9:02 AM
  • Number is a reserved Jet SQL keyword. Used in a SQL statement is must be enclosed in brackets. However, I would highly recommend that you change the name of the column since you won't be able to use it in a "SELECT * ..." statement.
    Wednesday, July 2, 2008 12:48 PM

All replies

  • First, you should never formulate SQL with string operations.  Type "SQL injection" into your favorite search engine to find out why.

     

    Try this instead:

     

    Code Block

    string cmdText = "update donumber set number = ? where id = ?";

    OleDbCommand c = new OleDbCommand(cmdText);

    c.Parameters.AddWithValue("number", n);

    c.Parameters.AddWithValue("id", id);

     

    OLEDB doesn't use named parameters in text queries, so the names you provide in AddWithValue aren't relevant, just the order.

    Friday, January 4, 2008 10:26 AM
  • Hi,

    Building a SQL statement using string concatenation can be risky from a security point of view (think SQL injection attacks). The preferred way to build command text is using a parameterized query. Here I will show you an example:

    Code Snippet

    public void UpdateCarPetName(int id, string newPetName)
    { sqlconnection conection=new sqlconection(connnectionstring);
    // Get ID of car to modify and new pet name.
    string sql =
    string.Format("Update Inventory Set PetName = '{0}' Where CarID = '{1}'",
    newPetName, id);
    using(SqlCommand cmd = new SqlCommand(sql, connection))
    {
    cmd.ExecuteNonQuery();
    }

    connection.Close();
    }

     

    Regards

    Jing

    Wednesday, July 2, 2008 9:02 AM
  • Number is a reserved Jet SQL keyword. Used in a SQL statement is must be enclosed in brackets. However, I would highly recommend that you change the name of the column since you won't be able to use it in a "SELECT * ..." statement.
    Wednesday, July 2, 2008 12:48 PM