[Cmore86]

Skydiver, on 08 August 2012 - 11:43 AM, said:

Notice the difference between the code you have:

contactsHandled = (string)dvData.Rows[0].Cells["scallshandled"];

and the code I had posted:

contactsHandled = (int) dvData.Rows[0].Cells["scallshandled"].Value;

1. I'm accessing the Value property of the DataGridViewCell. You are just accessing the DataGridViewCell.

2. I'm casting the value into integer since I'll be storing the value into contactsHandled which is an integer. You are casting the DataGridViewCell into a string and trying to store it into an integer.

When attempted with the changes it runs into this error:

there was an error - System.InvalidCastException: Specified cast is not valid.

[Skydiver]

You'll have to use the Convert.ToInt32() instead of the cast, if the cast is failing.

[Cmore86]

Skydiver, on 08 August 2012 - 12:01 PM, said:

You'll have to use the Convert.ToInt32() instead of the cast, if the cast is failing.

Awesome that worked perfectly.


Thank you for all your help.

Slightly off topic, how would I refresh the form.

I read that this.Refresh(); works but When I used it, it doesn't refresh.

Thanks again.

[Skydiver]

Skydiver, on 08 August 2012 - 12:01 PM, said:

You'll have to use the Convert.ToInt32() instead of the cast, if the cast is failing.

Interesting... It looks like Informix is returning the SUM() as a string rather than as an int. In the spoiler is my small scale experiment trying to figure out why the cast would have failed, and I could only get it to fail and resort to using Convert.ToInt32() if the field was a string.

Spoiler

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Globalization;

namespace SimpleWinForm
{
    class CallData
    {
        public int ContactsWaiting { get; set; }
        public int OldestContact { get; set; }
        public int TotalContacts { get; set; }
        public string ContactsHandled { get; set; }
        public int ContactsAbandoned { get; set; }
        public double PercentageHandled { get; set; }
    }

    public partial class Form1 : Form
    {
        DataGridView dgv = new DataGridView();

        public Form1()
        {
            InitializeComponent();

            var myData = new List<CallData>();
            myData.Add(new CallData());

            this.Controls.Add(dgv);
            dgv.Dock = DockStyle.Fill;
            dgv.DataSource = myData;

            int contactsHandled = Convert.ToInt32(dgv.Rows[0].Cells["ContactsHandled"].Value);
            int contactsAbandonded = (int) dgv.Rows[0].Cells["ContactsAbandoned"].Value;
            double percentageHandled = (double) dgv.Rows[0].Cells["PercentageHandled"].Value;
        }
    }
}

Cmore86, on 08 August 2012 - 12:09 PM, said:

Slightly off topic, how would I refresh the form.

I read that this.Refresh(); works but When I used it, it doesn't refresh.

Thanks again.

You are still thinking in terms of Web based apps where a refresh hits the server again. For Windows apps, refresh simply means redraw the screen. Most people extend refresh to also mean pull fresh data as well.

So you'll have to move your code that sets the values in the controls out of the constructor and into a function. Most people end up calling this function UpdateUi(), RefreshUi(), or LoadData(). Then on the events where you want to update the UI, you call the function.

[Cmore86]

This is the updated code:

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();
            //Get Castle Group data - queue =1
            GetUCCXData(1);
            //Get Connection data - quueue =2
            GetUCCXData(2);
        }
      
        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
                //double percentageHandled = 0;

                // 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
                    valCGTotal.Text = Convert.ToInt32(dvData.Rows[0].Cells[0].Value).ToString();
                    valCGWaiting.Text = Convert.ToInt32(dvData.Rows[0].Cells[1].Value).ToString();
                    valCGOldest.Text = Convert.ToInt32(dvData.Rows[0].Cells[2].Value).ToString();
                    valCGHandled.Text = Convert.ToInt32(dvData.Rows[0].Cells[3].Value).ToString();
                    valCGAbandoned.Text = Convert.ToInt32(dvData.Rows[0].Cells[4].Value).ToString();
                    valCGPercentHandled.Text = Convert.ToInt32(dvData.Rows[0].Cells[5].Value).ToString();
                    contactsHandled = Convert.ToInt32(dvData.Rows[0].Cells["scallshandled"].Value);
                    totalContacts = Convert.ToInt32(dvData.Rows[0].Cells["stotalcalls"].Value);
                    valCGPercentHandled.Text = CalculatePercentHandled(contactsHandled, totalContacts) + "%";
                    valCGOldest.Text = CalculateTime(oldestContact);
                    

                    
                }
                else if (queue == 2)
                {
                    //Elevator 
                    valCETotal.Text = Convert.ToString(dvData.Rows[0].Cells[0].Value);
                    valCEWaiting.Text = Convert.ToString(dvData.Rows[0].Cells[1].Value);
                    valCEOldest.Text = Convert.ToString(dvData.Rows[0].Cells[2].Value);
                    valCEHandled.Text = Convert.ToString(dvData.Rows[0].Cells[3].Value);
                    valCEAbandoned.Text = Convert.ToString(dvData.Rows[0].Cells[4].Value);
                    valCEPercentHandled.Text = Convert.ToString(dvData.Rows[0].Cells[5].Value);
                    contactsHandled = Convert.ToInt32(dvData.Rows[0].Cells["scallshandled"].Value);
                    totalContacts = Convert.ToInt32(dvData.Rows[0].Cells["stotalcalls"].Value);
                    valCEPercentHandled.Text = CalculatePercentHandled(contactsHandled, totalContacts) + "%";
                    valCEOldest.Text = CalculateTime(oldestContact);
                }

            }
            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 Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'dataSet1.rtcsqssummary' table. You can move, or remove it, as needed.
            this.rtcsqssummaryTableAdapter.Fill(this.dataSet1.rtcsqssummary);
            this.Update();
        }
        
              
    }
}

