Variable usage in custom SQL statement

  • Thread starter Thread starter paul
  • Start date Start date
P

paul

As I understand it, variables can be introduced to an ASP
page in several ways, including:
--- posted to a page from a form
--- variables in the URL
--- set in the code in the page through something like:
<% Dim name
name = "housing" %>

When using these variables in a custom SQL statement in
the Database results properties, I know that the syntax
for using the variable is different. For example:
--- for posted, use ::name::
--- when in the URL, use ('%::ctgy::%')
--- but what is the right syntax when the variable is
created in the page code as shown in the third example
above? Thanks for any suggestions.
 
Actually you can basically use the same approach (I hand code everything):

<%
Dim ID
ID = request.form("ID")
or
ID = request.querystring("ID")
or
ID = Session("ID")
%>

For string field type:

Where AcctNo = '" & ID & "'

Where AcctNo = '" & request.form("ID") & "'

Where AcctNo = '" & session("ID") & "'

For autonumber field type (in these case I place this as the last part of
the query):

Where AcctNo = " & ID

Where AcctNo = " & request.form("ID")

Where AcctNo = " & session("ID")

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================
 
Hi Paul,
you can't - at least not directly. There's a little hack that may be of use
to you - stick the variable into the querystring and redirect -
<%
str = "something"
if request.querystring("str") = "" then
response.redirect request.servervariables("script_name") & "?str=" & str
end if
%>
Then pull the var into the drw with
select * from table where somefield = '::str::'

But truth be told I'm not too up on the drw - maybe Stephen Travis is out
there with a better answer?
 
Hi Jon: Thanks for your suggestion. I've tried to
modify your sample code, but I still can't make it work.
When I view the page in a browser, I get the error "Data
type mismatch in criteria expression."

Here's what I'm using to try and catch the lack of a
variable in the URL. Do I need to replace "script_name"
with something else?

<%
category = "Silicon"
if request.querystring("category") = "" then
response.redirect request.servervariables
("script_name") & "?category=" & category
end if
%>

Here is what I am using in the DRW:

select * FROM Articles WHERE ('::category::' = 1)

Thanks for any further suggestions you can offer!
-----Original Message-----
Hi Paul,
you can't - at least not directly. There's a little hack that may be of use
to you - stick the variable into the querystring and redirect -
<%
str = "something"
if request.querystring("str") = "" then
response.redirect request.servervariables
Click to expand...
("script_name") & "?str=" & str
 
Thomas: Thank you for your suggestion. I've tried to
modify your sample code, but I am still having problems.
When I view the page in a browser, I get the error "Data
type mismatch in criteria expression."

This is how I am trying to set the variable:

<%
Dim category
category = Session("Silicon")
%>

Here's what I am using in the database results properties:

select * FROM Articles WHERE (('" & session("category")
& "') = 1)

Do you see what I am doing wrong? Thanks for your
assistance.
 
You must create the session value somewhere else before using it on this
page.

If you were hand coding your ASP, the following would work:

<%
category = "Silicon"
%>

select * FROM Articles WHERE Category = '" & category & "' "

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================
 
Hi Paul,
lose the quotes
select * FROM Articles WHERE (::category:: = 1)
everything else as you have it
 
Back
Top