quaiton89 / dapper-dot-net

Automatically exported from code.google.com/p/dapper-dot-net
Other
0 stars 0 forks source link

System.Data.SqlClient.SqlException Must declare the scalar variable "@parameter". #61

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago
Hey guys,

We're using Dapper in an application hosted on Windows Azure and are struggling 
to fix a problem that we've had periodically since deployment.

Occasionally we get the following error:

System.Data.SqlClient.SqlException
Must declare the scalar variable "@blogId".

Our code:

        public Post GetPost(Guid blogId, string slug)
        {
            var query = @"
                select p.Id, p.Title, p.Slug, p.Summary, p.MetaDescription, p.Content, p.ContentFormat, p.PublishDate, t.Id, t.Title, t.Slug 
                from Tags t 
                    inner join BlogPostTags pt on t.Id = pt.TagId
                        right outer join BlogPosts p on pt.PostId = p.Id
                            where p.BlogId = @blogId and p.Deleted = 0 and p.PublishDate <= @date and p.Slug = @slug and p.PublishStatus = 1";

            Post post = null;

            session.Connection.Query<Post, Tag, Post>(query, (p, t) =>
            {
                if (post == null)
                {
                    post = p;
                }

                return post;
            }, new { slug = slug, blogId = blogId, date = DateTime.UtcNow });

            return post;
        }

Our Mvc controller passes the "blogId" parameter in the same way to several 
other actions, all of which are fine. Currently we have to recycle the 
application to resolve.

Full stack trace is below. Any help would be much appreciated.

System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar 
variable "@blogId".
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Dapper.SqlMapper.<MultiMapImpl>d__29`6.MoveNext() in D:\MyApp\Dapper\SqlMapper.cs:line 495
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at MyApp.Web.Public.Application.Services.BlogService.GetPost(Guid blogId, String slug) in D:\MyApp\Application\Services\BlogService.cs:line 156
   at MyApp.Web.Public.Application.Services.CachingBlogService.GetPost(Guid blogId, String slug) in D:\MyApp\Application\Services\CachingBlogService.cs:line 46
   at MyApp.Web.Public.Controllers.PostsController.Details(String id) in D:\MyApp\Controllers\PostsController.cs:line 80
   at lambda_method(Closure , ControllerBase , Object[] )
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   ... ommitted for brevity

Original issue reported on code.google.com by b...@planetcloud.co.uk on 31 Aug 2011 at 11:49

GoogleCodeExporter commented 8 years ago
I am not following this code, why are you selecting tags if you have no 
intention of returning them? 

the code looks like it *should* work (in a somewhat odd way) can you confirm 
you are running latest from here

Original comment by sam.saff...@gmail.com on 5 Sep 2011 at 3:52

GoogleCodeExporter commented 8 years ago
Hi Sam,

Looks like I missed out a line of code here. Should have been

if (post == null)
{
    post = p;
}

post.AddTag(t)

This code does work *most* of the time. It just seems that occasionally I get 
the above error.

Original comment by b...@planetcloud.co.uk on 5 Sep 2011 at 7:58

GoogleCodeExporter commented 8 years ago
Hey guys, just wondered if you have any further thoughts on this as we continue 
to experience this issue periodically when using MultiMapping.

We've never been able to replicate it in development and there are no other 
errors on the server when it happens other than the above.

We load this parameter "postId" in the same way as we do on plenty of other 
queries (although these aren't using MultiMapping) and these continue to work 
fine when we have the above issue.

Any help would be greatly appreciated.

Original comment by b...@planetcloud.co.uk on 22 Sep 2011 at 5:41

GoogleCodeExporter commented 8 years ago
Can you confirm you are running the latest code. (from here) 

I really see nothing wrong with your sample and do not understand why it would 
not be extracting the blogid param 

Original comment by sam.saff...@gmail.com on 27 Sep 2011 at 12:18

GoogleCodeExporter commented 8 years ago
About latest version the nuget package is getting kind of "old". 

Personally I use the latest from source repository, but a more frequent nugget 
release schedule would be great as it’s an active project.

Also nuget is probably where many people discover/install dapper from, and they 
will give you feedback on "old" code.

Original comment by matt...@amigarulez.se on 28 Sep 2011 at 9:32