Everything is working fine, and thank you for the example. It is possible that the data from informix is defaulted as a string.
The last few things I need to figure out is adding a Blinking Number that changes color.
Have the form update in realtime.
And finish the time conversion calculation.

Thank you so very much for your assistance.

It's been a while since I coded, and nice to have some guidance to get that back from storage.

[Cmore86]

Skydiver, on 08 August 2012 - 12:23 PM, said:

Skydiver, on 08 August 2012 - 12:01 PM, said:

You'll have to use the Convert.ToInt32() instead of the cast, if the cast is failing.

Interesting... It looks like Informix is returning the SUM() as a string rather than as an int. In the spoiler is my small scale experiment trying to figure out why the cast would have failed, and I could only get it to fail and resort to using Convert.ToInt32() if the field was a string.

Spoiler

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Globalization;

namespace SimpleWinForm
{
    class CallData
    {
        public int ContactsWaiting { get; set; }
        public int OldestContact { get; set; }
        public int TotalContacts { get; set; }
        public string ContactsHandled { get; set; }
        public int ContactsAbandoned { get; set; }
        public double PercentageHandled { get; set; }
    }

    public partial class Form1 : Form
    {
        DataGridView dgv = new DataGridView();

        public Form1()
        {
            InitializeComponent();

            var myData = new List<CallData>();
            myData.Add(new CallData());

            this.Controls.Add(dgv);
            dgv.Dock = DockStyle.Fill;
            dgv.DataSource = myData;

            int contactsHandled = Convert.ToInt32(dgv.Rows[0].Cells["ContactsHandled"].Value);
            int contactsAbandonded = (int) dgv.Rows[0].Cells["ContactsAbandoned"].Value;
            double percentageHandled = (double) dgv.Rows[0].Cells["PercentageHandled"].Value;
        }
    }
}

Cmore86, on 08 August 2012 - 12:09 PM, said:

Slightly off topic, how would I refresh the form.

I read that this.Refresh(); works but When I used it, it doesn't refresh.

Thanks again.

You are still thinking in terms of Web based apps where a refresh hits the server again. For Windows apps, refresh simply means redraw the screen. Most people extend refresh to also mean pull fresh data as well.

So you'll have to move your code that sets the values in the controls out of the constructor and into a function. Most people end up calling this function UpdateUi(), RefreshUi(), or LoadData(). Then on the events where you want to update the UI, you call the function.

Wouldn't it be the case that I would simply have to call on

private void GetUCCXData(int queue)

Somewhere up in

public RSQ()

In order to "pull" the data again?

[Skydiver]

You can.

[Cmore86]

Skydiver, on 08 August 2012 - 01:32 PM, said:

You can.

Ok now it's just a matter on having it trigger that again.

Thank you.

[Cmore86]

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();
            

            

        }
      
        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 RSQ1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'dataSet1.rtcsqssummary' table. You can move, or remove it, as needed.
            this.rtcsqssummaryTableAdapter.Fill(this.dataSet1.rtcsqssummary);
            //Get Castle Group data - queue =1
            GetUCCXData(1);
            //Get Connection data - quueue =2
            GetUCCXData(2);
        }
        
              
    }
}

I cleaned up the code a bit and now should be pulling directly. I am stumped on how I can get it to grab the new data again. The functions each run only once.

[Skydiver]

Start simple first: Add a "Refresh" button and call your function again in the Click event handler.

If you are satisfied that your code can handle being called multiple times, move up to the next level. Add a Timer. (You'll want the one the System.Windows.Forms namespace as opposed to the one in the System.Timers namespace.) This time call your code on the timer Tick event handler.

(BTW, another timer to change the color of your text for is one way to get the blinking number that you were saying you needed to implement.)

I don't know if you're DBA will appreciate you pounding on his servers every X number of milliseconds. Maybe, yes: that's what DB's are supposed to do handle hundreds of queries a second. Maybe, no: although DB's are suppose to handle such a load, a better design maybe some kind of notification event from the DB. I don't know. That's for you to work out with the DBA.

[Cmore86]

Skydiver, on 08 August 2012 - 03:07 PM, said:

Start simple first: Add a "Refresh" button and call your function again in the Click event handler.

If you are satisfied that your code can handle being called multiple times, move up to the next level. Add a Timer. (You'll want the one the System.Windows.Forms namespace as opposed to the one in the System.Timers namespace.) This time call your code on the timer Tick event handler.

(BTW, another timer to change the color of your text for is one way to get the blinking number that you were saying you needed to implement.)

I don't know if you're DBA will appreciate you pounding on his servers every X number of milliseconds. Maybe, yes: that's what DB's are supposed to do handle hundreds of queries a second. Maybe, no: although DB's are suppose to handle such a load, a better design maybe some kind of notification event from the DB. I don't know. That's for you to work out with the DBA.

Thank you for your help and guidance. So far the app is working like the original with a few missing tweaks that can be done easily.

There is no real DBA here as I am pulling from the Cisco Servers database of our phone system.

Again thank you for your help.