Unable to handle System.Data.SqlTypes.SqlNullValueException

Question

I have following code:

public string getFinalCon(string desid)
        {
            string finalConId = null;
            try
            {
                query = "select finalConID from discussions where desid=@did";
                com = new SqlCommand(query, con);
                com.Parameters.AddWithValue("@did", desid);
                con.Open();
                sdr = com.ExecuteReader();
                while (sdr.Read())
                {
                    if (sdr.GetString(0).Equals("none") == false && sdr.GetString(0)!=null)
                    {
                        finalConId = sdr.GetString(0);
                        break;
                    }
                }
                con.Close();
            }
            catch (Exception)
            {
            }
            return finalConId;
        }

As you can see I am catching the "Exception", the global exception. But the problem is that whenever this line finalConId=sdr.GetString(0) is executed, the system throws System.Data.SqlTypes.SqlNullValueException. Yes it will surely throw it whenever there is NULL value in the database in the corresponding field. But what I want is that this exception should be catched by the catch block and the function should return the default value of finalConId that is NULL as declared in starting of the function. But this is not happening instead it shows up my error page. I am calling this function like this:

string conid = getFinalCon(Request["id"].ToString());

if (conid == null)
{ /*---some code---*/}
else
{/*---some code---*}

Please anyone tell me how to handle this exception.

Solution 1

Don't catch exceptions when you don't need to. The proper way to do this is to test sdr.IsDBNull(0) before calling sdr.GetString(0). If IsDBNull() returns true, then GetString() will throw an exception and you should not call it.

It's also very poor practice to swallow all exceptions without indicating some sort of error. catch { } or catch (Exception) { } should be avoided in almost all cases. If something catastrophic happens (the DB connection goes down, for example) you want to allow that exception to propagate. Otherwise, how is the caller to distinguish between the cases "null value in that column" and "database connection died?"

Solution 2

its better to check value with DBNull.Value or IsDBNull() like this

if (reader.IsDBNull(0) && sdr.GetString(0).Equals("none") == false)
          //sdr.GetString(0)!=DBNull.Value)

if you want return null when exception than do like this

string conid;
try
{
  conid = getFinalCon(Request["id"].ToString());
}
Catch(Exception ex)
{
  conid =null;
}

Solution 3

You get an System.Data.SqlTypes.SqlNullValueException because the program trying to read something is NULL from database. You could fix this by checking if the value is NULL before you read it.

Try the following code:

query = "select finalConID from discussions where desid=@did";
com = new SqlCommand(query, con);
com.Parameters.AddWithValue("@did", desid);
con.Open();
sdr = com.ExecuteReader();
while (sdr.Read())
{
    if (sdr.GetString(0).Equals("none") == false && !sdr.IsDBNull(0) )
    {
        finalConId = sdr.GetString(0);
        break;
    }
}
con.Close();
enter code here

sdr.IsDBNull(0) will check if the value you want to read is NULL. This gonna fix your error :-)