Replacing/Adding single quotes for values in WHERE clause

[xcaliber2222]

Hello,

I have a strSQL statement where I'm taking in arguments that are building the 'IN' part of my WHERE clause. For example:

       strSQL = "SELECT " & _
                "invoice.invoice_num, " & _
                "invoice.invoice_date, " & _
                "pickup.zip_code, " & _
                "delivery.company_name, " & _
                "job.weight, " & _
                "job.dim_weight, " & _
                "WHERE " & _
                " invoice.invoice_date = '" & dttmStart & "' AND " & _
                " invoice.cust_code IN '" & strCustCodeList & "'"

The strCustCodeList is taking in values from the database table as such: 123454,123456,123457

Can someone please show me how to form this string so it will come in as such: ('123454','123456','123457') with the single quotes? Unfortunately, these are varchar values in the database and dong a cast or convert on these is not an option either.

Any help would be greatly appreciated.

Thank you,
Alejandro

 

[jmeckley]

do not use injected sql, use parameterized queries.
1. they prevent sql injection
2. they can execute faster
3. they negate the problems you are having.

as for your scenario you need to loop through the values and add parameters.

var sql = @"select [columns] from [tables] where date = @date and id in (";
var ids = get_ids_from_database();
for(var i = 0; i < ids.Count; i++)
{
   if(i > 0) sql+=",";
   sql += "@p"+i;
}
sql+=")";

var command = connection.CreateCommand();
command.CommandText = sql;
command.Parameters.Add("@date", DateTime.Now);
for(var i = 0; i < ids.Count; i++)
{
   command.Parameters.Add("@p"+1, ids[i]);
}
command.ExecuteReader();
...

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[xcaliber2222]

Thanks Jason. Actually, I'm limited to what I can do because it is a fairly large established project. My solution was this:

" invoice.cust_code IN ('" & Replace(strCustCodeList, ",", "','") & "')"

Thanks for the great advice and sample though.

Alejandro