locked
Help: Specified cast is not valid. RRS feed

  • Question

  • User74717037 posted

    Hello all,

    I have (what I think is) a very straightforward query that throws the "Specified cast is not valid." error message and I cannot figure out why.  The code is:

    int iTransactionID = 0;
    using (SqlConnection SqlConn = new SqlConnection(<<my connection string>>) {
        try {
            SqlConn.Open();
            using (SqlCommand SqlCmd2 = new SqlCommand("SELECT TOP 1 TransactionID FROM tblSales ORDER BY TransactionID DESC; ", SqlConn)) {
                iTransactionID = (int)SqlCmd2.ExecuteScalar();
            }
            SqlConn.Close();
        }
        catch (Exception ex) {
            iTransactionID = -1;
        }
    }        

    This is a simplified version of an SQL Insert that is to return an identity value.  The "Specified cast is not valid." error is thrown for that code, so I tried a simplified version just to figure out what the error is being thrown (the code above) yet it still does not work.

    Any help is greatly appreciated.

    Thanks!

    Tuesday, May 18, 2021 11:20 PM

Answers

  • User74717037 posted

    I just tried the following at it worked:

                            object obj1 = (object)SqlCmd.ExecuteScalar();
                            iTransactionID = Convert.ToInt32(obj1);

    I know I have used (int) in the past and it has worked on columns that are bigint.  But this will work.

    Thanks to all of you who have responded.  I appreciate it!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 19, 2021 2:07 AM

All replies

  • User303363814 posted

    What is the type of TransactionID?

    Are there any rows in the table?

    Split the line with the cast into two lines.  One which does the ExecuteScalar and one which does the cast.  Put a breakpoint on the Cast line and see what the result of the ExecuteScalar was

    Wednesday, May 19, 2021 12:40 AM
  • User74717037 posted

    Hi Paul,

    Thanks for replying, yes there are rows in the table (the query works in SSMS), TransactionID is a bigint.

    Wednesday, May 19, 2021 1:24 AM
  • User-158764254 posted

    TransactionID is a bigint.

    then i believe you'd want iTransactionID to be an int64 or a long rather than an int

    Wednesday, May 19, 2021 1:38 AM
  • User74717037 posted

    Hi Paul, I am using C# so there isn't an int64 datatype, or at least I am not aware of it.

    Wednesday, May 19, 2021 2:05 AM
  • User74717037 posted

    I just tried the following at it worked:

                            object obj1 = (object)SqlCmd.ExecuteScalar();
                            iTransactionID = Convert.ToInt32(obj1);

    I know I have used (int) in the past and it has worked on columns that are bigint.  But this will work.

    Thanks to all of you who have responded.  I appreciate it!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 19, 2021 2:07 AM