DB Null error - what is a good way to catch this error?

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 3188 Views - Last Post: 13 August 2012 - 09:09 AM Rate Topic: -----

#1 Cmore86  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 01-August 12

DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 06:15 AM

I have successfully created my app and when it runs over long periods of time it runs into Object cannot be cast from DBNull to other types. I know that there might be null data coming from the DB that it is pulling from.

There is this string in the original code that looks for this error:
(It was done in VB)
'Open connection
            connection.Open()

            'Execute reader
            Dim reader As OdbcDataReader = command.ExecuteReader

            'Check if reader has any rows
            If reader.HasRows Then

                'Check that first column is not Null
                If reader.IsDBNull(0) = False Then
                    Dim dt As New DataTable("CSQ")
                    dt.Load(reader)

                    'Set data values
                    contactsWaiting = dt.Rows(0).Item("scallswaiting")
                    totalContacts = dt.Rows(0).Item("stotalcalls")
                    oldestContact = dt.Rows(0).Item("soldestcontact")
                    contactsHandled = dt.Rows(0).Item("scallshandled")
                    contactsAbandoned = dt.Rows(0).Item("scallsabandoned")

                    If queue = 1 Then
                        lblCGStatus.Text = ""
                    ElseIf queue = 2 Then
                        lblCEStatus.Text = ""
                    End If
                Else

                    If queue = 1 Then
                        lblCGStatus.Text = "Restart - NULL"
                    ElseIf queue = 2 Then
                        lblCEStatus.Text = "Restart - NULL"
                    End If
                End If

            Else
                If queue = 1 Then
                    lblCGStatus.Text = "Restart - NO ROWS"
                ElseIf queue = 2 Then
                    lblCEStatus.Text = "Restart - NO ROWS"
                End If
            End If

            reader.Close()
            connection.Close()


