Using SQL "IN()" in parameterized query

[MustangPriMe]

I'm trying to parameterize a SQL command with SQL IN() as part of the WHERE clause:

        Dim sqlCommD As New OleDbCommand("DELETE * FROM tblAdminUsersToPermissions WHERE (fldUserID=@UserId) AND (fldPermissionID NOT IN (@PermissionList));", sqlConn)
        sqlCommD.Parameters.Add(New OleDbParameter("@UserId", intUserId))
        sqlCommD.Parameters.Add(New OleDbParameter("@PermissionList", strSelectedIds))
        sqlCommD.ExecuteNonQuery()

Where strSelectedIds is a comma separated ID string e.g. "3,5,9" (number of Ids will vary)
It's picking up the @UserId parameter correctly, but not the @PermissionList parameter.

Can anyone see what I'm doing wrong? Is there a special requirement for using parameters in IN() clauses, as the parameter "value" is technically a string, and the IN() clause needs a list of integers?

Thanks in advance
Paul

 

[jmeckley]

Can anyone see what I'm doing wrong? Is there a special requirement for using parameters in IN() clauses, as the parameter "value" is technically a string, and the IN() clause needs a list of integers?

sql commands cannot automatically convert "1,3,9" to in(1,3,9). Since "1,3,9" should be an array of numbers, you should pass the values as such. a parameter is a a single scalar value, so you need to dynamically create parameters before executing the sql.

int[] ints = new int[] {1,3,9};
string[] parameters = new string[ints.Length];
for(int i=0;i<ints.Length;i++)
{
   parameters[i] = "@p"+i;
   cmd.Parameters.AddWithValue(parameters[i], ints[i]);
}
cmd.CommandText = "delete from table where id in (" + string.Join(",", parameters) + ")"
cmd.ExecuteNonQuery();

which will produce the sql

delete from table where id in(@p0, @p1, @p2)
--@p0 = 1
--@p1 = 3
--@p2 = 9

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[MustangPriMe]

Got it. That makes sense, thanks.

I'm trying to get into the habit of using parameterized queries because of SQL injection.
Previously, I'd have just used

... WHERE fldPermissionID NOT IN (" & strSelectedIds & ") ...

- nice and simple.

This requirement seems like a quite bit of extra work for each query, considering I think I might have quite a few, but I guess it's necessary to do it right. You're solution actually looks quite elegant.

I'm assuming that is C#? I think I can see what you're doing so should be easy to replicate in VB.

Thanks again
Paul

 

[jbenson001]

You should be doing this is a stored procedure instead of the code-behind.

 

[jmeckley]

yes it's c#.

This requirement seems like a quite bit of extra work for each query, considering I think I might have quite a few, but I guess it's necessary to do it right. You're solution actually looks quite elegant.

if you find yourself repeating the code is many locations consider creating a sql command builder object to dynamically build the command and add parameters. Or let an ORM (object relational mapper)do this for you. I would recommend ActiveRecord and NHibernate. I fell in love with NH this year and haven't looked back. These 2 are OSS frameoworks and the community is very helpful. LLBL Pro is easy to use and has great support. (small cost. definately worth it if you enter the arena of ORM and domain models.) Wilson ORMapper is another ORM tool. I haven't used this though.

Jason Meckley
Programmer
Specialty Bakers, Inc.