Filling a Dataset using sql at runtime in vb.net

Posted 29 November 2012 - 06:52 AM

Dear All,

I have a dataset to create crystal report.
The data I have used is depends on the selection of database(3nos) which contains same tables and columns and keys.
So I have created a dataset thru designer in vb 2005 and add tables like the database to load the data thru sql statement with parameter using access database. The sql statement works fine.

The Sql statement is under the code section

 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.RCNo, Customers.SurveyNo, Trucks.TruckNo, Trucks.License," & _
            " Trucks.ValidTill, Godown.LicenseNo, Godown.Village, Godown.Taluk, Units.UnitSName FROM Units INNER JOIN" & _
            " (Trucks INNER JOIN (Products INNER JOIN (Godown INNER JOIN ((Customers INNER JOIN Delivery ON" & _
            " Customers.CustID=Delivery.CustID) INNER JOIN (Brands INNER JOIN OPDDetails ON Brands.BrID=OPDDetails.BrandID)" & _
            " ON Delivery.DelID=OPDDetails.DelID) ON Godown.GodID=OPDDetails.GID) ON Products.ProdID=Brands.ProdID) ON" & _
            " Trucks.TruckID=Delivery.TruckID) ON Units.UnitID=Products.UnitID WHERE Delivery.DelID = @DID AND OPDDetails.NetQty >0"
            Dim daDelivery As New OleDb.OleDbDataAdapter(sql1, con.Conn)
            daDelivery.SelectCommand.Parameters.AddWithValue("@DID", Me.txtID.Text)
            Dim dsDelivery As New DataSet
            dsDelivery.Clear()
            daDelivery.Fill(dsDelivery, "Delivery")

How can i load the data in the dataset's virtual table.
I have used a connection class which defines which database should be load

Dim FilePath As String = "D:\ESoft\" & frmMain.ToolStripStatuslblGodown.Text & ".mdb" & ";Jet OLEDB:Database Password= admin"
    Public ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & FilePath
    Public Conn As New OleDb.OleDbConnection(ConnString)

Any suggestion would be appreciated