Member Avatar for Mike Bishop

i am trying to create a dataset for use with crystal reports.

i have a dataset called dataset.xsd with two tables
BatchHeader and BatchDetail these have a relationship of BatchID on both tables.
(see picture1)

I have this working when there is only one table in the dataset with this code

Public Class HMRCReport
    Dim paramFields As New ParameterFields
    Dim paramField As New ParameterField
    Dim discreteVal As New ParameterDiscreteValue

    Public Sub HMRCReport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim rpt As New HRMCClaimRep() 'The report you created.
        Dim myConnection As SqlConnection
        Dim MyCommand As New SqlCommand()
        Dim myDA As New SqlDataAdapter()
        Dim myDS As New DataSet1() 'The DataSet you created.


        paramField.ParameterFieldName = "BatchClaimID"
        discreteVal.Value = HMRCClaim.LBClaimID.Text
        paramField.CurrentValues.Add(discreteVal)
        paramFields.Add(paramField)


        Try
            myConnection = New SqlConnection("Data Source=" & ServerV & ";Initial Catalog=" & databaseV & ";Persist Security Info=True;User ID=" & usernameV & ";Password=" & passwordV & "")
            MyCommand.Connection = myConnection
            MyCommand.CommandText = "SELECT * FROM BatchHeader"
            MyCommand.CommandType = CommandType.Text
            myDA.SelectCommand = MyCommand
            myDA.Fill(myDS, "BatchHeader")
            rpt.SetDataSource(myDS)

i have tried for weeks to get this to work with mulitpul tables but with no joy
i think i am close but just cant put my finger on what i am doing wrong.

myConnection = New SqlConnection("Data Source=" & ServerV & ";Initial Catalog=" & databaseV & ";Persist Security Info=True;User ID=" & usernameV & ";Password=" & passwordV & "")
            MyCommand.Connection = myConnection
            MyCommand.CommandText = "SELECT * FROM BatchHeader; Select * From BatchDetail"
            MyCommand.CommandType = CommandType.Text
            myDA.SelectCommand = MyCommand
            myDA.TableMappings.Add("table", "BatchHeader")
            myDA.TableMappings.Add("table1", "Batchdetail")
            myDA.Fill(myDS)

            rpt.SetDataSource(myDS)

this is my first major project in .Net i am so close please if anyone can help.
thanks in advanced

Picture1.jpg 82.77 KB
Edited by Ezzaral because: Fixed code tag.
  • 2 Contributors
  • 1 Reply
  • 389 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Momerath
Member Avatar for Momerath

Create two data adapters, one for each table. Set the CommandText to the appropriate command, call the Fill method on each DA, passing the DataSet and table name.

You could do it with one data adapter, but you still have to make two fill calls.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.