Why is Visual Studio's table adapter query not returning the same data as the stored procedure it represents?
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)
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.
Use Sql Profiler to see how the sql sent to sql server actually looks like. This has helped me many times.
Dates need to have quotes around them in SQL else they don't work.
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.