How to create a chart with parameters using visual studio, using 3-tier

See more:

VB

SQL

I need the user to enter schoolName and select a year from a dropdown.

I've got the following code.

PRESENTATION LAYER

<form id="form1" runat="server">
   <div>
  <table>
   <tr>
   <td>
       <asp:Label ID="EmisCode" runat="server" Text="Emiscode">
   </td>
   <td>
   </td>
   <td>
       <asp:TextBox ID="txtCode" runat="server">
   </td>
   </tr>
   <tr>
   <td>
       <asp:Label ID="Label1" runat="server" Text="Year">
   </td>
   <td></td>
   <td><asp:DropDownList ID="ddyear" runat="server" Width="126px">
       <asp:ListItem Value="0">Select Year
       <asp:ListItem Value="1">2010
       <asp:ListItem Value="2">2009
       <asp:ListItem Value="3">2008
       <asp:ListItem Value="4">2007
       </td>
   </tr>
   <tr>
   <td></td>
   <td>
       <asp:Button ID="btnsearch" runat="server" Text="View School Performance" /></td>
   <td> </td>
   </tr>
   </table>
   <table></table></div>

Protected Sub btnsearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnsearch.Click
       Dim code As String = txtCode.Text
       Dim objs As New SearchPerBySchool
       Dim year As String = ddyear.SelectedItem.Text

       If code.Length <> 9 Or code = "" Or code.Substring(0, 3) <> "500" Then
           MsgBox("Please enter a valid EmisCode")
       ElseIf objs.Search_School_performance(code, year).Rows.Count > 0 Then
           'Response.Redirect("ViewSchoolPer.aspx?EmisCode=" & txtCode.Text & "&ddyear=" & ddyear.SelectedItem.Text)
           Chart1.DataSource = objs.Search_School_performance(code, year)
           'Chart1.Series("grade").XValueMember = "grade"
           'Chart1.Series("TotalPass").YValueMembers = "TotalPass"
           Chart1.DataBind()
       End If
       'Response.Redirect("ViewSchoolPer.aspx")

   End Sub

BUSINESS LAYER

Imports Emis_Data_Layer
Imports System.Data
Public Class SearchPerBySchool
    Private _EmisCode As Integer
    Private _year As String
    Private _grade As Char
    Private _passrate As Integer
    Public Sub New()
        _EmisCode = 0
        _year = ""
        _grade = ""
        _passrate = 0
    End Sub
    Public Property passrate As Integer
        Get
            Return _passrate
        End Get
        Set(ByVal value As Integer)
            _passrate = value
        End Set
    End Property
    Public Property grade() As Char
        Get
            Return _grade
        End Get
        Set(ByVal value As Char)
            _grade = value
        End Set
    End Property
    Public Property EmisCode() As String
        Get
            Return _EmisCode
        End Get
        Set(ByVal value As String)
            _EmisCode = value
        End Set
    End Property
    Public Property Year() As String
        Get
            Return _year
        End Get
        Set(ByVal value As String)
            _year = value
        End Set
    End Property
    Public Sub New(ByVal emiscode As String, ByVal yea As String, ByVal grad As Char, ByVal pas As Integer)
        emiscode = emiscode
        Year = yea
        grade = grad
        passrate = pas
    End Sub
    Public Function Search_School_performance(ByVal EmisCode As String, ByVal Year As String) As DataTable
        Try
            Dim objDal As New csSQLDALVB
            Dim objParList As New List(Of csParameterListType)
            With objParList
                .Add(New csParameterListType("@EmisCode", SqlDbType.VarChar, EmisCode))
                .Add(New csParameterListType("@Year", SqlDbType.NVarChar, Year))
            End With
            Return objDal.executespreturndt("calPassRatePerSchool", objParList)
        Catch ex As Exception
            MsgBox(ex.Message())
            Return Nothing
        End Try
    End Function
    Public Function get_passRateProc() As DataTable
        Try
            Dim objDal As New csSQLDALVB
            Dim objParList As New List(Of csParameterListType)
            
            Return objDal.executespreturndt(" get_passRateProc", objParList)
        Catch ex As Exception
            MsgBox(ex.Message())
            Return Nothing
        End Try
    End Function
    Private Sub populate_reader(ByVal objRead As IDataReader, ByVal objL As SearchPerBySchool)
        Try
            objL.EmisCode = objRead.GetInt16(0)
            objL.Year = objRead.GetString(1)
            objL.grade = objRead.GetChar(2)
            objL.passrate = objRead.GetInt16(4)
        Catch ex As Exception
        End Try
    End Sub
