locked
syntax error in update statement RRS feed

  • Question

  • User-604986703 posted

    iam doing my academic project. iam using msaccess as  database, technology as asp.net

    and i have written the following code, but i am getting the error as "Syntax error in Update statement'

    please help me

     

     

    protected void Button1_Click(object sender, EventArgs e)

    {

    OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\website\\anji\\projectdb.mdb"

    );

    string sql = null;

    try

    {

    sql = "update project set end='" + TextBox6.Text + "',perf='" + TextBox10.Text + "',stat='" + TextBox11.Text + "' WHERE empid='" + TextBox3.Text + "' and proid='" + TextBox4.Text + "'";

    Response.Write(sql);

    con.Open();

    OleDbCommand oledbCmd = new OleDbCommand(sql, con);

    int x;

    x = oledbCmd.ExecuteNonQuery();

    if

    (x > 0)

    {

                      Response.Write("<script language='javascript'>alert('Staff route details updated Successfully')</script>");

    }

    else

    {

    Response.Write( "<script language='javascript'>alert('no such row exist')</script>");

    }

    }

    catch (Exception ex)

    {

    Response.Write(ex.Message);

    }

    Saturday, June 11, 2011 8:52 AM

Answers

  • User-740122464 posted

    first of all, you code is vulnerable for SQL injection....please correct that first...

    "Never build Transact-SQL statements directly from user input."

     read more on sql injection on : http://msdn.microsoft.com/en-us/library/aa728894(v=vs.71).aspx

    To get the proper syntax, you can go thru this website : http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 11, 2011 10:14 AM
  • User3866881 posted

    Hello:)

    I notice that your update query has a key word "end"——This is a key word in SQL, if your fieldname is this, please use [end] instead.

    What's more, please check out that you cannot add single quote to type of numeric in SQL statement.

    In the end, just like what the MVP said, please use OleDbParameters instead of combination of splitted strings together to avoid SQL injection.

    Thx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 13, 2011 10:20 PM

All replies

  • User-740122464 posted

    first of all, you code is vulnerable for SQL injection....please correct that first...

    "Never build Transact-SQL statements directly from user input."

     read more on sql injection on : http://msdn.microsoft.com/en-us/library/aa728894(v=vs.71).aspx

    To get the proper syntax, you can go thru this website : http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 11, 2011 10:14 AM
  • User3866881 posted

    Hello:)

    I notice that your update query has a key word "end"——This is a key word in SQL, if your fieldname is this, please use [end] instead.

    What's more, please check out that you cannot add single quote to type of numeric in SQL statement.

    In the end, just like what the MVP said, please use OleDbParameters instead of combination of splitted strings together to avoid SQL injection.

    Thx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 13, 2011 10:20 PM