Professional Web Applications Themes

how to use a variable into a select? - ASP Database

Hi, I receive from a hidden field a date (in weekday format) ('dat'). I have a table "day" which has field "monday", tuesday" etc .. <% dat=request.form("myhiddenfield") 'dat contains any weekday (monday or tuesday ..). .... sql="select 'dat' from day" .... In the select statement, i need the right field (must match the variable 'dat'). How can i do this? I tried on several ways without succes. Thanks Mich...

  1. #1

    Default how to use a variable into a select?

    Hi,
    I receive from a hidden field a date (in weekday format) ('dat').
    I have a table "day" which has field "monday", tuesday" etc ..

    <%
    dat=request.form("myhiddenfield")
    'dat contains any weekday (monday or tuesday ..).
    ....
    sql="select 'dat' from day"
    ....

    In the select statement, i need the right field (must match the variable
    'dat').
    How can i do this? I tried on several ways without succes.
    Thanks
    Mich


    Mich Guest

  2. #2

    Default Re: how to use a variable into a select?

    Assuming I know what you mean here and that you're trying to get the value
    of your variable into your query, simply remember that all you're doing is
    building a string, that will then be used as a sql query statement. As far
    as the string building part, it's different from doing something like:

    dat = Request.Form("myhiddenfield")
    sOutput = "The value of dat is " & dat
    Response.Write sOutput

    So, similarly

    dat = Request.Form("myhiddenfield")
    sql = "SELECT '" & dat & "' FROM [Day]"

    Ray at home


    "Mich" <michnomail> wrote in message
    news:phx.gbl... 


    Ray Guest

  3. #3

    Default Re: how to use a variable into a select?

    Mich wrote: 

    You could use dynamic sql as Ray suggests, but that leaves you open to
    hackers using SQL Injection. Better is to use parameters with a stored
    procedure (SQL Server) or saved parameter query (Access).

    See these links:
    Access:
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.ph x.gbl

    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP1 1.phx.gbl

    http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gb l&oe=UTF-8&output=gplain

    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl


    SQL Server:
    http://tinyurl.com/jyy0


    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gb l&rnum=11&prev=/groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barro ws%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D10%26sa%3DN

    Bob Barrows


    PS. Never ask a database-related question wtihout telling us what database
    type and version you are using. It Is ALWAYS relevant.


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  4. #4

    Default Re: how to use a variable into a select?

    thanks both

    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    >
    > You could use dynamic sql as Ray suggests, but that leaves you open to
    > hackers using SQL Injection. Better is to use parameters with a stored
    > procedure (SQL Server) or saved parameter query (Access).
    >
    > See these links:
    > Access:
    >[/ref]
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.ph x.gbl 
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP1 1.phx.gbl 
    http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gb l&oe=UTF-8&output=gplain 
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl 
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gb l&rnum=11&prev=/groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barro ws%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D10%26sa%3DN 


    Mich Guest

  5. #5

    Default Re: how to use a variable into a select?

    Sorry, I see now that it's not exactly what i need.

    The table "day" contains 6 fields: 5 fields ("monday", "tuesday"
    ...."friday") and 1 field ("hour"). The field "hour" conatins the opening
    hours of the day ("8.00-9.00", "9.00-10.00" etc till "17.00-18.00").
    The table contains 10 records.
    Each field ("monday", "tuesday" ...) contains for each hour "Yes" or "NO"
    (open or not).

    In a previous ASP page, the user must choose a weekday, which is passed to
    this page with a hidden field (dat=request.form("myhiddenfield").
    What i want is to select in the query the right field and not any parameter.
    I need the openinghours of the weekday contained in 'dat'.

    Thanks again

    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    >
    > You could use dynamic sql as Ray suggests, but that leaves you open to
    > hackers using SQL Injection. Better is to use parameters with a stored
    > procedure (SQL Server) or saved parameter query (Access).
    >
    > See these links:
    > Access:
    >[/ref]
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.ph x.gbl 
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP1 1.phx.gbl 
    http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gb l&oe=UTF-8&output=gplain 
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl 
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gb l&rnum=11&prev=/groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barro ws%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D10%26sa%3DN 


    Mich Guest

  6. #6

    Default Re: how to use a variable into a select?

    "day" is a reserved keyword and should not be used for table/column names.
    http://www.aspfaq.com/show.asp?id=2080

    YOU STILL HAVEN'T TOLD US WHAT TYPE AND VERSION OF DATABASE YOU ARE USING

    Something like this will work (this will work in Access, not in SQL Server -
    you would use CASE instead of iif in SQL Server):

    SELECT iif([pdat] = 'monday',[monday],iif([pdat] =
    'tuesday',[tuesday],iif([pdat] = 'wednesday',[wednesday],iif([pdat] =
    'thursday',[thursday],[friday])))) FROM [day]

    Bob Barrows

    Mich wrote: 
    >>
    >> You could use dynamic sql as Ray suggests, but that leaves you open
    >> to
    >> hackers using SQL Injection. Better is to use parameters with a
    >> stored
    >> procedure (SQL Server) or saved parameter query (Access).
    >>
    >> See these links:
    >> Access:
    >>[/ref]
    > http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.ph x.gbl 
    > http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP1 1.phx.gbl 
    > http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gb l&oe=UTF-8&output=gplain 
    > http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl 
    > http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gb l&rnum=11&prev=/groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barro ws%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26start%3D10%26sa%3DN [/ref]

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  7. #7

    Default Re: how to use a variable into a select?

    The table is not "day" but "dagu" so no problem.
    It's Access XP.
    Finally, I tried this:

    sql="SELECT iif([dat] = 'monday';[monday];iif([dat] = 'tuesday';[tuesday]; "
    _
    & "iif([dat] = 'wednesday';[wednesday];iif([dat] =
    'thursday';[thursday];[friday])))) FROM daguur;"

    but i get "syntax error in query expression". (

    error '80040e14')
    Maybe missing "" or is the use of [ ] not allowed here or other little
    details?



    Mich Guest

  8. #8

    Default Re: how to use a variable into a select?

    First, thanks for your time.
    Sorry again, i found the syntax error: ';' instead of ',' inside the IIF().

    So the code is now:
    sql="SELECT iif([dat] = 'monday',[monday];iif([dat] = 'tuesday',[tuesday], "
    _
    & "iif([dat] = 'wednesday',[wednesday],iif([dat] =
    'thursday',[thursday],[friday])))) FROM udag;"

    But the error is now:
    Microsoft JET Database Engine error '80040e10'
    No value given for one or more required parameters.

    ??


    "Mich" <michnomail> wrote in message
    news:%phx.gbl... 



    Mich Guest

  9. #9

    Default Re: how to use a variable into a select?

    Open your database in Access. Click into the Queries tab. Create a new query
    in Design View. Close the Choose Table dialog without choosing a table.
    Switch to SQL View (toolbar button, or right-click menu, or View menu) Copy
    and paste only the sql from below into the SQL Veiw window. It should say:

    SELECT iif([dat] = 'monday',[monday];iif([dat] =
    'tuesday',[tuesday],iif([dat] = 'wednesday',[wednesday],iif([dat] =
    'thursday',[thursday],[friday])))) FROM udag


    Test your query by clicking the Execute toolbar button. Note that you will
    be prompted to supply a value for dat. Enter tuesday and press Enter. Verify
    that the query works as desired. Switch back to SQL View (try not to switch
    to Design View). Save the query, calling it "qGetHours" (no quotes). Close
    Access. Back to ASP. Use this code to call the saved query, passing the
    value in dat to the query as a parameter:

    set rs=createobject("adodb.recordset")
    conn.qGetHours dat,rs

    Your recordset is now open.


    Bob Barrows

    Mich wrote: [/ref]

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  10. #10

    Default Re: how to use a variable into a select?

    dat = request.form("myhiddenfield")
    sql = "SELECT " & dat & " FROM day"


    "Mich" <michnomail> wrote in message
    news:phx.gbl... 


    RunneR Guest

  11. #11

    Default Re: how to use a variable into a select?

    thanks
    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    query 
    Copy 
    Verify 
    switch [/ref]
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >[/ref]


    Mich Guest

  12. #12

    Default Re: how to use a variable into a select?

    > The table "day" contains 6 fields: 5 fields ("monday", "tuesday" 

    Pardon me for saying so, but this is terrible design, IMHO. If you decide
    to open on Saturdays, now you have to add a column to the table? Ugh.
    Monday, Tuesday etc. are data, not data descriptors. That data should be in
    a single column, not indicated in multiple columns.

    A


    Aaron Guest

Similar Threads

  1. Using a variable as a SELECT field
    By tjIII in forum Coldfusion Database Access
    Replies: 3
    Last Post: October 29th, 10:30 PM
  2. How to SELECT DatePart as a new Variable
    By Gahiggidy in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 30th, 06:25 PM
  3. variable select statement
    By Karzy in forum PHP Development
    Replies: 2
    Last Post: October 4th, 01:49 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139