TableAdapter and IN Statements

[Terry Wahl]

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

 

[Miha Markic]

Sure, why not. However, correct statements would be
Select * from SomeTable where ID IN (1,2,3,4);

 

[Terry Wahl]

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

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,
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

 

[Patrice]

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

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

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,
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

 

[Terry Wahl]

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?

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

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

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,
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

 

[Miha Markic]

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?

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).

 

[William Vaughn]

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)
____________________________________________________________________________________________

 

[Cor Ligthert[MVP]]

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

 

[Terry Wahl]

Hi Cor,
I'm not sure I understand. Can you please elaborate?
Thanks,
Terry

 

[Cor Ligthert[MVP]]

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

 

[lery tapanes]

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 b
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.co
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, ModTim
FROM TcmCashRe
WHERE (ValueDate >= @StartDate) AND (ValueDate <= @EndDate) AND
(CashRecStateId in (@CashRecStateIds)


@CashRecStateIds = "1,2" <- string paramete

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..

-
Patric

"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.co
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

 

[lery tapanes]

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 b
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.co
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, ModTim
FROM TcmCashRe
WHERE (ValueDate >= @StartDate) AND (ValueDate <= @EndDate) AND
(CashRecStateId in (@CashRecStateIds)


@CashRecStateIds = "1,2" <- string paramete

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..

-
Patric

"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.co
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

TableAdapter and IN Statements
Hi, Terry
I have the same problem .Please let me know how did you solve it?

thank you
Lery

EggHeadCafe - Software Developer Portal of Choice
Treeview Explorer / Dialog for SmartPhone
http://www.eggheadcafe.com/tutorial...29-8f0336e05a0a/treeview-explorer--dialo.aspx

 

[William Vaughn \(MVP\)]

Yes, you can code an IN expression in the WHERE clause of the TableAdapter.
The problem is, you can't pass a parameter to the expression without
regenerating the code. There are work-arounds like creating a Table-value
function that takes a delimited string and returning a Table that can be
used as the IN clause expression.

--
__________________________________________________________________________
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)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

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