Specified cast is not valid

[Socarsky]

I face with a matter that says "Specified cast is not valid." and then started to dig it to figured out. So, I found the reason but one thing made me curious to learn why does MSSQL Sum(nQty) with int32 datatype by itself? is this normal?
I fount the matter with this code!

for (int i = 0; i < reader.FieldCount; i++) 
 {
      MessageBox.Show(reader.GetName(i) + ":" + reader.GetFieldType(i).FullName);
 }

and thrown occurs here:

getBalanceQty = reader.GetInt16(reader.GetOrdinal("QTY"));

getBalanceQty is a type of int16

using (SqlConnection conn = new SqlConnection(strConn)) 
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                try 
                {
                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader()) 
                    {
                        //reader.Read();
                        while (reader.Read()) 
                        {
                            if (!reader.IsDBNull(reader.GetOrdinal("QTY"))) 
                            {
                                getBalanceQty = reader.GetInt16(reader.GetOrdinal("QTY"));
                            }
                            else 
                            {
                                getBalanceQty = 0;
                            }
                        }
                    }
                } 
                catch (Exception ex) 
                {
                    MessageBox.Show(ex.Message);
                }
            }
            return getBalanceQty;
        }

 

[jmcilhinney]

Your nQty column in SQL Serer is type `int`. That's a 32-bitr integer. If you sum that column then you're going to get an `int`, which is a 32-bit integer. If you retrieve that value into a C# app then it is still a 32-bit integer, so it will be type Int32. The GetInt16 method of your data reader is for getting an Int16, as the name suggests. Your value is an Int32, not an Int16, so GetInt16 can't get it. Internally, that method casts the value as type Int16. It is not that type so the cast is not valid. If the value is type Int32 then use the GetInt32 method to get it. It's that simple.

 

[Socarsky]

Your nQty column in SQL Serer is type `int`. That's a 32-bitr integer. If you sum that column then you're going to get an `int`, which is a 32-bit integer. If you retrieve that value into a C# app then it is still a 32-bit integer, so it will be type Int32. The GetInt16 method of your data reader is for getting an Int16, as the name suggests. Your value is an Int32, not an Int16, so GetInt16 can't get it. Internally, that method casts the value as type Int16. It is not that type so the cast is not valid. If the value is type Int32 then use the GetInt32 method to get it. It's that simple.

Thanks jmcilhinney, I open my eyes
I handled that below

getBalanceQty = Convert.ToInt16(reader.GetInt32(reader.GetOrdinal("QTY")));

I do not need SUM's return with Int32 that's why I don't care it's return as Int32, and also data always be in the boundary of Int16.

 

[jmcilhinney]

Thanks jmcilhinney, I open my eyes
I handled that below

getBalanceQty = Convert.ToInt16(reader.GetInt32(reader.GetOrdinal("QTY")));

I do not need SUM's return with Int32 that's why I don't care it's return as Int32, and also data always be in the boundary of Int16.

I feel like I'm hitting my head against a brick wall here. First you're using GetInt32 when the data is not type Int32 and now the data is type Int32 and you're using GetInt16. Are you trying to make this as difficult as possible?

It doesn't matter what you want. It doesn't matter what the value of the data is. All that matters is the data type. Your database column is type `int` so you get the data as Int32 values. As such, you can ONLY use GetInt32 to get it. If you want to convert it to a different data type after you get it then that's up to you but you have to get it as the type it is. If you want to be able to use GetInt16 then the data has to be type Int16, in which case your database column must be smallint.

Get the message: use the correct data type!

 

[Socarsky]

I feel like I'm hitting my head against a brick wall here. First you're using GetInt32 when the data is not type Int32 and now the data is type Int32 and you're using GetInt16. Are you trying to make this as difficult as possible?

It doesn't matter what you want. It doesn't matter what the value of the data is. All that matters is the data type. Your database column is type `int` so you get the data as Int32 values. As such, you can ONLY use GetInt32 to get it. If you want to convert it to a different data type after you get it then that's up to you but you have to get it as the type it is. If you want to be able to use GetInt16 then the data has to be type Int16, in which case your database column must be smallint.

Get the message: use the correct data type!

might it be a misunderstanding that caused this confusing? I don't use GetInt16 because it caused problem when SQL makes SUM()'s return with Int32 even my field is int16(smallint). When I noticed that SQL uses Int32 with SUM() function then I changed my code with

 int16 getBalanceQty = Convert.ToInt16(reader.GetInt32(reader.GetOrdinal("QTY")));

That is the story. is There still an issue?