ExecuteScalar results in Specified cast is not valid.

[Steven]

I'm trying to setup a query that just needs to check whether an item exists in the database. So I tried:

using (FluentModel db = new FluentModel())
{
  string sql = "select count(AccountId) from Account where Account = @Account";

  OAParameter param = new OAParameter();
  param.ParameterName = "Account";
  param.DbType = System.Data.DbType.String;
  param.Value = AccountAlphaNum;

  int result = db.ExecuteScalar<int>(sql, param);

  if (result > 0)
  {
    exists = true;
  }
}

But this results in "System.InvalidCastException: Specified cast is not valid."

 Is there anything obvious that I'm missing here? Thanks!

[Simeon Simeonov]

Hello Steven,

Thank you for contacting us. From your question doesn't become clear what is the mapping of your database. For this reason I will assume that you have a table called "Account" in which you have a string column (varchar, nvarchar, etc.) also named "Account". If this assumption is correct then your query should work correctly. I have actually tested your case on my side with a database in which I had a table called "Categories" and in it I had a column called also "Categories" of type varchar(50). I had used your exact query and it worked correctly.

Because of the results from my test I come to the conclusion that their is something specific in your setup. For that reason could you provide us with more information about:
1/ What is the mapping of the "Account" table, what columns does it have and what are their types?
2/ What is the end goal of the query - trying to check if in the "Account" table their is already a record with a given string value in a certain column?
3/ Is there a specific reason for using a SQL query directly instead of allowing DataAccess to generate it for you?

I also wanted to provide you with an alternative code using the DataAccess Api to generate the required SQL:

bool exists = false;
using (var context = new EntitiesModel())
{
    var account = "Some value";

    bool isRecordAlreadyPresent = context.GetAll<Account>().Any(a => a.Account == account );
    if (isRecordAlreadyPresent)
        exists = true;
}

I hope you find this information helpful.

Regards,
Simeon Simeonov
Telerik