CODEWITHSUNDEEP
c#sqlvisual-studiotableadapterdata-access-layer
Dale Marshall
Dale Marshall

Reputation: 1135

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)

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.

Upvotes: 0

Views: 2313

Answers (3)

user168814
user168814

Reputation:

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.

Upvotes: 0

terjetyl
terjetyl

Reputation: 9565

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

Upvotes: 1

Nick Berardi
Nick Berardi

Reputation: 54854

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

Upvotes: 1

Related Questions