CODEWITHSUNDEEP
c#asp.netnull
Neo
Neo

Reputation: 16219

need to handle NULL exception

private int MyTimeValue()
{
    int Value = 0;
    string connectionString = GetConfigurationSettingValue("ConnectionString");
    int Days = 120;

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (SqlCommand cmd = new SqlCommand("select [dbo].[My_function] (@Span)", connection))
        {
            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Parameters.Add(new SqlParameter("@Span", Days));

            Value = Convert.ToInt32(cmd.ExecuteScalar());

        }
    }

    return (Value);
}

I'm getting Object cannot be cast from DBNull to other types.

How can i handle null exception here using try and catch

please help me for syntax.

Upvotes: 2

Views: 203

Answers (5)

Akshay
Akshay

Reputation: 1901

This can be done in 2 ways.

Case 1: Handling Specific Exception 

private int MyTimeValue()
{
    int Value = 0;
    try
    {      
       string connectionString = GetConfigurationSettingValue("ConnectionString");
       int Days = 120;

       using (SqlConnection connection = new SqlConnection(connectionString))
       {
           connection.Open();
           using (SqlCommand cmd = new SqlCommand("select [dbo].[My_function] (@Span)", connection))
           {
               cmd.CommandType = System.Data.CommandType.Text;

               cmd.Parameters.Add(new SqlParameter("@Span", Days));

               Value = Convert.ToInt32(cmd.ExecuteScalar());

           }
       }
     catch(NullReferenceException ex)
     {
        \\Handle Exception
     }

     return (Value);

}

Case 2: Check variables for Null and also Catch General Exception for other unexpected exceptions

    private int MyTimeValue()
{
    int Value = 0;
    try
    {      
       string connectionString = GetConfigurationSettingValue("ConnectionString");
       int Days = 120;

       using (SqlConnection connection = new SqlConnection(connectionString))
       {
           connection.Open();
           using (SqlCommand cmd = new SqlCommand("select [dbo].[My_function] (@Span)", connection))
           {
               cmd.CommandType = System.Data.CommandType.Text;

               cmd.Parameters.Add(new SqlParameter("@Span", Days));
               var result = cmd.ExecuteScalar();
               if(result !=null)
                    Value = Convert.ToInt32(result );

           }
       }
     catch(Exception ex)
     {
        \\For handling unexpected exceptions
     }   
     return (Value);
}

Upvotes: 1

Damith
Damith

Reputation: 63065

you can validate before convert 

object result = cmd.ExecuteScalar();
if(result != null)
    Value = Convert.ToInt32(result);

and also add the parameter as below 

 cmd.Parameters.AddWithValue("@Span", Days);

as side note, line cmd.CommandType = System.Data.CommandType.Text; is no need. default command type is Text

Upvotes: 3

eeshwr
eeshwr

Reputation: 268

You need to check if the value return by the ExecuteScalar is null or not first, so that the exception wont occur while trying to cast the object to Int32.

private int MyTimeValue()
    {
        int Value = 0;
        string connectionString = GetConfigurationSettingValue("ConnectionString");
        int Days = 120;

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand cmd = new SqlCommand("select [dbo].[My_function] (@Span)", connection))
            {
                cmd.CommandType = System.Data.CommandType.Text;

                cmd.Parameters.Add(new SqlParameter("@Span", Days));
                object o=cmd.ExecuteScalar();
                if(o!=null)
                   Value = Convert.ToInt32(o);

            }
        }

        return (Value);

    }

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

try this

          Value = cmd.ExecuteScalar();    
          if (((Value != null) || (Value != DBNull.Value)))
          {
          Value =(int)Value ;
          }

Upvotes: 1

Selman Genç
Selman Genç

Reputation: 101681

I guess the error is here:

Value = Convert.ToInt32(cmd.ExecuteScalar());

Try this:

var result = cmd.ExecuteScalar();
Value = result == DBNull.Value ? 0 : Convert.ToInt32(result);

Note: Also your command string seems wrong to me.

Upvotes: 2

Related Questions