Problem passing string param to query with IN statement

[Bobby Edward]

Using latest version of asp.net and mysql. Using XSD datasets and dataset
designer in Visual Studio.

Created a tableadapter query with some params in the WHERE clause...

SELECT projects.ProjectId, projects.LocationCountyId,
counties.StateIdFROM counties INNER JOIN projects ON
counties.CountyId = projects.LocationCountyIdWHERE
(projects.LocationCountyId IN countyids)) OR
(counties.StateId IN stateids))
The idea is that I would like to send 1 or MORE countyids or stateids to the
query. For example:

WHERE (projects.LocationCountyId IN ('33','41','44','55')) OR
(counties.StateId IN ('10','41','32'))


So, I defined countyids and stateids as string params. If I hard code some
test strings in the query it works fine, but if I preview the query in the
XSD designer or use code to try to retrieve values it doesn't work.

Any ideas here? Am I doing it correctly?

 

[imransyed63]

Write a function in sql to split the string into a table and return the table, use that function in your IN statement,
following will help you,

CREATE FUNCTION fn_Split(@str varchar(2000), @sDelim varchar(2))
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(2000))
AS
BEGIN
Declare @idx smallint
Declare @Value varchar(8000)
SET @idx = 0
While len(@str) <> 0
BEGIN
Select @Value = SUBSTRING(@str,0,CHARINDEX(@sDelim,@str))
BEGIN
INSERT @retArray (idx, value) VALUES (@idx, @Value)
END
set @str = SUBSTRING(@str,len(@Value)+2,8000)
SET @idx = @idx + 1
END
RETURN
END

In your sql statement where you are using
projects.LocationCountyId IN countyids)
use this,
projects.LocationCountyId IN (select value from fn_splitcountyids,'''')