Hi, Terry
I have the same problem .Please let me know how did you solve it?
thank you
Lery
TerryWah wrote:
TableAdapter and IN Statements
04-Feb-08
Hi,
I'm trying to configure a TableAdapter with an SQL statement fo the form:
Select * from SomeTable where ID IN "1,2,3,4";
Can I do this with a tableadapter?
Thanks in advance for your help!
Terry
Previous Posts In This Thread:
TableAdapter and IN Statements
Hi,
I'm trying to configure a TableAdapter with an SQL statement fo the form:
Select * from SomeTable where ID IN "1,2,3,4";
Can I do this with a tableadapter?
Thanks in advance for your help!
Terry
Sure, why not.
Sure, why not. However, correct statements would be
Select * from SomeTable where ID IN (1,2,3,4);
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development
www.rthand.com
Blog:
http://cs.rthand.com/blogs/blog_with_righthand/
Hi Miha,Thanks for the reply.
Hi Miha,
Thanks for the reply.
My SQL is:
SELECT CashRecId, Rev, ValueDate, InstitutionId, CashRecStateId,
ModUser, ModTime
FROM TcmCashRec
WHERE (ValueDate >= @StartDate) AND (ValueDate <= @EndDate) AND
(CashRecStateId in (@CashRecStateIds))
@CashRecStateIds = "1,2" <- string parameter
I've changed the DbType to string for the parameter @CashRecStateIds by
using the designer, selecting the query, viewing it's properties,
selecting
the Parameters property, choosing the column and changing DbType from int
to
String.
When I run the application I receive the following error:
Type : System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089 Message : Conversion
failed
when converting the nvarchar value '1, 2' to data type int.
Do you see what I am doing wrong?
Thanks again,
Terry
:
It won't work this way. Following IN you have a parameters *list*.
It won't work this way. Following IN you have a parameters *list*. Here
you
are using a *single* parameter that contains "1,2".
Depeding on how you create the SQL statement you could create a parameter
for each value and set the value for each of those parameters to get
something like :
.... In (@p1,@p2) etc...
--
Patrice
"Terry Wahl" <
[email protected]> a ?crit dans le message
de news: (e-mail address removed)...
Hi Patrice,I understand what you are saying but because the number of
Hi Patrice,
I understand what you are saying but because the number of parameters in
the
IN statement is user driven the number of parameters is dynamic. I was
hoping I could pass a list of values for the IN statement. Can't this be
done?
:
Re: TableAdapter and IN Statements
That's one of the forever lasting annoyances databases - there is no easy
way to pass an array. Either you dynamically create sql statament and add
necessary parameter values (as Patrice suggested) or you use another
tecnique which involves string parsing on server side (there was a recent
discussion on the topic in this newsgroup with a link to a website that
lists various techniques).
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development
www.rthand.com
Blog:
http://cs.rthand.com/blogs/blog_with_righthand/
I agree with Miha so I wrote my own routines to do it--both in TSQL and
using
I agree with Miha so I wrote my own routines to do it--both in TSQL and
using a CLR SP. Basically they create Table-valued functions that accept a
delimited string and return a Table. This way you can do something like
this:
SELECT....
FROM...
WHERE x IN (SELECT y FROM myTVF(@MyDelimitedListParms))
Other approaches include building strings that are compiled on the fly...
but there are a number of arguments against that approach.
Incidentally, while the CLR approach took only a small fraction of the
code
as the TSQL it ran slower.
hth
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker???s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Miha Markic" <miha at rthand com> wrote in message
Terry,Keep the resultSet and the DataTable appart.
Terry,
Keep the resultSet and the DataTable appart.
The resultSet is something for TSQL, the DataTable for AdoNet.
Every resultset will result in a datatable a DBDataAdapter.Fill is used.
Cor
Hi Cor,I'm not sure I understand. Can you please elaborate?
Hi Cor,
I am not sure I understand. Can you please elaborate?
Thanks,
Terry
:
Terry,As long as you can use a SQL Select statement in the query analyzer
Terry,
As long as you can use a SQL Select statement in the query analyzer which
create a resultset, then you can use a table adapter.
Cor
EggHeadCafe - Software Developer Portal of Choice
ASP.NET RSS/Atom FeedReader with just one line of code
http://www.eggheadcafe.com/tutorial...7e9-810fc9b21a3c/aspnet-rssatom-feedread.aspx
