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
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"
news:OopLqtKxEHA.2572@tk2msftngp13.phx.gbl...
> 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 wrote:
> 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
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.phx.gbl
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl
http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=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&thr eadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev= /groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3 Den%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"
thanks both
"Bob Barrows [MVP]"
news:uvcS1sNxEHA.2196@TK2MSFTNGP14.phx.gbl...
> Mich wrote:
> > 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
>
> 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.phx.gbl
>
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl
>
>
http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain
>
>
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=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&thr eadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev= /groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3 Den%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"
>
>
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]"
news:uvcS1sNxEHA.2196@TK2MSFTNGP14.phx.gbl...
> Mich wrote:
> > 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
>
> 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.phx.gbl
>
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl
>
>
http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain
>
>
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=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&thr eadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev= /groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3 Den%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"
>
>
"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:
> 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]"
> news:uvcS1sNxEHA.2196@TK2MSFTNGP14.phx.gbl...
>> Mich wrote:
>>> 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
>>
>> 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.phx.gbl
>>
>>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl
>>
>>
> http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain
>>
>>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=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&thr eadm=O31NZa%230DHA.3436%40tk2msftngp13.phx.gbl&rnum=11&prev= /groups%3Fq%3Ddelimiter%2Bauthor:Bob%2Bauthor:Barrows%26hl%3 Den%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"
--
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"
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?
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"
news:%23PPr3EQxEHA.2016@TK2MSFTNGP15.phx.gbl...
> 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?
>
>
>
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:
> 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"
> news:%23PPr3EQxEHA.2016@TK2MSFTNGP15.phx.gbl...
>> 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?
--
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"
dat = request.form("myhiddenfield")
sql = "SELECT " & dat & " FROM day"
"Mich"
news:OopLqtKxEHA.2572@tk2msftngp13.phx.gbl...
> 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
>
>
thanks
"Bob Barrows [MVP]"
news:umxGDnQxEHA.3168@TK2MSFTNGP15.phx.gbl...
> 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:
> > 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"
> > news:%23PPr3EQxEHA.2016@TK2MSFTNGP15.phx.gbl...
> >> 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?
>
> --
> 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"
>
>
> 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).
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