OrmLite - Must declare the scalar variable error with GetScalar Method

[KenB]

I am encountering an error with OrmLite when trying to use the GetScalar method with a distinct count.

The error message I receive is:  Must declare the scalar variable "@customerId".

Any help would be appreciated!

Here is the code:

 public int GetNumberOfShippedOrders(Guid customerAccountId)
        {
            const string sql = @"SELECT COUNT(OrderID) AS ShippedOrderCount
                                FROM
                                    (SELECT DISTINCT 
		                                a.OrderID		
                                    FROM 
		                                [Order] a (nolock) 
                                        INNER JOIN dbo.OrderDetail b (nolock)
                                            ON a.OrderID = b.OrderID
	                                        AND b.QtyShipped > 0
                                    WHERE 
                                        a.CustomerAccountId = @customerId
                                ) AS T";
 
            
 
            return _dbFactory.Exec(dbCmd => dbCmd.GetScalar<int>(sql, new { customerId = customerAccountId }));
        }

[KenB]

Looks like I need to use the QueryScalar method instead of GetScalar and now it works! 

[Demis Bellot]

yep thats it :)

--
- Demis

http://twitter.com/demisbellot

http://www.servicestack.net/mythz_blog

[Jason Kurian]

Hi, 

I'm trying something similar, but with a Stored Procedure:

            return
                _dbFactory.Run(
                    command =>
                        command.QueryScalar<double>(
                            "EXEC [dbo].[GetPatrolAndServiceHoursForAgencies2] @StartDate, @EndDate, @HoursType",
                            new { StartDate = startDate, EndDate = endDate, HoursType = type }));

This is the [JSON response] error I'm seeing:

{

• responseStatus: 

  {

  • errorCode: "NullReferenceException",
  • message: "Object reference not set to an instance of an object.",
  • errors: [ ]

  }

}

Please let me know what I'm doing wrong or if I can provide more information. Thanks!

[Jason Kurian]

Just saw this in the OrmLite docs overview:

Query statements take in parameterized SQL using properties from the supplied anonymous type (if any)

var track3 = dbCmd.Query<Track>("select * from Track Where AlbumName = album and TrackNo = trackNo", 
    new { album = "Throwing Copper", trackNo = 3 })

So I changed my statement accordingly:

                            "EXEC [dbo].[GetPatrolAndServiceHoursForAgencies2] @StartDate = StartDate, @EndDate = EndDate, @HoursType = HoursType",

                            new { StartDate = startDate, EndDate = endDate, HoursType = type }));

but I'm still getting the NullReferenceException. I even tried creating a POCO so that the parameters wouldn't be in an anonymous type:

public class HoursRequest {
  public string StartDate { get; set; }
  public string EndDate { get; set; }
  public string HoursType { get; set; }
}
        public double GetHoursFromType(string startDate, string endDate, string type)
        {

            return
                _dbFactory.Run(
                    command =>
                        command.QueryScalar<double>(

                            "EXEC [dbo].[GetPatrolAndServiceHoursForAgencies2] @StartDate = StartDate, @EndDate = EndDate, @HoursType = HoursType",
                            new HoursRequest { StartDate = startDate, EndDate = endDate, HoursType = type }));
        }

and got the same error.

[Jason Kurian]

I'm also wondering why I can't just use this for my query string:

_dbFactory.Run(command => command.GetScalar<double>(string.Format("EXEC [stored_procedure_name] @Param1 = {0}, @Param2 = {1} ...", param1, param2)));

since that's much simpler and such a command works in SSMS.