Why is Visual Studio's table adapter query not returning the same data as the stored procedure it represents?

Question

I'm using a table adapter in Visual Studio to make a query to a stored procedure in my SQL Server 2005 database. When I make the call via my website application it returns nothing. When I make the same call via SQL Server Manager it returns the expected data.

I put a breakpoint on the call to the adapter's getData method and looked at all the parameters and their values and matched them in a query from server management to make sure. I'm sending the following query:

getData(string, date, date, int, int?, int?, string, int?, string)

further

getData('0000-rtg', '1/1/2007', '3/12/2008', 0, null, null, null, null, null)

I guess I'm wondering if Visual Studio does something with the null's before it tries to send the query to the SQL server. If not, how do I fix this problem?

EDIT: All these values are passed by variables, I just typed what was in those variables at that break point.

Answer 1

Dates need to have quotes around them in SQL else they don't work.

Answer 2

Use Sql Profiler to see how the sql sent to sql server actually looks like. This has helped me many times.

Answer 3

Visual Studio can be funny with query parameters. Make sure each variable has the correct length and type. For example, I use several date parameters in a query. Everytime I edit the query, Visual Studio automatically detects the date parameters and limits the variable to a length of 7. I pass the date in as "9/12/2009", which gets cut off to "9/12/20", so I need to manually change my date paramters to a length of 10.