where in clause parameter

[erbek]

hello,

I wonder if it's possible to correctly pass parameter to query:

select * from my_table where Field in (@parameter)

when i put '00,01' into parameter it does not return anything of course,
becouse query goes like:

select * from my_table where Field in ('00,01')

while correct query would be

select * from my_table where Field in ('00','01')

if it's not possible then maybe you can tell me how to dynamicly modify sql statement in tableadapter before filling()?

thank you
Rafal

 

[JohnH]

You would have to add another parameter to the command text, and more parameters to the command.

 

[erbek]

it would be difficult becouse in-list would contain selection from previous datagridview (beggining from 1 to 30 records)

 

[erbek]

I have already found an answer to my problem. While it's impossible to pass multivalue parameter we can use sql function (not mine):

ALTER FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO


and then in designer configure sql:

select * from my_table where Field in (select Data from split(@parameter,','))

 

[cjard]

it would be difficult becouse in-list would contain selection from previous datagridview (beggining from 1 to 30 records)

Would be better (more correct) to do:

SELECT * FROM table WHERE field = @id


Then in VB:

tableAdapter.ClearBeforeFill = false
datatable.Clear

For Each row as DataGridViewRow in myDataGridView.SelectedRows
  tableAdapter.Fill(datatable, row("id"))
Next

Don't do thousands of rows by either method. IN is a horrendous SQL operator and is best avoided