How to use IN () syntax in asp.net datasets (xsd) | Newbie | | Join Date: Sep 2009
Posts: 3
| | |
Hi,
I use Visual studio datasets to create table adapters etc. for queries. This normally works fine for me with 'simple' queries (i.e. only 1 parameter of type string, int ...). However if I specify the following query in the dataset:
SELECT * from tblTable WHERE tblId IN (@idList)
the query editor accepts, but the generated code will create a function that just accepts a single integer. Changing the type of idList to string and passing in a string in the form of "1,2,3" also doesn't work.
Is it at all possible to use the IN keyword in queries using the datasets, and if so, what is the way to do this?
Thanks very much in advance!
Arthur
|  | Expert | | Join Date: Jul 2008 Location: Utrecht, The Netherlands
Posts: 283
| | | re: How to use IN () syntax in asp.net datasets (xsd)
Hi Arthur,
I'm not quite sure what you're trying to accomplish with your query, but it sounds to me like you can use a subquery. Can you explain what format your table has and what output do you expect?
Steven
| | Newbie | | Join Date: Sep 2009
Posts: 3
| | | re: How to use IN () syntax in asp.net datasets (xsd)
Hi Steven,
I like to retrieve records of which the ID is one of the IDs i supply..
so
SELECT * FROM tblTable WHERE id IN (1,2,3,4,5)
would be the thing I'm after...
However if I try this with
SELECT * FROM tblTable WHERE id IN (@idList)
I don't get it to work ...
Thanks!
Arthur
|  | Member | | Join Date: Aug 2009 Location: Bengaluru, India
Posts: 124
| | | re: How to use IN () syntax in asp.net datasets (xsd)
check these methods -
DataView.RowFilter
DataTable.Select
| | Newbie | | Join Date: Sep 2009
Posts: 3
| | | re: How to use IN () syntax in asp.net datasets (xsd)
Thanks ssnaik84!
However I cant help but thinking that these functions do a 'non' MSSQL operation on the dataset. Ie aren't these functions potentially slow on large result sets?
| | Member | | Join Date: Aug 2009 Location: Bengaluru, India
Posts: 124
| | | re: How to use IN () syntax in asp.net datasets (xsd)
ok.. you need MSSQL function then.. - CREATE FUNCTION [dbo].[GetTableFromCSV] ( @StringInput VARCHAR(100) )
-
RETURNS @OutputTable TABLE ( [id] int identity(1,1), [String] NVARCHAR(4000) )
-
AS
-
BEGIN
-
-
DECLARE @String VARCHAR(10)
-
-
WHILE LEN(@StringInput) > 0
-
BEGIN
-
SET @String = LEFT(@StringInput,
-
ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
-
LEN(@StringInput)))
-
SET @StringInput = SUBSTRING(@StringInput,
-
ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
-
LEN(@StringInput)) + 1, LEN(@StringInput))
-
-
INSERT INTO @OutputTable ( [String] )
-
VALUES ( @String )
-
END
-
-
RETURN
-
END
to run it.. -
select * from dbo.GetTableFromCSV('1,2,3')
| | Member | | Join Date: Aug 2009 Location: Bengaluru, India
Posts: 124
| | | re: How to use IN () syntax in asp.net datasets (xsd)
Quote:
Originally Posted by arthurvanderwal 
SELECT * from tblTable WHERE tblId IN (@idList)
-
SELECT * from tblTable WHERE tblId IN (
-
select id from dbo.GetTableFromCSV('1,2,3')
-
)
-
|  |
Similar .NET Framework bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|
