Dataset designer problem using sql IN clause

I was not exactly sure what to post for the code. I am using the dataset designer in visual studion 2005 for a vb .net project. I opened the datasets designer.vb code and found what I think is actually being executed. See code below highlighted in yellow is the sql statement that fails. Note that the application compiles and runs when I pass to the parameter a single integer value as a string it works fine when I pass in something like '23,96' it fails saying it can not convert to integer.

The sql is fine in that I have cut and pasted it and run it against the database directly with both 23 and 23,96 as parameters. Somewhere along the line the dataset code appears to be checking the parameter data type against the data type of the field used in the where clause. I good check for an = operator but provides a false negative for the in operator.

I tried to paste in the xml for the schema for the dataset in question but I kept getting an error when trying to post it. If that would help perhaps I can find a way to post it as well.

Thanks

Private Sub InitCommandCollection()

Me._commandCollection = New System.Data.OleDb.OleDbCommand(2) {}

Me._commandCollection(0) = New System.Data.OleDb.OleDbCommand

Me._commandCollection(0).Connection = Me.Connection

Me._commandCollection(0).CommandText = "SELECT spec_type_id, spec_parent_id, spec_type_name, sortorder, property_groups, "& _

"form_format_string, Check_security_flg, displaylist, OwningKey, traceOptions, tr"& _

"aceSQL, itemColumnName, tracePropertyGroup, description, help_url, expand_flg FR"& _

"OM SpecificationType"

BradyGuy at 2007-9-3 >

I stripped out some of the directives that prevent the debugger from stepping in to the code that is generated by the designer and found the line of code where the exception is raised.

Not sure if this will help or confuse matters. Thanks

Public Overridable Overloads Function GetDataByParentID(ByVal Param1 As String) As ADMDS.SpecificationTypeDataTable

Me.Adapter.SelectCommand = Me.CommandCollection(1)

If (Param1 Is Nothing) Then

Throw New System.ArgumentNullException("Param1")

Else

Me.Adapter.SelectCommand.Parameters(0).Value = CType(Param1, String)

End If

Dim dataTable As ADMDS.SpecificationTypeDataTable = New ADMDS.SpecificationTypeDataTable

Me.Adapter.Fill(dataTable)

Return dataTable

End Function

BradyGuy at 2007-9-3 >

Sorry, I just re-read your original post and realized you already knew that :)

This appears to be a limitation of the TableAdapter designer. My best suggestion is to create the Select statement as a stored procedure which takes the varchar parameter with the delimited list of values and inserts into a dynamic SQL string. Not very pretty, but I can't come up with anything else.

CommonGenius.com at 2007-9-3 >

I think I'll open up a ticket with microsoft and see if I can find anything out. I'll post something here if I do. Thanks for taking the time to read and respond.

I agree a stored procedure would work but I hate to go to all that trouble just to make an in clause work.

Brady

BradyGuy at 2007-9-3 >