System.InvalidCastException: Specified cast is not valid in C# using postgres datareader

Question

I am trying to pull data from database using postgre in C# and putting the values returned in label controls. I keep getting System.InvalidCasaeExeception. The database field is an integer so I a using a data reader to get the value.

here is my code

private void Get_Defects()
    {
        NpgsqlConnection conn = Connection.getConnection();

        try
        {
            conn.Open();
            NpgsqlCommand cmd = new NpgsqlCommand("select * from defect where defect_id >= :MinID and defect_id <= :MaxID and location_id = 102 and top_or_bottom = :TopBottom;", conn);
            cmd.Parameters.Add(new NpgsqlParameter("MinID", MinID));
            cmd.Parameters.Add(new NpgsqlParameter("MaxID", MaxID));
            cmd.Parameters.Add(new NpgsqlParameter("TopBottom", TopBottom));

            NpgsqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                lblCrookedPart.Text = dr.GetInt32(12).ToString();
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            conn.Close();
        }
    }

}

Not sure why it wont work. I pulled the first element and it displays correctly. some of the data is integers but have null values in the DB. I tried an element with data but I get the cast exception error. Please help.

Vivek S. · Accepted Answer · 2014-12-17 05:09:13Z

1

Try something like this

NpgsqlDataReader da = default(NpgsqlDataReader);
NpgsqlCommand cmd = new NpgsqlCommand("select * from myTable", GenConnection);
string strVAL = null;
da = cmd.ExecuteReader;
if (da.HasRows) {
    while (da.Read) {
        strVAL = (Information.IsDBNull(da["field"]) ? 0 : da["field"]).ToString;
    }
}

answered Dec 17, 2014 at 5:09

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

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

Comments

Evanark · Accepted Answer · 2014-12-17 14:22:20Z

I got it working, thank you all for you help and tips!!

here is a snippit of the working code

conn.Open();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from defect where defect_id >= '"+MinID+"' and defect_id <= '"+MaxID+"' and location_id = 102 and top_or_bottom = '"+TopBottom+"';", conn);
            ds.Reset();
            da.Fill(ds);
            dt = ds.Tables[0];

            //Used to sum the column values

            object CrookedPartTotal = dt.Compute("Sum(crooked_part)", "defect_id >= '" + MinID + "' and defect_id <= '" + MaxID + "'");
            if (CrookedPartTotal.ToString() == "")
                lblCrookedPart.Text = "0";
            else
                lblCrookedPart.Text = CrookedPartTotal.ToString();


            object TooMuchSolder = dt.Compute("Sum(too_much_solder)", "defect_id >= '" + MinID + "' and defect_id <= '" + MaxID + "'");
            if (TooMuchSolder.ToString() == "")
                lblTooMuchSolder.Text = "0";
            else
                lblTooMuchSolder.Text = TooMuchSolder.ToString();

gmiley · Accepted Answer · 2014-12-16 20:22:07Z

0

You should check that the value is not null. Try the following instead:

lblCrookedPart.Text = (dr.IsDBNull(12)) ? "NULL" : dr.GetInt32(12).ToString();

answered Dec 16, 2014 at 20:22

gmiley

6,6041 gold badge15 silver badges25 bronze badges

4 Comments

Evanark Over a year ago

That takes care of the error but the value is not coming up. I ran a query in postgres and there is 1 but a 0 shows up.(I changed your "NULL" to "0") Do I need to put the result set in an array because I have to sum each defect and display the total in label controls for each defect?

gmiley Over a year ago

First, are you certain that the column returned in position 12 is the correct one? Why don't you change your select statement to only return the specific column(s) you need. Second, have you ensured that the parameters you are passing contain the correct values?

Evanark Over a year ago

I figured out half of the problem. I am using a data adaptor and putting into a data table. I can now calculate the sum, however if there are no values, the label control is blank instead of displaying a zero. I am using the compute method an it works but I want to display a zero if there are nulls. Not sure how to check for nulls using a data table. I tried a foreach loop but it does not calculate the sum.

gmiley Over a year ago

Why not return a 0 in place of a null in your sql select. Take a look at coalesce(). SELECT Coalesce(field_name, 0) AS field_name FROM ....

Collectives™ on Stack Overflow

System.InvalidCastException: Specified cast is not valid in C# using postgres datareader

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related