locked
System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'order'.' RRS feed

  • Question

  • User1932979281 posted

    This message is popping up when I try to click the update button on my web form.

    else if (proc.Equals("u"))
    {
    string sql = "SELECT id,fName,lName,email,phone,make,model,yearmodel,problem FROM dbo.CarCenter where id="+id+" order by id";
    sc = new SqlConnection(cs);
    sc.Open();
    cmmd = new SqlCommand(sql, sc);
    dr = cmmd.ExecuteReader(); - System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'order'.'

    Monday, November 25, 2019 1:27 AM

All replies

  • User303363814 posted

    Put a breakpoint on the line

    sc = new SqlConnection(cs);

    What does the variable 'sql' look like?

    Monday, November 25, 2019 3:49 AM
  • User665608656 posted

    Hi sadProgrammer_SQL,

    What is the content of parameter id here?

    As PaulTheSmith said, I suggest you to use break point to debug the current sql statement, get the complete content of your current sql statement, and show it to us for reference.

    Or you can put the content of the sqlstatement into your SQL Server for testing.

    Best Regards,

    YongQing.

    Monday, November 25, 2019 6:50 AM
  • User1932979281 posted

    Here's the entire code for reference.

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    namespace CarRepairCenter.Pages
    {
    public partial class delup : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {
    string id;
    string proc = Request["proc"];

    id = Request["id"];
    string cs = Session["cs"].ToString();
    string msg = "";
    SqlConnection sc;
    SqlDataAdapter adpt = new SqlDataAdapter();
    SqlDataAdapter updateadpt = new SqlDataAdapter();
    SqlDataReader dr;
    SqlCommand cmmd, cmmd2;

    if (proc.Equals("d"))
    {
    string delSql = "delete from dbo.CarCenter where id =" + id;
    sc = new SqlConnection(cs);
    cmmd = new SqlCommand(delSql, sc);
    try
    {
    sc.Open();
    adpt.DeleteCommand = new SqlCommand(delSql, sc);
    adpt.DeleteCommand.ExecuteNonQuery();
    }
    catch (SqlException se)
    {
    msg = se.Message.ToString();
    }
    finally
    {
    cmmd.Dispose();
    sc.Close();
    }

    if (String.IsNullOrEmpty(msg))
    {
    msg = "Deletion of ID:" + id + " was successful.";
    Response.Redirect("Results.aspx?msg=" + msg);
    }
    }

    else if (proc.Equals("u"))
    {
    string sql = "SELECT id,fName,lName,email,phone,make,model,yearmodel,problem FROM dbo.CarCenter where id="+id+" order by id";
    sc = new SqlConnection(cs);
    sc.Open();
    cmmd = new SqlCommand(sql, sc);
    dr = cmmd.ExecuteReader();

    while (dr.Read())
    {
    uid.Value = dr.GetValue(0).ToString();
    fName.Text = dr.GetValue(1).ToString();
    lName.Text = dr.GetValue(2).ToString();
    email.Text = dr.GetValue(3).ToString();
    phone.Text = dr.GetValue(4).ToString();
    make.Text = dr.GetValue(5).ToString();
    model.Text = dr.GetValue(6).ToString();
    yearmodel.Text = dr.GetValue(7).ToString();
    problem.Text = dr.GetValue(8).ToString();

    }
    }
    else if (proc.Equals("s"))
    {
    string updateSql = "update dbo.CarCenter set fName = '" + Request.Form.Get("fName") + "'," +
    "lName='" + Request.Form.Get("lName") + "', email = '" + Request.Form.Get("email") + "'," +
    "phone='" + Request.Form.Get("phone") + "', make = '" +Request.Form.Get("make") + "', model = '" + Request.Form.Get("model") + "', yearmodel = '" + Request.Form.Get("yearmodel") + "', problem= '" + Request.Form.Get("problem") + "' where id=" + Request.Form.Get("uid");
    sc = new SqlConnection(cs);
    cmmd2 = new SqlCommand(updateSql, sc);
    try
    {
    sc.Open();
    adpt.UpdateCommand = new SqlCommand(updateSql, sc);
    adpt.UpdateCommand.ExecuteNonQuery();
    }
    catch (SqlException se)
    {
    msg = se.Message.ToString();
    }
    finally
    {
    cmmd2.Dispose();
    sc.Close();
    }

    if (String.IsNullOrEmpty(msg))
    {
    msg = "Update on record ID:" + Request.Form.Get("uid") + " was successful.";
    Response.Redirect("Results.aspx?msg=" + msg);
    }

    }
    }


    }
    }

    Tuesday, November 26, 2019 12:21 AM
  • User665608656 posted

    Hi sadProgrammer_SQL,

    Your code indicates that the parameter id is the value obtained through Request["id"].

    id = Request["id"]; // to ensure this id is not null or empty
    string sql = "SELECT id,fName,lName,email,phone,make,model,yearmodel,problem FROM dbo.CarCenter where id="+id+" order by id";

    I suggest that you add breakpoints to the Page_Load method for debugging, and make sure that the value obtained by id is not empty or null.

    I tried to set the id directly to null and it reproduced your issue, so I guess the problem is to get the id value.

    Please debug step by step through the breakpoint to ensure that Request["id"] can get the value.

    Here is a link about how to debug in your code: First look at the Visual Studio Debugger

    Best Regards,

    YongQing.

    Tuesday, November 26, 2019 1:59 AM