Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    Unanswered: SELECT * FROM (rslogin field lookup)

    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

  2. #2
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    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

  3. #3
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    Btw you should avoid using "SELECT *"; explicitly declare the column list instead.

    -A

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Is there a common field between the two tables, which could be used to join rows from the second table to the first?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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!
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2008
    Posts
    5
    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

  8. #8
    Join Date
    Apr 2008
    Posts
    5
    thanks all for the quick replies.. i am very greatful. you have all helped me heaps

Posting Permissions

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