I was wondering if anyone might know how I can take this code, and turn the datagrid into a graph?
I haven't been doing this very long, and fighting it the whole way. Managed to learn some cool things along the way though.
also the breakdown of what I wish to acomplish is
if * just a bar that shows all tickets. if day a bar chart or line chart that shows the daily tickets same with weekly and monthly.
Thanks again for any help on this.
I haven't been doing this very long, and fighting it the whole way. Managed to learn some cool things along the way though.
also the breakdown of what I wish to acomplish is
if * just a bar that shows all tickets. if day a bar chart or line chart that shows the daily tickets same with weekly and monthly.
Thanks again for any help on this.
Code:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<HTML>
<script language="VB" runat="server">
Dim MyConnection As SqlConnection
Dim nmStart As DateTime = DateTime.Today
Dim nmTomorrow As DateTime = nmStart.AddDays(1)
Dim dtStart As New DateTime(1970, 1, 1)
Dim nmStartOfWeek = nmStart.AddDays( - nmStart.DayOfWeek )
Dim nmStartOfNextWeek = nmStartOfWeek.AddDays(7)
Dim nmStartOfMonth = new DateTime( nmStart.Year, nmStart.Month, 1 )
Dim nmStartOfNextMonth = nmStartOfMonth.AddMonths(1)
Dim difStart As TimeSpan = nmStart.Subtract(dtStart)
Dim difTomorrow As TimeSpan = nmTomorrow.Subtract(dtStart)
Dim difStartOfWeek As TimeSpan = nmStartOfWeek.Subtract(dtStart)
Dim difStartOfNextWeek As TimeSpan = nmStartOfNextWeek.Subtract(dtStart)
Dim difStartOfMonth As TimeSpan = nmStartOfMonth.Subtract(dtStart)
Dim difStartOfNextMonth As TimeSpan = nmStartOfNextMonth.Subtract(dtStart)
Dim unxStart As Double = System.Math.Floor(difStart.TotalSeconds) ' can't include fractional seconds otherwise
Dim unxTomorrow As Double = System.Math.Floor(difTomorrow.TotalSeconds)
Dim unxStartOfWeek As Double = System.Math.Floor(difStartOfWeek.TotalSeconds)
Dim unxStartOfNextWeek As Double = System.Math.Floor(difStartOfNextWeek.TotalSeconds)
Dim unxStartOfMonth As Double = System.Math.Floor(difStartOfMonth.TotalSeconds)
Dim unxStartOfNextMonth As Double = System.Math.Floor(difStartOfNextMonth.TotalSeconds)
Sub Page_Load(Sender As Object, E As EventArgs)
MyConnection = New SqlConnection("server=bksql04;database=ARSystem;uid=;pwd=")
If MySelect.Value ="" Then
MySelect.Items.Insert(0,"*")
MySelect.SelectedIndex = 0
End If
If Not (IsPostBack)
If MySelect.Value ="*" Then
Dim SelectCmd1 As String = "select count(*) as 'tcktOpen' from HPD_HelpDesk where status < 4"
Dim DS1 As DataSet
Dim MyCommand1 As SqlDataAdapter
MyCommand1 = New SqlDataAdapter(SelectCmd1, MyConnection)
MyCommand1.SelectCommand.Parameters.Add(New SqlParameter("@Group", SqlDbType.NVarChar, 2))
MyCommand1.SelectCommand.Parameters("@Group").Value = MySelect.Value
DS1 = new DataSet()
MyCommand1.Fill(DS1, "Tickets")
MyDataGrid.DataSource= DS1.Tables("Tickets").DefaultView
MyDataGrid.DataBind()
End If
Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
MyCommand = New SqlDataAdapter("select distinct Assigned_To_Group_ from HPD_HelpDesk", MyConnection)
DS = new DataSet()
MyCommand.Fill(DS, "Group")
MySelect.DataSource= DS.Tables("Group").DefaultView
MySelect.DataBind()
If MySelect.Value ="" Then
MySelect.Items.Insert(0,"*")
MySelect.SelectedIndex = 0
End If
End If
End Sub
Sub GetChkDay(Sender As Object, E As EventArgs)
' response.write ("
Today=" & unxStart.tostring)
' response.write ("
Tomorrow=" & unxTomorrow.tostring)
End Sub
Sub GetTickets_Click(Sender As Object, E As EventArgs)
If MySelect.Value ="*" and MyList.SelectedItem.Value="*" Then
Dim SelectCmd As String = "select count(*) as 'tcktOpen' from HPD_HelpDesk where status < 4"
Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
MyCommand = New SqlDataAdapter(SelectCmd, MyConnection)
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@Group", SqlDbType.NVarChar, 2))
MyCommand.SelectCommand.Parameters("@Group").Value = MySelect.Value
DS = new DataSet()
MyCommand.Fill(DS, "Tickets")
MyDataGrid.DataSource= DS.Tables("Tickets").DefaultView
MyDataGrid.DataBind()
Else if Mylist.SelectedItem.Value="day" Then
Dim SelectCmd As String = "select count(*) as 'tcktOpen' from HPD_HelpDesk where Assigned_To_Group_ = '" & MySelect.Value & "' and status < 4 and Create_time >=" & unxStart
Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
MyCommand = New SqlDataAdapter(SelectCmd, MyConnection)
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@Group", SqlDbType.NVarChar, 2))
MyCommand.SelectCommand.Parameters("@Group").Value = MySelect.Value
DS = new DataSet()
MyCommand.Fill(DS, "Tickets")
MyDataGrid.DataSource= DS.Tables("Tickets").DefaultView
MyDataGrid.DataBind()
Else If Mylist.SelectedItem.Value="wkly" Then
Dim SelectCmd As String = "select create_time as 'Date Open',count(*) as 'tcktOpen' from HPD_HelpDesk where Assigned_To_Group_ = '" & MySelect.Value & "' and status < 4 and Create_time >=" & unxStartOfWeek &" group by create_time"
Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
MyCommand = New SqlDataAdapter(SelectCmd, MyConnection)
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@Group", SqlDbType.NVarChar, 2))
MyCommand.SelectCommand.Parameters("@Group").Value = MySelect.Value
DS = new DataSet()
MyCommand.Fill(DS, "Tickets")
MyDataGrid.DataSource= DS.Tables("Tickets").DefaultView
MyDataGrid.DataBind()
Else If Mylist.SelectedItem.Value="mon" Then
Dim SelectCmd As String = "select create_time as 'Date Open',count(*) as 'tcktOpen' from HPD_HelpDesk where Assigned_To_Group_ = '" & MySelect.Value & "' and status < 4 and Create_time >= " & unxStartOfMonth &" group by (dayofyear, create_time)"
Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
response.write ("
MySelectValue=" & MySelect.value.tostring)
MyCommand = New SqlDataAdapter(SelectCmd, MyConnection)
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@Group", SqlDbType.NVarChar, 2))
MyCommand.SelectCommand.Parameters("@Group").Value = MySelect.Value
DS = new DataSet()
MyCommand.Fill(DS, "Tickets")
MyDataGrid.DataSource= DS.Tables("Tickets").DefaultView
MyDataGrid.DataBind()
End If
End Sub
</script>
<body>
<font face="verdana">
<form runat="server" ID="Form1">
<h3><font face="Verdana">Test of selecting group to narrow ticket count</font></h3>
Select a Group:
<select id="MySelect" DataTextField="Assigned_To_Group_" runat="server" NAME="MySelect" />
<FONT size="1">Chart Type:</FONT>
<asp:dropdownlist id="MyList" runat="server" Font-Size="XX-Small" OnLoad="GetChkDay" >
<asp:ListItem Value="*" Selected=True>All Open Tickets</asp:ListItem>
<asp:ListItem Value="day">Daily</asp:ListItem>
<asp:ListItem Value="wkly">Weekly</asp:ListItem>
<asp:ListItem Value="mon">Monthly</asp:ListItem>
</asp:dropdownlist><FONT size="1"></FONT>
<input type="submit" OnServerClick="GetTickets_Click" Value="Get Tickets" runat="server" ID="Submit1" NAME="Submit1" />
<ASP:DataGrid id="MyDataGrid" runat="server" Width="700" BackColor="#ccccff" BorderColor="black" ShowFooter="false" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" EnableViewState="True" AutoGenerateColumns="True" />
</form>
</font>
</body>
</HTML>