Imports System.Data.SqlClient
Imports System.Data

Partial Class _Vendite_Ricerca2
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            ddlanno.DataSource = anno()
            ddlanno.DataValueField = "RolAnno"
            ddlanno.DataTextField = "RolAnno"
            ddlanno.DataBind()

            ' ddlcentro.DataSource = centro()
            ' ddlcentro.DataValueField = "RolCodcen"
            ' ddlcentro.DataTextField = "centro"
            ' ddlcentro.DataBind()

        End If
    End Sub

    Private Function anno() As DataTable
        Dim sql As String = "SELECT DISTINCT RolAnno FROM ... UNION SELECT  RolAnno = NULL"
        Dim conn As New SqlConnection()
        conn.ConnectionString = ConfigurationManager.ConnectionStrings("BackupConnectionString").ConnectionString
        conn.Open()
        Dim cmd As New SqlCommand(sql, conn)
        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()
        da.Fill(dt)
        conn.Close()
        Return dt
    End Function

    Private Function centro(ByVal annoID As Integer) As DataTable
        Dim sql As String = "SELECT DISTINCT RolCodcen,centro " & _
        "..." & _
        " FROM ..." & _
        " WHERE RolAnno =" & annoID & " UNION " & _
        " SELECT RolCodcen=null, centro = null"
        Dim conn As New SqlConnection()
        conn.ConnectionString = ConfigurationManager.ConnectionStrings("BackupConnectionString").ConnectionString
        conn.Open()
        Dim cmd As New SqlCommand(sql, conn)
        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable
        da.Fill(dt)
        conn.Close()
        Return dt
    End Function

    Protected Sub ddlanno_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlanno.SelectedIndexChanged

        Dim annoID As Integer = Convert.ToInt32(ddlanno.SelectedItem.Value)

        ddlcentro.DataSource = centro(annoID)
        ddlcentro.DataValueField = "RolCodcen"
        ddlcentro.DataTextField = "centro"
        ddlcentro.DataBind()
    End Sub
End Class