This is what my code looks like:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace RSQ_App_V1
{
    public partial class RSQ : Form
    {
        public RSQ()
        {
            InitializeComponent();
            timer();
         
        }
      
        private void GetUCCXData(int queue)
        {
            
            try
            {
                //Initialize variables
                int contactsWaiting = 0;
                //rtcsqssummary = callswaiting
                int oldestContact = 0;
                //rtcsqssummary = oldestcontact
                int totalContacts = 0;
                //rtcsqssummary = totalcalls
                int contactsHandled = 0;
                //rtcsqssummary = callshandled
                int contactsAbandoned = 0;
                //rtcsqssummary = callsabandoned
                

                // Establishes Connection to UCCX
                OdbcConnection connection = new OdbcConnection();
                connection.ConnectionString = "DSN=UCCX";
            //Selects Data 3
                string queryString = "";
                if (queue == 1)
                {
                    queryString = "SELECT SUM(totalcalls) AS stotalcalls, SUM(callswaiting) AS scallswaiting, SUM(oldestcontact) AS soldestcontact, SUM(callshandled) AS scallshandled, SUM(callsabandoned) AS scallsabandoned, SUM(longesttalkduration) AS slongesttalkduration FROM rtcsqssummary WHERE csqname='RSQ-Spanish' OR csqname='RSQ-English'";
                }
                else if (queue == 2)
                {
                    queryString = "SELECT SUM(totalcalls) AS stotalcalls, SUM(callswaiting) AS scallswaiting, SUM(oldestcontact) AS soldestcontact, SUM(callshandled) AS scallshandled, SUM(callsabandoned) AS scallsabandoned, SUM(longesttalkduration) AS slongesttalkduration FROM rtcsqssummary WHERE csqname= 'RSQ-Elevator'";
                }
               
                // Query Command
                OdbcCommand command = new OdbcCommand(queryString, connection);
                //Open Connection
                connection.Open();
                //Executes Data Reader
                OdbcDataReader reader = command.ExecuteReader();
                DataTable dt = new DataTable("UCCXData");                
                dt.Load(reader);
                reader.Close();
                connection.Close();
                //Fills the dataview
                dvData.DataSource = dt;
                

                if (queue == 1)
                {
                    //Resident Services                    
                    totalContacts = Convert.ToInt32(dvData.Rows[0].Cells["stotalcalls"].Value);
                    valCGTotal.Text = totalContacts.ToString();
                    contactsWaiting = Convert.ToInt32(dvData.Rows[0].Cells["scallswaiting"].Value);
                    valCGWaiting.Text = contactsWaiting.ToString();
                    oldestContact = Convert.ToInt32(dvData.Rows[0].Cells["soldestcontact"].Value);
                    valCGOldest.Text = CalculateTime(oldestContact).ToString();                
                    contactsHandled = Convert.ToInt32(dvData.Rows[0].Cells["scallshandled"].Value);
                    valCGHandled.Text = contactsHandled.ToString();
                    contactsAbandoned = Convert.ToInt32(dvData.Rows[0].Cells["scallsabandoned"].Value);
                    valCGAbandoned.Text = contactsAbandoned.ToString();                    
                    contactsHandled = Convert.ToInt32(dvData.Rows[0].Cells["scallshandled"].Value);
                    totalContacts = Convert.ToInt32(dvData.Rows[0].Cells["stotalcalls"].Value);
                    valCGPercentHandled.Text = CalculatePercentHandled(contactsHandled, totalContacts) + "%";                                    
                }
                else if (queue == 2)
                {
                    //Elevator 
                    totalContacts = Convert.ToInt32(dvData.Rows[0].Cells["stotalcalls"].Value);
                    valCETotal.Text = totalContacts.ToString();
                    contactsWaiting = Convert.ToInt32(dvData.Rows[0].Cells["scallswaiting"].Value);
                    valCEWaiting.Text = contactsWaiting.ToString();
                    oldestContact = Convert.ToInt32(dvData.Rows[0].Cells["soldestcontact"].Value);
                    valCEOldest.Text = CalculateTime(oldestContact).ToString();
                    contactsHandled = Convert.ToInt32(dvData.Rows[0].Cells["scallshandled"].Value);
                    valCEHandled.Text = contactsHandled.ToString();
                    contactsAbandoned = Convert.ToInt32(dvData.Rows[0].Cells["scallsabandoned"].Value);
                    valCEAbandoned.Text = contactsAbandoned.ToString();
                    contactsHandled = Convert.ToInt32(dvData.Rows[0].Cells["scallshandled"].Value);
                    totalContacts = Convert.ToInt32(dvData.Rows[0].Cells["stotalcalls"].Value);
                    valCEPercentHandled.Text = CalculatePercentHandled(contactsHandled, totalContacts) + "%";
                }

            }
            catch (Exception ex)
            {
                //Error Textbox
                MessageBox.Show("there was an error - " + ex);
                errorBox1.Text = " " + ex;
            }
            
            
        }


        //Calculate Time
        private string CalculateTime(int millis)
        {

            int Hours = TimeSpan.FromMilliseconds(millis).Hours;
            int Minutes = TimeSpan.FromMilliseconds(millis).Minutes;
            int Seconds = TimeSpan.FromMilliseconds(millis).Seconds;

            return Hours.ToString("D2") + ":" + Minutes.ToString("D2") + ":" + Seconds.ToString("D2");

        }
        //Calculate Percent
        private string CalculatePercentHandled(double handled, int total)
        {

            if (total > 0)
            {
                return Math.Round(((handled / total) * 100)).ToString();
            }
            else
            {
                return "0";
            }

        }

        private void timer()
        {
            System.Windows.Forms.Timer timer = new System.Windows.Forms.Timer();
            timer.Tick += new EventHandler(RSQ1_Load);
            timer.Interval = 3000;
            timer.Enabled = true;
            timer.Start();
        }

    
        private void RSQ1_Load(object sender, EventArgs e)
        {
            
            //Get Castle Group data - queue =1
            GetUCCXData(1);
            //Get Connection data - quueue =2
            GetUCCXData(2);
            
        }
        
              
    }
}



