DataSet Loop Question

[Elysium]

I'm not sure how to accomplish the following, so any help is much appreciated:

I have a table in MS Access that holds information about orders. What I want to do is loop through each record in the orders table, reading the value for the Quantity field. Then I'd like to add the current row of data to another table, creating copies of this row equating to the Quantity value. For example, if the current row has a Quantity of 10, then I need to add 9 copies of this record, along with the original record, to the new table. If the current row has a Quantity of 1, then I only want to add this row to the new table without any copies. Make sense?

My development environment is VB.Net.

Thanks!

Randy

 

[ThatRickGuy]

Something like this would probrably work:

private function UnstackRecords(dtSource as datatable) as datatable

  dim dtReturn as datatable = dtSource.clone

  dim dr as datarow
  dim i as integer

  for each dr in dtSource
    for i = 1 to dr("Quantity")
      dtReturn.import(dr)
      dtReturn.rows(dtreturn.rows.count-1)("Quantity")=1
    next
  next

  return dtReturn
end function

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Elysium]

Rick,

Could you explain what's happening in the For Loop?

Randy

 

[ThatRickGuy]

The outter most for loop will loop through every record of the source data table.

The inner most for loop will loop X times. where X is your quantity. So if the record from the source has a quantity of 10, the inner loop will run 10 times.

Inside the inner loop the code imports the record from the source table into the return table and then sets the quantity of the line to 1.

This would take:
Item Quantity
1225 3

and create a new table with:
Item Quantity
1225 1
1225 1
1225 1

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Elysium]

Excellent. Now how do I save the contents of the new table just created to a table in MS Access called "keyedImportSource"?

Randy

 

[ThatRickGuy]

Open a datatable for it and import the rows into it instead of the table in that function.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Elysium]

Is there a bulk insert or do I import one row at a time via a loop?

Randy

 

[ThatRickGuy]

So far as I know, you have to import one row at a time.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Elysium]

Rick,

Thanks for the patience. In order to open a datatable, I have to create a dataadapter, correct?

Randy

 

[ThatRickGuy]

Yup.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Elysium]

I'm still having trouble with this. Zero rows are being inserted into the table "keyedImportSource". I'll post the code here:

Public Function createWorldShipSource() As Boolean
 Dim oDBO As New clsDB_Operations
 Dim da As New OleDbDataAdapter("SELECT * FROM keyedImportSource", ConfigurationSettings.AppSettings("connectionString"))
 Dim ds As New DataSet
 Dim dtSource As New DataTable, dtDestination As New DataTable
 Dim dr As DataRow
 Dim recordCounter As Integer, recordsToAdd As Integer

   Try
      With oDBO
         dtSource = .returnDataTable
         da.Fill(ds, "keyedImportData")
         dtDestination = ds.Tables("keyedImportData")
         
         For Each dr In dtSource.Rows
            For recordCounter = 1 To dr("Quantity")
               dtDestination.ImportRow(dtSource.Rows(recordCounter))
               dtDestination.Rows(dtDestination.Rows.Count - 1)("Quantity") = 1
            Next
         Next
      End With
   Catch ex as Exception
      MsgBox(Err.Description)
   End Try
End Function

    Public Function returnDataTable() As DataTable
        Dim dbConnectionString As String = ConfigurationSettings.AppSettings("connectionString")
        Dim SQL As String = "SELECT * FROM tempKIS_1"
        Dim da As New OleDbDataAdapter(SQL, dbConnectionString)
        Dim ds As New DataSet

        da.Fill(ds, "tempKIS_1")
        returnDataTable = ds.Tables("tempKIS_1")

    End Function

Randy

 

[ThatRickGuy]

put a break point on the first for statement. See what the dtSource.rows.count is. If it is 0, then there are no rows to import because there is no data in your source table.

If that looks good, put a break on the 2nd for statement. See what dr("Quantity") is returning. It may be a good idea to explicitly cast the value to integer (with CINT or ParseInt). If dr("Quantity") (or CINT(dr("Quantity")) ParseInt(dr("Quantity")) ) is returning 0 then you may want to look at the data and make sure that the quantity column it properly filled.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Elysium]

dtSource.Rows.Count = 24
CINT(dr("Quantity")) is returning the correct values

No other errors are coming back. However, the destination table still is empty.

Any other suggestions?

Randy

 

[ThatRickGuy]

ahh, are you doing a dtDestination.update err .save (what ever that method is) after you load all the rows?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Elysium]

No. Should I?

Randy

 

[ThatRickGuy]

Yeah, until you do the .update (err .save) the changes are only stored locally in memory. Using .update/.save will send the updates to the database.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.