Last post Jun 26, 2010 10:36 PM by urthere
Jun 25, 2010 10:05 AM|urthere|LINK
i meet a very very strange problem today, hope any one could help me.
the problem is that i have a Stored Procedure, there are 2 fields' values are null after executing the
Stored Procedure. this should be ok. i used this Stored
Procedure to gather the data from database for a crystal report. but when i export it to a excel file, the strange thing happened, there are some data shown in the 2 fields which values are null just now. i check many times whether connected a right
sql server or called the right Stored Procedure and so on, these are all right. i did not use session or cache anywhere.
then i use another way to gather the data from sql server. i found if i changed sqlcommand type from
Stored Procedure to Text and used the sql script directly, this mean by create a SqlCommand object and stored the
Stored Procedure's content to the object, the exporting works fine. the 2 fields shown nothing as the
Stored Procedure executing in sql server.
i thought this situation could nerver happen, but i really meet this today, hope any one could give me some advise and explain how could it happen.
Thanks & Regards
Jun 25, 2010 01:18 PM|sansan|LINK
go to Report Explorer and see if there are any other fields that you dragged onto the report designer apart from the two fields
Jun 25, 2010 11:35 PM|urthere|LINK
thanks for your replying, sansan.
the other fields are shown the data correct, means by shown the data as the stored procedure executing in sql server, only these 2 fields and these 2 fields are in one record, just these 2.
i checked the result carefully and found that it seems the crystal report called the last version of stored procedure which i didn't modified, because the requirement was updated. so i modified the sql statement a little, just added a "=" in the where clause,
for simply, the problem is the crystal report didn't show the data same as the stored procedure executing correctly, i thought it will never happen, too.
Jun 26, 2010 10:36 PM|urthere|LINK
finally, i got the reason now, it is all about the datetime in sql server, for exmple, try to think about that TODAY is 27th, June 2010, the getdate() function will get the date like 2010-06-27 10:10:10, my crystal report will pass the datetime parameter
like 2010-06-27 to the stored procedure, there is no time in this parameter but only date. when compared in the sql server, the parameter will be converted into 2010-06-27 00:00:00, so the result of express "getdate()>parameter" would be TRUE, because in my
crystal report the parameter was stored by 2010-06-27, there was not any time in this parameter. i used getdate() function to executeing the stored procedure, so in the result, there was not any data in that 2 fields, but when crystal report exporting, there
was some data in the excel file.
hope this could help someone like me.