Stored Proc works fine when execute but not ASP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Stored Proc works fine when execute but not ASP

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2007-07-31 : 11:38:44

I have this Stored Procedure:

Create PROCEDURE ListEvent
as
If MONTH(GetDate()) <= 6
Begin
SELECT EventTitle, EventDuration,
(CASE WHEN MONTH(StartDate) = 1 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jan',
(CASE WHEN MONTH(StartDate) = 2 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Feb',
(CASE WHEN MONTH(StartDate) = 3 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Mar',
(CASE WHEN MONTH(StartDate) = 4 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Apr',
(CASE WHEN MONTH(StartDate) = 5 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'May',
(CASE WHEN MONTH(StartDate) = 6 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jun'
FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)
WHERE YEAR(StartDate) = Year(GetDate())
group by EventTitle, EventDuration,StartDate,EndDate
End
Else
Begin
SELECT EventTitle, EventDuration,
(CASE WHEN MONTH(StartDate) = 7 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Jul',
(CASE WHEN MONTH(StartDate) = 8 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Aug',
(CASE WHEN MONTH(StartDate) = 9 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Sep',
(CASE WHEN MONTH(StartDate) = 10 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Oct',
(CASE WHEN MONTH(StartDate) = 11 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Nov',
(CASE WHEN MONTH(StartDate) = 12 THEN Datename(Day,StartDate) + ' - ' + Datename(Day,EndDate) ELSE '' END) AS 'Dec'
FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)
WHERE YEAR(StartDate) = Year(GetDate())
group by EventTitle, EventDuration,StartDate,EndDate
End

When I execute it in the SQLExpress, the result returned as expected. But when I bind to Gridview I got this error:

A field or property with the name 'Jan' was not found on the selected data source.

And when changed the system date to let say March, it work fine in Gridview, but not when i changed it back to July.

How do I solve this? Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-31 : 11:50:31

Your stored proc returns different columns depending on a condition, so it cannot be bound! A stored procedure should *always* return consistent results with consistent column definitions.

This is a classic case of trying to force presentation code into your SQL code. You are putting way too much presentation data into your sql statement -- let ASP.NET do all the formatting and presenting, just return clean, raw data from your database.

You can return all months and let your data grid either hide or show certain columns, or bind the columns in the grid dynamically to the months you want to see. But don't try to return different columns based on a parameter from a sp, it is not a good design.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 11:51:36

Gridview need the column names to be persistent.

Either rename column as MonthA, MonthB, MonthC MonthD, MonthE and MonthF and rename gridview header in application, or always return all twelve months and hide the columns not needed in application.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 11:52:05