Crystal Rpeorts - recordsource at runtime

[briancps]

I have seen this question in the forums before - but can't seem to find an answer

Does anyone how to reallocate the recordsource of a Crystal Report in VB.NET at runtime to an SQL Query created based on parameters a User has entered form a Rpoert Selection FOrm - I don't want CR to pop up asking for the parameters. I can set up the report fine as per Karl Moore's book but I have gone thru the help and cannot find how to change this at Runtime. Its has to be able to be done !! CAn anybody help

thanks BH

[briancps]

The help files for Crystal reports in VB.NET are just not very useful to say the least. I've tried to find a book that can tell me how to do this. The closest I got was a SetRecordSource method, but nowhere in the help can I find how to do this.

Do I give up and go back to VS.VIEW or what

Doses anybody have any positive input

thanks in advance
BH

[stingrae]

brian,

i've spent the past 3 weeks banging my head against a brick wall over this and still can't find a solution. i've received some support from crystal on the matter but nothing that has worked yet. all they do is point me to confusing "walkthroughs" and other pdf "help" files.

here's the most "useful " link!

Please consult our Developer Zone site, which has walkthroughs, samples,
whitepapers, and articles dealing with Crystal Reports and devloping in
VB, VB.NET, etc...

Here is a whitepaper about the object models:

http://support.crystaldecisions.com/...ers/crnet_obje
ct_models.pdf

Here are some articles about Parameters.

How to pass a string parameter to a report at runtime using VB .NET
--------------------------------------------------------------------------
--
http://support.crystaldecisions.com/...s/c2010277.asp
--------------------------------------------------------------------------
--

How to pass a Date parameter to a report at runtime using VB .NET
--------------------------------------------------------------------------
--
http://support.crystaldecisions.com/...s/c2010247.asp
--------------------------------------------------------------------------
--


How to pass a numeric parameter to a Report at runtime using VB .NET
--------------------------------------------------------------------------
--
http://support.crystaldecisions.com/...s/c2010276.asp
--------------------------------------------------------------------------
--

How to pass a Boolean parameter to a Report at runtime using VB .NET
--------------------------------------------------------------------------
--
http://support.crystaldecisions.com/...s/c2010242.asp
--------------------------------------------------------------------------
--

How to pass a Currency parameter to a Report at runtime using VB .NET
--------------------------------------------------------------------------
--
http://support.crystaldecisions.com/...s/c2010245.asp
--------------------------------------------------------------------------
--

Crystal Reports for Visual Studio .NET - Walkthrough - Reporting off
ADO.NET Datasets

http://support.crystaldecisions.com/...ers/rtm_Report
ingOffADONETDataSets.pdf.asp

This document describes reporting off an ADO.NET Dataset using Crystal
Reports for Visual Studio .NET. This document is only for use with Crystal
Reports for Visual Studio .NET and later versions.

maybe if we both work together on this we can get a better result. if you like, email crystal quoting "CTL:20021219-10103"

[Lunatic3]

I think what you have to do is working with Datasets and that tutorial, 'Reporting off ADO.NET Datasets ' is a good help, at least it was for me.

You have two choices:
First: Passing parameters to your datadapter selectcommand and choose the records on that basis. Then you dont need to use a parameter in your CR to choose the records for you. This seems to be more practical for me.
Second: Using parametes in crystal reports. Basically parameters in CR have a variety of uses and one is to select your records. I dont know why it had to be like this, but you have to pass parameters to the parameters collection of CrystalReportViewer, not to your report, and this raises some problems, for example each time you refresh your report you have to pass them again, and if you want to use multiple reports in one viewer then each time you want to load one of them you have to pass the parametes once!
As a solution to the problems above i advise you to use formula fields instead of parameters as they belong to the report not the viewer.

[briancps]

g'day Stingrae - I hope we can sort this out - I gather you are from Sydney - not Rio -

I printed out the PDF file for the reporting from ADONET Datasets.
Move over - I need some of the brick wall for me to hit my head on.

I dont understand c# and this documnet is designed for a Web App. The code from page 11 on re setting the dataset must have an equivalent in VB.NET - surely there cant be a report program as sophisticated as Crystal that can't assign a recordset at Runtime.

eg this is the SQL statement generated by Crystal for part of my app

SELECT `Students`.`STNumber`, `Students`.`STSurname`, `Students`.`STFirstNames`, `Students`.`STBirthDate`, `Employers`.`EMName`, `Students`.`ID`
FROM `Students` `Students` INNER JOIN `Employers` `Employers` ON `Students`.`STEmployer`=`Employers`.`ID`
ORDER BY `Employers`.`EMName`

