creating dataset from 2 database based on the criteria

[vijaycare]

Hi All,

I am working for a database solution using vs 2005, and it is in final stage for reporting.
I have 3 access databases. One is for selecting godown. And the remaining is godownwise databases.
The godownwise databases having same structure (Tables and relationship but the data may be differ)

Actually the user work thru selection on the godown (i.e: first database named Comp_God.mdb)
After the users selection my application goes thru according to the godown number (i.e: Database 50000, 60000 etc) according to the selection of the first database of comp_god.

What my problem is actually I know how to work with a single database for reporting
But my present situation is to select the report data based on the users current godown.

Please guide me how to create it. The following code is working upto saving the data to the tables.
how to get the dataset data into crystal report?

Any help would be appreciated.

Dim con As New ClassConnect
        If con.Conn.State = ConnectionState.Closed Then con.Conn.Open()

        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand
        Dim cmd1 As OleDb.OleDbCommand = New OleDb.OleDbCommand

        cmd.Connection = con.Conn
        cmd1.Connection = con.Conn



        cmd.CommandText = "Insert into Delivery" & _
       "(DelID, DelDate, CustID, MatID, TruckID, Remarks, InvAmt, LoginUser, LUpdate, MUser, MDate)" & _
       "Values (@DelID, @DelDate, @CID, @MID, @TID, @Rem, @InvAmt, @LUser, @LUpdate, @MUser, @MDate)"

        cmd1.CommandText = "Insert into OPDDetails" & _
        "(OPDDate, DelID, SID, BrandID, BatchNo, BatchDate, UnitsPerPack, Quantity, NetQty, Rate, Amount, LoginUser, LUpdate, MUser, MDate, Packs, LPacks)" & _
        "Values (@OPDate, @DelID, @SID, @BID, @Batch, @BDate, @Units, @Qty, @NQty, @Rate, @Amt, @LUser, @UDate, @MUser, @MDate, @Packs, @LPacks)"

        cmd.Parameters.AddWithValue("@DelID", Me.txtID.Text)
        cmd.Parameters.AddWithValue("@DelDate", Me.dtpDDate.Value.Date)
        cmd.Parameters.AddWithValue("@CID", Me.cboCustomer.SelectedValue)
        cmd.Parameters.AddWithValue("@MID", Me.cboMat.SelectedValue)
        cmd.Parameters.AddWithValue("@TID", Me.cboTruck.SelectedValue)
        cmd.Parameters.AddWithValue("@Rem", Me.txtRemarks.Text)
        cmd.Parameters.AddWithValue("@InvAmt", Me.txtGrAmt.Text)
        cmd.Parameters.AddWithValue("@LUser", Me.ToolStripStatuslblUser.Text)
        cmd.Parameters.AddWithValue("@UDate", DateTime.Now.ToString)
        cmd.Parameters.AddWithValue("@MUser", Me.ToolStripStatuslblUser.Text)
        cmd.Parameters.AddWithValue("@MDate", DateTime.Now.ToString)
        cmd.ExecuteNonQuery()

        If dgvDetails.Rows.Count > -1 Then

            For i As Integer = 0 To dgvDetails.Rows.Count - 1

                cmd1.Parameters.AddWithValue("@OPDDate", Me.dtpDDate.Value.Date)
                cmd1.Parameters.AddWithValue("@DelID", Me.txtID.Text)
                cmd1.Parameters.AddWithValue("@SID", Me.dgvDetails.Rows(i).Cells(0).Value)
                cmd1.Parameters.AddWithValue("@BID", Me.dgvDetails.Rows(i).Cells(1).Value)
                cmd1.Parameters.AddWithValue("@Batch", Me.dgvDetails.Rows(i).Cells(3).Value)
                cmd1.Parameters.AddWithValue("@BDate", Me.dgvDetails.Rows(i).Cells(4).Value)
                cmd1.Parameters.AddWithValue("@Units", Me.dgvDetails.Rows(i).Cells(7).Value)
                cmd1.Parameters.AddWithValue("@Qty", Me.dgvDetails.Rows(i).Cells(8).Value)
                cmd1.Parameters.AddWithValue("@NQty", Me.dgvDetails.Rows(i).Cells(8).Value)
                cmd1.Parameters.AddWithValue("@Rate", Me.dgvDetails.Rows(i).Cells(9).Value)
                cmd1.Parameters.AddWithValue("@Amt", Me.dgvDetails.Rows(i).Cells(10).Value)
                cmd1.Parameters.AddWithValue("@LUser", Me.ToolStripStatuslblUser.Text)
                cmd1.Parameters.AddWithValue("@UDate", DateTime.Now.ToString)
                cmd1.Parameters.AddWithValue("@MUser", Me.ToolStripStatuslblUser.Text)
                cmd1.Parameters.AddWithValue("@MDate", DateTime.Now.ToString)
                cmd1.Parameters.AddWithValue("@Packs", Me.dgvDetails.Rows(i).Cells(5).Value)
                cmd1.Parameters.AddWithValue("@LPacks", Me.dgvDetails.Rows(i).Cells(6).Value)

                cmd1.ExecuteNonQuery()
                cmd1.Parameters.Clear()
            Next

        End If

        MessageBox.Show("Delivery Saved Successfully!" & vbCrLf & "    Do you want to print?", msgcap, MessageBoxButtons.YesNo, MessageBoxIcon.None)
        If Me.DialogResult = System.Windows.Forms.DialogResult.Yes Then

            Dim conection As New ClassConnectGodown
            If con.Conn.State = ConnectionState.Closed Then con.Conn.Open()

            Dim cmd5 As OleDb.OleDbCommand
            cmd5 = New OleDb.OleDbCommand("Select LicenseNo, ValidTill, Village, Taluk, District from Godown Where GodSName = @GID", con.Conn)
            cmd5.Parameters.AddWithValue("@GID", Me.ToolStripStatuslblGodown.Text)
            Dim da5 As New OleDb.OleDbDataAdapter
            Dim ds5 As New DataSet
            da5.Fill(ds5, "Godown")

            conection.Conn.Close()
            conection.Conn.Dispose()
            conection.Conn = Nothing

            'Setting report path
            Dim ReportPath As String = "D:\ESoft\Reports"

            'Declaring report document
            Dim rpt As New ReportDocument 'The report you created
            Dim rpt1 As New ReportDocument

            Dim cmd2 As New OleDb.OleDbCommand  'Set command object
            Dim cmd3 As New OleDb.OleDbCommand

            Dim da As New OleDb.OleDbDataAdapter 'Set data adaptor
            Dim da1 As New OleDb.OleDbDataAdapter

            Dim ds As New DataSet 'The DataSet you created.
            Dim ds1 As New DataSet

            rpt.Load(ReportPath & "\DeliveryChallan.rpt")
            rpt1.Load(ReportPath & "\MatForm.rpt")

            Try
                cmd2.Connection = con.Conn
                cmd2.CommandText = "SELECT Delivery.DelID, Delivery.DelDate, OPDDetails.BatchNo, OPDDetails.BatchDate," & _
                " OPDDetails.Packs, OPDDetails.LPacks, OPDDetails.NetQty, Products.ProdName, Brands.BrClass, Brands.BrDivision," & _
                " Customers.CustName, Customers.Village, Customers.Taluk, Customers.RCNo, Customers.SurveyNo, Trucks.TruckNo," & _
                " Trucks.License, Trucks.ValidTill FROM Trucks INNER JOIN (Customers INNER JOIN (Products INNER JOIN" & _
                " (Brands INNER JOIN (Delivery INNER JOIN OPDDetails ON Delivery.DelID = OPDDetails.DelID) ON" & _
                " Brands.BrID = OPDDetails.BrandID) ON Products.ProdID = Brands.ProdID) ON Customers.CustID = Delivery.CustID)" & _
                " ON Trucks.TruckID = Delivery.TruckID WHERE Delivery.DelID = @DID AND OPDDetails.NetQty >0"
                cmd2.Parameters.AddWithValue("@DID", Me.txtID.Text)
                da.Fill(ds, "Delivery")
                rpt.SetDataSource(ds)
                frmViewDelivery.CRVDC.ReportSource = rpt

                cmd3.Connection = con.Conn
                cmd3.CommandText = "SELECT OPDDetails.DelID, OPDDetails.OPDDate, OPDDetails.NetQty, Units.UnitSName," & _
                " Products.ProdName, MineMat.MatName, Customers.Village, Customers.RCNo, Customers.SurveyNo" & _
                " FROM Customers INNER JOIN (MineMat INNER JOIN (Delivery INNER JOIN (Units INNER JOIN (Products INNER JOIN" & _
                " (Brands INNER JOIN OPDDetails ON Brands.BrID = OPDDetails.BrandID) ON Products.ProdID = Brands.ProdID) ON" & _
                " Units.UnitID = Products.UnitID) ON Delivery.DelID = OPDDetails.DelID) ON MineMat.MatID = Delivery.MatID) ON" & _
                " Customers.CustID = Delivery.CustID WHERE OPDDetails.DelID = @DID AND OPDDetails.NetQty >0"
                cmd3.Parameters.AddWithValue("@DID", Me.txtID.Text)
                da1.Fill(ds1, "MineMat")
                rpt1.SetDataSource(ds1)
                frmViewDelivery.CRVMat.ReportSource = rpt1

            Catch Excep As Exception
                MessageBox.Show(Excep.Message, msgcap, MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

[vijaycare]

After trying a lot i have changed the code slightly and it shows that "The report has no tables. Please help......

` Dim ReportPath As String = "D:\Explosive Soft\Explosoft\Explosoft"

    'Declaring report document
    Dim rpt1 As New ReportDocument
    Dim rpt2 As New ReportDocument

    rpt1.Load(ReportPath & "\DeliveryChallan.rpt")
    rpt2.Load(ReportPath & "\MatForm.rpt")



    Try

        'Creating report
        'and get the godown details as a dataset
        Dim conection As New ClassConnectGodown
        If conection.Conn.State = ConnectionState.Closed Then conection.Conn.Open()

        Dim sql As String = "Select LicenseNo, ValidTill, Village, Taluk, District from Godown Where GodSName = @GID"
        Dim da As New OleDb.OleDbDataAdapter(sql, conection.Conn)
        da.SelectCommand.Parameters.AddWithValue("@GID", frmMain.ToolStripStatuslblGodown.Text)
        Dim ds As New DataSet
        Dim dv As New DataView
        Dim dt As New DataTable
        ds.Clear()
        da.Fill(ds, "Godown")
        dv = New DataView(ds.Tables(0))
        dt = dv.ToTable


        conection.Conn.Close()
        conection.Conn.Dispose()
        conection.Conn = Nothing



        '-------------------------------

        Dim ID As Integer
        ID = frmDelivery.txtID.Text

        Dim con As New ClassConnect
        If con.Conn.State = ConnectionState.Closed Then con.Conn.Open()




        Dim sql1 As String = "SELECT Delivery.DelID, Delivery.DelDate, OPDDetails.BatchNo, OPDDetails.BatchDate," & _
            " OPDDetails.Packs, OPDDetails.LPacks, OPDDetails.NetQty, Products.ProdName, Brands.BrClass, Brands.BrDivision," & _
            " Customers.CustName, Customers.Village, Customers.Taluk, Customers.RCNo, Customers.SurveyNo, Trucks.TruckNo," & _
            " Trucks.License, Trucks.ValidTill FROM Trucks INNER JOIN (Customers INNER JOIN (Products INNER JOIN" & _
            " (Brands INNER JOIN (Delivery INNER JOIN OPDDetails ON Delivery.DelID = OPDDetails.DelID) ON" & _
            " Brands.BrID = OPDDetails.BrandID) ON Products.ProdID = Brands.ProdID) ON Customers.CustID = Delivery.CustID)" & _
            " ON Trucks.TruckID = Delivery.TruckID WHERE Delivery.DelID = @DID AND OPDDetails.NetQty >0"
        Dim da1 As New OleDb.OleDbDataAdapter(sql1, con.Conn)
        da1.SelectCommand.Parameters.AddWithValue("@DID", ID)
        Dim ds1 As New DataSet
        'Dim dv1 As New DataView
        'Dim dt1 As New DataTable
        ds1.Clear()
        da1.Fill(ds1, "Delivery")
        'dv1 = New DataView(ds1.Tables(0))
        'dt1 = dv1.ToTable

        rpt1.SetDataSource(ds1.Tables(0))
        Me.CRVDC.ReportSource = rpt1




        Dim sql2 As String = "SELECT OPDDetails.DelID, OPDDetails.OPDDate, OPDDetails.NetQty, Units.UnitSName," & _
            " Products.ProdName, MineMat.MatName, Customers.Village, Customers.RCNo, Customers.SurveyNo" & _
            " FROM Customers INNER JOIN (MineMat INNER JOIN (Delivery INNER JOIN (Units INNER JOIN (Products INNER JOIN" & _
            " (Brands INNER JOIN OPDDetails ON Brands.BrID = OPDDetails.BrandID) ON Products.ProdID = Brands.ProdID) ON" & _
            " Units.UnitID = Products.UnitID) ON Delivery.DelID = OPDDetails.DelID) ON MineMat.MatID = Delivery.MatID) ON" & _
            " Customers.CustID = Delivery.CustID WHERE OPDDetails.DelID = @DID AND OPDDetails.NetQty >0"
        Dim da2 As New OleDb.OleDbDataAdapter(sql2, con.Conn)
        da2.SelectCommand.Parameters.AddWithValue("@DID", ID)
        Dim ds2 As New DataSet
        'Dim dv2 As New DataView
        'Dim dt2 As New DataTable
        ds2.Clear()
        da2.Fill(ds2, "MineMat")
        'dv2 = New DataView(ds2.Tables(0))
        'dt2 = dv2.ToTable

        rpt2.SetDataSource(ds2.Tables(0))
        Me.CRVMat.ReportSource = rpt2





        con.Conn.Close()
        con.Conn.Dispose()
        con.Conn = Nothing



    Catch Excep As Exception
        MessageBox.Show(Excep.Message, msgcap, MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

`