CODEWITHSUNDEEP
c#sql-server
sebastian.roibu
sebastian.roibu

Reputation: 2859

Error when receiving null from database

When i execute this command on my database i receive sometimes result=null. i want to enter the "else" part when is this happening, but i receive

An unhandled exception of type 'System.FormatException' occurred in mscorlib.dll. Additional information: Input string was not in a correct format.

DB database = new DB();
            int reservedSeats = 0;
            using (database.sqlConnection)
            {
                database.sqlConnection.Open();
                string command = "select SUM(nrLocuri) as result from Rezervari where idRand = @idRand and idShow=@idShow";
                using (SqlCommand cmd = new SqlCommand(command, database.sqlConnection))
                {
                    cmd.Parameters.Add("@idRand", SqlDbType.Int).Value = idRand;
                    cmd.Parameters.Add("@idShow", SqlDbType.Int).Value = idShow;
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        if (dr.Read())
                            if (dr["result"].ToString() != null)
                                reservedSeats = Convert.ToInt32(dr["result"].ToString());
                            else
                                return totalSeats;
                    }
                }
            }
            return totalSeats-reservedSeats;

Upvotes: 0

Views: 113

Answers (4)

Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34152

try:

if(!dr.IsDBNull(i)){   //replace i with the column id

  //get the data

}

Upvotes: 2

Oded
Oded

Reputation: 498914

Instead of:

if (dr["result"].ToString() != null)

Do:

if (dr["result"] != DbNull.Value)

When the dr["result"] returns a database null, its value is DbNull.Value - when you try to call Convert.ToInt32 on this value, you get a format exception.

Upvotes: 6

4b0
4b0

Reputation: 22323

try isnull:

string command = "select isnull(SUM(nrLocuri),0.00 )as result from Rezervari where idRand = @idRand and idShow=@idShow";

Upvotes: 1

gahooa
gahooa

Reputation: 137262

If you want SUM() to return 0.00 in the event no records were found, replace it with:

COALESCE(SUM(...), 0.00)

COALESCE will return the first non-null value passed to it.

Upvotes: 1

Related Questions