Member Avatar for zachattack05

Hi everyone!

I am in major need of some help here and before I go off and write a SP to handle this for me, I though I would ask here for advice first.

I am writing a report to display on our asp.net site using the ReportViewer component and the report editor in VS.

We have a SQL table that contains a DateTime field (two actually) and those fields also accept null values.

The report I am writing in ASP.NET needs to select the data from the table and determine if the DateTime field is null to show certain values on the report.

Some of the solutions I have found involve testing for DateTime.MinValue, but the problem is that some of the dates in the database legitimately have that value. So I can't check against that.

Is there a way, besides me creating a SP on the SQL server to check for NULL values, for me to do this?

Because I am using a report, my options for codebehind are rather limited. I have tried using the IsNothing() and IsDate() on the field, but it simply throws an error saying the report couldn't be processed.

Any thoughts?
Capture.PNG

Edited by zachattack05
  • 2 Contributors
  • 4 Replies
  • 336 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by zachattack05
Member Avatar for rproffitt

OK, how about 2 reports? You have some SQL command to select the set you want to report on so one report for when the dates have nulls and the other report for non-null dates.

Member Avatar for zachattack05

rproffitt,

I don't think that will work. I probably should have explained more about the report.

Essentially the report contains a matrix of data, and two of those columns contain a gauge indicator control. The sql command selects a set of data from the table and the report (is supposed to) show a matrix similar to this:
Capture.PNG

Those checks and X images are based on whether or not a date is present or not. If it is, a check, if not, an X.

Two reports won't work simply because the report shows multiple records from the database.

Member Avatar for rproffitt

Then you'll have to do what I had to do years ago. Export the set to a temporary table, use some SQL command to change the date to a string and if null, try 0/0/0 which you can test in your report. After the report, I drop the temp table.

Member Avatar for zachattack05

I kinda figured I wouldn't be able to do it easily.

I think I'm just going to use a SP or view since I can check if a datetime field is null in a SQL Query and just convert that result to a bit field instead. The date is rather irrelevant for the report, all that's important is that a date is present or not.

Yay for moving business logic to a database...not. Ugh.

Thanks for your input rproffitt.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.