Empty Parameter

Hello,
I have sql command like: "Select... Where myColumn = @myParameter"

Now, I can simply add parameter to the collection and filter data.

What if I want to return not filtered data, I mean to skip, avoid
executing this "myColumn = @myParameter"?!

Is this possible, or I have to change select command every time I don't
want filtered data?

Marina
Thu Mar 30 07:25:52 CST 2006

You would have to change your query.

You could also play tricks like always the where clause be like:

myColumn = @myParam Or 1 = @fakeParam

When you want to filter, you set @fakeParam to 2. When you don't, you set
it to 1, and all rows will be returned.

<milan.letic@gmail.com> wrote in message
news:1143721460.110382.150080@t31g2000cwb.googlegroups.com...
> Hello,
> I have sql command like: "Select... Where myColumn = @myParameter"
>
> Now, I can simply add parameter to the collection and filter data.
>
> What if I want to return not filtered data, I mean to skip, avoid
> executing this "myColumn = @myParameter"?!
>
> Is this possible, or I have to change select command every time I don't
> want filtered data?
>

Marina
Thu Mar 30 07:28:46 CST 2006

Actually you could also have your where clause be this:

myCol = @myParam OR @myParam IS NULL

That should do it.

<milan.letic@gmail.com> wrote in message
news:1143721460.110382.150080@t31g2000cwb.googlegroups.com...
> Hello,
> I have sql command like: "Select... Where myColumn = @myParameter"
>
> Now, I can simply add parameter to the collection and filter data.
>
> What if I want to return not filtered data, I mean to skip, avoid
> executing this "myColumn = @myParameter"?!
>
> Is this possible, or I have to change select command every time I don't
> want filtered data?
>

milan
Thu Mar 30 09:05:24 CST 2006

Thanks for answering. But...

This is my query...

SELECT
... tblStudent.BudzetSamofinansiranje, ISNULL(tblUplate.Iznos, 0) AS
Uplate, tblUplate.Datum...
FROM
tblStudent LEFT OUTER JOIN tblUplate ON tblStudent.JMB = tblUplate.JMB
WHERE
(tblUplate.Datum > @datumOd) AND (tblUplate.Datum < @datumDo) AND
(tblStudent.BudzetSamofinansiranje = @BudzetSamofinansiranje OR
@BudzetSamofinansiranje IS NULL)

As you can see, I added, as you suggested, "or @BudzetSamofinansiranje
IS NULL" and when I run query WITHOUT giving value to
@BudzetSamofinansiranje it returns nothing.

When I run query with this set to "Budzet", since this is one of
values, it return Error: "Application uses a value of the wrong type
for the current operation."

Marina
Thu Mar 30 09:07:40 CST 2006

You should give it a value of DBNull.Value (although I would think that is
the default for a parameter).

You did not show your code where you are actually setting up the parameters
and their values.

<milan.letic@gmail.com> wrote in message
news:1143731124.026003.101400@e56g2000cwe.googlegroups.com...
> Thanks for answering. But...
>
> This is my query...
>
> SELECT
> ... tblStudent.BudzetSamofinansiranje, ISNULL(tblUplate.Iznos, 0) AS
> Uplate, tblUplate.Datum...
> FROM
> tblStudent LEFT OUTER JOIN tblUplate ON tblStudent.JMB = tblUplate.JMB
> WHERE
> (tblUplate.Datum > @datumOd) AND (tblUplate.Datum < @datumDo) AND
> (tblStudent.BudzetSamofinansiranje = @BudzetSamofinansiranje OR
> @BudzetSamofinansiranje IS NULL)
>
> As you can see, I added, as you suggested, "or @BudzetSamofinansiranje
> IS NULL" and when I run query WITHOUT giving value to
> @BudzetSamofinansiranje it returns nothing.
>
> When I run query with this set to "Budzet", since this is one of
> values, it return Error: "Application uses a value of the wrong type
> for the current operation."
>

milan
Thu Mar 30 09:23:28 CST 2006

You mean to set "...OR @BudzetSamofinansiranje = DBNull"? This reports
error.

This is my code for working in VB.NET with this parameter:

Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@BudzetSamofinansiranje",
System.Data.SqlDbType.VarChar, 50, "BudzetSamofinansiranje"))

...and later in code I asign a value to it...

SqlSelectCommand1.Parameters("@BudzetSamofinansiranje").Value =
"Budzet"

Please help.

Marina
Thu Mar 30 09:45:55 CST 2006

No, that is not what I meant.

I meant: SqlSelectCommand1.Parameters("@BudzetSamofinansiranje").Value =
DBNull.Value

Also, I'm not sure why you are creating the SqlParameter using the overload
that takes the source column as the last parameter. I think you should take
it out.

Additionally, your query could be not returning the correct results because
of some of the other clauses in the WHERE clause. You should test things in
the most simple way possible and eliminate all other factors. First get the
hang of using the parameters and getting the various results under different
conditions, then add in all the complexity once you have that working.

<milan.letic@gmail.com> wrote in message
news:1143732208.518880.137080@g10g2000cwb.googlegroups.com...
> You mean to set "...OR @BudzetSamofinansiranje = DBNull"? This reports
> error.
>
> This is my code for working in VB.NET with this parameter:
>
> Me.SqlSelectCommand1.Parameters.Add(New
> System.Data.SqlClient.SqlParameter("@BudzetSamofinansiranje",
> System.Data.SqlDbType.VarChar, 50, "BudzetSamofinansiranje"))
>
> ...and later in code I asign a value to it...
>
> SqlSelectCommand1.Parameters("@BudzetSamofinansiranje").Value =
> "Budzet"
>
> Please help.
>

milan
Thu Mar 30 10:13:48 CST 2006

Well this will return all rows having no values. But I want query to
return ALL rows if @BudzetSamofinansiranje is not set to some value OR
only those that pass @BudzetSamofinansiranje = something.

Marina
Thu Mar 30 10:25:02 CST 2006

Ok, I hadn't considered that this column can contain NULLs, sorry.

In which case, refer to my first post. You would either have to change your
query, or you could do something like the fake parameter suggestion.
Although my suggestion would be to just change the query.

<milan.letic@gmail.com> wrote in message
news:1143735228.481982.115280@u72g2000cwu.googlegroups.com...
> Well this will return all rows having no values. But I want query to
> return ALL rows if @BudzetSamofinansiranje is not set to some value OR
> only those that pass @BudzetSamofinansiranje = something.
>

milan
Thu Mar 30 11:00:16 CST 2006

OK,
I'm really grateful. I'll be changing my query.

Thanks.