Valid SQL Statement Returning 'Syntax Error" in Asp.NET

Question

I'm making an ASP.Net web application, but it is returning a syntax error whenever I try to load the page.

My DB schema can be seen here http://sqlfiddle.com/#!2/739c4/7

Here is the SQL query:

SELECT
    tblOrderTransactions.ordertransaction_orderid AS orderid,
    tblProducts.product_name AS productname,
    tblOrders.order_customer AS ordercustomer
FROM
    tblProducts 
    LEFT JOIN tblOrderTransactions 
        ON tblProducts.product_id = tblOrderTransactions.ordertransaction_productid
    LEFT JOIN tblOrders
        ON tblOrderTransactions.ordertransaction_orderid = tblOrders.order_id
WHERE
    (
        (
            (
                tblOrderTransactions.ordertransaction_orderid
            )
            =3
        )
    ) and (
        (
            (tblOrders.order_customer)
            =3
        )
    )

The SQL works in sqlfiddle, and if I remove the part that says

LEFT JOIN tblOrders
   ON tblOrderTransactions.ordertransaction_orderid = tblOrders.order_id

in my web application, the table loads. Adding this second INNER JOIN seems to be messing it up, but the same query works in sqlfiddle, so I believe the SQL is correct.

The error message I receive is

Syntax error (missing operator) in query expression 'tblProducts.product_id = tblOrderTransactions.ordertransaction_productid LEFT JOIN tblOrders ON tblOrderTransactions.ordertransaction_orderid = tblOrders.order_i'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'tblProducts.product_id = tblOrderTransactions.ordertransaction_productid LEFT JOIN tblOrders ON tblOrderTransactions.ordertransaction_orderid = tblOrders.order_i'.

C# Code inside the application:

string orderID = Request.QueryString["id"];
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["finalConnString"].ConnectionString;
string commText = @"SELECT tblOrderTransactions.ordertransaction_orderid AS orderid, tblProducts.product_name AS productname 
                    FROM tblProducts 
                    LEFT JOIN tblOrderTransactions
                        ON tblProducts.product_id = tblOrderTransactions.ordertransaction_productid 
                    LEFT JOIN tblOrders
                      ON tblOrderTransactions.ordertransaction_orderid = tblOrders.order_id
                    WHERE (((tblOrderTransactions.ordertransaction_orderid)=?)) ";

    conn.Open();
    OleDbCommand comm = conn.CreateCommand();
    comm.Connection = conn;
    comm.CommandText = commText;

    OleDbParameter param;
    param = comm.CreateParameter();
    param.DbType = DbType.Int32;
    param.Direction = ParameterDirection.Input;
    param.Value = Int32.Parse(orderID);
    comm.Parameters.Add(param);

    //param = comm.CreateParameter();
    //param.DbType = DbType.Int32;
    //param.Direction = ParameterDirection.Input;
    //param.Value = Session["LoggedInId"];
    //comm.Parameters.Add(param);

    OleDbDataReader reader = comm.ExecuteReader();

    if (reader.HasRows)
    {
        rptOrders.DataSource = reader;
        rptOrders.DataBind();
        lblOrderNumber.Text = orderID.ToString();
    }
    else
    {
        Response.Write("You are not authorized to view this order.");
    }

//Free up the connection
conn.Close();

Are you running it against MySQL as specified in the fiddle or are you using another database server locally? — Matti Virkkunen
– Matti Virkkunen, Commented Nov 18, 2014 at 3:31
@MattiVirkkunen I'm not too sure what type of SQL server it runs on. Its in production, so its running on my machine locally. The data is being read from a mircosoft access .accdb file — sire
– sire, Commented Nov 18, 2014 at 3:41
@sirjustinwhite: Well there's your problem, Access seems to have a weird syntax for JOINs. It looks like it requires extra parenthesis, but I don't use Access (I thought nobody did for serious work) so I can't say exactly where. You should also always use the same type of database server when testing as in production. — Matti Virkkunen
– Matti Virkkunen, Commented Nov 18, 2014 at 3:44
@MattiVirkkunen I'll look into the access specific syntax. Thank you. I think that is going to help me solve this. We are only using access because it is for a class and the class required it. Didn't even think about being able to switch the db server type on sqlfiddle! — sire
– sire, Commented Nov 18, 2014 at 3:49
@MattiVirkkunen alright you solved it. Access needs the joins to be nested within parentheses in order to work. Added the parentheses in the correct spots, and it's working! — sire
– sire, Commented Nov 18, 2014 at 4:25

sire · Accepted Answer · 2014-11-18 04:32:01Z

1

The SQL Statement must have the joins nested in parentheses because of Access specific syntax (Thank you @MattiVirkkunen for that knowledge).

The correct way to write this SQL statement is:

SELECT tblOrderTransactions.ordertransaction_orderid, tblProducts.product_name, tblOrders.order_customer FROM tblProducts INNER JOIN (tblOrders INNER JOIN tblOrderTransactions ON tblOrders.[order_id] = tblOrderTransactions.[ordertransaction_orderid]) ON tblProducts.[product_id] = tblOrderTransactions.[ordertransaction_productid] WHERE (((tblOrderTransactions.ordertransaction_orderid)=3)) and (((tblOrders.order_customer)=3))

answered Nov 18, 2014 at 4:32

sire

438 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DRapp · Accepted Answer · 2014-11-18 03:46:14Z

0

First, I would suggest working with aliases to keep your query a little shorter to read (and type) such as below.

SELECT 
      OT.ordertransaction_orderid AS orderid, 
      P.product_name AS productname, 
      O.order_customer AS ordercustomer
   FROM 
      tblProducts P
         LEFT JOIN tblOrderTransactions OT
            ON P.product_id = OT.ordertransaction_productid
            LEFT JOIN tblOrders
               ON OT.ordertransaction_orderid = O.order_id
   WHERE 
          OT.ordertransaction_orderid = 3 
      and O.order_customer = 3

Second, it does look completely legit and nothing glaring out as invalid. But error of loading the page MIGHT be due to no records being returned. You have your query with left-joins, but then throwing in your WHERE clause for both the "tblOrderTransactions" and "tblOrders" tables changes it to an INNER JOIN result.

So, your query looking for order ID = 3 and also the customer = 3, what if that was not the case, that order #3 was customer #1 which would result in NO RECORDS.

Getting back to now seeing your posted syntax, it looks unbalanced parens ((( )) that might be the issue.

And a final review.. Your "ID" from the query string. Is it properly doing an int.Parse() against it? I know on some instances of int.parse() I've done in the past, if the string started with a space vs actual number, int.Parse() would throw an error... Confirm the incoming values and did it really convert as you expected.

edited Nov 18, 2014 at 3:46

answered Nov 18, 2014 at 3:39

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

3 Comments

Matti Virkkunen Over a year ago

He did paste the exception in his question, and it is an SQL error: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression

Matti Virkkunen Over a year ago

Nope, his parens are balanced. I checked.

DRapp Over a year ago

@MattiVirkkunen, then it must have been in-between edits when I saw and it's current state. Sometimes over-use of parens can cause problems too, blinding the root problem.

Collectives™ on Stack Overflow

Valid SQL Statement Returning 'Syntax Error" in Asp.NET

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related