[2005] remove quotes from string in parametrized query

[phillipi]

How can I romove double-quotes from a string I'm passing into a parametized query? They cause an error...

inString = "101,102,110,114"
Try
                Me.ITMTableAdapter.FillByItemIDList(Me.ItemDBDataSet.ITM, inString)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

The WHERE clause in my table adapter fill by query has the following: WHERE ITM_ID IN (@ITM_ID_LST)

Any thoughts would be helpful.

Phillip

[jmcilhinney]

Your string doesn't contain any double quotes to remove. That's not the issue there. The issue is that you cannot use a single parameter to specify multiple values like that. You're going to either have to find a way to split the string you're supplying into individual values using SQL functions supported by your data source, or else you're just going to have to execute a query that returns data for one ID and call it multiple times. If you set the ClearBeforeFill property (or whatever its exact name is) to False then each call will not remove the existing data.

[phillipi]

Thanks.... I was hoping there was a function, setting, or something that would enable such a string to be passed ... but i guess not..

Now I'm stuck with having to deal with other work-arounds that will inevitably degrade the performace of the app.

[jmcilhinney]

If you ask in the Database Development forum some DB guru may be aware of the best way to do this. With my less-than-complete knowledge of SQL I would probably just create a query that took one ID and execute it multiple times. If you do that make sure you explicitly open the connection first and close it afterwards. If you don't do that it will be closed and reopened between each pair of calls, which will slow it down considerably. To access the Connection property of the TableAdapter you'll have to make it Public in the DataSet designer.

[phillipi]

I found an easy work-around and it works like a charm.

I merely add the filter string to the bindingsource.filter property, then call the table adapters fill method... works well.

Phillip

[jmcilhinney]

I found an easy work-around and it works like a charm.

I merely add the filter string to the bindingsource.filter property, then call the table adapters fill method... works well.

Phillip

Most would consider it undesirable to retrieve an entire table when only a few records were required. If the table doesn't contain may records then it's probably not such a big deal but it's not a good practice to get into.