Unable to handle System.Data.SqlTypes.SqlNullValueException

Question

I have the 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 a 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 caught 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---*}

How can I handle this exception?

Turnkey

Answer 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?"

Study

thanks it worked and another thing I want to ask. How can we predict that what Exception is going to come? Due to this I used Exception class to catch Exception. I was doing the same before as you said. But that irritated me because of continues exceptions, so I just went to catch 'Exception' exception. – Turnkey

If you are getting exceptions then you need to figure out why. Ignoring exceptions, as I've said, is very bad practice. An exception needs to be investigated, not ignored. – Study

Answer 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;
}

Sherrylsherurd

following error encountered when trying your code => Operator '!=' cannot be applied to operands of type 'string' and 'System.DBNull' – Turnkey

Answer 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 :-)

Spearhead

Answer 4

You can use the following structure inside your 'while' loop (considering if you are using ADO connected based Data Access Layer).

                if (reader["X"]is DBNull)
                { a = "N/A";}
                else
                { a = reader.GetString(reader.GetOrdinal("X")); }
                
                

where X is the name of the alias or row name of your DB and a is the object you are linking that reader to.

one thing to consider, in my example, X is a string.

as you can see, you are handling that specific Null reference.

Dianthe