By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,217 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,217 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Apr 25 '07 #1
Share this Question
Share on Google+
3 Replies


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

"Rich" <Ri**@discussions.microsoft.comschrieb im Newsbeitrag
news:B4**********************************@microsof t.com...
>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

Apr 25 '07 #2

P: n/a
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.
"Rich" wrote:
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
Apr 25 '07 #3

P: n/a
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.

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

"Rich" <Ri**@discussions.microsoft.comschrieb im Newsbeitrag
news:B4**********************************@microsof t.com...
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

Apr 25 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.