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.

What happens? Does it just hang? Does an invalid value get returned? Does an exception happen? What happens? Also: is there any chance the SQL is simply doing something silly? What is query? — Marc Gravell
– Marc Gravell, Commented Jul 8, 2016 at 10:43
Try putting it in a try catch to see the error. Is your connection open? Is your query syntax correct. It's helpful to show all relevant code to get help. — zulqarnain
– zulqarnain, Commented Jul 8, 2016 at 10:47

zulqarnain · Accepted Answer · 2016-07-08 11:32:25Z

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

edited Jul 8, 2016 at 11:32

answered Jul 8, 2016 at 10:51

zulqarnain

1,7452 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Marc Gravell Over a year ago

the exception should already be being thrown, so that doesn't necessarily help much compared to just looking at the existing exception, even if it compiled (which it doesn't); it also assumes that the code is running in a console, which I suspect is highly unlikely

Thorsten Dittmar Over a year ago

@MarcGravell ... or a Forms/WPF application, the output of which would then be visible in Visual Studio.

Marc Gravell Over a year ago

@ThorstenDittmar well, possibly; but if you're going to be using Visual Studio, do you know what else is visible? the original exception, and break-points...

Thorsten Dittmar Over a year ago

@MarcGravell I know and I'm with you on criticising this answer, but your assumption that this code must be running in a console is wrong. Of course he should debug properly.

zulqarnain Over a year ago

@MarcGravell, I wrote the code on the browser but now editied to fix it. Based on the question he's saying client application and also it's just a hint for him to display the error in an appropriate way.

Marc Gravell · Accepted Answer · 2016-07-08 10:50:31Z

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.

Collectives™ on Stack Overflow

How to return value from database into a variable

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related