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