I am familiar with the SqlCommand object available in .NET (VBScript or C#)
which provides a powerful security feature by using a template in conjunction
with SqlParameters. I have been searching without success to determine
whether classic ASP with VBScript has something equivalent. Does it? Failing
that, are there any libraries available that would perform the same
functionality? I simply balk at attempting to use raw dynamic SQL commands
(e.g. "SELECT xyz from t1 where u=" & userName & . . .")

The challenge is that I am inheriting a rather old system, I believe:
VBScript engine is version 5.6
SERVER_SOFTWARE value is "Microsoft-IIS/4.0" (which one reference indicated
means "IIS4.0 with ASP 2.0")

Thanks for the useful information. I neglected to mention that I am using
SqlServer with this application. Do these techniques work with SqlServer as
well?

It took some digging through your posts and from there to some other material
before I was able to cobble a working example together.

I went from this:
==========================================================================
sqlS1 = "SELECT SiteName FROM Sites S WHERE IDSite=" & S1
set rsS1 = server.CreateObject("ADODB.Recordset")
rsS1.Open sqlS1, WebconnectString, adOpenStatic, ,adCmdText
Site = rsS1("SiteName")
==========================================================================

To this (looks good with a fixed width font:-):
==========================================================================
sqlS1 = "SELECT SiteName FROM Sites S WHERE IDSite=?"
arParams = array(S1)
set cmd = createobject("ADODB.Command")
cmd.CommandText = sqlS1
set cmd.ActiveConnection = myConn
set rsS1 = cmd.Execute(,arParams)
Site = rsS1("SiteName")
==========================================================================

....resulting in the same Site name in both cases.

Is my revised code above the proper way to do it? You actually did not have
any examples in the reference posts you provided that showed queries
returning data; they were all for INSERT statements.

I appreciate your working through this so readily with me, and for the added
example of setting a parameter. I do not quite follow your details about
cursors and not sure when or why I would want to use one, but I will wait
until the occasion arises before burdening you further. :-)

BTW, I was unable to find a good reference in MSDN for ADO (perhaps too
old?) but for your tidbit collection here is a good one from DevGuru:
http://www.devguru.com/technologies/ado/home.asp. The page on the
command.execute method, for example, provides all the juicy details:
http://www.devguru.com/technologies/ado/8529.asp

[quoted text, click to view]
With good reason.
Here you go:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

If you want type safety, which is not really needed in vbscript, you
will need to code the parameter objects by hand.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[quoted text, click to view]

Absolutely - the OLE DB provider for the database supplies the necessary
datatypes to make it work..

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[quoted text, click to view]
========================================================================
==
[quoted text, click to view]
========================================================================
==
[quoted text, click to view]
========================================================================
==
[quoted text, click to view]
========================================================================
==
[quoted text, click to view]

Yes, that's it. I would add the CommandType statement:

cmd.CommandType = 1 'adCmdText

99 times out of a hundred, this will suffice for asp. For the rare
occasions when you need a different cursor type from the default
firehose cursor (perhaps a clientside static cursor), you need to build
the Parameters collection and use the recordset's Open method to open
it:

sqlS1 = "SELECT SiteName FROM Sites S WHERE IDSite=?"
set cmd = createobject("ADODB.Command")
cmd.CommandText = sqlS1
cmd.CommandType = 1 'adCmdText
Const adInteger = 3
Const adParamInput = &H0001
Const adUseClient = 3
cmd.Parameters.Append cmd.CreateParameter("SiteID", _
adInteger, adParamInput,,S1)
Set rs=CreateObject("adodb.recordset")
rs.CursorLocation=adUseClient
rs.Open cmd




--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[quoted text, click to view]
========================================================================
==
[quoted text, click to view]
========================================================================
==
[quoted text, click to view]
========================================================================
==
[quoted text, click to view]
========================================================================
==
[quoted text, click to view]

PS. An alternative to using the Open method is to set the Connection's
cursorLocation to adUseClient, thus making a clientside static cursor
the default cursor built by the Execute method.

In the unlikely event that you need a serverside static, keyset or
dynamic cursor, you need to use the Open method as described in my
previous reply

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[quoted text, click to view]

A recordset is a cursor :-)

[quoted text, click to view]

http://msdn2.microsoft.com/en-us/library/ms807498.aspx
--
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"

I ran into one minor glitch. If I add in this line as you suggested...

cmd.CommandType=adCmdText

.... I receive this error:

ADODB.Command error '800a0bb9' Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.

If, however, I add the type onto the end of the Execute call, as in...

set rsS1 = cmd.Execute(,arParams, adCmdText)

....then it runs without complaint. So two questions:
(1) Are those approaches equivalent?
(2) Why would I see the error indicated?

The assignment to CommandType throws the error. That is consistent with the
documentation, which states that it will complain upon assignment if it is
incompatible. But it should *not* be incompatible here, right?!

I guess as long as it works when I put it in the Execute call, I can get
by... but I do dislike an anomaly that even stumps you, the expert. Sigh.

[quoted text, click to view]

Which line throws the error? This line? or the .Execute line?

[quoted text, click to view]

They should be.

[quoted text, click to view]

Frankly I'm at a loss. I've got pages and pages using this property
assignment with no error.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Aha! I was fooled into thinking the constant was defined because (a) it
seemed to work in the Execute call (whereas in reality it just did not
complain about its failure) and (b) it was used in several places elsewhere
in the same file (which I inherited:-( ).

If I wanted to include adovbs.inc, what is its path?

[quoted text, click to view]

Right
Is the constant defined properly? Try this:

on error resume next
cmd.CommandType = adCmdText
if err<>0 then
response.write err.description & "'<BR>"
response.write "adCmdText contains '" & adCmdText & "'<BR>"
end if
on error goto 0

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[quoted text, click to view]
It can be any place you want it to be. The MDAC installation puts it into
....\program files\common files\system\ado
so you could create a virtual directory in IIS pointing at that location.

A better way is to use the metadata tag in global.asa to reference the type
library:
http://www.aspfaq.com/show.asp?id=2112
--
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 to pester you further... Reviewing your example of explicitly defining
a Parameters collection, I am wondering if there is any additional security
afforded by that approach vs. just passing a generalized array to the Execute
method?

[quoted text, click to view]

Nope. They are processed as parameters and therefore not parsed as SQL,
so no sql injection is possible.

The only things gained with the explicit Parameters collection are:
1. Type safety -
a) you will get an "earlier" error if you attempt to set the Value
of a numeric parameter object to a non-numeric value
b) Using the variant array method forces ADO to "guess" at the
correct datatype to be used for the parameter. It's never happened to
me, and I've never seen a report anywhere of it happening, but I suppose
ADO could guess incorrectly leading to a hard-to-debug runtime error.
2. The ability to pass and retrieve output and return parameter values
to and from a stored procedure

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.