Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Apr 2004
    Posts
    75

    Exclamation Unanswered: Syntax error (missing operator) in query expression

    Here is my sql statement:
    sql = "SELECT CASE_NAMES.ID, CASE_NAMES.CASE_NAME, CASE_USERS.CaseID, CASE_USERS.UserName FROM CASE_NAMES INNER JOIN CASE_USERS ON CASE_NAMES.ID = CASE_USERS.CaseID WHERE CASE_USERS.UserName = " & strID

    Here is the error I get:
    Error Type:
    Microsoft JET Database Engine (0x80040E14)
    Syntax error (missing operator) in query expression 'CASE_USERS.UserName = Randall S. Acree'.
    /extended_user_info.asp, line 84

    Here is line 84:
    objrs.Open sql, Myconn

    I am lost, I have been working on this for two days now.. any suggestions PLEASE!..

    Thanks.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    you need to wrap your string parameters in quotes to be able to do the search
    Code:
    sql = "SELECT CASE_NAMES.ID, CASE_NAMES.CASE_NAME, CASE_USERS.CaseID, CASE_USERS.UserName FROM CASE_NAMES INNER JOIN CASE_USERS ON CASE_NAMES.ID = CASE_USERS.CaseID WHERE CASE_USERS.UserName = '" & strID & "'"

  3. #3
    Join Date
    Apr 2004
    Posts
    75
    ok, did that, now I get this error:
    Error Type:
    ADODB.Recordset (0x800A0E7D)
    The connection cannot be used to perform this operation. It is either closed or invalid in this context.
    /extended_user_info.asp, line 98

    Here is line 98:
    objrs.Open sql, Myconn

    what should I look for now?

    Thanks for your reply..

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    well either you are not opening your connection or you are closing it before you use it or it is becoming invalid some how.

    Do you want to attach the whole page and I'll have a look for you....

  5. #5
    Join Date
    Apr 2004
    Posts
    75
    Ok attached is the file.. hope you can help.. thanks...

    The file name if extended_user_info.txt (.asp)
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie, you have these three commands to open the database
    Code:
    'Myconn.Open "driver={Microsoft Access Driver (*.mdb)};;dbq=C:\inetpub\database\USERNAMES_PASSWORDS.mdb"
    'Myconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\inetpub\database\USERNAMES_PASSWORDS.mdb"
    'Myconn.Open "DBQ=" & Server.Mappath("../database/USERNAMES_PASSWORDS.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
    but they are all commented out so the database is never actually being opened.

  7. #7
    Join Date
    Apr 2004
    Posts
    75
    Sorry about that, I guess I was trying something else, but this is the one I try to use:
    Myconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\inetpub\database\USERNAMES_PASSWORDS.mdb"

    and here is the error:
    Error Type:
    Microsoft JET Database Engine (0x80040E07)
    Data type mismatch in criteria expression.
    /extended_user_info.asp, line 98

    Line 98:
    objrs.Open sql, Myconn

    Now if I use this one:
    Myconn.Open "driver={Microsoft Access Driver (*.mdb)};;dbq=C:\inetpub\database\USERNAMES_PASSWO RDS.mdb"

    I get thie error:
    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
    [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
    /extended_user_info.asp, line 98


    Line 98 same as above..

    I am lost..

    Thanks

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    can you do a response.write on the sql string so we can see exactly what is being sent to the database?

  9. #9
    Join Date
    Apr 2004
    Posts
    75
    OK here is the response write from what you told me to do to the string at the end:
    SELECT CASE_NAMES.ID, CASE_NAMES.CASE_NAME, CASE_USERS.CaseID, CASE_USERS.UserName FROM CASE_NAMES INNER JOIN CASE_USERS ON CASE_NAMES.ID = CASE_USERS.CaseID WHERE CASE_USERS.UserName = 'Randall S. Acree'

    I do not think this will work because of the single quotes..

    Here is the response.write without the single quotes:
    SELECT CASE_NAMES.ID, CASE_NAMES.CASE_NAME, CASE_USERS.CaseID, CASE_USERS.UserName FROM CASE_NAMES INNER JOIN CASE_USERS ON CASE_NAMES.ID = CASE_USERS.CaseID WHERE CASE_USERS.UserName = Randall S. Acree

    But then if I do that, this is the error I get using this connection:
    Myconn.Open "driver={Microsoft Access Driver (*.mdb)};;dbq=C:\inetpub\database\USERNAMES_PASSWO RDS.mdb"

    Error:
    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'CASE_USERS.UserName = Randall S. Acree'.
    /extended_user_info.asp, line 98

    Line 98:
    objrs.Open sql, Myconn

    Hope this helps...

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie, I am assuming that Username is a string value. If it is then you need the quotes. No question at all..... it's a standard trap for young players.

    now what could be a problem is the join in your statement. Are both of those fields of the same time? eg, both integers or longs. Otherwise you can't join on them.

    Another test you can do is replace your sql with this...
    Code:
    SELECT CASE_NAMES.ID, CASE_NAMES.CASE_NAME FROM CASE_NAMES
    .. and see if that works... if it does try this...
    Code:
    SELECT CASE_NAMES.ID, CASE_NAMES.CASE_NAME, CASE_USERS.CaseID, CASE_USERS.UserName FROM CASE_NAMES INNER JOIN CASE_USERS ON CASE_NAMES.ID = CASE_USERS.CaseID
    if that executes without a problem then you have a conflict in your datatypes for your username field.

  11. #11
    Join Date
    Apr 2004
    Posts
    75
    With the first code:
    SELECT CASE_NAMES.ID, CASE_NAMES.CASE_NAME FROM CASE_NAMES

    with the second code:
    SELECT CASE_NAMES.ID, CASE_NAMES.CASE_NAME, CASE_USERS.CaseID, CASE_USERS.UserName FROM CASE_NAMES INNER JOIN CASE_USERS ON CASE_NAMES.ID = CASE_USERS.CaseID

    both write out for me, it is when I use the WHERE and the & str part that is gets all messed up..

    Oh, all I did was a response.write on the two, did you want me to do anything else?

    Thanks again for all your help, this has really been bugging me..

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    As well as response.writing them can you execute then against the database? I need to see if those parts fo the query are correct.
    Last edited by rokslide; 10-24-04 at 22:53.

  13. #13
    Join Date
    Apr 2004
    Posts
    75
    Both did ok, no errors...

  14. #14
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    do you know how to execute sql directly against your access database? create a new query, don't add any tables, change the view to sql, paste in your code and then execute it and see what happens (this is with this bit of code - SELECT CASE_NAMES.ID, CASE_NAMES.CASE_NAME, CASE_USERS.CaseID, CASE_USERS.UserName FROM CASE_NAMES INNER JOIN CASE_USERS ON CASE_NAMES.ID = CASE_USERS.CaseID WHERE CASE_USERS.UserName = 'Randall S. Acree')

  15. #15
    Join Date
    Apr 2004
    Posts
    75
    it says:
    Data type mismatch in criteria expression.

    I got that sql from the DB I just tested it on..

    If I do just the sql query with out teh WHERE part it show the table with the correct info just not the specific user since I am not doing the WHERE part..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •