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.

you need to wrap your string parameters in quotes to be able to do the search

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..

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....

Ok attached is the file.. hope you can help.. thanks...

The file name if extended_user_info.txt (.asp)

okie, you have these three commands to open the database
but they are all commented out so the database is never actually being opened.

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

can you do a response.write on the sql string so we can see exactly what is being sent to the database?

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...

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...
.. and see if that works... if it does try this...
if that executes without a problem then you have a conflict in your datatypes for your username field.

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..

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.

Both did ok, no errors...

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')

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..