The first issue was formatting the date. Yes you can format the date in code but why do that when SQL Server already has built in functions to do so? The way to format the date as in figure 1 is to use the SQL CONVERT() function to turn the date into an nvarchar and then break down the date into it's separate parts.
We needed to display the month's name first which is best done using the SQL DATENAME() function. Then the numerical day and year both of which use the DATEPART() function.
The other issue was creating a unique ID for each popup Div element. In this case it's a concatenation of the chars "PopUp" with the records ID number appended to it. In order to do that we use the CAST() function to convert the record's ID number from an integer to an nvarchar.
Please see the Co13_GetTopThreeEvents Sproc for a working example.
-- =============-- =============================================
-- Author: Duane Urban
-- Create date: 05/19/2009
-- Description: Retrieves the Top 3 events and formats the html for an
-- AJAX popup
-- =============================================
CREATE PROCEDURE [dbo].[Co13_GetTopThreeEvents]
AS BEGIN SET NOCOUNT ON
SELECT TOP (3)
CONVERT(nvarchar(20), DATENAME(month, Co13_Events.EventDate)) + ' '
+ CONVERT(nvarchar(2), DATEPART(d, Co13_Events.EventDate)) + ' '
+ CONVERT(nvarchar(4), DATEPART(yyyy, Co13_Events.EventDate)) ' - ' +
+ Co13_EventsCatagories.EventsCatName + '<br />
<a href="javascript: void(0)" onmouseover=''this.style.cursor="pointer" '' onfocus=''this.blur();'' onclick="document.getElementById(''PopUp' + CAST(Co13_Events.Id AS nvarchar(20)) + ''').style.display = ''block'' " > <span style="text-decoration: underline;">' + Co13_Venue.VenueName + '</span></a> <div id=''PopUp' + CAST(Co13_Events.Id AS nvarchar(20)) + ''' class="eventspopup"><div class="eventspopupinner">'
+ CONVERT(nvarchar(20), DATENAME(month, Co13_Events.EventDate)) + ' '
+ CONVERT(nvarchar(2), DATEPART(d, Co13_Events.EventDate)) + ' '
+ CONVERT(nvarchar(4), DATEPART(yyyy, Co13_Events.EventDate)) + ' - '
+ Co13_EventsCatagories.EventsCatName + '<br />'
+ Co13_Events.EventStartTime + ' - ' + Co13_Events.EventEndTime + '<br
+ />' Co13_Events.EventName + '<br /><br />' Co13_Venue.VenueName + '<br
+ />' + Co13_Venue.VenueCity + ', ' + Co13_Venue.VenueState +
'<br /><br /><div class="eventslink"><a href="../../News/Default.aspx?eid=' + CAST(Co13_Events.Id AS nvarchar(20)) + '">more info</a> <a href="javascript: void(0)" onmouseover=''this.style.cursor="pointer" '' onfocus=''this.blur();'' onclick="document.getElementById(''PopUp'
+ CAST(Co13_Events.Id AS nvarchar(20)) + ''').style.display = ''none'' "
+ >close X</a></div></div></div>'
AS Events
FROM Co13_VenueCatagories WITH(NOLOCK)
INNER JOIN Co13_Venue WITH(NOLOCK)
ON Co13_VenueCatagories.Id = Co13_Venue.VenueCatId
INNER JOIN Co13_Events WITH(NOLOCK)
INNER JOIN Co13_EventsCatagories WITH(NOLOCK)
ON Co13_Events.EventCatId = Co13_EventsCatagories.Id
ON Co13_Venue.Id = Co13_Events.VenueId
WHERE (Co13_Events.EventDate >= GETDATE())
ORDER BY Co13_Events.EventDate
END
The user control:
<code>
<%@ Control Language="vb" Debug="true" AutoEventWireup="false" CodeBehind="NextEvents.ascx.vb" Inherits="TFR.NextEvents" %>
<div style="width:190px">
<div class="eventsnavheader">
Next Events
</div>
<div class="eventsnav">
<asp:DataList id="dlEvents" runat="server" BorderStyle="None" RepeatDirection="Vertical" RepeatLayout="Flow">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Events") %>
</ItemTemplate>
</asp:DataList>
</div>
</div>
</code>
The user controls codebehind:
Public Partial Class NextEvents
Inherits System.Web.UI.UserControl
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
Me.GetEvents()
End If
End Sub
Public Sub GetEvents()
Dim EventTitles As TFR.PageLevelDB = New TFR.PageLevelDB()
dlEvents.DataSource = EventTitles.GetTopThreeEvents()
Try
dlEvents.DataBind()
Catch ex As Exception
End Try
End Sub
End Class
The middle tier class that calls the stored procedure:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Public Class PageLevelDB
#Region "================ Global Variables ================"
Protected myConnection As SqlConnection = New SqlConnection(ConfigurationManager.AppSettings("cnn"))
Protected myCommand As SqlCommand
Protected myDataReader As SqlDataReader
#End Region
#Region "=========== Database Select operations ==========="
''' <summary>
''' GetTopThreeEvents() Method
''' The GetTopThreeEvents() method returns the top three events
''' for display on the events menu.
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Function GetTopThreeEvents() As SqlDataReader
' Create Instance of Connection and Command Object
Me.myCommand = New SqlCommand("Co13_GetTopThreeEvents", Me.myConnection)
Try
' Mark the Command as a SPROC
Me.myCommand.CommandType = CommandType.StoredProcedure
' Execute the command
Me.myConnection.Open()
Catch ex As Exception
Finally
' Execute the command
Me.myDataReader = Me.myCommand.ExecuteReader(CommandBehavior.CloseConnection)
Me.myCommand.Dispose()
End Try
'Return the datareader result
Return Me.myDataReader
End Function
#End Region
End Class
Subscribe