locked
Populate Crystal Report from SQL DB RRS feed

  • Question

  • Hello,

    I have a little problem, I need a fill a crystal report from SQL table

    what i need exactly is to display the rows that have a specific value

    let's say i have a table with columns(ID, Name, Credits, state) 

    and i need to display only the rows that have State have value"Active" in "State"  column

    the query is 

    Select*
    
    from table
    
    WHERE state =@State

    and my code to fill the data is 

    Dim AC as string = "Active"
    tableAdapter.fill(Me.DataSet.Temp_A, AC)

    So how to fill this specific data in crystal report while my table might have rows with state value is "Nonactive" which not need in my report


    • Edited by AbanoubZak Thursday, June 20, 2019 4:26 PM
    Thursday, June 20, 2019 4:25 PM

Answers

All replies

  • My Crystal Report experiences has been executing a Stored Procedure and binding the results to a report. IMO, that's what you should be doing

    https://www.c-sharpcorner.com/blogs/crystal-report-using-stored-procedure1

    Thursday, June 20, 2019 5:50 PM
  • When working with TableAdapter component the standard Fill method returns all columns, the proper way to perform custom SELECT statements is to create a overloaded Fill method by right clicking on the table in the designer file and adding a new SELECT...WHERE statement.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, June 20, 2019 6:11 PM
  • When working with TableAdapter component the standard Fill method returns all columns, the proper way to perform custom SELECT statements is to create a overloaded Fill method by right clicking on the table in the designer file and adding a new SELECT...WHERE statement.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    I have already recreated the fill method to work how i need it by the next query

    Select*
    
    from table
    
    WHERE state =@State

    I made the condition Where State=@State

    what i need now is the code to pass this information to the report by a press of button

    Friday, June 21, 2019 6:20 AM
  • Hello,

    Look at the following links in order they appear

    https://docs.microsoft.com/en-us/previous-versions/cc411355(v%3Dvs.90)

    https://social.msdn.microsoft.com/Forums/en-US/91495fc3-4fcf-4390-983b-9d89835c3ebd/tableadapter-and-crystal-report?forum=vscrystalreports


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by AbanoubZak Saturday, June 22, 2019 9:06 AM
    Friday, June 21, 2019 10:07 AM
  • Hi ,

    Take  a look at this example ,maybe it helps you.

      Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
            Try
                Dim rpt As New rptService 'The report you created.
                Dim myConnection As SqlConnection
                Dim MyCommand, MyCommand1 As New SqlCommand()
                Dim myDA, myDA1 As New SqlDataAdapter()
                Dim myDS As New DataSet 'The DataSet you created.
                myConnection = New SqlConnection(cs)
                MyCommand.Connection = myConnection
                MyCommand1.Connection = myConnection
                MyCommand.CommandText = "SELECT * FROM Service INNER JOIN Customer ON Service.CustomerID = Customer.ID INNER JOIN InvoiceInfo1 ON Service.S_ID = InvoiceInfo1.ServiceID INNER JOIN Invoice1_Product ON InvoiceInfo1.Inv_ID = Invoice1_Product.InvoiceID where InvoiceInfo1.InvoiceDate between @d1 and @d2 order by invoiceDate"
                MyCommand.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
                MyCommand.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
                MyCommand1.CommandText = "SELECT * from Company"
                MyCommand.CommandType = CommandType.Text
                MyCommand1.CommandType = CommandType.Text
                myDA.SelectCommand = MyCommand
                myDA1.SelectCommand = MyCommand1
                myDA.Fill(myDS, "InvoiceInfo1")
                myDA.Fill(myDS, "Invoice1_Product")
                myDA.Fill(myDS, "Service")
                myDA.Fill(myDS, "Customer")
                myDA1.Fill(myDS, "Company")
                rpt.SetDataSource(myDS)
                rpt.SetParameterValue("p1", dtpDateFrom.Value.Date)
                rpt.SetParameterValue("p2", dtpDateTo.Value.Date)
                rpt.SetParameterValue("p3", Today)
                frmReport.CrystalReportViewer1.ReportSource = rpt
                frmReport.ShowDialog()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub

    Best Regards.


    • Edited by Bajtitou Friday, June 21, 2019 11:29 AM
    Friday, June 21, 2019 11:28 AM