|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Empty ParameterHello,
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. <milan.le***@gmail.com> wrote in message Show quote 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? > Actually you could also have your where clause be this:
myCol = @myParam OR @myParam IS NULL That should do it. <milan.le***@gmail.com> wrote in message Show quote 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? > 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. <milan.le***@gmail.com> wrote in message Show quote 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." > 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. <milan.le***@gmail.com> wrote in message Show quote 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. > 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. <milan.le***@gmail.com> wrote in message Show quote 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. > |
|||||||||||||||||||||||
Other interesting topics