I need joins to Employers Tables which seems to work and selection criteria (say on Birth Date) based on details the user has entered into text boxes etc.

Can anyone translate steps 1-4 from pages 11-12 from that document -as below to apply to a VB.NET app. Which form events (from my report display form) do I put these in ?? Would appreciate it . I can setup the report fine - works oK - just need to redefine the SQL statement at runtime based on parameters the user has entered.


Sorry about the unformatted mess below - help!!!!

Page 11
To specify xtreme.mdb as the database and bind CustomerSales.rpt to a Web
Forms Viewer
1. Highlight WebForm1.aspx in the Solution Explorer. Then click the View Code icon.
This opens the source file for the Web Form, WebForm1.aspx.cs.
2. In WebForm1.aspx.cs, type at the beginning of the namespace definition:
using System.Data.OleDb;
3. At the beginning of the class definition for WebForm1, type:
public CustomerSales oRpt = null;

4. After the call to base.OnInit(e) in OnInit(), type:
oRpt = new CustomerSales ();
/* Use ADO.NET object model to set up connection information */
OleDbConnection oleConn = new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Program
Files\\Microsoft Visual Studio .NET\\Crystal
Reports\\Samples\\Database\\xtreme.mdb");
OleDbDataAdapter oleAdapter = new OleDbDataAdapter("SELECT * FROM
Customer", oleConn);
Dataset1 dataSet = new Dataset1();
/* Connect to, fetch data and disconnect from database */
oleAdapter.Fill (dataSet, "Customer");/* Use Report Engine object model to pass populated dataset
to report */
oRpt.SetDataSource (dataSet);
/* bind report object with data to the Web Forms Viewer */
CrystalReportViewer1.ReportSource = oRpt;

[EagleEye]

Step 1:
Create a Data Adapter. For your SQL select statement use:

SELECT Students.STNumber, Students.STSurname, Students.STFirstNames, Students.STBirthDate, Employers.EMName,Students.ID
FROM Students INNER JOIN Employers ON Students.STEmployer = Employers.ID
WHERE Students.STBirthDate like @stbd
ORDER BY `Employers`.`EMName`

Step 2:
Right click on data adapter and click "Generate Dataset..." to create a new dataset based on your SELECT statement.

Step 3:
Add a new Crystal Reports component to your Project. "Use Report Expert" to create the report.
(a) For the data tab (first one shown) select the newly created dataset under the "Project Data", "ADO.Net Datasets".
(b) For the select tab just select the fields you plan to display on your report. NO SELECTION CRITERIA WILL WE USED HERE (selection criteria is handled in the form not in the report).
(c) All the other tabs are left to your own taste/style.
(d) Modify the report layout to your liking

Step 4:
From your toolbox, add a crystal report to the form that has the crystal report viewer (found under components). Select typed report and select the report you just created.

Step 5: (mmmm code...)
I am assuming that this report is loaded in a crystal report viewer on the same form as a button called "Create". If it isn't (which is usually the case) then move the code to the event calling this report.
Under the Create button load event:
(a) SQL SELECT COMMAND NAME.Parameters.Clear()
(b) SQL SELECT COMMAND NAME.Parameters.Add("@stbd", SqlDbType.DATA TYPE
(c) SQL SELECT COMMAND NAME.Parameters("@stbd").Value = TEXT BOX NAME.Text
(d) Fill your dataset
(e) REPORT NAME.SetDataSource(DATASET.TABLE)
(f) REPORT VIEWER NAME.ReportSource = REPORT NAME

On occasion I have seen failures in the crystal report code itself due to missing references. Make sure you have all the required references to CrystalDecisions.

This is how I create all my reports that have variables. It's really early over here and I am exhausted so if I missed something let me know.

[stingrae]

yeah, briancps, i am in sydney at the moment. i was in Rio when i created my account and live there for 2-3 months every year. (it gets too cold down here for me)

did you have anyluck with the above code? i've got some hardware problems i'm working on with a client at the moment, but i will get back to working on this crystal thing tomorrow afternoon.

cheers.

[briancps]

thanks for you response - its really late over here any i'm just abou tto head to bed. I'll try what you suggested tomeorrow - it sounds like you have got it working. will try tomorrorrow - thanks again

regards

BH

[briancps]

thanks for your help stingrae and Lunatic3. I have had no luck whatsoever. I finally managed to work out how to get the .setdatasouce to work but it uses a dataset, I'd need to use a datareader with a complex SQL Statement with multiple joins and selection criteria, and I don't think I can do this with a dataset unless I set up all the realtionships in code ?. The dataset has to extract the data from a table rather than a query. Can someone please corect me if i'm wrong.

I can't get any useful asistance from Crystal - as you said they just point you in the direction of some walktrhoughs - very confusing stuff. - I have been programming in VB since 1992 so I do have a fair amount of experience.

I'd like to suggest the folowing solution - I found it in some old VB3 code I wrote years ago to get over a similar problem with the version of crystal reports that shipped then - we used VSVIEW since 1995 so havn't used it since.

1. Set up a query using all the fields from multiple tables - lookups, joins to other tables needed etc. - some of the queries I need are quite complicated (joins across 10-12 tables)

2. Set up a table in the database (prefix with something significant such as rptTable1 ) which is only going to be used for this report in Crystal with the fields exactly the same as the fields in the query

3. At run time, once the user has entered the parameters for this report, date ranges, employer filters, whatever, run a query based on those paramters to create a Datareader. Delete the contents of the rpt table and fill it with the results from the DAtareader.

4. run the report - if you have set it up to use this table structure, it should report on just the records that have been selected and updated to this table

????? Iknow this is pretty crappy spaggeti code but I need a quick solution and can't get any answers from crystal and everybody else seems confused as well.

problems - Multi user - more than one user running the same report at the same time - havn't thought far enough ahead at this point - I am just going to plod ahead to see if this concept works

ANy suggestions greaty appreciated - if anyone knows how to use the .setdatasource with a datareader -PLEASE tell me

regards
BH

[briancps]

also thanks for your help Eagleeye - forgot you in the previous reply

regards

BH

[stingrae]

Yeah I've used work tables in the past too when dealing only with VB6 Data Reports. in Data Reports you could only have one child and got too confused when you added more than say 4 tables. At least in data reports you could say "use this connection"!!!!

[stingrae]

briancps,

any further advancements on this? i'm at the stage where i need it now too and am going back to my roots of midrange programming and filling a worktable.

very very

[EagleEye]

Sorry to be stubborn but ...

I have used the method I stated above repeatedly and it works wonderfully. If you are able to write the SQL Query with all your complexities included and put 'WHERE field like @field' (which is a SQL Parameter) for all your selection criteria then you can set the parameters equal to your selection criteria.

Maybe I am missing something you are asking for but it sure looks like I have given you what you asked for.

[briancps]

going absolutely nowhere - I have to install this system next week with probably 20 reports/labels/certificates etc. I'm using work tables set up with all the fields I need from the querys. Getting the user to set the parameters from an input screen and then filling a table that reflects all the fields in my query. Messy but I don't have any other solutions - surely we must be able to do this at runtime ?????

To make it work multiuser I will have to store the workstation no in the table as well and use a runtime parameter to only extract records from that workstaion just in case two users are printing the same report at the same time with different parameters- havn't got this far yet

if you have any more answers would love to hear them!!
thanks BH

[briancps]

missed your earlier reply - thanks for your response. I have some very complicated queries with joins over multiple tables (possibly up to 10) on one query with up to 15 or so parameters in my WHERE clause. I appreciate your input but I just thinsk it would take me a lot longer to do it that way than to set up another table that reflects the result set I want.

The ideal soultion is to be able to pass an SQL statement to Crystal at run time to produce the datasource but this dosn't appear to be the case.

I will definetely check your suggestions out for future projects but I have to get this installed ASAP. Not pretty but it works

thanks again

BH

[EagleEye]

Step 2:
Right click on data adapter and click "Generate Dataset..." to create a new dataset based on your SELECT statement.

The above step does the "temp" table concept without having to worry about multiple users because all the data is stored in the dataset as 1 table.

[stingrae]

EagleEye,

I followed your walkthrough step by step and I thought it was going to work, except it fell over when i got to the form.show line. I got the error message attached.

[EagleEye]

Hmm... I seem to remember seeing something like that before. I believe it had something to do with my references. Here is a list of references that I know that you need to make it work:

• CrystalDecisions.CrystalReports.Engine
• CrystalDecisions.ReportSource
• CrystalDecisions.Shared
• CrystalDecisions.Windows.Forms
• CrystalKeyCodeLib
• KEYCODEV2Lib <= This one is a COM used to verify your Crystal Decisions license

Also, in your Setup Project you need to add:

• managed.msm
• regwiz.msm <= This one requires you to put your AlphaNumeric KeyCode (not the numeric one) from HELP ... ABOUT ... INSTALLED PRODUCTS ... Crystal Reports for Visual Studio.Net {This Number is the one you need} and put it in it's properties under (MergeModuleProperties) ... License Key
• VC_CRT.msm
• VC_STL.msm

I hope this fixes the error ... I will look to see if there is something else that had to be done too. Let me know if it doesn't help. I do know that the stuff above is required and I apologize for not posting it before (I forgot that some things are not automatically added by VB.Net)

[EagleEye]

briancps & stingrae,

Did the last post help? Just curious.

[stingrae]

EagleEye,

thanks for your curiosity (you are a aware that it killed the cat )

Unfortunately, no, I still get the same error as I posted above. Please tell me, do I ad the references to the top of the form that has the function to create the report, or to the form that houses the report viewer?

[EagleEye]

You add references by right-clicking the project and left-click add reference.

[EagleEye]

Here is the exact cause of the error as per Crystal Decisions Knowledge Base

http://support.crystaldecisions.com/...s/c2011164.asp

Synopsis

An application uses Crystal Reports for Visual Studio .NET as the reporting development tool to report off an ADO.NET dataset.

This application runs successfully on the development computer, but when you deploy this application to a client computer, the following error message appears:

"Query Engine error in C:\Temp\YourReport.rpt"

Why does this error message appear and how do you resolve it?

Solution

The error message appears because the runtime files for the ADO.NET dataset are missing from the application setup.

To resolve this error message, include the following merge modules in the setup project:

- VC_CRT.msm

- VC_STL.msm

In addition, ensure Msvcr70.dll and Msvcp70.dll exist in the system folder on the client computer.

For further information on deploying a .NET application, go to http://support.crystaldecisions.com/docs and search for "crnet_deployment.pdf".

[briancps]

Thanks for all your input on this subject - I have been reading the posts. As i said i had deadlines for my current project and chose to use temporary tables and this seems to be working but its not my preferred solution. However I have a few projects coming up where I have a bit more free time and will definitely follow your responses thru and with the posts you have made - it dosn't seem a problem - I just find it hard that the solutions aren't documented in simple terms

thanks again

BH

[stingrae]

eagleeye,

thanks for finding that. the only thing is that i'm getting that error my development machine. i haven't actually deployed this application yet.

cheers.

[jcavard]

i have the same problem of binding runtime dataset to crystal reports, but this sure gave me some pointer. i know where to start now!

[stingrae]

I actually figured this out a while go. I'm pretty sure I posted the solution here too, but cannot find it, so this should help you.

Basically, I create a form with a Crystal Report Viewer object on it. Then put a button on another form and put some code like the following in it:

    Private Sub btnPrintDaily_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrintDaily.Click
        Dim conDB As New OleDb.OleDbConnection()
        conDB = ConnectToDatabase()

        Dim fdate, sdate As DateTime
        Dim tbl As CrystalDecisions.CrystalReports.Engine.Table
        Dim rpt As New rpt0003()

        rpt.DataDefinition.RecordSelectionFormula = "{tbl_TableName.FieldName} = " & ParameterValue
        For Each tbl In rpt.Database.Tables
            Dim tbllogin As New CrystalDecisions.Shared.TableLogOnInfo()
            tbllogin.ConnectionInfo.ServerName = gstrDatabasePath
            tbllogin.ConnectionInfo.UserID = "admin"
            tbllogin.ConnectionInfo.Password = gcDatabasePassword
            tbl.ApplyLogOnInfo(tbllogin)
        Next

        Dim frmRpt As New frmReportViewer()
        With frmRpt
            .CrystalReportViewer1.ReportSource = rpt
            .CrystalReportViewer1.RefreshReport()
            .CrystalReportViewer1.DisplayGroupTree = False
            .Left = Me.Left
            .Top = Me.Top
            .Icon = Me.Icon
            .Text = "Title Text Here"
            .ShowDialog()
        End With
        conDB.Close()
        conDB.Dispose()
    End Sub

[Lunatic3]

stingrae: This is off topic and relates to your web site. When you navigate back from "Contact Me" to other pages except for "Home", it fails because of Email validation in contact page. I think you need to fix that.

[stingrae]

lunatic3, that's strange. i have looked at it on a few different computers and it doesn't do that for me. i'll check again tomorrow though. thanks.