CODEWITHSUNDEEP
c#sql-server-expressdata-conversiontinyint
B. Clay Shannon-B. Crow Raven
B. Clay Shannon-B. Crow Raven

Reputation: 10227

How can I cast a TinyInt value from a SQL Server Table?

I have this SQL Server (Express) table:

enter image description here

...with a GenreId of TinyInt (there will only be a few dozen distinct Genres, at most).

This C# code is failing, with "Specified cast is not valid."

int genreId = 0;
. . .
genreId = GetGenreIdForGenre(_genre);

The value of "_genre" at the point of failure is "Adventure". The call to GetGenreIdForGenre() should return "1":

enter image description here

This is the line that fails, in GetGenreIdForGenre():

return (int)command.ExecuteScalar();

In context, the GetGenreIdForGenre() method is:

private int GetGenreIdForGenre(string genre)
{
    try
    {
        string qry = "SELECT GenreId FROM dbo.GENRES WHERE Genre = @genre";
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            using (SqlCommand command = new SqlCommand(qry, connection))
            {
                command.Parameters.AddWithValue("@genre", genre);
                connection.Open();
                return (int)command.ExecuteScalar();
            }
        }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
         return 0;
     }
 }

There is no (TinyInt) conversion available. Int32 also failed. What do I need to do to retrieve the TinyInt value?

Upvotes: 2

Views: 943

Answers (2)

Athanasios Kataras
Athanasios Kataras

Reputation: 26342

Cast it on the query:

string qry = "SELECT CAST(GenreId as int) FROM dbo.GENRES WHERE Genre = @genre";

That way you won't need to worry about client side conversions.

Upvotes: 2

madreflection
madreflection

Reputation: 4937

command.ExecuteScalar's return type is object so the value it's returning a boxed byte. Before you can cast it to int, you have to unbox the byte:

return (byte)reader.ExecuteScalar();

After the unboxing cast to byte, it will use the available implicit conversion to int (to match the method's return type) so you don't need another cast.

Upvotes: 3

Related Questions