Can someone show me the simplest way of perform a parametrized SQL query using Classic ASP in VBscript?
A compilable example would be best.
- 1Are you referring to a parameterized SQL query?Jose Basilio– Jose Basilio2009-04-20 22:59:27 +00:00Commented Apr 20, 2009 at 22:59
- yes. going to update the question to clarify. thanks!Pablo Santa Cruz– Pablo Santa Cruz2009-04-20 23:06:05 +00:00Commented Apr 20, 2009 at 23:06
Add a comment |
3 Answers
Use the adodb.command object.
with createobject("adodb.command") .activeConnection = application("connectionstring") .commandText = "select * from sometable where id=?" set rs = .execute( ,array(123)) end with I would also advise to use a custom db access object instead of using adodb directly. This allows you to build a nicer api, improves testability and add hooks for debuging/logging/profiling. Secondly you can add request scoped transactions with implicit rollback's on errors using the class_terminiate event. Oure db access object offers the following query api
call db.execute("update some_table set column=? where id=?", array(value, id)) set rs = db.fetch_rs("select * from some_table where id=?", array(id)) count = db.fetch_scalar("select count(*) from some_table where column > ?", array(value)) 3 Comments
Matthew Lock
very elegant and minimal vbscript - I like it!
Bruno S.
This doesn't work if the column is numeric and the value is null. Is there a way for this to work without using ADODB.Command.CreateParameter?
Bruno S.
It will work if you call objCommand.Parameters.Refresh after setting the CommandText property. Empty elements in the array have to be replaced with Null.
I'm assuming you are referring to a parameterized SQL Query. If this is the case, then the VBScript code would look something like this:
Set adoCon = Server.CreateObject("ADODB.Connection") adoCon.Open "connectionstring" SET cmd = Server.CreateObject("ADODB.Command") cmd.ActiveConnection = adoCon cmd.CommandType= adCmdStoredProc cmd.CommandText = "GetCustomerByFirstName" cmd.Parameters.Append cmd.CreateParameter("@FirstName",adVarchar,adParamInput,50,"John") Set Rec = cmd.Execute() While NOT Rec.EOF 'code to iterate through the recordset Rec.MoveNext End While UPDATE: You need to include the ADOVBS.inc file for the constants to be recognized.
Here's a link: ADOVBS.inc
3 Comments
Pablo Santa Cruz
do I need to include some file to get ASP to recognize adVarchar and adParamInput constants?
Jose Basilio
Yes. Here's a link to that file asp101.com/articles/john/adovbs/adovbs.inc.txt
Thanigainathan
This suggesstion was very helpful in stored procedures scenario
Another option to including adovbs.inc is to add a reference to the following type library near the top of your ASP. Supposedly this has better performance than an include:
<!--METADATA TYPE="TypeLib" NAME="ADODB Type Library" UUID="00000205-0000-0010-8000-00AA006D2EA4" FILE="C:\Program Files\Common Files\System\ado\msado15.dll" VERSION="2.5" --> Here is a list of some type libraries.
