SELECT * FROM (rslogin field lookup)

Chevron22 · #1 (**permalink**) 04-04-08, 06:10

Hi all,
I am new to DB and web work so please forgive if i sound like a dummy.
I am trying to select a table in the line below based on the lookup of another table entry. It is in ASP and using an Access DB

Is this possible

set rs = conn.Execute("SELECT * FROM <%= rs("AccNumber")%> ORDER BY ID")

thanks in advance
Tony

architect · #2 (**permalink**) 04-04-08, 09:37

I would recommend putting the SQL in a variable, and then executing using that.

Code:

Dim SqlStmt
SqlStmt = "SELECT * FROM " & rs("AccNumber") & " ORDER BY ID"

Set rs = conn.Execute(SqlStmt)

-A

architect · #3 (**permalink**) 04-04-08, 09:38

Btw you should avoid using "SELECT *"; explicitly declare the column list instead.

-A

loquin · #4 (**permalink**) 04-04-08, 16:25

Is there a common field between the two tables, which could be used to join rows from the second table to the first?

gvee · #5 (**permalink**) 04-05-08, 07:36

Threads merged.

Chevron, please refrain from posting the same question twice; if it is considered to be in the wrong topic a moderator will move it appropriately.

Cheers,
George

gvee · #6 (**permalink**) 04-05-08, 07:43

Code:

set rs = conn.Execute("SELECT * FROM <%= rs("AccNumber")%> ORDER BY ID")

The above would only work if you were writing "set rs ..." to the page. If this was in ASP code you would be nesting <% and %> which you cannot do!

Either assign the value from the recordset to a variable

Code:

<%
Dim myVar

myVar = rs("AccNumber")
%>

And use it later

Code:

<%
set rs = conn.Execute("SELECT * FROM " & myVar & " ORDER BY id")
%>

Also note that in the example you provided you were using the same recordset variable (rs) to refer to two separate open objects; which will fail! You can achieve the same effect using two separate recrodsert objects

Code:

<%
set rs1 = conn.Execute("SELECT * FROM " & rs2("AccNumber") & " ORDER BY id")
%>

Finally, you have a syntax error in your SQL; unless of course your tables are named after account numbers!!

Code:

SELECT col1, col2, ... , colN
FROM   tableName
WHERE  col1 = <someValue>
ORDER
    BY col1

Hope this helps!

Chevron22 · #7 (**permalink**) 04-07-08, 00:15

Quote:

Originally Posted by loquin

Is there a common field between the two tables, which could be used to join rows from the second table to the first?

Yes there is a common field between the two tables and it is the AccNumber field

thanks loquin

Chevron22 · #8 (**permalink**) 04-07-08, 00:17

thanks all for the quick replies.. i am very greatful. you have all helped me heaps

Internet Services

Web Development

Digital Marketing

Consumer Tech