ASP/SQL (Access) Query

Karoma

hey, can anybody show me an example of a dynamically generated SQL query, using stored Session variables. i'm trying to do it but i just cannay do it the session variables are stored ok- Response.Write on other pages displays them no prob.
The idea: take a variable from a form.
(validate form input (login & id) ) Store logon as Session var.
user is then taken to a page where they can simply click on a link and the query is generated. ex: SelectSQL = "SELECT * FROM " & Session("dbID") & ";" .

Any help would be appreciated. (Google searches failed to find anything new that i haven't already tried )

IIS5.0, MS Access DB

Terminator

It would be helpful if you could post the error you're getting.

Off the top of my head it sounds like you might need to declare the session as an integer, e.g.,

id = int(session("dbid"))

especially if the field you're referencing in the table is a number field.

The Corinthian

Originally posted by Karoma
SelectSQL = "SELECT * FROM " & Session("dbID") & ";"

Looking at your concatonated SQL string, I’d hazard agues that your problem is a SQL syntax error (I’m assuming that Session("dbID") is not a table name, but the record index/ID).

In that case your ASP/SQL should look something like:

SelectSQL = "SELECT * FROM myTable WHERE tblID=" & Session("dbID")

Where myTable is your table name and tblID is the index/ID field name for the record (normally of type autonumber in Access).

Scruff

i had a lot of trouble with sql statements before, mostly to do with syntax and date\time errors. If The Corinthian's syntax doesn't work try

SelectSQL = "SELECT * FROM myTable WHERE tblID = ' " & Session("dbID") & " '

i found you need to use the ' single quotes if the type is char or nvarchar.

Where myTable is your table name and tblID is the index/ID field name for the record (normally of type autonumber in Access).

Karoma

Problem resolved. I pasted the wrong the wrong statement,but you guessed it right. Anyways, it works now, the help was -much appreciated-;)

Winters

If your having problems (Like I Was) just get a copy of Dreamweaver UltraDev. It will do all the Querys for you which is nice, expecily if your in a hurry

Mutz

"Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[INTERSOLV][ODBC SQL Server driver][SQL Server]Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query"

CODE:

strSQL = "SELECT * FROM Web_Passwords WHERE o_owner_id =' " & strUserID & " ' "


Can Anyone see anything wrong here:

o_owner_id is an INT in the DB so i really havent got a clue.

P.S. Using ASP

Hobbes

My SQL sucks but guessing at that error message are you trying to tell SQL to look up a string on an INT field?

Mutz

Got her Running......

Its those stupid Quote Signs. ' = string.

Muah

amen

Its those stupid Quote Signs. ' = string.

not really
what you were trying to do was search on a Integer field (o_owner_id ) by passing in a VARCHAR value(strUserID).

If you really wanted to do that what you could do would be
strSQL = "SELECT * FROM Web_Passwords WHERE o_owner_id ='CONVERT(INT, " & strUserID & ") ' "

Which would convert your varchar to int

Mutz

Cheers m8

Didnt know how to use that Convert function.. Now I do