EDN Admin
Well-known member
Hi All,
I am working for a database solution using vs 2005, and it is in final stage for reporting.<br/>
I have 3 access databases. One is for selecting godown. And the remaining is godownwise databases.<br/>
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)<br/>
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<br/>
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.<br/>
how to get the dataset data into crystal report?
Any help would be appreciated.
<pre class="prettyprint lang-vb 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[/code]
<br/>
View the full article
I am working for a database solution using vs 2005, and it is in final stage for reporting.<br/>
I have 3 access databases. One is for selecting godown. And the remaining is godownwise databases.<br/>
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)<br/>
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<br/>
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.<br/>
how to get the dataset data into crystal report?
Any help would be appreciated.
<pre class="prettyprint lang-vb 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[/code]
<br/>
View the full article