Subsonic IN Query with Multiple Strings

subsonic

I'm trying to do a Subsonic Query with an IN statement containing multiple strings. If I manually hard-code the strings, it works fine. Example:

Dim qry As New [Select]("mySelectColumn")
qry.From(table.Schema)
qry.Where(table.Columns.mycolumn).In("string1", "string2", "string3")

Hoever, I need to be able to pull the IN statement strings from a single variable in VB, which would make the last line look like:

qry.Where(table.Columns.mycolumn).In(combinedString)

But whenever I try and concat the strings together into a single VB string, I get no results. I can't even tell exactly what SQL it's trying to pass. Using buildsqlstatement() just shows the IN statement with :mycolumn0In1, :mycolumn0In2, :mycolumn0In3…I can't tell what it's actually trying to do.

I've tried these variations for the VB variable to no avail:
mystring = """mystring1"", ""mystring2"""
mystring = "'mystring1', 'mystring2'"

Any ideas how I can concat multiple strings into one IN clause with VB and Subsonic?

Best Answer

A coworker pointed me to http://blog.levo.us/index.php/2008/06/25/subsonic-select-where-in-solution/ and I eventually found a solution. My problem was that I was not explicitly declaring the VB variable as an arraylist. I was simply Dim'ing it and assigning it the results of a function which were returned as an ArrayList.

what didn't work - the getstrings() function returns an ArrayList:

Dim myarraylist = getstrings()
...
qry.Where(table.Columns.mycolumn).In(myarraylist)

What did work:

Dim myarraylist as ArrayList = getstrings()
...
qry.Where(table.Columns.mycolumn).In(myarraylist)
Related Topic