I tried putting in the same concept that was used like this:
//Check if reader has any rows

			if (reader.HasRows) {
				//Check that first column is not Null
				if (reader.IsDBNull(0) == false)


But run into a variety of errors. Is there another way to check for DBNull and not run the code if it runs into it?

Thank you.

Is This A Good Question/Topic? 0
  • +

Replies To: DB Null error - what is a good way to catch this error?

#2 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 488
  • Joined: 13-June 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 06:30 AM

Do you want to skip over an entire row because one value is null? Surely you wan't to be able to create an object that matches the DB schema, including the null.

Did you know you can create nullable types in C#? For example, string ? nullableString; has created a string variable that can hold a null value, due to the use of ? following the type declaration.

Otherwise, did you want to replace a null value with a default? If so, the code you have presented should allow that, by using if(reader.IsDBNull(0)) myVariable = "default";
Was This Post Helpful? 1
  • +
  • -

#3 Cmore86  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 01-August 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 06:38 AM

What I would like to do is restart the code if it finds the dbnull. That way the application does not hit a wall so to speak. The default value could be a good idea too. I would like to skip pulling the data if any are found null. This tends to happen with the database ever so often.
Was This Post Helpful? 0
  • +
  • -

#4 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 488
  • Joined: 13-June 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 06:51 AM

Hmm. Why do you have nulls in the database if you shouldn't have nulls? Was the table setup incorrectly? Is there no data validation that occurs on database entry?

When you say "restart the code", I'm assuming you don't mean "start from the start", since you would create an infinite loop if there's a null value. Do you mean you want to skip over reading in the data if there's a null in column 0?

Do you want to use GetValues() when column 0 isn't null? Or maybe NextResult() is what you need.
Was This Post Helpful? 1
  • +
  • -

#5 Cmore86  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 01-August 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 06:55 AM

That is a good question on why that database has nulls. It is a database that comes from the cisco phonesystem and it is preset that way when there is no activity from what it looks like.

What I would like to skip over reading the data if it is null as it causes the error to occur. If it can do that or just go back and pull the data again it should work either way. This app can run for minutes to hours until it runs into the Null. Avoiding the null would be best as the data is refreshed every 5 seconds in the database.
Was This Post Helpful? 0
  • +
  • -

#6 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4440
  • View blog
  • Posts: 7,721
  • Joined: 08-June 10

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 07:03 AM

Quote

Did you know you can create nullable types in C#? For example, string ? nullableString; has created a string variable that can hold a null value, due to the use of ? following the type declaration.


Sorry, but this is a bad example. Strings are already classes, so they can already be null.

A better example would be something like int?.
Was This Post Helpful? 1
  • +
  • -

#7 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 488
  • Joined: 13-June 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 07:04 AM

OK. Then you do not want it to pull the data again when the null is encountered, or it will again only pull data until it encounters the null. I personally think you should use nullable types so you're capturing all the data: after all, it might just be the first column that's null, with true data in the other columns that you want to catch; otherwise, you might present an inaccurate picture.

Untested code:
while(reader.Read())
            {
                if (reader.IsDBNull(0))
                {
                    reader.NextResult();
                }
            }

Was This Post Helpful? 1
  • +
  • -

#8 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4440
  • View blog
  • Posts: 7,721
  • Joined: 08-June 10

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 07:05 AM

Next result moves to the next result set, not the next row. Most queries only return one result set. Basically, all you'd want to do to skip a row is continue;
Was This Post Helpful? 2
  • +
  • -

#9 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 488
  • Joined: 13-June 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 07:06 AM

View PostCurtis Rutland, on 10 August 2012 - 02:03 PM, said:

Quote

Did you know you can create nullable types in C#? For example, string ? nullableString; has created a string variable that can hold a null value, due to the use of ? following the type declaration.


Sorry, but this is a bad example. Strings are already classes, so they can already be null.

A better example would be something like int?.


Word.
Was This Post Helpful? 1
  • +
  • -

#10 Cmore86  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 01-August 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 07:52 AM

When attempting this I get an error that the reader is closed.

I also get an error that the index is out of range.

while(reader.Read())
                {
                    if (reader.IsDBNull(0))
                    {
                        reader.NextResult();
                    }
                }


Where would this be the best place to put this?

I attempted to use continue

 while (reader.Read())
                {
                    if (reader.IsDBNull(0))
                    {
                        continue;
                    }
                }


I also get the same issues.
Was This Post Helpful? 0
  • +
  • -

#11 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3491
  • View blog
  • Posts: 10,748
  • Joined: 05-May 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 09:41 AM

Are you sure you recompiled the code after changing the line to use continue?

Are you sure that it is that call to IsDBNull() that is throwing the index of range exception? Could it be some other line of code that is throwing the exception?

I find it extremely hard to believe that an exception would be thrown if OdbcDataReader.IsDBNull() is called in C#, but not if called in VB.
Was This Post Helpful? 1
  • +
  • -

#12 Cmore86  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 01-August 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 09:52 AM

View PostSkydiver, on 10 August 2012 - 09:41 AM, said:

Are you sure you recompiled the code after changing the line to use continue?

Are you sure that it is that call to IsDBNull() that is throwing the index of range exception? Could it be some other line of code that is throwing the exception?

I find it extremely hard to believe that an exception would be thrown if OdbcDataReader.IsDBNull() is called in C#, but not if called in VB.


I have recompiled. I am sure that the placement of that line of code is what is causing the errors.

Not sure where I should place it. When I place it before the conection closes, the index is out of range.

Where is the proper place to put this
while (reader.Read())
               {
                   if (reader.IsDBNull(0))
                   {
                       continue;
                   }
               }



Into this"

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace RSQ_App_V1
{
    public partial class RSQ : Form
    {
        public RSQ()
        {
            InitializeComponent();

            timer();
         
        }
      
        private void GetUCCXData(int queue)
        {
            
            try
            {
                //Initialize variables
                int contactsWaiting = 0;
                //rtcsqssummary = callswaiting
                int oldestContact = 0;
                //rtcsqssummary = oldestcontact
                int totalContacts = 0;
                //rtcsqssummary = totalcalls
                int contactsHandled = 0;
                //rtcsqssummary = callshandled
                int contactsAbandoned = 0;
                //rtcsqssummary = callsabandoned
                

                // Establishes Connection to UCCX
                OdbcConnection connection = new OdbcConnection();
                connection.ConnectionString = "DSN=UCCX";
            //Selects Data 3
                string queryString = "";
                if (queue == 1)
                {
                    queryString = "SELECT SUM(totalcalls) AS stotalcalls, SUM(callswaiting) AS scallswaiting, SUM(oldestcontact) AS soldestcontact, SUM(callshandled) AS scallshandled, SUM(callsabandoned) AS scallsabandoned, SUM(longesttalkduration) AS slongesttalkduration FROM rtcsqssummary WHERE csqname='RSQ-Spanish' OR csqname='RSQ-English'";
                }
                else if (queue == 2)
                {
                    queryString = "SELECT SUM(totalcalls) AS stotalcalls, SUM(callswaiting) AS scallswaiting, SUM(oldestcontact) AS soldestcontact, SUM(callshandled) AS scallshandled, SUM(callsabandoned) AS scallsabandoned, SUM(longesttalkduration) AS slongesttalkduration FROM rtcsqssummary WHERE csqname= 'RSQ-Elevator'";
                }
               
                // Query Command
                OdbcCommand command = new OdbcCommand(queryString, connection);
                //Open Connection
                connection.Open();
                //Executes Data Reader
                OdbcDataReader reader = command.ExecuteReader();
                DataTable dt = new DataTable("UCCXData");
                dt.Load(reader);
                reader.Close();
                connection.Close();
                //Fills the dataview
                dvData.DataSource = dt;
                

                if (queue == 1)
                {
                    //Resident Services                    
                    totalContacts = Convert.ToInt32(dvData.Rows[0].Cells["stotalcalls"].Value);
                    valCGTotal.Text = totalContacts.ToString();
                    contactsWaiting = Convert.ToInt32(dvData.Rows[0].Cells["scallswaiting"].Value);
                    valCGWaiting.Text = contactsWaiting.ToString();
                    oldestContact = Convert.ToInt32(dvData.Rows[0].Cells["soldestcontact"].Value);
                    valCGOldest.Text = CalculateTime(oldestContact).ToString();                
                    contactsHandled = Convert.ToInt32(dvData.Rows[0].Cells["scallshandled"].Value);
                    valCGHandled.Text = contactsHandled.ToString();
                    contactsAbandoned = Convert.ToInt32(dvData.Rows[0].Cells["scallsabandoned"].Value);
                    valCGAbandoned.Text = contactsAbandoned.ToString();                    
                    contactsHandled = Convert.ToInt32(dvData.Rows[0].Cells["scallshandled"].Value);
                    totalContacts = Convert.ToInt32(dvData.Rows[0].Cells["stotalcalls"].Value);
                    valCGPercentHandled.Text = CalculatePercentHandled(contactsHandled, totalContacts) + "%";                                    
                }
                else if (queue == 2)
                {
                    //Elevator 
                    totalContacts = Convert.ToInt32(dvData.Rows[0].Cells["stotalcalls"].Value);
                    valCETotal.Text = totalContacts.ToString();
                    contactsWaiting = Convert.ToInt32(dvData.Rows[0].Cells["scallswaiting"].Value);
                    valCEWaiting.Text = contactsWaiting.ToString();
                    oldestContact = Convert.ToInt32(dvData.Rows[0].Cells["soldestcontact"].Value);
                    valCEOldest.Text = CalculateTime(oldestContact).ToString();
                    contactsHandled = Convert.ToInt32(dvData.Rows[0].Cells["scallshandled"].Value);
                    valCEHandled.Text = contactsHandled.ToString();
                    contactsAbandoned = Convert.ToInt32(dvData.Rows[0].Cells["scallsabandoned"].Value);
                    valCEAbandoned.Text = contactsAbandoned.ToString();
                    contactsHandled = Convert.ToInt32(dvData.Rows[0].Cells["scallshandled"].Value);
                    totalContacts = Convert.ToInt32(dvData.Rows[0].Cells["stotalcalls"].Value);
                    valCEPercentHandled.Text = CalculatePercentHandled(contactsHandled, totalContacts) + "%";
                }

            }
            catch (Exception ex)
            {
                //Error Textbox
                MessageBox.Show("there was an error - " + ex);
                errorBox1.Text = " " + ex;

            }
            
            
        }


        //Calculate Time
        private string CalculateTime(int millis)
        {

            int Hours = TimeSpan.FromMilliseconds(millis).Hours;
            int Minutes = TimeSpan.FromMilliseconds(millis).Minutes;
            int Seconds = TimeSpan.FromMilliseconds(millis).Seconds;

            return Hours.ToString("D2") + ":" + Minutes.ToString("D2") + ":" + Seconds.ToString("D2");

        }
        //Calculate Percent
        private string CalculatePercentHandled(double handled, int total)
        {

            if (total > 0)
            {
                return Math.Round(((handled / total) * 100)).ToString();
            }
            else
            {
                return "0";
            }

        }

        private void timer()
        {
            System.Windows.Forms.Timer timer = new System.Windows.Forms.Timer();
            timer.Tick += new EventHandler(RSQ1_Load);
            timer.Interval = 3000;
            timer.Enabled = true;
            timer.Start();
        }

    
        private void RSQ1_Load(object sender, EventArgs e)
        {
            
            //Get Castle Group data - queue =1
            GetUCCXData(1);
            //Get Connection data - quueue =2
            GetUCCXData(2);
            
        }
        
              
    }
}



I just need to catch the Null and have the application call the RSQ1_Load function again in order for the application to pull new values.

Thanks for the help.
Was This Post Helpful? 0
  • +
  • -

#13 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3491
  • View blog
  • Posts: 10,748
  • Joined: 05-May 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 10:24 AM

Wait a minute... where did the "while (reader.Read())..." bit of code come from?

Both of your queries return a single row, why is there a need for a while loop.

Additionally, you already have the DataTable load in the data for you on line 61. So why not just check the DataTable instead of fooling around with the reader?
Was This Post Helpful? 1
  • +
  • -

#14 Cmore86  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 01-August 12

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 10:35 AM

What is a good way to check the datatable for Null?

I found this example:

foreach(DataRow row in table.Rows) 
{ 
    object value = row["ColumnName"]; 
    if (value == DBNull.Value) 
        // do something 
    else 
        // do something else 
} 



Would I have to name each column?
Was This Post Helpful? 0
  • +
  • -

#15 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4440
  • View blog
  • Posts: 7,721
  • Joined: 08-June 10

Re: DB Null error - what is a good way to catch this error?

Posted 10 August 2012 - 10:47 AM

Ah, I see your issue here. You only want one row. You want to skip the null columns.

Yes, that should work, and yes you would need to do it for each column. However, you could do that in a loop.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2