I'm trying read data from sql database. When I run the code, it gives an error when it comes to where it should read data.

"System.InvalidCastException: Specified cast is not valid."

I get an error. I researched my mistake but couldn't find the solution that works for me. While the query text I wrote works in ssms, it does not work in code.

 private void pbx_frame_MouseUp(object sender, MouseEventArgs e) //
    {
        //take cropped image to picture box of crooped images
        try
        {
            string[] resim = lbx_raw_image.SelectedItem.ToString().Replace(':','.').Split('.'); 
            string sorgu_sql = "Data Source=DESKTOP-OON7EET\\SQLEXPRESS;Initial Catalog=Target;Integrated Security=True;"; //sql bağlantısı kuruldu
            //string query = "SELECT * FROM MP_Data$ WHERE time= '19.53.06'"; //'"   lbx_raw_image.SelectedItem.ToString()   "'"; //time=19.53.06 tek bir veriyi çağrır. muhtemelen yorum haline getirilen kısım olacaktı.
            string query = "SELECT * FROM MP_DATA_15_mayıs_2019$ WHERE time='"   lbx_raw_image.SelectedItem.ToString()   "'"; //12.50.23
            DB_islem db = new DB_islem(sorgu_sql, query); // 
            
            pic_info_from_MP = db.Raw_Image(pic_info_from_MP);      



    public Target Raw_Image(Target pic_info) 
    {
        sql.Open();
       
        sql_read = sql_command.ExecuteReader();

        //while (sql_read.Read())
        if (sql_read.Read())
        {
            pic_info.lat = sql_read.GetDouble(0);
            pic_info.lon = sql_read.GetDouble(1);
            pic_info.alt = sql_read.GetDouble(2);
            pic_info.yaw = sql_read.GetDouble(3);
        }

        sql.Close();

        return pic_info;
    }

CodePudding user response：

The reason I asked for the table definition is because there are particular column types on the DB side that map to particular data types on the C# side. The screenshot posted is not enough to realistically determine the column types. The lat and long aren't real, otherwise they'd be truncated but other than that we can't really say

Broadly speaking:

• If you've used real column type, call GetFloat(..)
• If you've used float column type, call GetDouble(..)
• If you've used decimal(or numeric) column type, call GetDecimal(..)

GetXxx will not convert for you because it does an unboxing cast. If your reader has a decimal in column 0 you can only call GetDecimal on it. You cannot call GetDouble and expect you really-a-decimal to be converted to a double, for the same reason as this doesn't work:

object o = 0.1m; //decimal boxed inside object
double d = (double)o;

Unboxing casts and converting casts are different things. If you want your decimal-in-an-object as a double you have to cast twice, once to unbox and once to convert:

object o = 0.1m; //decimalboxed inside object
double d = (double)(decimal)o;

Similarly if your thing inside reader column 0 is a decimal and you want a double you must get it as a decimal, because that's what it really is, and then convert it:

var dbl = (double)reader.GetDecimal(0);

Simple rule:

• Pull it out according to what it really is, then convert it

As to what you have, we don't know; your yaw could be a real and cannot be GetDouble'd (has to be GetFloat'd). Your alt is probably a decimal (do you really need altitude to the tenth of a picometer/pico-ft?) and cannot be GetDouble'd (has to be GetDecimal'd) .. but you have all the necessary info to solve this one yourself.

If all else fails and the above just went completely over your head "try changing GetDouble to GetDecimal for the line that doesn't work. And if that doesn't work, try changing it to GetFloat. And if that doesn't work, supply the table definition and we'll tell you what it should be"

Update: youve indicated they're all varchar. Personally I would convert them to float or real - open SSMS, Design the table, change the column type to eg float and save the table. Then they can be GetDouble'd

CodePudding user response：

First of all you should execute your queries ONLY with parameters. Secondly, we don't know your table schema - please show it.

But most probably there is problem in how you store this value. GetDouble doesn't do any conversions, so your data should already be saved as double.

What's more, there can be problem with globalization. What you should do is:

using System.Globalization;
//...

double result = double.NaN;

if(!sql_read.IsDbNull(0))
{
    string s = sql_read.GetString(0);
    result = Convert.ToDouble(s, CultureInfo.InvariantCulture);
}