select *...where x in (" & obj & ")" -- what collection obj can I

[Guest]

I need to build a sql string that looks like this:

strSql = "Select * from tbl1 Where x In (123,456,789)"
or
strSql = "Select * from tbl1 Where x In (123,456,789,527,914)"

The numbers represent RecordID's from rows I will select from a
datagridview. I tried storing these values in an arrayList, and even a
string array and tried this:

s1 = New ArrayList
For Each row As DataGridViewRow In SelectedRows
s1.Add(row.Cells("DetailID").Value.ToString)
Next
....
strSql = "Select * from tbl1 Where x In (" & s1 & ")

VS complained until I added s1.ToString, which of course, did not work.

strSql = "Select * from tbl1 Where x In (" & s1.ToString & ")

My alternative is to build a "Where" string

For i As Integer = 0 to s1.Count - 1
str1 += s1(i).ToString & ","
Next
strSql = "Select * from tbl1 Where x In (" & str1 & ")"

This just seems a bit kludgy. I am pretty sure I have dealt with this
before, I just can't remember what object I used.

Any suggestions appreciated.

Thanks,
Rich

 

[Matthias Vastring]

Like this?
strSql = "Select * from tbl1 Where x In (" & String.Join(",",
s1.ToArray(Type.GetType("System.String"))) & ")
Regards
Matthias

 

[Guest]

Well here is one other option I remembered that makes it so I can use a
string array

strSql = "Select * from tbl1 Where x In (" & String.Join(",", s1) & ")"

This doesn't seem any less kludgy than using a straight string object
though, because I have to resize the string array, I have to add a counter
var to the for each loop, and I have to use String.Join in the sql statement.
But -- it is another option.

 

[Guest]

Yes. Exactly. This is what I came up with also, so I guess this is the
consensus. And the reason I need some kind of collection object is because
in the rest of this particular process I have to loop through the object
after I build the sql string and retrieve rows. With the string array I can
use For Each Next.