QueryBuilder and OLEDB

I'm using VS .NET 2003 ASP.NET project C#.

If I create a query like this in the query builder (using the
selectcommand of a oledbDataAdapter)

SELECT * FROM TABLE WHERE ID IN (?)

and pass the parameters in this way

myDataAdapter.SelectCommand.Parameters["paramName"].Value="'COD1','COD2'";

when I fill the dataset I obtain no rows.

If I change the commandtext in

SELECT * FROM TABLE WHERE ID IN ('COD1','COD2')

I obtain 10 rows when fill the dataset.

Can someone help me?

Thanks
Gianluca Miseria

William
Wed Sep 17 07:42:26 CDT 2003

The in statement is looking at those as two seperate values. The parameter
you are passing in is one value and the quote marks are being escaped by the
command object.

I've tried getting In to work before to no avail so I have (only needed to
do it once so I'm no expert) used an arraylist to dynamically generate a SQL
Statement and add parameters...but this was Ugly. So I created a Temp table,
parsed everythign out with the Commas and queried against it as the 'In'
values.

Not sure if either of these will help, and if there's a better way(there's
got to be b/c my solutions were ugly) I too would like to know. I'm going
to look around and will post back shortly.

Cheers,

Bill
"Gianluca Miseria" <gianluca.miseria@systeam.it> wrote in message
news:d26cbc8b.0309170257.669355f8@posting.google.com...
> I'm using VS .NET 2003 ASP.NET project C#.
>
> If I create a query like this in the query builder (using the
> selectcommand of a oledbDataAdapter)
>
> SELECT * FROM TABLE WHERE ID IN (?)
>
> and pass the parameters in this way
>
> myDataAdapter.SelectCommand.Parameters["paramName"].Value="'COD1','COD2'";
>
> when I fill the dataset I obtain no rows.
>
> If I change the commandtext in
>
> SELECT * FROM TABLE WHERE ID IN ('COD1','COD2')
>
> I obtain 10 rows when fill the dataset.
>
> Can someone help me?
>
> Thanks
> Gianluca Miseria

gianluca
Thu Sep 18 11:00:03 CDT 2003

The IN clause take one parameter in this form:

('value1',value2',....,'valueN') if the field is a char or varchar,

but I can't set this value with the Parameters method.

Simple doesn't work, return 0 rows.

I know there are workaround like dynamic building of the query ecc.
but I think that the query builder is a good tool and should work with
the IN clause.

Thanks
GM


"Patrick" <PatrickM@idinet.com> wrote in message news:<06ba01c37d26$9dbbddb0$a001280a@phx.gbl>...
> Gianluca, I think the problem is that you are assigning
> one parameter two values. Try using:
> myDataAdapter.SelectCommand.Parameters
> ["param1"].Value="'COD1'";
>
> myDataAdapter.SelectCommand.Parameters
> ["param2"].Value="'COD2'";
>
> assuming there are two input parameters in the parameter
> collection named param1 and param2.
>
> Hope this helps.
>
>
>
> >-----Original Message-----
> >I'm using VS .NET 2003 ASP.NET project C#.
> >
> >If I create a query like this in the query builder
> (using the
> >selectcommand of a oledbDataAdapter)
> >
> >SELECT * FROM TABLE WHERE ID IN (?)
> >
> >and pass the parameters in this way
> >
> >myDataAdapter.SelectCommand.Parameters
> ["paramName"].Value="'COD1','COD2'";
> >
> >when I fill the dataset I obtain no rows.
> >
> >If I change the commandtext in
> >
> >SELECT * FROM TABLE WHERE ID IN ('COD1','COD2')
> >
> >I obtain 10 rows when fill the dataset.
> >
> >Can someone help me?
> >
> >Thanks
> >Gianluca Miseria
> >.
> >