In a C# program with .NET framework to make a windows form I have added the following code to make a connection and to get the value of result and store in a list for later usage

public List<Info> GetInfo(string Col)
        {
            string connectionString = "Server=VIS-12\\TESTSQLSERVER;Database=SPCC;User Id=sa;Password=admin1234;";
            string comand = $"select {Col} from Tbl_Reading where [LogTime] between '2017-07-06 14:30:26.000' and '2017-07-06 14:30:26.000' ";
            using (SqlConnection conn = new SqlConnection())
            {
                double[] val1 = new double [100] ;
                conn.ConnectionString = connectionString;
                conn.Open();
                SqlCommand c1 = new SqlCommand(comand, conn);
                SqlDataReader reader = c1.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {

                    int x = 0;
                    val1[x] = Convert.ToDouble(reader.GetDouble(0)); //this line is throwing the error 
                    x  ;
                }
            }
            reader.Close();
            List<Info> d1 = new List<Info>();
            d1 = (List<Info>)val1.Cast<Info>();//ToList();
            conn.Close();
            return d1;
            //return val1;
        }
    }

I still keep getting an invalid cast Exception even though the return type and the variable type is same

CodePudding user response：

GetDouble does indeed return type double but there has to be a double value to get. Internally, that method will get an object reference from the specified column and cast it as type double. It is that cast that is invalid, not one that you're performing. If you look at the stack trace of the exception then you should be able to see that.

Either the column you're retrieving is the wrong data type or at least one row contains NULL in that column. If the data type is correct then you need to either filter out NULL values in your query or else account for them in your reading code.

CodePudding user response：

GetDouble will throw an exception if the value internally is not a double. This will happen obviously if the column is a different type, but also if the value is a null. So you need to handle that case.

Other notes:

• if (reader.HasRows) is not necessary as you can use reader.Read() to tell you the same thing.
• Use a multi-line string with @ to make your query more readable.
• I hope Col is not coming from user-input, otherwise you have a major injection vulnerability.
• You are missing using blocks to dispose your objects.
• There seems to be no need for the array, just convert each value as you go along and insert into a list.
• between doesn't seem to make sense here, also you should probably use a parameter for the date. If there is really a start and end time, use >= AND < rather than BETWEEN.
• Consider putting the connection string in a settings file, rather than hard-coding it.

public List<Info> GetInfo(string Col)
{
    string connectionString = "Server=VIS-12\\TESTSQLSERVER;Database=SPCC;User Id=sa;Password=admin1234;";
    string comand = @$"
select {Col}
from Tbl_Reading
where LogTime = @time;
";

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand c1 = new SqlCommand(comand, conn))
{
    c1.Parameters.Add("@time", SqlDbType.DateTime).Value = DateTime.Parse("2017-07-06 14:30:26.000");
    List<Info> d1 = new List<Info>();
    conn.Open();
    using (SqlDataReader reader = c1.ExecuteReader())
    {
        while (reader.Read())
        {
            d1.Add((reader[0] as double) as Info);
        }
        return d1;
    }
}