getchildrows help

getchildrows help

• Markus B.

  This is what I have so far

  Dim XL As Microsoft.Office.Interop.Excel.Application

  Dim xlwb As Microsoft.Office.Interop.Excel.Workbook

  Dim iddatatables As DataTable = Me.RcsDataSet.Customers

  For Each row As DataRow In Me.RcsDataSet.Customers

  Me.InvoicesTableAdapter.Fill(RcsDataSet.Invoices)

  For Each crow As DataRow In Me.RcsDataSet.Invoices

  Me.InvoiceDetailsTableAdapter.Fill(RcsDataSet.InvoiceDetails)

  Dim xlsheet As Microsoft.Office.Interop.Excel.Worksheet

  XL = New Microsoft.Office.Interop.Excel.Application

  xlwb = XL.Workbooks.Open("C:\tryout.xls")

  xlsheet = xlwb.Worksheets(1)

  XL.Visible = True

  Dim inddatatables As DataTable = Me.RcsDataSet.InvoiceDetails

  Dim idx As Integer = 1

  For idx = 0 To iddatatables.Columns.Count - 1

  Dim arow As DataRow

  Dim rowidx As Integer = 18

  For Each arow In inddatatables.Rows

  xlsheet.Cells(rowidx, idx + 1) = arow(idx)

  rowidx = rowidx + 1

  Next

  Next

  Next

  Next

  This does open a new sheet for each row in invoices but unfortunately it prints everything in the invoicedetails table. (obviously) How could I code this by using the getchildrows method to fix this problem

  
  

• wsalomon

  Hi

  You simply need to have an active datarow in the parent Customers table and then call its GetChildRows method passing in a datarelation that describes to the method how to locate the child data. I've included a simple example of how you might do this (assuming the customer and invoice datatables are contained within the same dataset).

  Dim ds As DataSet = LoadDataSet()

  Dim pPK As DataColumn = ds.Tables("Customer").Columns("CustomerID")

  Dim cFK As DataColumn = ds.Tables("Invoices").Columns("CustomerID")

  Dim rel As New DataRelation("CustomerInvoices", pPK, cFK)

  For Each dr As DataRow In ds.Tables("Customer").Rows

  Dim drs() As DataRow = dr.GetChildRows(rel)

  If drs.Length > 0 Then

  End If

  Next

  Next

  Give me a shout if the above is not what you were looking for

  Richard

  
  

• Nima_DK

  Probably the simplest way is to churn out a csv file. Excel can read this format.
  
  Richard
  
  

• Bill Gates II

  Hi

  Enclosed is a function I wrote some time ago to turn a datatable into a csv. It's by no means a bullet-proof solution (the data cannot contain ' characters etc) but hopefully you can adapt it to meet your needs.

  Hope if helps

  Richard

  Private Function TryGenerateCSV(ByVal data As DataTable, ByVal targetPath As String) As Boolean

  If data Is Nothing Then Return False

  Dim sec As New System.Security.Permissions.FileIOPermission(Security.Permissions.FileIOPermissionAccess.Append Or _

  Security.Permissions.FileIOPermissionAccess.Write, targetPath)

  Try : sec.Demand()

  Catch ex As Security.SecurityException : Trace.WriteLine("TryGenerateCSV: Insufficient priviige to write to " & targetPath) : Return False

  End Try

  '' Build the item array format from the data row definition (types contained therein), double quoting strings where applicable

  Dim sb As New System.Text.StringBuilder

  Dim dblQuote As String = """"

  Dim strType As Type = GetType(String)

  Dim loopEnd As Integer = data.Columns.Count - 1

  For n As Integer = 0 To loopEnd Step 1

  If data.Columns(n).DataType Is strType Then

  If n = loopEnd Then

  sb.Append(String.Concat(dblQuote, "{", n.ToString, "}", dblQuote))

  Else

  sb.Append(String.Concat(dblQuote, "{", n.ToString, "}", dblQuote, ","))

  End If

  ElseIf n = loopEnd Then

  sb.Append(String.Concat("{", n.ToString, "}"))

  Else

  sb.Append(String.Concat("{", n.ToString, "},"))

  End If

  Next

  '' Ok .. grab the format and append the item array into the file

  Dim format As String = sb.ToString

  Using stream As New IO.StreamWriter(targetPath, True, System.Text.Encoding.ASCII)

  stream.AutoFlush = True

  For Each dr As DataRow In data.Rows

  stream.WriteLine(format, dr.ItemArray)

  Next

  End Using

  End Function

  
  

• Can-Ann

  That is very helpful, Thank you. Now if I understand correctly getchildrows returns an array of rows. Any idea how to send that array to excel

  
  

• Ion101

  Can you give me an example of how to send the array to a csv
  
• getchildrows help