How to return value from database into a variable

Question

static float ExecuteQueryWithResult_fl(SqlConnection connection, string query)
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                float prev_val = ((float)command.ExecuteScalar());
                return prev_val;
            }
        }

Call :

float f = ExecuteQueryWithResult_fl(connection, prev_status);

Iam using this in a client application that communicates with server , whenever this line is about to be executed : The connection breaks ! No error comes out . What may be the problem ??

public static void update_data_base(string type_id,int station, int ioa, byte by_val, float fl, uint bcr, bool is_float, bool is_bcr_iti,byte ov, byte bl, byte sb,byte nt, byte iv, byte seq, byte cy, byte ca)
        {
            string connectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\Ahmed Aek Ben Jemia\\Desktop\\newest\\command combo\\1\\iec104_master_slave_rtu\\Combo_1.mdf;Integrated Security=True";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string updateCommand = null,updateCommand1=null;
                connection.Open();
                ConnectionState state = connection.State;

                float f = 0;

                string prev_status = string.Format("SELECT STATUS FROM ") + type_id + string.Format("_Table WHERE ASDU={0} AND IOA={1}", station, ioa);
                    try
                    {
                    f = ExecuteQueryWithResult_fl(connection, prev_status);
                    }
                    catch (Exception e)
                    {
                        SetText2(e.ToString());
                    }                  
            }

        }

Error : System.InvalidCastException: Specified cast is not valid

PS : I can get string & byte values from database , this only happens with float and int.

Answer 1

Try the following and ensure your that your connection is open already.

static float ExecuteQueryWithResult_fl(SqlConnection connection, string query)   {
            try {        
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    var prev_val = command.ExecuteScalar().ToString() == null ? default(float) : float.Parse(command.ExecuteScalar().ToString());;
                    return prev_val;
                }
            }

           catch (Exception x) 
           { 
                Console.WriteLine("Error fetching due to {0}", x.Message); 
                return default(float);
           }
     }

Update: Replace your query with:

string prev_status = string.Format("SELECT cast(STATUS as float) prev_status FROM {0}_Table WHERE ASDU={1} AND IOA={2}",type_id, station, ioa);

Answer 2

What you have looks ... usable (although I'd be very concerned about how you are passing in parameters); your code works fine when called like this:

public static void Main()
{
    float f;
    using(var conn = new SqlConnection("Server=.;Integrated Security=SSPI"))
    {
        conn.Open();
        f = ExecuteQueryWithResult_fl(conn, "select cast(1.23 as float(24))");
    }
    Console.WriteLine(f); // 1.23
}

So the problem isn't the method you've shown (by itself). To understand what is happening, you should use a debugger and see what happens. My guess would be one of:

• an invalid cast (the difference between float in C# and the SQL Server data types)
• invalid SQL from concatenating values into the query (a very very bad thing to do)

combined with calling code that is swallowing an Exception without displaying it to you. The debugger will tell you, once you have a break-point at the location that isn't working.