Missing parameter for data adapter select

Hi

I am using below in my dataadapter SELECT statement;

SELECT <field list>
FROM Clients
WHERE (Status = ?)
ORDER BY ID

What if I need all clients regardless of status, is there a way to modify
the above statement to include this situation or is a second query needed
for that?

Thanks

Regards

Cor
Thu Jul 03 22:00:30 CDT 2008

John,

I never tried this with an inline Sql procedure , but you can try

Where(status is null or status = ?)

And please let us know if this works. I assume you are using OleDB because
you use the ?

In a SQL stored procedure you simple declare a parameter as

@Status bool = null

and then

Where (@Status is null or Status = @Status)

Cor

"John" <info@nospam.infovis.co.uk> schreef in bericht
news:%2372UxGX3IHA.5060@TK2MSFTNGP02.phx.gbl...
> Hi
>
> I am using below in my dataadapter SELECT statement;
>
> SELECT <field list>
> FROM Clients
> WHERE (Status = ?)
> ORDER BY ID
>
> What if I need all clients regardless of status, is there a way to modify
> the above statement to include this situation or is a second query needed
> for that?
>
> Thanks
>
> Regards
>

John
Fri Jul 04 17:34:09 CDT 2008

Hi Cor

Doesn't work.

Thanks

Regards

"Cor Ligthert[MVP]" <notmyfirstname@planet.nl> wrote in message
news:62A90734-00F6-4BB1-B02F-409D5C980628@microsoft.com...
> John,
>
> I never tried this with an inline Sql procedure , but you can try
>
> Where(status is null or status = ?)
>
> And please let us know if this works. I assume you are using OleDB because
> you use the ?
>
> In a SQL stored procedure you simple declare a parameter as
>
> @Status bool = null
>
> and then
>
> Where (@Status is null or Status = @Status)
>
> Cor
>
> "John" <info@nospam.infovis.co.uk> schreef in bericht
> news:%2372UxGX3IHA.5060@TK2MSFTNGP02.phx.gbl...
>> Hi
>>
>> I am using below in my dataadapter SELECT statement;
>>
>> SELECT <field list>
>> FROM Clients
>> WHERE (Status = ?)
>> ORDER BY ID
>>
>> What if I need all clients regardless of status, is there a way to modify
>> the above statement to include this situation or is a second query needed
>> for that?
>>
>> Thanks
>>
>> Regards
>>
>

Jack
Sat Jul 05 00:42:48 CDT 2008

On Fri, 4 Jul 2008 02:02:57 +0100, "John" <info@nospam.infovis.co.uk>
wrote:

>Hi
>
>I am using below in my dataadapter SELECT statement;
>
>SELECT <field list>
>FROM Clients
>WHERE (Status = ?)
>ORDER BY ID
>
>What if I need all clients regardless of status, is there a way to modify
>the above statement to include this situation or is a second query needed
>for that?
>
>Thanks
>
>Regards
>

You could try:
SELECT ... FROM ... WHERE ? IS NULL OR Status = ? ORDER BY ...

and pass in NULL for the parameter to get all records. I assume you
are using OLEDB, and I don't know it if allows a parameter to be
tested like that.

John
Sat Jul 05 12:50:18 CDT 2008

Does not work as both ? become two separate parameters.

Thanks

Regards

"Jack Jackson" <jjackson@cinnovations.net> wrote in message
news:382u64599ju6vavsugq5id9s932oncdtn2@4ax.com...
> On Fri, 4 Jul 2008 02:02:57 +0100, "John" <info@nospam.infovis.co.uk>
> wrote:
>
>>Hi
>>
>>I am using below in my dataadapter SELECT statement;
>>
>>SELECT <field list>
>>FROM Clients
>>WHERE (Status = ?)
>>ORDER BY ID
>>
>>What if I need all clients regardless of status, is there a way to modify
>>the above statement to include this situation or is a second query needed
>>for that?
>>
>>Thanks
>>
>>Regards
>>
>
> You could try:
> SELECT ... FROM ... WHERE ? IS NULL OR Status = ? ORDER BY ...
>
> and pass in NULL for the parameter to get all records. I assume you
> are using OLEDB, and I don't know it if allows a parameter to be
> tested like that.

Jack
Sun Jul 06 01:58:44 CDT 2008

Why would that matter? Supply the same value for both parameters.

On Sat, 5 Jul 2008 18:50:18 +0100, "John" <info@nospam.infovis.co.uk>
wrote:

>Does not work as both ? become two separate parameters.
>
>Thanks
>
>Regards
>
>"Jack Jackson" <jjackson@cinnovations.net> wrote in message
>news:382u64599ju6vavsugq5id9s932oncdtn2@4ax.com...
>> On Fri, 4 Jul 2008 02:02:57 +0100, "John" <info@nospam.infovis.co.uk>
>> wrote:
>>
>>>Hi
>>>
>>>I am using below in my dataadapter SELECT statement;
>>>
>>>SELECT <field list>
>>>FROM Clients
>>>WHERE (Status = ?)
>>>ORDER BY ID
>>>
>>>What if I need all clients regardless of status, is there a way to modify
>>>the above statement to include this situation or is a second query needed
>>>for that?
>>>
>>>Thanks
>>>
>>>Regards
>>>
>>
>> You could try:
>> SELECT ... FROM ... WHERE ? IS NULL OR Status = ? ORDER BY ...
>>
>> and pass in NULL for the parameter to get all records. I assume you
>> are using OLEDB, and I don't know it if allows a parameter to be
>> tested like that.
>