I have two applications that are using the calendar control. One is an events calendar and the other is a rental calendar system. These calendars are running really sloooooow, particularly the rental calendar system. Any suggestions on making these more efficient? Would it help to see my code?
Yes,would make it easier to analyze the problem if you could provide us w/ the url and if necessary the code. Vikram DiscountASP.NET www.DiscountASP.NET
Here is the URL: http://www.mcmobilitysystems.com/newerhold/AdminLogin.aspx use the username: temp and the password: temp1 For obvious reasons, I am only going to leave that username and password up for a couple of days. Click on "Events Calendar" and "Rental Calendar". There are probably no events or rentals in either at the moment. I would think that once there were events and rentals it would run EVEN SLOWER. Let me know if you want to see my code. Ann
Yeah,it is extremely slow.I dont think ,its something to do with the Calendar control.It could be your backend processing thats slowing it down (an inefficient query or something).Cant say,still if possible you could provide us w/ a snippet of you code. Vikram DiscountASP.NET www.DiscountASP.NET
Here is the rental calendar code. Be kind: Imports System.Data.OleDb Imports System.Data Imports System.Data.SqlClient Imports system.IO Partial Class Newer_AdminRentalCalendar Inherits System.Web.UI.Page Dim dbConn As String = "Data Source=mssql09.discountasp.net;Initial Catalog=DB_261536_annbransom;Persist Security Info=True;User ID=DB_261536_annbransom_user;Password=??????" Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'must be logged in If Session("logger") <> True Then Response.Redirect("default.aspx") End If If Session("Level") = 1 Then lblAvailVans.Visible = True GridView3.Visible = True lblSameDay.Visible = True GridView4.Visible = True End If If Session("Level") = 2 Then lblAvailVans.Visible = False GridView3.Visible = False lblSameDay.Visible = False GridView4.Visible = False End If If Not Page.IsPostBack Then Calendar1.SelectedDate = Date.Today lblAvailVans.Visible = False lblSameDay.Visible = False End If End Sub Protected Sub Cal_DayRender(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) Handles Calendar1.DayRender Dim sqlConn As SqlConnection = New SqlConnection(dbConn) Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand() Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter Dim dt As New DataTable Dim SelectStatement As String SelectStatement = "SELECT Ohio_Rentals.VanNum, VanNumber, CustLastName, Date, Ohio_Rentals.Location From Ohio_Rentals, Ohio_Vans WHERE Ohio_Rentals.VanNum = Ohio_Vans.VanNumber" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt) sqlConn.Close() If e.Day.IsOtherMonth Then e.Cell.Text = "" Exit Sub End If Dim Index As Integer If dt.Rows.Count <> 0 Then For Index = 0 To dt.Rows.Count - 1 If dt.Rows(Index).Item("Location") = "Columbus" And chkCol.Checked Then Dim a As String = dt.Rows(Index).Item("VanNumber").ToString If e.Day.Date = dt.Rows(Index).Item("Date") Then e.Cell.Controls.Add(New LiteralControl("<font color=Red style='font-size:9pt;'>" & a & "</font>")) End If End If If dt.Rows(Index).Item("Location") = "Mentor" And chkMen.Checked Then Dim a As String = dt.Rows(Index).Item("VanNumber").ToString If e.Day.Date = dt.Rows(Index).Item("Date") Then e.Cell.Controls.Add(New LiteralControl("<font color=Green style='font-size:9pt;'>" & a & "</font>")) End If End If If dt.Rows(Index).Item("Location") = "Dayton" And chkDay.Checked Then Dim a As String = dt.Rows(Index).Item("VanNumber").ToString If e.Day.Date = dt.Rows(Index).Item("Date") Then e.Cell.Controls.Add(New LiteralControl("<font color=Purple style='font-size:9pt;'>" & a & "</font>")) End If End If If dt.Rows(Index).Item("Location") = "Cincinnati" And chkCin.Checked Then Dim a As String = dt.Rows(Index).Item("VanNumber").ToString If e.Day.Date = dt.Rows(Index).Item("Date") Then e.Cell.Controls.Add(New LiteralControl("<font color=Navy style='font-size:9pt;'>" & a & "</font>")) End If End If Next End If build() dt.Clear() Avail_Vans() Avail_SameDay_Vans() End Sub Public Sub build() Dim time As Date = Calendar1.SelectedDate.ToShortDateString Dim sqlConn As SqlConnection = New SqlConnection(dbConn) Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand() Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter Dim dtBuild As New DataTable Dim Cin As String Dim Col As String Dim Men As String Dim Day As String If chkCin.Checked Then Cin = "Cincinnati" Else Cin = "NotCincinnati" End If If chkCol.Checked Then Col = "Columbus" Else Col = "NotColumbus" End If If chkMen.Checked Then Men = "Mentor" Else Men = "NotMentor" End If If chkDay.Checked Then Day = "Dayton" Else Day = "NotDayton" End If With sqlCmd .CommandType = CommandType.Text .Connection = sqlConn .CommandText = "Select Ref, Notes, Location, VanNum from Ohio_Rentals Where Date = '" & time & "' And (Location = '" & Cin & "' OR Location = '" & Col & "' OR Location = '" & Men & "' OR Location = '" & Day & "') ORDER BY VanNum" End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dtBuild) sqlConn.Close() If dtBuild.Rows.Count <> 0 Then DataList1.Visible = True DataList1.DataSource = dtBuild DataList1.DataBind() Else DataList1.Visible = False End If End Sub Sub Avail_Vans() Dim time As Date = Calendar1.SelectedDate.ToShortDateString Dim sqlConn As SqlConnection = New SqlConnection(dbConn) Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand() Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter Dim dt2 As New DataTable Dim dt3 As New DataTable Dim SelectStatement As String SelectStatement = "SELECT VanNum FROM Ohio_Vans, Ohio_Rentals WHERE Date = '" & time & " 12:00:00 AM' AND Ohio_Vans.VanNumber = Ohio_Rentals.VanNum" With sqlCmd .CommandType = CommandType.Text .Connection = sqlConn .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt2) sqlConn.Close() Dim LocationState As String = "Location = 'Null' OR" If chkCin.Checked Then LocationState = " Location = 'Cincinnati' or " End If If chkCol.Checked Then LocationState = LocationState & " Location = 'Columbus' or " End If If chkMen.Checked Then LocationState = LocationState & " Location = 'Mentor' or" End If If chkDay.Checked Then LocationState = LocationState & " Location = 'Dayton' or " End If LocationState = LocationState & " Location = 'Mars'" If dt2.Rows.Count > 1 Then Dim Index As Integer Dim ORString As String = "" Dim Add As String For Index = 0 To dt2.Rows.Count - 2 Add = "VanNumber <> '" & dt2.Rows(Index).Item(0).ToString & "' AND " ORString = ORString & Add Next Dim Last As Integer Last = dt2.Rows.Count - 1 Add = "VanNumber <> '" & dt2.Rows(Last).Item(0).ToString & "'" ORString = ORString & Add Add = "And Active = '1'" ORString = ORString & Add SelectStatement = "Select * From Ohio_Vans WHERE (" & LocationState & ") AND " & ORString & "" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With dt2.Clear() sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt3) sqlConn.Close() Else If dt2.Rows.Count = 1 Then SelectStatement = "Select * From Ohio_Vans WHERE (" & LocationState & ") AND VanNumber <> '" & dt2.Rows(0).Item("VanNum") & "' AND Active = '1'" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With dt2.Clear() sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt3) sqlConn.Close() Else If dt2.Rows.Count = 0 Then SelectStatement = "Select * From Ohio_Vans WHERE (" & LocationState & ") AND Active = '1'" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With dt2.Clear() sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt3) sqlConn.Close() End If End If End If Dim Index2 As Integer Dim dtBigGun As New DataTable Dim dtLittleGun As New DataTable If dt3.Rows.Count <> 0 Then For Index2 = 0 To dt3.Rows.Count - 1 SelectStatement = "Select Distinct MassID, Ohio_VanLog.VanNumber, StartDate, VanMake, VanModel, VanSize, Location, HandControls, TransferSeat From Ohio_VanLog, Ohio_Vans WHERE Ohio_VanLog.VanNumber = '" & dt3.Rows(Index2).Item("VanNumber") & "' AND Ohio_VanLog.VanNumber = Ohio_Vans.VanNumber AND (StartDate > '" & time & "' OR StartDate Is Null) ORDER BY StartDate Asc" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dtLittleGun) sqlConn.Close() If dtLittleGun.Rows.Count = 1 Then SelectStatement = "Select Distinct Top 1 MassID, Ohio_VanLog.VanNumber, StartDate, VanMake, VanModel, VanSize, Location, HandControls, TransferSeat From Ohio_VanLog, Ohio_Vans WHERE Ohio_VanLog.VanNumber = '" & dt3.Rows(Index2).Item("VanNumber") & "' AND Ohio_VanLog.VanNumber = Ohio_Vans.VanNumber AND (StartDate > '" & time & "' OR StartDate Is Null) ORDER BY StartDate Asc" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dtBigGun) sqlConn.Close() dtLittleGun.Clear() Else If dtLittleGun.Rows.Count > 1 Then SelectStatement = "Select Distinct Top 1 MassID, Ohio_VanLog.VanNumber, StartDate, VanMake, VanModel, VanSize, Location, HandControls, TransferSeat From Ohio_VanLog, Ohio_Vans WHERE Ohio_VanLog.VanNumber = '" & dt3.Rows(Index2).Item("VanNumber") & "' AND Ohio_VanLog.VanNumber = Ohio_Vans.VanNumber AND (StartDate > '" & time & "') ORDER BY StartDate Asc" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dtBigGun) sqlConn.Close() dtLittleGun.Clear() End If End If Next End If If dtBigGun.Rows.Count <> 0 And Session("Level") = 1 Then lblAvailVans.Text = "Vehicles Available On Selected Date:" lblAvailVans.Visible = True End If GridView3.DataSource = dtBigGun GridView3.DataBind() End Sub Sub Avail_SameDay_Vans() Dim time As Date = Calendar1.SelectedDate.ToShortDateString Dim sqlConn As SqlConnection = New SqlConnection(dbConn) Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand() Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter Dim dt6 As New DataTable Dim dt7 As New DataTable Dim SelectStatement As String SelectStatement = "SELECT VanNum FROM Ohio_Vans, Ohio_Rentals WHERE Date = '" & time & " 12:00:00 AM' AND Ohio_Vans.VanNumber = Ohio_Rentals.VanNum" With sqlCmd .CommandType = CommandType.Text .Connection = sqlConn .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt6) sqlConn.Close() Dim LocationState As String = "Location = 'Null' OR" If chkCin.Checked Then LocationState = " Location = 'Cincinnati' or " End If If chkCol.Checked Then LocationState = LocationState & " Location = 'Columbus' or " End If If chkMen.Checked Then LocationState = LocationState & " Location = 'Mentor' or" End If If chkDay.Checked Then LocationState = LocationState & " Location = 'Dayton' or " End If LocationState = LocationState & " Location = 'Mars'" If dt6.Rows.Count > 1 Then Dim Index As Integer Dim ORString As String = "" Dim Add As String For Index = 0 To dt6.Rows.Count - 2 Add = "Ohio_Vans.VanNumber <> '" & dt6.Rows(Index).Item(0).ToString & "' AND " ORString = ORString & Add Next Dim Last As Integer Last = dt6.Rows.Count - 1 Add = "Ohio_Vans.VanNumber <> '" & dt6.Rows(Last).Item(0).ToString & "'" ORString = ORString & Add Add = "And Active = '1'" ORString = ORString & Add SelectStatement = "Select Ohio_VanLog.VanNumber, EndDate From Ohio_Vans, Ohio_VanLog WHERE (" & LocationState & ") AND Ohio_Vans.VanNumber = Ohio_VanLog.VanNumber AND EndDate = '" & time & "'" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With dt6.Clear() sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt7) sqlConn.Close() Else If dt6.Rows.Count = 1 Then SelectStatement = "Select Ohio_VanLog.VanNumber, EndDate From Ohio_Vans, Ohio_VanLog WHERE (" & LocationState & ") AND Ohio_Vans.VanNumber = Ohio_VanLog.VanNumber AND EndDate = '" & time & "' And Active = '1'" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With dt6.Clear() sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt7) sqlConn.Close() Else If dt6.Rows.Count = 0 Then SelectStatement = "Select Ohio_VanLog.VanNumber, EndDate From Ohio_Vans, Ohio_VanLog WHERE (" & LocationState & ") AND Ohio_Vans.VanNumber = Ohio_VanLog.VanNumber AND EndDate = '" & time & "' AND Active = '1'" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With dt6.Clear() sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt7) sqlConn.Close() End If End If End If Dim Index2 As Integer Dim dtBigGun2 As New DataTable Dim dtLittleGun2 As New DataTable If dt7.Rows.Count <> 0 Then For Index2 = 0 To dt7.Rows.Count - 1 SelectStatement = "Select Distinct MassID, Ohio_VanLog.VanNumber, StartDate, VanMake, VanModel, VanSize, Location, HandControls, TransferSeat From Ohio_VanLog, Ohio_Vans WHERE Ohio_VanLog.VanNumber = '" & dt7.Rows(Index2).Item("VanNumber") & "' AND Ohio_VanLog.VanNumber = Ohio_Vans.VanNumber AND (StartDate >= '" & time & "' OR StartDate Is Null) ORDER BY StartDate Asc" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dtLittleGun2) sqlConn.Close() If dtLittleGun2.Rows.Count = 1 Then SelectStatement = "Select Distinct Top 1 MassID, Ohio_VanLog.VanNumber, StartDate, VanMake, VanModel, VanSize, Location, HandControls, TransferSeat From Ohio_VanLog, Ohio_Vans WHERE Ohio_VanLog.VanNumber = '" & dt7.Rows(Index2).Item("VanNumber") & "' AND Ohio_VanLog.VanNumber = Ohio_Vans.VanNumber AND (StartDate >= '" & time & "' OR StartDate Is Null) ORDER BY StartDate Asc" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dtBigGun2) sqlConn.Close() dtLittleGun2.Clear() Else If dtLittleGun2.Rows.Count > 1 Then SelectStatement = "Select Distinct Top 1 MassID, Ohio_VanLog.VanNumber, StartDate, VanMake, VanModel, VanSize, Location, HandControls, TransferSeat From Ohio_VanLog, Ohio_Vans WHERE Ohio_VanLog.VanNumber = '" & dt7.Rows(Index2).Item("VanNumber") & "' AND Ohio_VanLog.VanNumber = Ohio_Vans.VanNumber AND (StartDate >= '" & time & "') ORDER BY StartDate Asc" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dtBigGun2) sqlConn.Close() dtLittleGun2.Clear() End If End If Next End If If dtBigGun2.Rows.Count <> 0 And Session("Level") = 1 Then lblSameDay.Text = "Vehicles Returning On Selected Date:" lblSameDay.Visible = True End If GridView4.DataSource = dtBigGun2 GridView4.DataBind() End Sub Protected Sub GridView3_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView3.SelectedIndexChanged Dim RowIndex As Integer RowIndex = GridView3.SelectedIndex() Dim time As Date = Calendar1.SelectedDate.ToShortDateString Dim sqlConn As SqlConnection = New SqlConnection(dbConn) Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand() Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter Dim dt2 As New DataTable Dim dt3 As New DataTable Dim SelectStatement As String SelectStatement = "SELECT VanNum FROM Ohio_Vans, Ohio_Rentals WHERE Date = '" & time & " 12:00:00 AM' AND Ohio_Vans.VanNumber = Ohio_Rentals.VanNum" With sqlCmd .CommandType = CommandType.Text .Connection = sqlConn .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt2) sqlConn.Close() Dim LocationState As String = "Location = 'Null' OR" If chkCin.Checked Then LocationState = " Location = 'Cincinnati' or " End If If chkCol.Checked Then LocationState = LocationState & " Location = 'Columbus' or " End If If chkMen.Checked Then LocationState = LocationState & " Location = 'Mentor' or" End If If chkDay.Checked Then LocationState = LocationState & " Location = 'Dayton' or " End If LocationState = LocationState & " Location = 'Mars'" If dt2.Rows.Count > 1 Then Dim Index As Integer Dim ORString As String = "" Dim Add As String For Index = 0 To dt2.Rows.Count - 2 Add = "VanNumber <> '" & dt2.Rows(Index).Item(0).ToString & "' AND " ORString = ORString & Add Next Dim Last As Integer Last = dt2.Rows.Count - 1 Add = "VanNumber <> '" & dt2.Rows(Last).Item(0).ToString & "'" ORString = ORString & Add Add = "And Active = '1'" ORString = ORString & Add SelectStatement = "Select * From Ohio_Vans WHERE (" & LocationState & ") AND " & ORString & "" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With dt2.Clear() sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt3) sqlConn.Close() Else If dt2.Rows.Count = 1 Then SelectStatement = "Select * From Ohio_Vans WHERE (" & LocationState & ") AND VanNumber <> '" & dt2.Rows(0).Item("VanNum") & "' AND Active = '1'" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With dt2.Clear() sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt3) sqlConn.Close() Else If dt2.Rows.Count = 0 Then SelectStatement = "Select * From Ohio_Vans WHERE (" & LocationState & ") AND Active = '1'" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With dt2.Clear() sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt3) sqlConn.Close() End If End If End If Dim Index2 As Integer Dim dtBigGun As New DataTable Dim dtLittleGun As New DataTable If dt3.Rows.Count <> 0 Then For Index2 = 0 To dt3.Rows.Count - 1 SelectStatement = "Select Distinct MassID, Ohio_VanLog.VanNumber, StartDate, VanMake, VanModel, VanSize, Location, HandControls, TransferSeat From Ohio_VanLog, Ohio_Vans WHERE Ohio_VanLog.VanNumber = '" & dt3.Rows(Index2).Item("VanNumber") & "' AND Ohio_VanLog.VanNumber = Ohio_Vans.VanNumber AND (StartDate > '" & time & "' OR StartDate Is Null) ORDER BY StartDate Asc" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dtLittleGun) sqlConn.Close() If dtLittleGun.Rows.Count = 1 Then SelectStatement = "Select Distinct Top 1 MassID, Ohio_VanLog.VanNumber, StartDate, VanMake, VanModel, VanSize, Location, HandControls, TransferSeat From Ohio_VanLog, Ohio_Vans WHERE Ohio_VanLog.VanNumber = '" & dt3.Rows(Index2).Item("VanNumber") & "' AND Ohio_VanLog.VanNumber = Ohio_Vans.VanNumber AND (StartDate > '" & time & "' OR StartDate Is Null) ORDER BY StartDate Asc" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dtBigGun) sqlConn.Close() dtLittleGun.Clear() Else If dtLittleGun.Rows.Count > 1 Then SelectStatement = "Select Distinct Top 1 MassID, Ohio_VanLog.VanNumber, StartDate, VanMake, VanModel, VanSize, Location, HandControls, TransferSeat From Ohio_VanLog, Ohio_Vans WHERE Ohio_VanLog.VanNumber = '" & dt3.Rows(Index2).Item("VanNumber") & "' AND Ohio_VanLog.VanNumber = Ohio_Vans.VanNumber AND (StartDate > '" & time & "') ORDER BY StartDate Asc" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dtBigGun) sqlConn.Close() dtLittleGun.Clear() End If End If Next End If If dtBigGun.Rows.Count <> 0 And Session("Level") = 1 Then lblAvailVans.Text = "Vehicles Available On Selected Date:" lblAvailVans.Visible = True End If Response.Redirect(<FONT c
Here is the events calendar code: Imports System.Data.OleDb Imports System.Data Imports System.Data.SqlClient Imports system.IO Partial Class Newer_AdminEventsCalendar Inherits System.Web.UI.Page Dim dbConn As String = "Data Source=mssql09.discountasp.net;Initial Catalog=DB_261536_annbransom;Persist Security Info=True;User ID=DB_261536_annbransom_user;Password=*****" Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'must be logged in If Session("logger") <> True Then Response.Redirect("default.aspx") End If Calendar1.SelectedDate = Date.Today chkAll.Checked = True End Sub Protected Sub Cal_DayRender(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) Handles Calendar1.DayRender Dim sqlConn As SqlConnection = New SqlConnection(dbConn) Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand() Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter Dim dt As New DataTable Dim SelectStatement As String SelectStatement = "SELECT Title, Date, Location From Ohio_Events" With sqlCmd .Connection = sqlConn .CommandType = CommandType.Text .CommandText = SelectStatement End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt) sqlConn.Close() If e.Day.IsOtherMonth Then e.Cell.Text = "" Exit Sub End If Dim Index As Integer If dt.Rows.Count <> 0 Then For Index = 0 To dt.Rows.Count - 1 If dt.Rows(Index).Item("Location") = "Columbus" And chkCol.Checked Then Dim a As String = dt.Rows(Index).Item("Title") If e.Day.Date = dt.Rows(Index).Item("Date") Then e.Cell.Controls.Add(New LiteralControl("<font color=Red style='font-size:9pt;'>" & a & "</font>")) End If End If If dt.Rows(Index).Item("Location") = "Mentor" And chkMen.Checked Then Dim a As String = dt.Rows(Index).Item("Title") If e.Day.Date = dt.Rows(Index).Item("Date") Then e.Cell.Controls.Add(New LiteralControl("<font color=Green style='font-size:9pt;'>" & a & "</font>")) End If End If If dt.Rows(Index).Item("Location") = "Dayton" And chkDay.Checked Then Dim a As String = dt.Rows(Index).Item("Title") If e.Day.Date = dt.Rows(Index).Item("Date") Then e.Cell.Controls.Add(New LiteralControl("<font color=Purple style='font-size:9pt;'>" & a & "</font>")) End If End If If dt.Rows(Index).Item("Location") = "Cincinnati" And chkCin.Checked Then Dim a As String = dt.Rows(Index).Item("Title") If e.Day.Date = dt.Rows(Index).Item("Date") Then e.Cell.Controls.Add(New LiteralControl("<font color=Navy style='font-size:9pt;'>" & a & "</font>")) End If End If If dt.Rows(Index).Item("Location") = "All" And chkAll.Checked Then Dim a As String = dt.Rows(Index).Item("Title") If e.Day.Date = dt.Rows(Index).Item("Date") Then e.Cell.Controls.Add(New LiteralControl("<font color=Orange style='font-size:9pt;'>" & a & "</font>")) End If End If Next End If build() dt.Clear() End Sub Public Sub build() Dim time As Date = Calendar1.SelectedDate.ToShortDateString Dim sqlConn As SqlConnection = New SqlConnection(dbConn) Dim sqlCmd As SqlCommand = New System.Data.SqlClient.SqlCommand() Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter Dim dt As New DataTable Dim Cin As String Dim Col As String Dim Men As String Dim Day As String Dim All As String If chkCin.Checked Then Cin = "Cincinnati" Else Cin = "NotCincinnati" End If If chkCol.Checked Then Col = "Columbus" Else Col = "NotColumbus" End If If chkMen.Checked Then Men = "Mentor" Else Men = "NotMentor" End If If chkDay.Checked Then Day = "Dayton" Else Day = "NotDayton" End If If chkAll.Checked Then All = "All" Else All = "NotAll" End If With sqlCmd .CommandType = CommandType.Text .Connection = sqlConn .CommandText = "Select Title, Date, Description, Location from Ohio_Events Where Date = '" & time & "' And (Location = '" & Cin & "' OR Location = '" & Col & "' OR Location = '" & Men & "' OR Location = '" & Day & "' OR Location = '" & All & "')" End With sqlConn.Open() sqlAdapter.SelectCommand = sqlCmd sqlAdapter.Fill(dt) sqlConn.Close() If dt.Rows.Count <> 0 Then DataList1.Visible = True DataList1.DataSource = dt DataList1.DataBind() Else DataList1.Visible = False End If End Sub End Class Post Edited By Moderator (Aristotle [DASP]) : 1/11/2007 9:33:10 PM GMT
I see that you have a lot of queries your application can run. Have you tried running the queries directly on the db server. Try running it with query analyzer and see if you get the same or similar latency as your web application. Maybe you just need to modify a query or you might have to go through your database base and make some optimization. Can't tell for sure, but running the queries directly on the database is a good place to start troubleshooting.
Thanks, but the queries seem to work fine in on the Database server itself. I am going to try and go through and make sure that the queries are only pulling exactly what they need. Anybody else got any suggestions?
Specially check the ones within for loops.There could be a logical flaw thats causing the for loop to run for a while and cause a delay. Vikram DiscountASP.NET www.DiscountASP.NET
