Solved

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

Posted on 2006-07-21
4
355 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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

734 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