• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

Convert DB object to INT in C#

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
hej613
Asked:
hej613
  • 3
  • 3
1 Solution
 
Shaun KlineLead Software EngineerCommented:
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
 
hej613Author Commented:
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
 
Shaun KlineLead Software EngineerCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hej613Author Commented:
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
 
Shaun KlineLead Software EngineerCommented:
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
 
hej613Author Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now