Append parameter for SQL in() statement

[BillLumbergh]

We've got a VB6 application that hits a MS SQL Server 2000 DB. We use command object to append query parameters. What I cannot figure out is how to use the command object paremeter append method when dealing with a SQL in() function. How do you use parameter.append when referring to a SQL in() value as follows:

strSQL = "select * from my_table where add_user_id = ? and type_id in (?)"

Set cnn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set objRS = CreateObject("ADODB.Recordset")

With cmd
.CommandText = strSQL
.CommandType = adCmdText
.Parameters.Append .CreateParameter("add_user_id", adVarChar, adParamInput, 30, "BillLumbergh")
.Parameters.Append .CreateParameter("type_id", adInteger, adParamInput, , "1,2,3")

Set objRS = .Execute
End With

Can it even be done, or do you have to break the "in()" out and say "=? or =? or =?"?

Thanks.

 

[neilharris]

Is there any reason why you wish to parameterise a simple statement like that.

Surely it would be much easier to simply set up the complete strSQL in the first place. I.e.:

strSQL = "select * from my_table where add_user_id = 'BillLumbergh' and type_id in (1,2,3)"

Set cnn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set objRS = CreateObject("ADODB.Recordset")

With cmd
    .CommandText = strSQL
    .CommandType = adCmdText

    Set objRS = .Execute
End With

 

[chiph]

This is the big limitation of command parameters -- you cannot use them with an IN clause. You have to concatenate your SQL together, which leaves you open to a SQL injection attack, as well as problems with apostrophes, etc. :-(

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[BillLumbergh]

neilharris:

I am doing it for the reasons specified by chiph.

chiph:

That's what I was thinking, it cannot be done.

Thanks.

 

[fredericofonseca]

why not use a stored procedure instead?
Pass the "in list" as one parameter

e.g.
CREATE PROCEDURE sp_MyProcedure (@MyCommaDelimitedString Varchar(255))
AS
BEGIN
EXEC ('SELECT * FROM MYTABLE
WHERE MYFIELD IN (' + @MyCommaDelimitedString + ')')
END
GO

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com