Solved

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

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

635 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