DataSet / Table or Stored Proc

[john_noble3]

Hi folks,

I have recently had a problem using the Crystal Report Viewer in Visual Studio (C#,  Winforms, mySQL). I kept on getting prompted for database log in info every time I ran a report. THe report was based on a stored proc via ODBC. The report required a parameter that was passed into the sproc. The problem has just started to happen the last couple of days. Im not sure what has changed to cause this to happen but I have spent the last 3 days trying to solve it without success. The report itself works fine when previewing it from within the Visual studio. It's onlty when I use the crystal report viewer that I get the problem. Although one strange behaviour is that every time I verify the database, I am prompted with "the table "table_name" (stored proc) does not exist. Do you want to remove all references from the report. But the stored procedure IS there.

After some testing, I noticed that rather than letting the report communicate with the database, if I just set the reports datasource to a datatable, the problem went away.(Presumably, because I am sending the data to the report directly and the report has no need to connect to database). Is this the preferred way of working with crystal reports or should I persevere and find out what is happening with the log in prompt problem.

J

[former_member11696 · Accepted solution]

Hi John,

It would only prompt for DB log on and when verified it would remove the database tables if they do not match what the report expects.

Verify someone did not change the SP on you, likely cause.

What SP are you using?

Don

[john_noble3 · Accepted solution]

Hi Don,

Below is the SP. Its quite simple with just one parameter.

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_CustomerNotepad`(

IN p_customerID varchar(7)

)

BEGIN

  SELECT * FROM customerNotepad WHERE customerID = p_customerID ORDER BY entryDate DESC, entryTime DESC;

END

There are only 3 fields on the report so it only takes a minute to re-create the report after the fields have been removed after I verify the database. But as soon as as I drag the fields from the SP on to the report. Save it and verify - I get the same problem.

Even when I start a with a new fresh blank report, the problem persists. Very frustrating.

[john_noble3 · Accepted solution]

Hello again Don,

When you asked what SP, I thought you meant stored proc!

I am using 13_0_16.

I think I have solved it now though....

When working with the rpt file in Visual Studio, it allowed me to connect to the DSN using a valid username / password for the database albeit a different username / password that was used originally to create the DSN.

I had no problems at all when working with the rpt (using a different username and password). Only when it came to using a crystal report viewer when actually running the program did I see the problem. Dont know if this is how it is supposed to be or a bug ?? Anyway as soon as I made both usernames and passwords the same, the problem went away.

Getting back to the original question, is it better to send a datatable / dataset  to the report rather than let the report communicate to the database or is it more efficient just to send a parameter to be used by the sproc that is in the report ?

Thanks,

John

[former_member11696 · Accepted solution]

It's better to hit the DB directly, let the Server do all of the data filtering, will always be quicker than getting CR do it. Add sorting etc, to the SP if you need it...

When using Stored Procedure Parameter you need to provide the value first before connecting to the DB if hitting the DB directly. Using a Dataset based on a SP can be difficult at times, depends on how you design the report because sometimes CR can't map Field used in the report to a Stored Procedure.

DatSets are limited, so if the reports query returns more than 5k rows then hit the DB directly, Due to MS's way of accessing Datasets CR has to make a copy of the DS and then access it that way and if you use more than one table then linking causes performance issues.

Don