I'm developing a calendar application that allows you to add new appointments. When you do, it:
1) Adds a new row to the dataset
2) Updates the dataset back to the data source.
For some reason, if you add two appointments to the calendar, calling them one and two, the database winds up with 3 apointsments, like:
one
one
two
(it doesn't show up until you close the application and reopen it, because I store the dataset in a Session variable and the page won't load data from the database unless the session variable is nothing).
So basically everything that was added at one point gets added again.
It's kind of hard to visualize, I know, so I posted it to a public site, so you can try it out and see what I mean. It's at
http://www.datasushi.net/Calendar/Calendar2.aspx
After you load the page, add two appointments for today. Then close your browser, open it again and go to the same page. you'll see the first appointment got added twice. BTW you have to add the two appointments in the same session for this error to occur. If you add an appointment, close the browser, open it again and add the second appointment, this error is not generated.
Below are the code-behinds for Calendar2.aspx and New.aspx.
Calendar2.aspx looks like this...
Imports System.Data
Imports System.Data.SqlClient
Public Class Calendar2
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.cnxn2 = New System.Data.SqlClient.SqlConnection
'
'cnxn2
'
Me.cnxn2.ConnectionString = "ConnectionStringGoesHere"
End Sub
Protected WithEvents Calendar1 As System.Web.UI.WebControls.Calendar
Protected WithEvents btnToday As System.Web.UI.WebControls.Button
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents MyDataGrid As System.Web.UI.WebControls.DataGrid
Protected WithEvents lblToday As System.Web.UI.WebControls.Label
Protected WithEvents lblError As System.Web.UI.WebControls.Label
Protected WithEvents btnNew As System.Web.UI.HtmlControls.HtmlInputButton
Protected WithEvents cnxn2 As System.Data.SqlClient.SqlConnection
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents Button3 As System.Web.UI.WebControls.Button
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Dim strUser As String
Dim ds As New DataSet
Dim da As New SqlDataAdapter
Protected WithEvents lblDetail As System.Web.UI.WebControls.Label
Dim cmd As New SqlCommand
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
strUser = Request.ServerVariables(7)
'lblError.Text = lblError.Text & strUser & "<br>"
If IsAuthorized(strUser) Then
btnNew.Disabled = False
End If
If Session("dss") Is Nothing Then
'populate dataset
cnxn2.Open()
cmd.Connection = cnxn2
cmd.CommandText = "SELECT * FROM qry_Calendar2"
da.SelectCommand = cmd
da.FillSchema(ds, SchemaType.Source, "Calendar")
da.Fill(ds, "Calendar")
cmd.CommandText = "Select * FROM lut_Rooms ORDER BY pkRoomID"
da.FillSchema(ds, SchemaType.Source, "Rooms")
da.Fill(ds, "Rooms")
cmd.dispose()
cnxn2.Dispose()
'Establish Data relation
Dim Room As New DataRelation("ds", ds.Tables("Rooms").Columns("pkRoomID"), ds.Tables("Calendar").Columns("fkRoomID"))
ds.Relations.Add(Room)
'Save dataset to Session as xml
Dim stream As New System.IO.StringWriter
ds.WriteXml(stream, XmlWriteMode.DiffGram)
Session("ds") = stream
Session("dss") = ds.GetXmlSchema
Else
'get xml back from Session
Dim sr As New System.IO.StringReader(Session("ds").ToString)
Dim srr As New System.IO.StringReader(Session("dss"))
ds.ReadXmlSchema(srr)
ds.ReadXml(sr)
End If
If FirstPageLoad() Then
SelectToday(Nothing, Nothing)
lblToday.Text = "Detail For " & Format(Calendar1.SelectedDate, "Long Date")
End If
End Sub
Public Sub SelectToday(ByVal sender As Object, ByVal e As EventArgs)
Calendar1.SelectedDate = Today
Calendar1.VisibleDate = Today
lblToday.Text = "Detail For " & Format(Calendar1.SelectedDate, "Long Date")
GetAppointments(Today)
End Sub
Public Sub GetAppointments(ByVal ChosenDate As DateTime)
Dim dr As DataRow, drArray() As DataRow
Dim strDetail As String
'Setup drArray
If Not ds.Tables("Calendar") Is Nothing Then
drArray = ds.Tables("Calendar").Select("txtStart = '" & BuildDateString(Calendar1.SelectedDate) & "'", "dtStart")
'lblError.Text = lblError.Text & "Items:" & drArray.Length.ToString & "<br>"
Else
lblError.Text = lblError.Text & "<br>Dataset is nothing<br>"
Exit Sub
End If
'Display Detail
strDetail = "<table cellpadding=0 cellspacing=0><tr height=0><td width=75></td><td width=130></td><td width=116></td></tr>"
For Each dr In drArray
strDetail = strDetail & "<tr>"
strDetail = strDetail & "<td colspan=3 style=""font-size:10pt; font-weight: bold;""><a style=""text-decoration: none;"" href=""javascript
: void(0)"" onClick=""window.open('Appointment2.aspx?ID=" & dr("pkCalendarItemID") & "', 'Appointment', 'width=400, height=400 top=50; left=50, location=0, status=0, scollbars=1')"", >" & dr("txtMeetingName").ToString & "</a></td>"
strDetail = strDetail & "</tr><tr style=""font-size: 9pt;"">"
strDetail = strDetail & "<td colspan=2 style=""border-bottom: #000000 1px solid;"">" & dr("dtStart").toShortTimeString() & " - " & dr("dtEnd").toShortTimeString() & "</td>"
strDetail = strDetail & "<td align=right style=""border-bottom: #000000 1px solid;"">" & dr("RoomName").ToString & "</td>"
strDetail = strDetail & "</tr>"
strDetail = strDetail & "<tr height=10><td colspan=3></td></tr>" 'spacer row
Next
If drArray.Length = 0 Then
strDetail = strDetail & "<tr>"
strDetail = strDetail & "<td colspan=3 style=""font-size:10pt; font-weight: bold;"">No Meetings or Events</td></tr>"
strDetail = strDetail & "<tr height=10><td colspan=3></td></tr>" 'spacer row
End If
strDetail = strDetail & "</table>"
lblDetail.Text = strDetail
End Sub
Public Sub OnSelectionChanged(ByVal sender As Object, ByVal e As EventArgs)
lblToday.Text = "Detail For " & Format(Calendar1.SelectedDate, "Long Date")
'Display today's appointments
GetAppointments(Calendar1.SelectedDate)
End Sub
Sub DayRender(ByVal source As Object, ByVal e As DayRenderEventArgs)
Dim drArray() As DataRow
' Change the background color of the days in the month to yellow.
' Make bold if not empty
drArray = ds.Tables("Calendar").Select("txtStart='" & BuildDateString(e.Day.Date()) & "'")
If drArray.Length > 0 Then
e.Cell.Font.Bold = True
End If
End Sub 'DayRender
Function IsAuthorized(ByVal user As String) As Boolean
IsAuthorized = True
Exit Function
'Set User = everything after the \ character
user = user.Substring(user.IndexOf("\") + 1)
'lblError.Text = user
'Compare User to a database of authorized Calendar Item Adders
cmd.CommandText = "Select Calendar from Users WHERE txtUserName='" & user & "'"
cnxn2.Open()
cmd.Connection = cnxn2
If cmd.ExecuteScalar = True Then
IsAuthorized = True
Else
IsAuthorized = False
End If
cmd.Dispose()
'cnxn.Dispose()
End Function
Function FirstPageLoad() As Boolean
FirstPageLoad = Not IsPostBack
End Function
Function BuildDateString(ByVal DateToConvert As DateTime) As String
Dim MonthString, DayString, YearString As String
MonthString = DateToConvert.Month
If Len(MonthString) = 1 Then MonthString = "0" & MonthString
DayString = DateToConvert.Day
If Len(DayString) = 1 Then DayString = "0" & DayString
YearString = DateToConvert.Year
BuildDateString = MonthString & "/" & DayString & "/" & YearString
End Function
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Server.Transfer("DisplayDS.aspx")
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim cmd As New SqlCommand
cnxn2.Open()
cmd.Connection = cnxn2
cmd.CommandText = "DELETE FROM Calendar"
cmd.ExecuteNonQuery()
cmd.Dispose()
cnxn2.Close()
Session("dss") = Nothing
Session("ds") = Nothing
Server.Transfer("Calendar2.aspx")
End Sub
End Class
And New.aspx.vb looks like this...
Imports System.Data
Imports System.Data.SqlClient
Public Class _New
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.cnxn = New System.Data.SqlClient.SqlConnection
'
'cnxn
'
Me.cnxn.ConnectionString = "Connection String Goes Here"
End Sub
Protected WithEvents cnxn As System.Data.SqlClient.SqlConnection
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents lblMeetingName As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents Calendar2 As System.Web.UI.WebControls.Calendar
Protected WithEvents ddlMeetingRooms As System.Web.UI.WebControls.DropDownList
Protected WithEvents ddlDuration As System.Web.UI.WebControls.DropDownList
Protected WithEvents lblTimeValidator As System.Web.UI.WebControls.Label
Protected WithEvents lblLocatioinInfo As System.Web.UI.WebControls.Label
Protected WithEvents lblDateValidator As System.Web.UI.WebControls.Label
Protected WithEvents Label6 As System.Web.UI.WebControls.Label
Protected WithEvents tbStartTime As System.Web.UI.WebControls.TextBox
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents tbDescription As System.Web.UI.WebControls.TextBox
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents lblWhereValidator As System.Web.UI.WebControls.Label
Protected WithEvents tbMeetingName As System.Web.UI.WebControls.TextBox
Protected WithEvents RequiredFieldValidator1 As System.Web.UI.WebControls.RequiredFieldValidator
Protected WithEvents lblError As System.Web.UI.WebControls.Label
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Dim ds As New DataSet
Public BodyAction As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Get dataset from Session
Dim sr As New System.IO.StringReader(Session("ds").ToString)
Dim srr As New System.IO.StringReader(Session("dss"))
ds.ReadXmlSchema(srr)
ds.ReadXml(sr)
'Bind Dropdown list to open rooms
PopulateDDL()
If Not IsPostBack Then
Calendar2.SelectedDate = Today
Calendar2.VisibleDate = Today
End If
End Sub
Sub PopulateDDL()
Dim tmpSelection
tmpSelection = ddlMeetingRooms.SelectedValue
Dim dr As DataRow, ConflictingAppointments() As DataRow, RoomArray() As DataRow, conflictSQL As String
Dim StartDateTime, EndDateTime As DateTime, StartTime As DateTime
Dim item As ListItem
If IsPostBack = False Or AllButRoomValid() = False Then
ddlMeetingRooms.Items.Clear()
item = New ListItem
item.Value = 0
item.Text = "(Fill in other data)"
ddlMeetingRooms.Items.Add(item)
Else
'Change Text in textbox to correct format
tbStartTime.Text = DateTime.Parse(tbStartTime.Text).ToShortTimeString
'put initial item in Dropdown LIst
ddlMeetingRooms.Items.Clear()
item = New ListItem
item.Value = 0
item.Text = "(Select Room)"
ddlMeetingRooms.Items.Add(item)
'Add Rooms to dropdown list that have no conflicting meetings
StartTime = DateTime.Parse(tbStartTime.Text)
StartDateTime = Calendar2.SelectedDate
StartDateTime = StartDateTime.AddHours(StartTime.Hour)
StartDateTime = StartDateTime.AddMinutes(StartTime.Minute)
StartDateTime = StartDateTime.AddSeconds(StartTime.Second)
EndDateTime = StartDateTime.AddHours(ddlDuration.SelectedValue)
'Check each room for conflicts at that time and date
For Each dr In ds.Tables("Rooms").Rows
'Look for Conflicts
conflictSQL = "fkRoomID = " & dr("pkRoomID") & " AND ((dtStart >= #" & StartDateTime & "# AND dtStart < #" & EndDateTime & "#) OR " _
& "(dtEnd > #" & StartDateTime & "# AND dtEnd <= #" & EndDateTime & "#))"
ConflictingAppointments = ds.Tables("Calendar").Select(conflictSQL)
If ConflictingAppointments.Length = 0 Then
item = New ListItem
item.Value = dr("pkRoomID")
item.Text = dr("RoomName")
ddlMeetingRooms.Items.Add(item)
End If
Next
ddlMeetingRooms.SelectedValue = tmpSelection
End If
End Sub
Private Sub ddlDuration_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
PopulateDDL()
End Sub
Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
If ValidateForm() = True Then
SaveToDatabase()
BodyAction = "onload=""window.opener.location='Calendar2.as px'; window.close()"""
Exit Sub
End If
End Sub
Function AllButRoomValid() As Boolean
Dim StartTime As DateTime, StartDate As DateTime, Duration As Single, Room As Int16
Dim tmpTimeValid As Boolean = True
AllButRoomValid = True
Try
StartTime = DateTime.Parse(tbStartTime.Text)
Catch ex As Exception
'lblTimeValidator.Visible = True
AllButRoomValid = False
tmpTimeValid = False
End Try
If tmpTimeValid = True Then lblTimeValidator.Visible = False
If Calendar2.SelectedDate = Nothing Then
'lblDateValidator.Visible = True
AllButRoomValid = False
Else
lblDateValidator.Visible = False
End If
If ddlDuration.SelectedValue = "0" Then
AllButRoomValid = False
End If
End Function
Function ValidateForm() As Boolean
Dim StartTime As DateTime, StartDate As DateTime, Duration As Single, Room As Int16
Dim tmpTimeValid As Boolean = True
ValidateForm = True
Try
StartTime = DateTime.Parse(tbStartTime.Text)
Catch ex As Exception
lblTimeValidator.Visible = True
ValidateForm = False
tmpTimeValid = False
End Try
If tmpTimeValid = True Then lblTimeValidator.Visible = False
If Calendar2.SelectedDate = Nothing Then
lblDateValidator.Visible = True
ValidateForm = False
Else
lblDateValidator.Visible = False
End If
If ddlMeetingRooms.SelectedIndex = 0 Then
ValidateForm = False
lblWhereValidator.Visible = True
Else
lblWhereValidator.Visible = False
End If
If ddlDuration.SelectedValue = "0" Then
ValidateForm = False
End If
'lblError.Text = ddlMeetingRooms.SelectedIndex.ToString & "<br>"
'lblError.Text = lblError.Text & ValidateForm.ToString
End Function
Sub SaveToDatabase()
'Add a new record to the dataset
Dim dr As DataRow
Dim StartTime, StartDateTime, EndDateTime As DateTime
dr = ds.Tables("Calendar").NewRow
dr("chrCreator") = Request.ServerVariables(7).Substring(Request.Serve rVariables(7).IndexOf("\") + 1)
StartTime = DateTime.Parse(tbStartTime.Text)
StartDateTime = Calendar2.SelectedDate
StartDateTime = StartDateTime.AddHours(StartTime.Hour)
StartDateTime = StartDateTime.AddMinutes(StartTime.Minute)
StartDateTime = StartDateTime.AddSeconds(StartTime.Second)
EndDateTime = StartDateTime.AddHours(ddlDuration.SelectedValue)
dr("txtStart") = BuildDateString(Calendar2.SelectedDate)
dr("dtStart") = StartDateTime
dr("dtEnd") = EndDateTime
dr("txtMeetingName") = tbMeetingName.Text
dr("txtMeetingDescription") = tbDescription.Text
dr("fkRoomID") = ddlMeetingRooms.SelectedValue
dr("RoomName") = ds.Tables("Rooms").Rows(ddlMeetingRooms.SelectedVa lue - 1)("RoomName")
ds.Tables("Calendar").Rows.Add(dr)
'dr back into dataset, dataset to Session
Dim stream As New System.IO.StringWriter
ds.WriteXml(stream, XmlWriteMode.DiffGram)
Session("ds") = stream
Session("dss") = ds.GetXmlSchema
'Update Database
cnxn.Open()
Dim cmd As New SqlCommand("SELECT * From Calendar", cnxn)
Dim da As New SqlDataAdapter
da.SelectCommand = cmd
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
Try
da.Update(ds.Tables("Calendar").Select(Nothing, Nothing, DataViewRowState.Added))
Catch ex As Exception
Server.Transfer("Error.aspx?Error=" & ex.ToString)
End Try
ds.Dispose()
da.Dispose()
cmd.Dispose()
cnxn.Dispose()
End Sub
Private Sub Calendar2_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
PopulateDDL()
End Sub
Private Sub tbStartTime_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
PopulateDDL()
End Sub
Function BuildDateString(ByVal DateToConvert As DateTime) As String
Dim MonthString, DayString, YearString As String
MonthString = DateToConvert.Month
If Len(MonthString) = 1 Then MonthString = "0" & MonthString
DayString = DateToConvert.Day
If Len(DayString) = 1 Then DayString = "0" & DayString
YearString = DateToConvert.Year
BuildDateString = MonthString & "/" & DayString & "/" & YearString
End Function
End Class
I Don't Understand Dataset Updates!
