Ahmed Aekbj Ahmed Aekbj - 5 months ago 9
SQL Question

How to return value from database into a variable

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

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);