End Class

DATA ACCCESS LAYER

Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports System
Public Class csParameterListType
    Dim C As New csSQLDALVB
    Dim P_cmd As New SqlCommand
    Dim P_adapter As New SqlDataAdapter
    Private _Name As String
    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property
    Private _SqlType As SqlDbType
    Public Property SqlType() As SqlDbType
        Get
            Return _SqlType
        End Get
        Set(ByVal value As SqlDbType)
            _SqlType = value
        End Set
    End Property
    Private _Value As String
    Public Property Value() As String
        Get
            Return _Value
        End Get
        Set(ByVal value As String)
            _Value = value
        End Set
    End Property
    Public Sub New(ByVal name__1 As String, ByVal Stype As SqlDbType, ByVal value__2 As String)
        Name = name__1
        SqlType = Stype
        Value = value__2
    End Sub

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports System.Text
Imports System.Data.SqlClient
Imports System.Configuration
Public Class csSQLDALVB
    Protected _strconn As String = " Data Source=tumi-pc\sqlexpress;Initial Catalog=EMIS_WAREHOUSE;User ID=sa; Password=server01"
    ' "Data Source=SELAE-PC\SQLEXPRESS;Initial Catalog=Emis;Integrated Security=True"
    Private conn As SqlConnection
    Private errorstr As String = String.Empty
    Public Sub New()
        conn = New SqlConnection()
    End Sub
    Public Property ConnectionString() As String
        Get
            Return _strconn
        End Get
        Set(ByVal value As String)
            _strconn = value
        End Set
    End Property
    Private Function Open_Connection() As Boolean
        If conn.State = System.Data.ConnectionState.Closed Then
            Try
                conn.ConnectionString = ConnectionString
                conn.Open()
                Return True
            Catch a As Exception
                ' errorstr += " " & a.Message
                MsgBox(a.Message)
                Return False
            End Try
        Else
            Return True
        End If
    End Function
    Private Function add_parameters(ByVal objpar As csParameterListType) As SqlParameter
        Dim sqlpar As New SqlParameter()
        sqlpar.ParameterName = objpar.Name
        sqlpar.SqlDbType = objpar.SqlType
        sqlpar.SqlValue = objpar.Value
        Return sqlpar
    End Function
    Public Function executespreturndr(ByVal spname As String, ByVal objlist As List(Of csParameterListType)) As IDataReader
        Dim cmd As New SqlCommand()
        Dim dr As IDataReader = Nothing
        Try
            If Open_Connection() Then
                cmd.Connection = conn
                cmd.CommandType = System.Data.CommandType.StoredProcedure
                cmd.CommandText = spname
                For Each par As csParameterListType In objlist
                    cmd.Parameters.Add(add_parameters(par))
                Next
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            End If
            Return dr
        Catch e As Exception
            errorstr += " " & e.Message
            MsgBox(e.Message())
            Return Nothing
        End Try
    End Function
    Public Function executespreturndr(ByVal spname As String) As IDataReader
        Dim cmd As New SqlCommand()
        Dim dr As IDataReader = Nothing
        Try
            If Open_Connection() Then
                cmd.Connection = conn
                cmd.CommandType = System.Data.CommandType.StoredProcedure
                cmd.CommandText = spname
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            End If
            Return dr
        Catch e As Exception
            errorstr += " " & e.Message
            MsgBox(e.Message)
            Return Nothing
        End Try
    End Function
    Public Function executespreturnds(ByVal spname As String, ByVal objlist As List(Of csParameterListType)) As DataSet
        Dim cmd As New SqlCommand()
        Dim ds As New DataSet()
        Dim da As New SqlDataAdapter()
        Try
            If Open_Connection() Then
                cmd.Connection = conn
                cmd.CommandType = System.Data.CommandType.StoredProcedure
                cmd.CommandText = spname
                For Each par As csParameterListType In objlist
                    cmd.Parameters.Add(add_parameters(par))
                Next
                da.SelectCommand = cmd
                da.Fill(ds)
            End If
            Return ds
        Catch e As Exception
            errorstr += " " & e.Message
            MsgBox(e.Message)
            Return Nothing
        End Try
    End Function
    Public Function executespreturnds(ByVal spname As String) As DataSet
        Dim cmd As New SqlCommand()
        Dim ds As New DataSet()
        Dim da As New SqlDataAdapter()
        Try
            If Open_Connection() Then
                cmd.Connection = conn
                cmd.CommandType = System.Data.CommandType.StoredProcedure
                cmd.CommandText = spname
                da.SelectCommand = cmd
                da.Fill(ds)
            End If
            Return ds
        Catch e As Exception
            errorstr += " " & e.Message
            MsgBox(e.Message)
            Return Nothing
        End Try
    End Function
    Public Function executespreturndt(ByVal spname As String, ByVal objlist As List(Of csParameterListType)) As DataTable
        Dim cmd As New SqlCommand()
        Dim dt As New DataTable()
        Dim da As New SqlDataAdapter()
        Try
            If Open_Connection() Then
                cmd.Connection = conn
                cmd.CommandType = System.Data.CommandType.StoredProcedure
                cmd.CommandText = spname
                For Each par As csParameterListType In objlist
                    cmd.Parameters.Add(add_parameters(par))
                Next
                da.SelectCommand = cmd
                da.Fill(dt)
            End If
            Return dt
        Catch e As Exception
            errorstr += " " & e.Message
            MsgBox(e.Message)
            Return Nothing
        End Try
    End Function
    Public Function executespreturndt(ByVal spname As String) As DataTable
        Dim cmd As New SqlCommand()
        Dim dt As New DataTable()
        Dim da As New SqlDataAdapter()
        Try
            If Open_Connection() Then
                cmd.Connection = conn
                cmd.CommandType = System.Data.CommandType.StoredProcedure
                cmd.CommandText = spname
                da.SelectCommand = cmd
                da.Fill(dt)
            End If
            Return dt
        Catch e As Exception
            errorstr += " " & e.Message
            MsgBox(e.Message)
            Return Nothing
        End Try
    End Function
    Public Sub executespreturnnd(ByVal spname As String, ByVal objlist As List(Of csParameterListType))
        Dim cmd As New SqlCommand()
        Try
            If Open_Connection() Then
                cmd.Connection = conn
                cmd.CommandType = System.Data.CommandType.StoredProcedure
                cmd.CommandText = spname
                For Each par As csParameterListType In objlist
                    cmd.Parameters.Add(add_parameters(par))
                Next
                cmd.ExecuteNonQuery()
                close_conn()
            End If
        Catch e As Exception
            errorstr += " " & e.Message
            MsgBox(e.Message)
        End Try
    End Sub
    Public Sub executespreturnnd(ByVal spname As String)
        Dim cmd As New SqlCommand()
        Try
            If Open_Connection() Then
                cmd.Connection = conn
                cmd.CommandType = System.Data.CommandType.StoredProcedure
                cmd.CommandText = spname
                cmd.ExecuteNonQuery()
                close_conn()
            End If
        Catch e As Exception
            errorstr += " " & e.Message
            MsgBox(e.Message)
        End Try
    End Sub
    Private Sub close_conn()
        If conn.State = System.Data.ConnectionState.Open Then
            conn.Close()
        End If
        conn = Nothing
    End Sub
End Class