Solved

Convert DB object to INT in C#

Posted on 2013-10-29
6
488 Views
Last Modified: 2013-10-29
There is a small program I'm working on trying to learn more C#... I have a section where I'm picking out "PRIME" pipe, I would like to add the weight up of all the prime pipe to come up with one number at the end... I dont know how to convert from an Object (When its pulled from the database) to an int where I could work with it.

I do know Icould do this with the Select statement by SUM, but I wanted to do it through C# programatically.... Here is what I have currently:

        private void Form1_Load(object sender, EventArgs e)
        {
            //connection string
            string connString = @"Data Source=STEVEN_LT\SQLEXPRESS;Initial Catalog=Stupp;Integrated Security=True";
            string sql = @"select PIPEID, THICKNESS, LENGTH, WEIGHT, CLASS from dbo.Pipeinfo where DATETIME like '10/17/2013%'";
            
            int primeCount = 0;
            int reworkCount= 0;
            int rejectCount = 0;
            int primeWeight = 0;

            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataReader rdr = cmd.ExecuteReader();

                do
                {
                    tb1.AppendText(rdr.GetName(0));
                    tb1.AppendText("\t");
                    tb1.AppendText(rdr.GetName(4));
                    tb1.AppendText("\n");


                    while (rdr.Read())
                    {
                        if (rdr[4].ToString() == "PRIME")
                        {
                            tb1.AppendText(rdr[0].ToString());
                            tb1.AppendText("\t");
                            tb1.AppendText(rdr[4].ToString());
                            tb1.AppendText("\n");
                            primeCount++;
   
                            //primeWeight = primeWeight +(int)rdr[3];
                            
                        }
                        else if (rdr[4].ToString() == "REWORK")
                        {
                            tb1.AppendText(rdr[0].ToString());
                            tb1.AppendText("\t");
                            tb1.AppendText(rdr[4].ToString());
                            tb1.AppendText("\n");
                            reworkCount++;
                        }
                        else if (rdr[4].ToString() == "REJECT")
                        {
                            tb1.AppendText(rdr[0].ToString());
                            tb1.AppendText("\t");
                            tb1.AppendText(rdr[4].ToString());
                            tb1.AppendText("\n");
                            rejectCount++;
                        }


                    }
                }
                while (rdr.NextResult());
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                conn.Close();
                lbl1.Text = primeCount.ToString();
                lbl2.Text = reworkCount.ToString();
                lbl3.Text = rejectCount.ToString();
            }
                    
            

        }

Open in new window


And what the finished form looks like:

Working Form
I have my prime weight commented out right now, but when I add it back it, the program runs but it stops after the first pipe that comes though.... Could someone take a look at this and 1)tell me how to accomplish what I'm looking for and 2) if I'm doing something thats really considered "bad" in c# through this?

Thanks,
0
Comment
Question by:hej613
  • 3
  • 3
6 Comments
 
LVL 25

Accepted Solution

by:
Shaun Kline earned 500 total points
Comment Utility
Here is an example from Microsoft's website to follow when you need additional details: http://msdn.microsoft.com/en-us/library/haa3afyz.aspx.

The part you are looking for comes after the while (reader.read()) line. Once you have read the line, you can access the individual items in the reader using various Get<Type> methods. In the Microsoft example, they are using "reader.GetInt32(0)", where the zero represents the column in the data reader.

You should also not us a "do while" loop without first checking that your reader has data. The reason is that a do while loop will always try to loop once. A "while(<condition>) {}" loop will loop only while the condition exists.
0
 
LVL 1

Author Comment

by:hej613
Comment Utility
Thank you shaun -

Instead of doing:

   do
                {
                    tb1.AppendText(rdr.GetName(0));
                    tb1.AppendText("\t");
                    tb1.AppendText(rdr.GetName(4));
                    tb1.AppendText("\n");


                    while (rdr.Read())
                    {

it would be better to change it to:

if(reader.hasrows)
  {
                    tb1.AppendText(rdr.GetName(0));
                    tb1.AppendText("\t");
                    tb1.AppendText(rdr.GetName(4));
                    tb1.AppendText("\n");


                    while (rdr.Read())

also, I just changed my PRIME section to include:

primeWeight = primeWeight+ rdr.getInt32(3);

and the form stops giving me data after one row (I dont get any errors, just no more results after one pipe)
0
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
Can you confirm for me that the lines you changed from your original post are 20 and 38?

Also, from your original posted code, you had been using the reader.NextResult, which is used when your SQL statement returns multiple record sets (i.e. multiple select statements). The SQL statement in the code you posted was only returning one record set. Was there more code that was not posted?

If you are doing this development in Visual Studio, have you attempted to step through the while statement? Is it possible that your Select is only returning one row? The changes you mentioned above should not have caused the loop to end abruptly without causing an exception. Is it possible that the returned weight is not numeric?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:hej613
Comment Utility
Shaun.

20 and 38 are correct those are the lines I changed, I also added an int primeWeight = 0 at the top.

The nextResult is not needed, I misunderstood the idea of it, I thought it would loop to the next record in the current record set, I see now that its looking for a new recordset (which does not exist)

I am using visual studio, and will try to step through it (Still new to all this) but my actual query never changes, so there are Multiple pipes, but for some reason when primeWeight = primeWeight + rdr.getInt32(3); it only returns one pipe... something breaks it out of the while there it appears because the bottom counts are showing 1 prime pipe, 0 reworked and 0 rejected, which means the code continues down to my lbl sections.....

(At least I assume)
0
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
In your Catch, add a second catch for all general errors. You are only trapping for SQL errors.

Something like this should work:
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
           catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }

Open in new window

0
 
LVL 1

Author Comment

by:hej613
Comment Utility
Ahhhh... Specified cast is not valid now pops up, so there is a problem converting my weight to an int... So back to square one... how do I take a value out of a database and make it something I can manipulate like this?

for every prime pipe I want to add up the weight of the pipe, so I can have a total weight of all my prime pipe for the day... Again, I know I can do it with a SUM SQL statement, but there has got to be a way to handle it in C#, eh?

I just noticed its stored in the database as a "varchar2"

Which is probably an issue....

That was it... If I change my SQL statement to cast(weight as int) I can manipulate it like I am trying to do!

Thank you for ALL of your help...
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now