Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Encryption Decryption in Oracle 12 102
How to open a link from vb.net app 3 21
VB.Net - Oracle BulkCopy from CSV Date Format 7 44
Saveas need to save a copy 16 26
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…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now