Vanishing Data... 1

[dBjason]

Hello,

I have a stored procedure in SQL Server which returns a small recordset. On the SQL side, I can open it and data is returned perfectly. 5 records with about 10 fields, all containing good data.

Then I try to create a Crystal Report (9.2). I do a new database / server connection, point to the right database, and find my stored procedure. I get a blank report as usual, and can select fields from the field explorer to drag onto my report. I should be able to drag a couple of fields from the explorer and drop them in the detail area and preview 5 records of data, right?

Wrong. I get back only 3 records, and one of my fields is always blank (no data in it). I go back into SQL Query Analyzer to re-run my stored procedure just in case somebody deleted something. I still get 5 rows of clean data, with numbers in all fields. I go back to Crystal, refresh, and still only 3 rows with an empty field which is populated on the SQL side.

Any ideas?

 

[vidru]

Make sure 'Save Data with Report' isn't check on the File menu, and make sure the your Record and Group Selection Formulas are empty.

If neither of those help, try using a different connectivity method as a test (e.g. switch from OLEDB to ODBC, or vice versa).

-dave

 

[dBjason]

Thanks, but found out it's an error which lies somewhere else.... I'll post just in case anyone else has the same problem...


In my report the stored procedure needs a datetime parameter. When Crystal Reports prompts for the parameter, for whatever reason the "time" area needs to be adjusted when entering the desired date. I changed the time to 12:00:00 AM, and the data came through. From here out I'm only going to use nvarchar as parameters, then CAST to datetime where needed.

 

[vidru]

We've trained our users to ignore the Time portion of the Date/Time parameters when we're using stored procedures, and we handle the Date portion (with or without the Time) within the stored procedure.

Switching to VARCHAR parameters introduces a new potential bomb - worrying about the format in which the end-user types in the date.

-dave