Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to Duplicate Records in a Datagrid based on the quantity field

Posted on 2006-07-21
4
Medium Priority
?
362 Views
Last Modified: 2012-08-14
I have an ASP.NET/VB.NET/MS SQL Web Application.
I am trying to do the opposite of a DISTINCT keyword.  
Is there a way, via ADO .NET, to explode records that have a quantity field (with qty > 1).  
If the quantity > 1 then I want to duplicate an exact copy of the record, up to the number in the quantity field, and change the quantity for each record to 1.  The records will be duplicated within the datagrid based on the quantity;
I do not wat to save any record into database. I just want to display them in a Datagrid exploded by quantity.
I know in SQL it's pretty much impossible to do it. But I know it possible to implement the logic from code behind, please help.

Just t be more explicit here's the Products table structure.

Products Table

Name      Qty
----      -------
Dress     3
Shirt      2
Shoes    4


Using ADO .NET DataSet I would like to display the products items  to end up like this in a datagrid:

dgPrducts
-----------

Dress     1
Dress     1
Dress     1
Shirt      1
Shirt      1
Shoes    1
Shoes    1
Shoes    1
Shoes    1


Thanks
0
Comment
Question by:yaney00074
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 17

Expert Comment

by:ZeonFlash
ID: 17158800
Where dtFromDB is the DataTable containing your original data (Dress 3, Shirt 2, etc):

   Dim dtFromDB As New DataTable
   Dim dtExplode As DataTable
   Dim dr As DataRow
   Dim i As Integer = 0

   dtExplode = dtFromDB.Clone

   For Each row As DataRow In dtFromDB.Rows
       For i = 0 To row.Item("qty") - 1
           dr = dtExplode.NewRow
           dr.Item("qty") = 1
           dr.Item("name") = row.Item("name")
           dtExplode.Rows.Add(dr)
       Next
   Next

   dgProducts.DataSource = dtExplode
0
 

Author Comment

by:yaney00074
ID: 17159849
Zeon,

Thanks for your feedback.

Can you explain in  details (if you don't mind)  what's happening; or in other words how the logic works

I mean, from the DataSet (let's call it dsProduvts)
0
 
LVL 17

Accepted Solution

by:
ZeonFlash earned 2000 total points
ID: 17160218
Sure thing.  Your DataSet (dsProducts) gets filled by whatever SQL adapter you're using.  In all actuality, it creates a new DataTable (dsProducts.Tables(0), which I called dtFromDB) and fills the table.  In the code above, you can replace all instances of dtFromDB with dsProducts.Tables(0).

Once the DataTable is filled, we create a new DataTable called dtExplode, which is going to be where we "explode" the contents of the Prodcuts data.  Since the exact same columns are going to be used in both DataTables, we can simply call the .Clone method to copy over the structure onto dtExplode.  

The first For loop has us looping through each Product, i.e. Dress 3, Shirt 2, etc.  The inner For loop is set to repeat the Quantity of the current item.  So for Dress 3, it will loop 3 times....Shirt 2 will loop 2 times.  

Inside that For loop, a new DataRow for the dtExplode table is created.  This will be a single quantity row for the item, so we set the Qty to 1 and take the name of the Product from the current product we're looping through.  This means that since we're looping "Dress 3" a total of 3 times, we will insert into dtExplode "name: Dress, qty: 1" a total of 3 times.  

After all the looping gets done, the newly filled dtExplode DataTable is bound to the DataGrid, and everything is merry :).  
0
 

Author Comment

by:yaney00074
ID: 17164178
Zeon,

I forgot to ask you something about this quote:

>>and TAKE THE NAME OF THE PRODUCT from the current product we're looping through.

I would like the entire row to be with all the fields it contains to be repeated (or exploded)

Do I need to select the entire row columns (ProductID, Name, Color, Size, Qty, etc...) or I just need to "select" the Name field....?

Thanks
0

Featured Post

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!

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 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