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

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.

How do I solve this?

I am using drag and drop SQLDatasource to call the Stored Proc and VB.net is the language. Thanks

This is because when you set AS 'Jan', your table's coloumn name is JAN now. If your first Statement If MONTH(GetDate()) <= 6 is true, you have column JAN, it should work. But, if this is not true, you don't have column JAN, you get error.

|||

I tried and it does give me the problem. Is there a workaround for this? Thanks

|||

Can you try this?

1) Modify your SP, add '' for other month to make whole year like:

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',

'' AS 'Jul',

'' AS 'Aug'

... for other month
FROM dbo.tblEvent INNER JOIN dbo.tbl ON (tblEvent.EventID = tblEventdate.EventID)
WHERE YEAR(StartDate) = Year(GetDate())
group by EventTitle, EventDuration,StartDate,EndDate

Do same thing for your second condition.

2). I am not sure which Data Control you used. Try to hide the column if the column value is ''.

(I am not sure the number 2 workable or not. Anyway, try to hide it).

|||

Ok, it is done. thanks