[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Dataset Grouping

Posted on 2004-11-02
9
Medium Priority
?
797 Views
Last Modified: 2008-09-12
I have some data loaded into a dataset.  I would like to create a new dataset based on the grouping of that data.

ie. Right now I have every record in the dataset (one field is date).  I want to group by day and sum some of my totaling values, so my new dataset will have one record per day.  

Is there anyway to do this?
0
Comment
Question by:Fraser_Admin
  • 4
  • 3
8 Comments
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 12474222
Is there anyway to do this?You are going to have to give a lot more detail then that.  What kind a dataset? flat file? XML? relational database? mySQL? SQLserver? Oracle?  What cscripting language you use.  A sample of what the data looks like.

Cd&
0
 
LVL 4

Expert Comment

by:jcrumble
ID: 12474629
Also,  would it be possible to group the data prior to loading it into your dataset?  If your data is coming from an RDBMS, then it's simply a matter of changing your SQL.  What purpose are you trying to accomplish in grouping your data after it's in a dataset/datatable and how much data are you talking about?  Depending on how much data you are pulling it might be easier (and more logical) to simply pass a variable to your data extraction process(SQL?) to tell you how to group the data and simply reload the dataset.

Jeff
0
 

Author Comment

by:Fraser_Admin
ID: 12475705
No I cannot group prior.  I could do the temporary table thing and group, but I thought there might be a way to group it in a dataset.  I'm using vb.net accessing sqlserver 2000.
0
Independent Software Vendors: 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!

 

Author Comment

by:Fraser_Admin
ID: 12475755
The problem is, I need to do updates based on the initial dataset.  These updates need to be done in an order since the update of the current record is based on the values of the last record, which is not possible in sqlserver.  I cannot specify an order by in an update statement, and I cannot guarentee the order that will be returned.

These operations are for a report I want to generate.  They are done on a temporary table.

So basically I have a stored proc that gets all the records (3 different selections) and put them all into a temporary table.  I then return that table (ordered) from the stored proc.  I load the data into a vb.net datatable.  I then perform some updates on these records (based on the last record).  I then want to group the data before displaying it on the report.

The grouping can be done by day, month or year.  I would rather not traverse all the records to do this, but it looks like I might need to.  
0
 

Author Comment

by:Fraser_Admin
ID: 12475768
Another question, can I insert the contents of a datatable into an sqlserver table?
0
 
LVL 4

Expert Comment

by:jcrumble
ID: 12475940
You can definitely insert a datatable into SQL Server,

Here's some very simple sample syntax for reference (inline not a stored procedure but you should get the jist):

private void InsertInfo() {
      SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
      myConnection.Open();
      SqlTransaction myTrans = myConnection.BeginTransaction();
      SqlCommand myCommand = new SqlCommand();
      myCommand.Connection = myConnection;
      myCommand.Transaction = myTrans;
      try {
      for(int i=dataSet1.Tables["RetainConts"].Rows.Count-1; i>-1; i--){
        DataRow thisRow = dataSet1.Tables["RetainConts"].Rows[i];
            myCommand.CommandText = " INSERT INTO Projects( " +
            " ProjectName, Description, City, State, TypeID, ProjectDate, ProjectLength1, ProjectLength2, ProjectLengthU, StatusID, ArrivalDate, ModifyUser, ModifyDate"+
            " ) VALUES ("+
            " @ProjectName, @Description, @City, @State, @TypeID, @ProjectDate, @ProjectLength1, @ProjectLength2, @ProjectLengthU, @StatusID, @ArrivalDate, @ModifyUser, @ModifyDate"+
            " )";
            myCommand.Parameters.Add(new SqlParameter("@ProjectName", SqlDbType.NVarChar, 255));
            myCommand.Parameters["@ProjectName"].Value = ProjectName.Text;
.....
            myCommand.ExecuteNonQuery();
          myTrans.Commit();
            Message.Text = "<b>Base Information Added</b><br />";
            Session["dsData"] = "";
      } catch (SqlException f) {
          myTrans.Rollback();
            if (f.Number == 2627)
            Message.Text = "ERROR: A record already exists with the same primary key<br /><hr noshade='noshade' size='1' width='100%' />";
            else
            Message.Text = "ERROR: Could not add record, please ensure the fields are correctly filled out " + f.Number +"<br /><hr noshade='noshade' size='1' width='100%' />";
      } finally {
            myConnection.Close();
      }
}



0
 
LVL 4

Accepted Solution

by:
jcrumble earned 1500 total points
ID: 12476005
Also,

Although I haven't had the need to sort a DataTable, You might try updating a value in a DataTable and then adding that to SQL Server in the order of your temp field.  Seems like a loop inside a loop (with an if statement) would do.  Anyway here's somemore simple synax for updating a row in a datatable you might find useful:

      dataSet1.Tables["RetainConts"].Rows[SelRow]["FirmName"] = FirmName.Text.ToString();
      dataSet1.Tables["RetainConts"].Rows[SelRow]["Title"] = Title.Text.ToString();
      dataSet1.Tables["RetainConts"].Rows[SelRow]["WebSite"] = WebSite.Text.ToString();
      dataSet1.Tables["RetainConts"].Rows[SelRow]["Address1"] = Address1.Text.ToString();
      dataSet1.Tables["RetainConts"].Rows[SelRow]["Address2"] = Address2.Text.ToString();
etc....

I also just realized that my example above was in C#.  Don't know if you can translate it or not but hopefully you still get the idea.  

Good Luck,
Jeff
0
 

Author Comment

by:Fraser_Admin
ID: 12476155
I get the idea, but I'm dealing with a temporary table, so after it is return from the sp, it no longer exists.
I'm looking to get out of traversing the whole table.  I thought briefly another table might help but it doesn't.  Basically what I need to do is further group this table and put it into another brand new table (sql server or vb.net datatable will do).
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
The viewer will learn how to count occurrences of each item in an array.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses
Course of the Month19 days, 14 hours left to enroll

872 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