Solved

Convert DB object to INT in C#

Posted on 2013-10-29
6
490 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 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 39608446
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
ID: 39608486
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 26

Expert Comment

by:Shaun Kline
ID: 39608608
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:hej613
ID: 39608635
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 26

Expert Comment

by:Shaun Kline
ID: 39608860
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
ID: 39608916
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A simple description of email encryption using a secure portal service. This is one of the choices offered by The Email Laundry for email encryption. The other choices are pdf encryption which creates an encrypted pdf of your email and any attachmen…

919 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

17 Experts available now in Live!

Get 1:1 Help Now