System.Data.SqlClient.SqlError: Incorrect syntax near 'dbo'
Even after so many years of expereince with ADO.Net and SQL, every now and then I run into problems which are very obvious
but sometime easy problems become hard to track. I was implementing a data access method and called ExecuteNonQuery method
on SqlCommand object and got the above exception thrown. I looked at the code and nothing seemed wrong. Then I started my regular
debugging steps and ran Sql profiler. When ExecuteNonQuery method was called, I looked at the trace and I saw something
unusual. I was trying to call a stored procedure [dbo].[AddContent]through SqlCommand object. I was expecting to
see the sored procedure being called. Instead I saw the following code being executed.
declare @p13 bigint
exec sp_executesql N'[dbo].[AddContent]',N'@AuthorId bigint,@CategoryId bigint...................
Looking at this in profiler gave hint that instead of executing my stored procedure, ADO.Net has
tried to execute command text. And then I looked at the implementation thats exactly the problem was.
I did not set CommandType property of SqlCommand to CommandType.StoredProcedure. After I set that
property the code worked. So if you are specifying a stored procedure as command text, make sure that
you have set CommandType property too.