ExecuteScalar results in Specified cast is not valid.

2 posts, 0 answers
  1. Steven
    Steven avatar
    3 posts
    Member since:
    Feb 2015

    Posted 24 Jul 2015 Link to this post

    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!

  2. Simeon Simeonov
    Admin
    Simeon Simeonov avatar
    24 posts

    Posted 29 Jul 2015 Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
Back to Top