Variable usage in custom SQL statement

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.
 
T

Thomas A. Rowe

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.
==============================================
 
J

jon spivey

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?
 
P

paul

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
 
P

paul

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.
 
T

Thomas A. Rowe

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.
==============================================
 
J

jon spivey

Hi Paul,
lose the quotes
select * FROM Articles WHERE :):category:: = 1)
everything else as you have it
 
Top