There is a strange issue hat causes a thrown

[Socarsky]

Here is the screenshot that proves an issue:


The thrown is mentioning this : "Specified cast is not valid."

And here is my method's code:

private Int32 MSSQLCheckQty() 
        {
            Int32 getBalanceQty = 0;
            string strConn =
                "Data Source=19xxxxx;Initial Catalog=NewTBT;Persist Security Info=True;User ID=sxxx;Password=xxxx;";
            string sql =
                "SELECT SUM(nQty) as QTY FROM tbRecords WHERE sMPS = '" + txtMPS.Text.Trim() + "' AND sTerminalCode = '" + splitchar + "'";
            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.GetInt32(reader.GetOrdinal("QTY"));
                            }
                            else 
                            {
                                getBalanceQty = 0;
                            }
                        }
                    }
                } 
                catch (Exception ex) 
                {
                    MessageBox.Show(ex.Message);
                }
            }

 

[wim sturkenboom]

Your screenshot does not prove anything. The problem is not related to the 'invalid column name'. I see it often in SSMS when I add a column to an existing table and next write a query using that column. Only solution till now is to close and start SSMS again or do some (tedious) refreshing of tables.

Check the stacktrace (ex.Stacktrace); it will probably tell you which line of the code fails. I suspect line 21 of the block that you show. Sql numeric datatype is not an int32.

 

[jmcilhinney]

The issue is most likely here:

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

If you expect the sum of all the nQTY values to be an Int32 then you obviously expect every nQTY value to be an integer, so you should be using the `int` data type in the database. If you use the `numeric` data type then I think you'll find that what you get back in your C# code is a Double, so you'd need to call GetDouble on your data reader.

 

[Socarsky]

Actually there was no problem when I use an Insert method because nQty always contain a date which is in the limit of float which set up as Decimal. There problem was as jmcilhinney's saying side. Now I solved it by adding typecast method.