Using dataset to populate Crystal Reports parameters

[Bluejay07]

Hello,

I am using VS 2005 and SQL Server 2005.

I have a dataset in which I am using to populate parameter fields in Crystal Reports. I want to loop through the dataset and display each row of information on the report.

I am encountering two problems. My first problem is that when I try the loop, the previous row is overwritten with the current row.

Here is my code. I am only referencing 2 rows for testing purposes.

Private Sub PrintPreview()

   Dim l_daPrint As New SqlDataAdapter
   Dim l_dsPrint As New DataSet
   Dim rptDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument
   Dim frmView As New frmViewer
   Dim strReportName As String = "ListingOfModels"
   Dim strReportPath As String

   strReportPath = My.Application.Info.DirectoryPath & "\" & strReportName & ".rpt"

   l_daPrint.SelectCommand = New SqlCommand
   With l_daPrint.SelectCommand
      .Connection = acCONN
      .CommandType = CommandType.Text
      .CommandText = "SELECT * from cusform WHERE  (RefNum IN ('KIKU-120602-3', 'KIKU-120602-3.'))"
   End With

   l_dsPrint.Clear()
   l_daPrint.Fill(l_dsPrint, "ModelPrintInfo")

   rptDocument.Load(strReportPath)
   rptDocument.SetDataSource(l_dsPrint)

   For Each Row As DataRow In l_dsPrint.Tables(0).Rows
      rptDocument.SetParameterValue("pDate", Row.Item("DT"))
      rptDocument.SetParameterValue("pImpNum", Row.Item("ImpNum"))
      rptDocument.SetParameterValue("pImpName", Row.Item("ImpName"))
      rptDocument.SetParameterValue("pRefNum", Row.Item("RefNum"))
      rptDocument.SetParameterValue("pArtNum", Row.Item("NO_ARTS"))
   Next

   frmView.CRViewer.ReportSource = rptDocument
   frmView.WindowState = FormWindowState.Maximized
   frmView.Show()

   End Sub

My second problem is that my crystal report file is stored in the project directory with the rest of my forms, yet the directory searches in the project\bin\debug directory. How can I search the proper directory?

Can anyone help with these two situations or provide me with a better method to pass values to crystal reports.

Thanks.

 

[Qamgine]

Okay, you are using parameters as fields in your report. Ideally, you want to create a stored procedure with input variables and then link the fields in the report. Then you can run the report based on the input parameters for your stored procedure.

Example:

I have a report that links to the following SQL stored procedure: (NOTE: You would want CREATE PROCEDURE to create it. ALTER PROCEDURE is for when it already exists.)

ALTER PROCEDURE dbo.sp_GETEBS_EMAIL 
	(
	@E_EMAILID INT = NULL,
	@FACILITYID INT = NULL
	)
AS
	SELECT E_EMAILID, FACILITYID, DISPLAYNAME, EMAIL, ACTIVE
	FROM   EBS_EMAIL
	WHERE  E_EMAILID=COALESCE(@E_EMAILID,E_EMAILID) AND FACILITYID=COALESCE(@FACILITYID,FACILITYID)

My input parameters are @E_EMAILID and @FACILITYID. These are the paramters that I have VB.NET update when I call the report to load. (Note: The coalesce is so that if I wanted every row in the table, I only need to set the parameters to NULL)

On the report, I put the fields DisplayName and Email. I have the report grouping by FacilityID and E_EmailID.

Now, in VB.NET, I load the report which I have set in a specific location on our server not in the users application path. After I load the report, I set the parameters @E_MAILID and @FACILTYID based on the user's response to the interface.

rptDoc.Load(<path to report> i.e.: "\\server\folder\reportname.rpt")
rptDoc.SetParameterValue("@E_EMAILID", NOTHING) ' I want all emails for a specific Facility.
rptDoc.SetParameterValue("@FACILITYID", selected_FacilityID) 'This is stored by the user interface.
crView.ReportSource = rptDoc

Viola' You get a nice report with which you can get varying data based on the input parameters which you can be as specific as you want to be. Just don't overcomplicate the sproc. Less is more!

 

[Bluejay07]

Qamgine,

Thank you for your post. I have been trying to get my report working by using your example, however, I have not yet been successful. Using the Coalesce, and @____ didn't seem to work as I was getting index error or the report (at runtime) would ask for me to enter values when it should be obtaining those values based on the user input.

