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

Can't get DefaultView.Find to work like I want it...

Hello experts,

I am working on a basic time card system that is linked to a trouble ticket submission system.  This morning i discovered (much to my chagrin) that there is some bug going on in the time card calculation method.  I am trying to use the DefaultView.Find method to check if an existing account code is already in a datatable.  If the account code is in the table, I merely want to add the accompanying value (number of hours) to the account in the Datatable instead of adding a whole new row.  To try and explain this better I want to see:

Acct1      2.5 hours
Acct2      3 Hours

Instead of

Acct1  2 hours
Acct2  3 hours
Acct1 .5 hours

I have some code which catches when there is a duplicate in the DB - but when I go to add it in the hours value is often put in the wrong area (generally column 0 row 0).  I would think that my code is totally wrong except sometimes - it works :/  Here is a snippet.

                //Call a stored procedure to add this Trouble Ticket to the database
      SqlCommand cmd = new SqlCommand("TURK.dbo.sp_GetHours", conn.Connection);
      cmd.CommandType = CommandType.StoredProcedure;
      SqlDataReader reader = null;
                        
      //Set up the DataTable with the display names for the Columns
      DataTable myTable = new DataTable("troubleTickets");
      myTable.Columns.Add("Grant Number");
      myTable.Columns.Add("Hours");
                        
      DataColumn[] colPk = new DataColumn[1];
      colPk[0] = myTable.Columns["Grant Number"];
      myTable.PrimaryKey = colPk;
      myTable.DefaultView.Sort = "Grant Number";
      
                ...            

while (reader.Read())
     {
                string[] myStringArray = new string[2];
      decimal totalHours = reader.GetDecimal(1);
      //Makes sure something is in the grants charged area
      if(reader.GetString(2).Length > 0)
     {
      //gets information for the specific trouble ticket bill time, total hours,
      //and percent charged that corresponds with the correct grant
      decimal percentCharged = (reader.GetDecimal(6));
      decimal billTime = CalculateHours(percentCharged,totalHours);
                                    
      //Set up the array to hold values to input into the dataTable
      //Pulls values from reader
      myStringArray[0] = reader.GetString(2);
      string showMe = reader.GetString(2);
      myStringArray[1] = System.Convert.ToString(billTime);
                                    
      //Check if row exists, if no row exists add a new row to the existing
      //DataTable, otherwise, add the extra time charged to the existing DataRow
      //"TimeCharged" column
      DataRow drFound;
                                          
      // Find the Row specified in txtFindArg
      drFound = myTable.Rows.Find(reader.GetString(2));
      if (drFound == null)
      {
            //No row with the same PK exists, add a new row to DT
            myTable.Rows.Add(myStringArray);
      }
      
else
      {
      //Otherwise, get the TimeCharged in the DataTable, and add it to
      //the TimeCharged in the reader
      int duplicateRow;
      decimal oldValue;
      decimal newValue;
                                                
      duplicateRow= myTable.DefaultView.Find(reader.GetString(2));
      //Grab the next three values for error checking - see what they are.
                string showMeHere = reader.GetString(2);
      string showMore = System.Convert.ToString(myTable.Rows[whoopie][0]);
      string evenMore = System.Convert.ToString(myTable.Rows[whoopie][1]);
      //end data checking
                oldValue = System.Convert.ToDecimal(myTable.Rows[duplicateRow][1]);
      newValue = System.Convert.ToDecimal(myStringArray[1]);
      newValue += oldValue;
      myTable.Rows[duplicateRow].BeginEdit();
      myTable.Rows[duplicateRow][1] = (System.Convert.ToString(newValue));
      myTable.Rows[duplicateRow].EndEdit();
      }                                    
      }

Sometimes the duplicateRow is the correct number, and adds the corresponding values accordingly.  Other times it is wrong and adds it to other columns (one of which is account code "none" - thank you users :P )

I will be glad to provide any more information you see fit.  Sorry for the long post wanted to be as specific as possible.
TIA
0
RuralInstitute
Asked:
RuralInstitute
  • 2
1 Solution
 
Rodney HelsensCommented:
why are you using the reader.GetString(2) and reader.GetDecimal(6) etc..? This sure makes it hard are hell to tell what we are looking at..

Have you seen this syntax reader["FieldName"]?

Sorry althought, I can't really help point out where this issue lies in your code, but it would seem that it should be possible to extract the data the way you want using GROUP BY and SUM in SQL server and save yourself alot of this jumping through hoops.


0
 
RuralInstituteAuthor Commented:
So summing is a good idea - don't know why I didn't do that a few months ago in the first place...I like to make things difficult i guess.

As for the GetString(2) etc, those are the corresponding columns in the current selection of the datareader.  GetString(2) would be the AccountCode to check against/add to the datatable.

Going to SQL Land right now.
0
 
Rodney HelsensCommented:
Well just a suggestion.. if you have trouble, you can get expert help over in the Sql Server area to get this data returned to you exactly as you want it...  

I guess that reader.getString() is just a preference, I guess i was just saying it's makes the code more readable when doing reader["AccountCode"] vs. reader.GetString(2)

good luck
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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