Empty Parameter

  • Thread starter Thread starter milan.letic
  • Start date Start date
M

milan.letic

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?
 
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.
 
Actually you could also have your where clause be this:

myCol = @myParam OR @myParam IS NULL

That should do it.
 
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."
 
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.
 
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.
 
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.
 
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.
 
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.
 
Back
Top