Dynamtic In Statement

Question

I am using a checkbox list to selected orders for export and I am using the following to export the orders My Question is how do i place the chracher , within the in statement using the below code ?

  For Each obj As Object In CheckedListBox1.CheckedItems
        tempstring = Chr(39) & obj.ToString() & Chr(39)
        sb.AppendLine(String.Join(",", tempstring)
    Next

Function to return sql

   Friend Function GetDistinctOrdersForLocations(OrderNumber As String) As String Implements  
    iScriptBuilder.GetDistinctOrdersForLocations
    Dim retVal As String = "SELECT DISTINCT location, ordernumber FROM orders "
    retVal &= "where orders.ordernumber in (" & OrderNumber & ")"
    Return retVal
   End Function

This is producing this sql through the above function

SELECT DISTINCT location, ordernumber FROM orders where orders.ordernumber
 in ('Puals Test V1' 
 'Puals Test V2'   

) 

Answer 1

Of course it does. You create tempstring by quoting object.ToString() in single quotes. Then you add it to the StringBuilder without any change, because the String.Join doesn't do anything for just one string. String.Join joins a list of strings together using the separator, but you're not passing in a list, but just a single string - there's nothing to join here.

What you need to do is

For Each obj As Object In CheckedListBox1.CheckedItems
    tempstring = Chr(39) & obj.ToString() & Chr(39)
    If sb.Length > 0 THEN
       sb.Append(",")
    End If
    sb.Append(tempstring)
Next

This appends a comma to the StringBuilder in case there's some content already and then it appends the new string.

Answer 2

Using built in functionality:

retVal &= string.Join(
    ",", 
    CheckedListBox1.CheckedItems
        .Select(x => string.Format("'{0}'", x.ToString())));

Also watch out for SQL injection when doing such a construct. It might be safer to move this to a stored procedure or to pass a variable number of prepared statements.

Check this answer for a safer way of achieving your goal.