Question Can't get SQL parameter to work here

[Robert_SF]

SQL CE 3.5 -- This query returns a list of account numbers plus columns for current and late amounts, a simple A/R aging.

SELECT accountnum, 
  SUM(CASE WHEN DATEDIFF(d, GETDATE(), billeddate) < 31 THEN amount ELSE 0 END) AS curbal, 
  SUM(CASE WHEN DATEDIFF(d, GETDATE(), billeddate) >= 31 THEN amount ELSE 0 END) AS latebal
FROM billingtrans
GROUP BY accountnum

The query works, but the aging is done based on GETDATE(), and I need to change that to a user-supplied value, so the aging can be done a few days before or a few days after. So I replaced GETDATE() with @agingdate. Now, if I use the SQL window in the database explorer or the SQL builder in the dataset designer, I get the prompt to enter the parameter, and then I get the error message: "The parameter is incorrect." I'm entering the date correctly, so what can be the problem?

If this is a CE 3.5 limitation, can you suggest a workaround?

Thanks!

 

[jmcilhinney]

You say that you're entering it correctly but, if everyone who thought they were doing the right thing actually was, this forum would have a lot fewer posts. What EXACTLY are you entering? Also, if you run it from VB code, does it work then?

 

[Robert_SF]

Yes, you're right. I finally got it to run in VB code. The SQL code was fine, but the Visual Studio dataset designer can't handle it. In addition to the behavior I described above, the designer ignores the parameter when saving the dataset. Consequently, the table adapter's fill method doesn't expect the argument.

So to get it to work in code, I manually created the parameter. In the dataset designer, you can access the Parameters (Collection) property for any fill method. Besides setting the parameter name, I only had to set the DbType properly. I'll have to remember that if I modify the dataset with the designer again. Now that I remember, it was the same issue with optional parameters. The data designer would reset all the AllowDBNulls to False.

I suppose I should start weaning myself off the designer and table adapters.