parametising sql statements in ASP classic

trufla · May 29th, 2008, 11:24 AM

I was wondering if someone could spot where I have gone wrong in my code please, as I have gone blind looking for the error in this:

Code:

strName = "myname"

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Driver={SQL Server}; SERVER=localhost;UID=no_test;PWD=test;DATABASE=No_Test"

strSql = "SELECT * FROM test WHERE test = ?;"
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.CommandText = strSql

objCommand.Parameters.Append objCommand.CreateParameter ("name", adVarChar, adParamInput, 50, strName)
Set rs = server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.LockType = adLockReadOnly

rs.Open objCommand.Execute

Error I get is:

Code:

ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/test2.asp, line 21

Line 21 is this line:

Code:

objCommand.Parameters.Append objCommand.CreateParameter ("name", adVarChar, adParamInput, 50, strName)

trufla · May 29th, 2008, 11:30 AM

I have also tried adding this:

Code:

set objParameter2 = objCommand.CreateParameter( , adVarChar, adParamInput, 30, strName)
objCommand.Parameters.Append objParameter2
objParameter2.value = strName

in place of this:

Code:

objCommand.Parameters.Append objCommand.CreateParameter ("name", adVarChar, adParamInput, 50, strName)

Mych · June 16th, 2008, 09:20 AM

Your SQL Select is shown as

strSql = "SELECT * FROM test WHERE test = ?;"

I'm not sure if this is the source of your problem but should that not be...

strSql = "SELECT * FROM test WHERE test = '?';"

HTH

I have not failed... I've just found 10,000 way that don't work!

Old Pedant · June 16th, 2008, 02:46 PM

I think it is likely that the root of your problem is that you didn't #include the "adovbs.inc" file.

See here:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=160

You also have a problem on this line:

rs.Open objCommand.Execute

Won't ever work. You are trying to invoke two methods of doing the same thing on the same line. I'm pretty sure all you want here is

rs.Open objCommand

*******

Mych: No. Never. If you use '?' then you just turned the question mark into a simple literal string. That is, a real question mark. It *MUST* be by itself to be a parameter place holder.

Mych · June 18th, 2008, 04:05 AM

Pedant,

Sorry, have never across come the use of ? on its own...

What would the sql statement select?

The way I understand, it will select all records from the table test where the field test would equal the variable ?...

Confusion!:( Can you assign ? as variable? This why I though it need to be enclosed in single quotes.

What is objParameter?

Sorry for my ignorance.
Mych

I have not failed... I've just found 10,000 way that don't work!

Old Pedant · June 18th, 2008, 02:38 PM

We use ? in parameterized queries with Access the same way you would use @xxx in parameterized stored procedures with SQL Server.

Each ? represents one parameter that will have it's value filled from one ADODB.Parameter.

So if you did something like
SELECT * FROM table WHERE id = ? AND postdate > ? AND topic = ?
then you would need three ADODB.Parameter objects appended to the Parameters collection of the ADODB.Command object.

The data type of each parameter is determined by the type you assign to the created ADODB.Parameter object. If you mess up (e.g., if you use a text parameter for the id when id is a numeric field in the DB), you can and will get an error. You don't need to worry about '...' or #...# delimiters (for text and date, respectively) and you are *NOT* subject to SQL Injection attacks.

Do you need a full blown example??