When I was programming in VB6, I used a ttx file, created a recordset and looped through the recordset and everything worked great.

The report will only display the specific parameters that are listed but I cannot display any other information obtained from the stored procedure. In addition, the user may be entering a range instead of one set value. As per my first post (testing example), I would most likely have a where clause WHERE RefNum >= _____ AND RefNum <= _____. The refnum is a value entered by the user.

SQL Procedure:

CREATE PROCEDURE rptImpExp

@RefNum1 varchar(20) = " ",
@RefNum2 varchar(20) = " "

AS

SELECT   ReferenceID, DT, RefNum, ImpNum, ImpName, NO_ARTS, Code, DUTY
FROM     cusform
WHERE RefNum >=  @RefNum1 AND RefNum <= @RefNum2
GO

rptDocument.Load(strReportPath)
rptDocument.SetParameterValue("pRefNum", ds.Tables("ModelPrintInfo").Rows(0).Item("RefNum").ToString)

I would appreciate any other helpful advice you can provide.

 

[WaZda]

Hello,

I am using VS 2005 and SQL Server 2000 (the sql version shouldn't be an issue here anyway).
I've having the same problem. I have a datareader or dataset I want to loop through the dataset and display each row of information on the report. My problem is that when I do the loop, the previous row is overwritten with the current row so intead of having multiple rows on the report, i have just one row and which is of the last record.

   Do While reader.Read
      rptDocument.SetParameterValue("Name", reader.Item("cName"))
      rptDocument.SetParameterValue("Address", reader.Item("cAddress"))
   loop

   frmView.CRViewer.ReportSource = rptDocument
   frmView.Show()

Please how do i solve this?

 

[Bluejay07]

Hello,

As I have made many changes, I cannot remember exactly what I had done to make things work, however, I do know that I took a different approach.

I completely removed the 'SetParameterValue...' and set the parameters though an SQL stored procedure and let the server perform verifications for null etc. Then I placed the values into the dataset.

Sample code from SQL:

CREATE PROCEDURE rptModelRef

@STARTREF varchar(20),
@ENDREF varchar(20)

AS

IF @STARTREF = ''
   IF @ENDREF = ''   -- Start Ref and End Ref are blank
      SELECT   ...
      FROM  ______
      WHERE ______
   ELSE   -- Get all records up to the end reference
      SELECT ... FROM ______
      WHERE ____
ELSE	-- Start Reference is not blank.
   IF @ENDREF = ''   -- Get all records starting with the start reference.
      SELECT ... FROM  _______
      WHERE _____   
   ELSE
      SELECT ... FROM ______
      WHERE _______

GO

Then in code I used:

Dim rpt As New ModelRef   'ModelRef is the name of the report.
rpt.SetDataSource(m_dsPrint)

If I am previewing the report, I then used:

Dim frmView As New frmViewer
frmView.CRViewer.ReportSource = rpt
frmView.Show()

I hope this helps you.

 

[WaZda]

HI Bluejay07,
i think my issue is quiet a lil bit different from yours. unless i didn't really understand your approach. The parameters i have on my report are the different fields to be displayed. for example i have like three parameters (Name, Address, Job) placed on the report and then my query is "select Name, Address, Job from...). So i would like to display the three fields accordingly to the parameter. I hope this one is more understandable.
Thank you for your help.
WaZda

 

[Bluejay07]

WaZda,

Yes, the two methods we are using are different. It appears as though you are creating parameters and trying to insert it directly onto the report (which I too tried at first but could not get the proper functionality); whereas the parameters I am using is the specific record(s) I wish to display. I take that parameter, send it to SQL (as part of the where clause) to obtain the remaining details, and place that into a dataset which is sent to the report.

(In your case, I think it would be SELECT ... WHERE NAME = ____, or an ID field, or something similar)

In my report, I create a dataset of its own and use those fields to place on the report. This dataset is just used as a template. Then using the code I have above to preview the report, the dataset from code and the dataset in the report match, providing the exact information required.

On my part, it took some trial and error, but it seems to work fine for me now.

The process may be a bit different (but still pretty similar) depending if you are using an actual version of crystal reports vs. the built in version of Crystal in Microsoft Visual Studio.

If trying from Visual Studio, I selected Data | Add New Data Source and followed the wizard, selecting the desired tables and fields. If you have more than one table, the wizard will ask how they are to be joined to form the relationship.

I hope this is clearer for you in what I did.

 

[WaZda]

ok Bluejay07,
I've been trying some much things that am now getting a lil bit mixed up. Could u please give me a simple way to put some parameter/textbox or whatever on a crystal report and then programmatically get a dataset and then bind it to the report to have the fields displayed on the report. If i can simply get this, it will solve my problem ( not using the wizard because i may change the query, so the dataset am finally getting is what i will bing to the report).
Thank you for your help.

WaZda

 

[Bluejay07]

WaZda,

I have created a new project from my original code. You can do the same to test things out.

- Create a new form and place a CrystalReportView on it.
- Add a CrystalReport to your project
- In the menu bar, select Data | Add New Datasource
- Select Database and set your connection. Should be something similar to: "DataSource=SERVERNAME;Initial Catalog=DBName;User ID=UserID"
- Select the table(s) and/or fields to use
- Back on the main screen, in the field explorer, right click on Database Fields and select Database Expert
- Select Project Data | ADO.NET Datasets
- Select the newly created table and move it to the right side (either by the arrow or double clicking), then OK
- the database is now set up in crystal reports. Just access the fields in the field explorer and drag and drop to the desired position.

- As a test, I then created a new form with a button on it, which calls the print procedure when clicked.

Public Sub Print_Info()

   Try
      Dim m_daPrint As New SqlClient.SqlDataAdapter
      Dim m_dsPrint As New DataSet

      m_daPrint.SelectCommand = New SqlClient.SqlCommand

      Dim CONN As New SqlClient.SqlConnection

      'Connect to the SQL Server with a stored procedure.
      With m_daPrint.SelectCommand
         CONN.ConnectionString = "Data Source=CATS2005;Initial Catalog=ARUCUST;User ID=sa;"
         .Connection = CONN
         .CommandType = CommandType.StoredProcedure
         .CommandText = "rptUsers"

         'Set the stored procedure parameters.
         .Parameters.Add("START", SqlDbType.VarChar, 50)
         .Parameters.Item("START").Value = Trim(g_strFirst)
         .Parameters.Add("END", SqlDbType.VarChar, 50)
         .Parameters.Item("END").Value = Trim(g_strLast)
      End With

      'Fill the dataset.
      m_dsPrint.Clear()
      m_daPrint.Fill(m_dsPrint, "Users")

      If m_dsPrint.Tables("Users").Rows.Count = 0 Then
         MessageBox.Show("There are no Users in the database that match your search criteria", _
             "No Users Found", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1)
      Else
         'Set up the report with the dataset.
         Dim rpt As New CrystalReport3
         rpt.SetDataSource(m_dsPrint)

         Dim frmView As New frmViewer
         frmView.CrystalReportViewer1.ReportSource = rpt
         frmView.Show()
      End If

   Catch ex As Exception
      MessageBox.Show(ex.Message)
   End Try
End Sub

I left my connection string in the code for your reference. Your connection string should match the one you used to set up the dataset in the report.

This is the method that worked for me. Hopefully this is a little bit clearer for you.

 

[WaZda]

Hi Bluejay07,
thank u for your help. Tell me, do u think the Stored Procedure is a must to get this worked? I've done what u've described expect that i didn't use a stored procedure and what i get as an output, is all the records in the tables. so i mean even if i create a new dataset programatically and bind it to the reports, i gives me no error but the output is the same.

 

[Bluejay07]

Hi WaZda,

It seems like you are finally making some progress. Great to hear.

I don't think that using a stored procedure is a must. There are many ways to program things which will lead to the same result. Using a stored procedure is a method I prefer and it's also the one that has worked the best for me so far.

I tend to reuse my code whereever and whenever possible (why reinvent the wheel for each task). However, when programming and doing some research, if I come across different methods for what I'm doing, I will usually take some time and try the new method. Sometimes the new method is easier and faster, while other times it may not be; but it doesn't mean that either method is wrong.

 

[WaZda]

Hi Bluejay07,
I've just got it working. I guess the main issue was to get the schema of the fields on the crystal report to be exactly the same as in the query. For those who might have the same problem here is the solution.
Bluejay07's code is working good and is for stored procedure. here is another way of doing the same thing but with programmatical dataset with sql query string.

Create a Dataset and define the schema by drag and drop the database table from Server Explorer.
If there are multiple tables then put all the tables within one dataset itself.
Make sure the schema of your drag and drop TableFields on the Report is exactly the same schema of your query output. That is all to be done on the report.
Then u go write your vb.net codes in whatever form u r working.

Here is a sample of my code

'So to start this, i've dragged and dropped "CatId" and "CatType" from the dataset in the server explorer onto the CrystalReport2 

	Dim frmReportView As New Form1 'Form1 is where the form on which I have the Crytal Report Viewer
        Dim myReport As New CrystalReport2 'CrystalReport2 is the report to generate

	'Do the Connection to the Data base programmatically
        Dim connectionstring As String = "Data Source=POSTE-91;Initial Catalog=MandatMission;Integrated Security=True"
        Dim connection As New SqlClient.SqlConnection(connectionstring)

	'Here is the Query String. On the Report I have only "CatId" and "CatType" so the query should be exactly the same therefore i select only "CatId" and "CatType"
        Dim strSelectStatement As String = "SELECT CatId, CatType FROM CategPro where CatId = @CatId "
        Dim SelectCommand As New SqlClient.SqlCommand(strSelectStatement, connection)

	'Adding the parameter stated in the query. If u don't have any parameter in your query u shouldn't add the line below
        SelectCommand.Parameters.Add(New Data.SqlClient.SqlParameter("@CatId", Trim$(txtCode1.Text)))

        connection.Open()

	'Create the Data Adapter
        Dim da As New SqlClient.SqlDataAdapter(SelectCommand)

	'Create the DataSet
        Dim ds As DataSet = New DataSet

	'Fill the DataSet with the Table from Which u are doing the Query
        da.Fill(ds, "CategPro") 'Note here that the Table name is VERY IMPORTANT. It doesn't work with me when i don't add the "CategPro" as per what I have in the query string above named "strSelectStatement"

	'Bind the DataSet to the Report
        myReport.SetDataSource(ds)

	'Load the Report in the Crystal Report Viewer
        frmReportView.CrystalReportViewer1.ReportSource = myReport

	'Close the Connection
        connection.Close()

	'Display the Report
        frmReportView.ShowDialog(Me)

	'Dispose the Report after the user has closed it
        frmReportView.Dispose()

 

[TakaAnn]

hi guys i am new to visual studio 2005 and crystal reports i have developed many programs but i got stuck on this one so here is my question...
i want to produce a report in which i search for the data to be produced on the report...
like when i enter lets say a department name o a form and then the query is passed on to the database then to the crystal reports displays the searched for data please help me....

 

[Bluejay07]

Hi TakaAnn,

Before I start, I think this would be a great place to start a new thread for your issue as more people could provide their input without reading the previous issues discussed.

As Wazda and I have communicated, there are several ways to obtain data and place it on a report.

What kind of issues are you facing; Is it connection issues, is it a problem accessing a stored procedure, is it a problem populating a dataset to send to Crystal?

The following sort of touches on everything, including the issue of how you obtain a form variable to insert into the query:

Dim conn As New SqlClient.SqlConnection

conn.ConnectionString = "Data Source=YourServerName;Initial Catalog=DatabaseName;User ID=sa;Password="

conn.Open()

' Private m_dsDept As New DataSet (Defined before this procedure)
m_daDept = New SqlClient.SqlDataAdapter("SELECT * FROM Department WHERE '" & Trim(txtDeptName.Text) & "'", conn)

' Private m_daDept As New SqlClient.SqlDataAdapter (Defined before this procedure)
' Private cmdBuilder As SqlClient.SqlCommandBuilder (Defined before this procedure)

' Note: using cmdBuilder makes it easier to use 'm_daDept.Update(m_dsDept, "DeptInfo")' if updating a database.

m_dsDept.Clear()
cmdBuilder = New SqlClient.SqlCommandBuilder(m_daDept)
m_daDept.Fill(m_dsDept, "DeptInfo")

conn.Close()

After the query and connection are made, you can try some of the steps for reports as discussed earlier in this thread.

I hope this helps.

 

[WaZda]

Hi,
There is something i had to add to the report but it's not working. I've put some other "Parameter Fields" on the report. Data send there are not from the database. I had it working fine when i was not yet binding the report to a dataset. Now that the dataset is bound to the report, the data is no more sent to the parameter fields. I rather had a pop-up window asking to enter value for the parameter fields. Which is weird and i can't figure out how to do it.
Can anybody please help?

Wazda