Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2008
    Posts
    10

    Unanswered: ASP with MS Access SQL query using a dynamic Table name

    This is for a problem/defect tracking system for products.
    Per the thread title I am using ASP and MS Access.

    I'm trying to keep the whole system very dynamic and simple for the user.
    To achieve this. many data fields use drop down lists populated from a table.
    Once finished, there will be an ability to edit the data in those tables.

    One of the Drop Down lists is to the Model name of the device.
    I want to be able to use the data from the Model name field to dynamically choose which table the data is added to.
    There are many reasons why I'm using a seperate table for each product.

    Process

    1 - Data Entry Form
    User enters the data into text fields and chooses other variables from drop lists presented in an ASP page.
    When SUBMIT is clicked the data is posted to the next page.

    2 - Data Process Page (invisible to the user)
    On this page I have the following line of code to open the table

    strSQL = "SELECT * FROM TABLE;"

    I'd like to be able to use a variable for the table name.
    The variable will come from the previous page in the form of a POST.
    (it will be chosen from a drop list).

    I imagine it should look similar to this:
    strSQL = "SELECT * FROM &TABLENAME&;"

    Any help greatly appreciated.
    Rob

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds like an icky design, but you're the boss!
    Code:
    strSQL = "SELECT * FROM " & Request.Form("TableName")
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2008
    Posts
    10
    GeorgeV - You the man

    Thanks so much.
    I've been stuck on this for a bit.

    Cheers
    Rob

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    rjaric

    what I do is

    strSQL = "SELECT * FROM [TABLENAME];"
    ^ just make it easyer to read

    strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))

    then it can come

    strSQL = "SELECT * FROM [TABLENAME] WHERE personid=[ID];"

    strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))
    strSQL = replace(strSQL,"[ID]",Request.Form("ID"))

    ...
    ...
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Nov 2008
    Posts
    10
    Thanks myle.

    I think your suggestion will give more flexibility to create dynamic Queries.

    Much appreciated.
    Rob

  6. #6
    Join Date
    Nov 2008
    Posts
    10
    Hi Myle

    I can get this to work:
    strSQL = "SELECT * FROM [TABLENAME];"
    strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))

    But this doesn't work:
    strSQL = "SELECT * FROM [TABLENAME] WHERE personid=[ID];"
    strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))
    strSQL = replace(strSQL,"[ID]",Request.Form("ID"))

    I get the Error:
    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
    /Display.asp, line 31
    Line 31 is:
    rsBBT.Open strSQL, adoCon

    I'm just using ASP, not ASP.NET.
    In what environment did this work for you?

    Thanks in advance
    Rob

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don't you Response.Write the strSQL variable to screen and see if you find a syntax error. If you struggle after this, post the results of the write here and we will take a look.
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Sorry wrong hat on


    msaccess needs the tablename after the select
    strSQL = "SELECT [TABLENAME].* FROM [TABLENAME]
    strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))

    Code:
    Sub Writeit(This,This1)
    'This will Display vlaue on a web site
    Response.Write "<b>" & This & "</b> = [" & This1 & "]<BR>"
    end sub
    I have this Sub in my main inc file
    then when I want to see something on screen all i need to do is

    call writeit("strSQL",strSQL)

    it out is

    strSQL=[SELECT [TABLENAME].* FROM [TABLENAME]]
    Last edited by myle; 11-26-08 at 18:28.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    Nov 2008
    Posts
    10
    Thanks GeorgeV and Myle,

    I did a response.write to the screen and was able to see the SQL query.
    Found that I needed to fine tune the first line a bit

    So this: strSQL = "SELECT * FROM [TABLENAME] WHERE personid=[ID];"
    Became:strSQL = "SELECT * FROM [TABLENAME] WHERE personid ='[ID]'"

    Replace lines remained the same:
    strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))
    strSQL = replace(strSQL,"[VALUE]", Request.Form("ID"))

    So it was just two missing single quotes.
    I did find that having ; at the end did nothing.

    Thanks again.
    Rob

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You only need to quote character fields - I would have guessed that ID was a number...

    And the semi-colon is good practice to include; it terminates the statement explicitly.
    George
    Home | Blog

Posting Permissions

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