Linking a Calendar To a Database

This sample uses the SQL Server Managed Provider to link with an ASP.Net Calendar Server Control. Here, we see how to do several things:
Set up a simple User Schedule
Enable and Disable Controls (LinkButtons)
See how to access the Date, Month and Year from the Date clicked in the Calendar
The Table used is called CalSchedule, and the columns/fields are ID (identity), CalDate (DateTime), and Schedule(Text).
A full tutorial will be coming for this soon.
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.2">
	<title>Linking a DataBase to a Calendar</TITLE>
<script language="VB" runat="server">
Dim sSchedule as String
Dim sqlSave as String
Dim blData as Boolean
Dim MySQL as String
Sub Calendar_Click(Source as Object, E as EventArgs)
	ph1.visible=true
	litSchedule.text=""
	Dim strConn as string = "server=(local);uid=YourUID;pwd=YourPWD;database=YourDataBase"
	MySQL = "Select CalDate, Schedule from CalSchedule " & _
	"Where CalDate = @caldate "
	Dim MyConn as New SQLConnection(strConn)
	Dim objDR as SQLDataReader
	Dim Cmd as New SQLCommand(MySQL, MyConn)
	cmd.Parameters.Add(New SQLParameter("@caldate", calendar1.selectedDate))
	MyConn.Open()
	objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
	if objDR.Read=True then
		MyConn.Close
		MyConn.Open
		objDR.Close
		objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
		While objDR.Read()
			sSchedule=objDR("Schedule")
			if sSchedule="" then
				litSchedule.text="(<i>Nothing scheduled for this day</i>)"
			else
				sSchedule=sSchedule.Replace(vbcrlf, "<br>")
				litSchedule.text= sSchedule

			End If
		End While
	Else
		litSchedule.text="(<i>Nothing scheduled for this day</i>)"
	End if
	lblFullDate.text=Calendar1.SelectedDate.ToShortDateString
	lblStatus.visible="false"
	lblDate.text=Calendar1.selectedDate.Day
	lblMonth.text=Calendar1.selectedDate.Month
	lblyear.text=Calendar1.selectedDate.Year

End Sub
Sub Admin_Click(Source as Object, E as EventArgs)
	lblNewDate.text=calendar2.selectedDate.ToShortDateString
	lblStatus.visible="false"
	txtinfo.text=""
	Dim strConn as string = "server=(local);uid=YourUID;pwd=YourPWD;database=YourDataBase"
	Dim MySQL as string = "Select CalSchedule.CalDate, CalSchedule.Schedule from " & _
	"CalSchedule Where CalDate = @caldate "
	Dim MyConn as New SQLConnection(strConn)
	Dim objDR as SQLDataReader
	Dim Cmd as New SQLCommand(MySQL, MyConn)
	cmd.Parameters.Add(New SQLParameter("@caldate", lblNewDate.text))

	MyConn.Open()
	objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
	if objDR.Read=True then
		MyConn.Close
		MyConn.Open
		objDR.Close
		objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
		While objDR.Read()
		   litSchedule.text=objDR("calDate") & " : <br>" & objDR("Schedule")
		txtInfo.text=objDR("Schedule")
		blData="True"
		End While
	else
		blData="False"
	End if
	lblSave.text=blData
End Sub

Sub doAdmin(Source as Object, E as EventArgs)
	ph2.visible=true
	lnkNormal.enabled=true
	lnkAdmin.enabled=false
	Calendar1.Visible=false
	Calendar2.visible=true
	''ph1.visible=false
	litSchedule.text=""
End Sub

Sub doNormal(Source as Object, E as EventArgs)
	lnkNormal.enabled=false
	lnkAdmin.enabled=true
	Calendar1.Visible=true
	Calendar2.visible=false
	ph2.visible=false
	ph1.visible=true
	litSchedule.text=""
End Sub

Sub doSave(Source as Object, E as EventArgs)
	if lblSave.text="True" then
		sqlSave = "Update CalSchedule Set calDate=@caldate, schedule=@schedule " & _
		"where caldate=@caldate"
	else
		sqlSave= "Insert into CalSchedule (calDate, schedule) values(@caldate, @schedule)"
	End If
	Dim strConn as string = "server=(local);uid=YourUID;pwd=YourPWD;database=YourDataBase"
	Dim MyConn as New SQLConnection(strConn)
	Dim Cmd as New SQLCommand(sqlSave, MyConn)
		cmd.Parameters.Add(New SQLParameter("@caldate", lblNewDate.text))
		cmd.Parameters.Add(New SQLParameter("@schedule", txtInfo.text))
	MyConn.Open()
	Cmd.ExecuteNonQuery
	MyConn.Close
	lblStatus.visible="true"
	lblStatus.text="Your Data has been entered"
	txtInfo.text=""
	blData="False"
	lblSave.text=blData
End Sub
</script>
</head>
<body><Form id="form1" runat="server">
<asp:LinkButton id="lnkAdmin"
Text="Admin"
onclick="doAdmin"
runat="server" />

<asp:LinkButton id="lnkNormal"
enabled="false"
Text="Normal"
onclick="doNormal"
runat="server" />
<table border="0">
	<tr>
		<td align="left" width="500" valign="Top">
			<asp:Calendar runat="server"
			id="calendar1"
			Backcolor="White"
			Forecolor="Black"
			Borderwidth="2"
			OnselectionChanged="Calendar_Click"
			ShowGridLines="true"
			Width="500" />

			<asp:Calendar id="Calendar2"
			runat="server"
			Backcolor="White"
			BorderColor="blue"
			Forecolor="Black"
			Visible="false"
			Borderwidth="2"
			OnselectionChanged="Admin_Click"
			ShowGridLines="true"
			Width="500" />
		</td>
		<td align="Left" valign="Top">
		<asp:placeholder ID="ph1"  runat="server" visible="false">
		<b>Month:</b><asp:Label ID="lblMonth"  runat="server" /><br>
		<b>Date: </b><asp:Label ID="lblDate"  runat="server" /><br>
		<b>Year: </b> <asp:Label ID="lblYear"  runat="server" /><br>
		<b>Short Date: </b><asp:Label ID="lblFullDate"  runat="server" /><p>
		<b>Schedule for the Day:</b><br>
		<asp:Literal ID="litSchedule" runat="server"></asp:literal>
		</asp:placeholder>
		</td>
	</tr>
</table>
<asp:placeholder ID="ph2" visible="false" runat="server">
	<b><i>Date:</i> </b><asp:Label ID="lblNewDate"  runat="server" /><br>
	<b><i>Enter Schedule:</i></b><br>
	<asp:TextBox id="txtInfo"
	TextMode="MultiLine"
	rows=10
	width=600
	runat="server" />
	<asp:Button id="btnSave"
	Text="Save"
	onclick="doSave"
	runat="server" />
</asp:placeholder>

</Form>
<br>
<i><b><asp:Label ID="lblStatus" runat="server" /></b></i>
<asp:Label ID="lblSave" Visible="false" runat="server" />
</body>
</html>
ASP.NET 101

ASP.NET and MVC Tutorials

Linking a Calendar To a Database

Leave a Reply Cancel reply
