I hope there is a quick answer to this question :)

I have a SQLDataSource I'm using to pull all of the information from a table housing our aging AR information.  I'm trying to build a search feature to narrow this information down since there are over 5000 accounts in aging AR.  The select statement works fine and the gridview looks great up until I try adding some some selectparameters (this is new to me, I normally just build a where clause dynamically and add it back to the select string, but parameters looks like it would be much cleaner if I can get it to work like the owners would like).  The problem I'm having is that if on the "Aging AR Report" you click generate without putting a value into the "CustNo" field I get back 0 rows of data.  I put AccNo "3" into the text box and click generate I get back 1 row for the CustNo requested.  What I would like to do is be able to leave all fields blank that reference a parameter and if the field is blank it is excluded.  So in short if all my textboxes that are associated with ControlParameters = Nothing Then I get back ~5000 rows, not 0, the AR Dept can then use the sort method of the gridview to put accounts with higher outstanding balances to the top of the list.

Rough Code Example:

Dim strSQL As String = "Select * From ARAging Where AccNo = @AccNo Order By AccNo"

try this

Dim strSQL As String = "Select * From ARAging Where AccNo = ISNULL(@AccNo,AccNo) Order By AccNo"

if(TBCustNo.Text <> string.empty)

{

SQLARSummary.SelectParameters.Add(New Parameter("AccNo", Data.DbType.Decimal, TBCustNo.Text))

}

else

{

SQLARSummary.SelectParameters.Add(New Parameter("AccNo", Data.DbType.Decimal, DBNull.Value))

}

Sorry this is a "no-go"

vemulapalli26:

SQLARSummary.SelectParameters.Add(New Parameter("AccNo", Data.DbType.Decimal, DBNull.Value))

The DBNull.value throws an error that this number of arguments not excepted.  If I manually replace the @AccNo in the query with "Null" it works fine so in theory this would work, but the parameter does not seem to properly pass a NULL value.  I tried setting the ConvertEmptyStringToNull property equal to "True" this didn't work either.  If I can just get the parameter to actually pass a "Null" into the SelectCommand that I think this would be the ticket and would be much easier and cleaner then trying to write on the fly Where statements.  I'll continue to dig around and see if I can find the missing piece of the puzzle.

Thanks!

Ok I got this to work, but I still can't seem to get it to work when dealing with Null or empty values: 

Where (AccNo = (Case When @AcctNo = 'NoValue' Then AccNo Else @AcctNo End))

If CustNo = "" Then

CustNo = "NoValue"

End If

try

Where (AccNo = (Case When ISNULL(@AcctNo,'NoValue') = 'NoValue' Then AccNo Else @AcctNo End))

Still a no-go grr.  I can't seem to get a null value to pass with the parameter the "IsNull" SQL function is checking as FALSE on that parameter no matter what I try...DBNull.Value.Equals(Parameter) ..... Parameter.ConvertEmptyStringToNull = True .... Nothing seems to work.  If I convert the empty string into "NoValue" then it works but not if I try to pass a Null.  It's working now so I'm just going to run with it like it is, it's still cleaner then trying to build a my where clause on the fly ... it just aggrevates me to no end that I can't seem to get a Null value to pass.  Everything I'm reading says this should work but I can't figure it out.

Here's what I'm finally running with maybe someone can see what I'm over looking:

PacctNo.ConvertEmptyStringToNull = True

What is the type of the parameter? May be you're passing an empty string instead?

E.g. test with

@AcctNo IS NULL or LEN(LTRIM(@AcctNo)) = 0

Yes it is passing an empty string.  I have already proven that.  What I was trying to figure out is why it would not pass a null value.

According to what I was reading on MSDN the attribute "ConvertEmptyStringToNull" for the parameter should force it to push a Null value to the query, "how-some-ever-comma" it keeps pushing an empty string, which SQL does interpret the same way of course 'empty string' <> Null = True

This is why I can't simply use the IsNull function in the query and have to work with strings.  As a test I made my routine convert '' to 'NoValue' just to as a test, but in short yes it passes empty string, EmptyStringToNull = Broken since I can't seem to actually pass a Null Value to the query with the parameter.  Also DBNull.Value.Equals(ParamaterValue) does not pass the value as null.  It's a minor inconvenience since programatically everything is working fine, I just don't understand why the Null value is not being passed.

Thanks!!!

Did you try to add parameter as text and only then set its value to DbNull.Value in the code from the second message?

Well I have played and played with this and I cannot get a null value check to work in the query using the value from the parameter.  I don't really think this is "Resolved" but I'm going to to mark it as such since I have a functional work around, and I'm sure we would all like to move on.  Thank you both for your time and input I've given you both credit for helping with the solution.

Thanks again!

 OK, The next code works fine.

When you want to specify a value for parameter:

dsSQL.SelectParameters.Add(New Parameter("Parametro", Data.DbType.Boolean, True))

And.... when you want to specify the null value... guess what... just remove parameter:

dsSQL.SelectParameters.Remove(dsSQL.SelectParameters.Item("Parametro"))