Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2006-07-21
4
Medium Priority
?
374 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 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …

606 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