Database connected DropDownList

Imports System
Imports System.Data
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.ComponentModel
Imports System.Data.SqlClient

'IdDdl Server Control Class for System.Web.UI.WebControls.DropDownList Tag

<DefaultProperty("TableName"), ToolboxData("<{0}:IdDdl runat=server></{0}:IdDdl>")> _
Public Class DBDropDownList
    Inherits System.Web.UI.WebControls.DropDownList

    'Private CONN_STRING As String
    Shared CONN_STRING As String = System.Configuration.ConfigurationSettings.AppSettings.Get("connectionString")

    ' Dim my_FeedbackService As FeedbackWebService.Service1
    ' my_FeedbackService= New FeedbackWebService.Service1()
    Dim dt As DataTable = New DataTable()
    Private tableName1 As String
    Private displayColumn1 As String
    Private valueColumn1 As String
    Private displayColumn1Seperator As String = "-"
    Private valueColumnSeperator1 As String = "#"
    Private firstField1 As Boolean = False
    Private firstFieldText1 As String = ""
    Private firstFieldValue1 As String = ""
    Private whereClause1 As String = ""


    'Browsable from the Visual Studio.NET Designer.
    <Bindable(True), _
    Category("Appearance"), _
    DefaultValue(""), _
    Browsable(True)> _
    Public Property Connection_String() As String
        Get
            Return CONN_STRING
        End Get
        Set(ByVal Value As String)
            CONN_STRING = Value
        End Set
    End Property

    ' Browsable from the Visual Studio.NET Designer.
    <Bindable(True), _
    Category("Appearance"), _
    DefaultValue(""), _
    Browsable(True)> _
    Public Property TableName() As String
        Get
            Return tableName1
        End Get
        Set(ByVal Value As String)
            tableName1 = Value
        End Set
    End Property


    <Bindable(True), _
    Category("Appearance"), _
    DefaultValue(""), _
    Browsable(True)> _
    Public Property DisplayColumn() As String
        Get
            Return displayColumn1
        End Get
        Set(ByVal Value As String)
            displayColumn1 = Value
        End Set
    End Property

    <Bindable(True), _
    Category("Appearance"), _
    DefaultValue(""), _
    Browsable(True)> _
    Public Property ValueColumn() As String
        Get
            Return valueColumn1
        End Get
        Set(ByVal Value As String)
            valueColumn1 = Value
        End Set
    End Property

    <Bindable(True), _
    Category("Appearance"), _
    DefaultValue(""), _
    Browsable(True)> _
       Public Property DisplayColumnSeperator() As String
        Get
            Return displayColumn1Seperator
        End Get
        Set(ByVal Value As String)
            displayColumn1Seperator = Value
        End Set
    End Property

    <Bindable(True), _
    Category("Appearance"), _
    DefaultValue(""), _
    Browsable(True)> _
       Public Property ValueColumnSeperator() As String
        Get
            Return valueColumnSeperator1
        End Get
        Set(ByVal Value As String)
            valueColumnSeperator1 = Value
        End Set
    End Property


    <Bindable(True), _
    Category("Appearance"), _
    DefaultValue(""), _
    Browsable(True)> _
       Public Property FirstField() As Boolean
        Get
            Return firstField1
        End Get
        Set(ByVal Value As Boolean)
            firstField1 = Value
        End Set
    End Property


    <Bindable(True), _
    Category("Appearance"), _
    DefaultValue(""), _
    Browsable(True)> _
       Public Property WhereClause() As String
        Get
            Return whereClause1
        End Get
        Set(ByVal Value As String)
            whereClause1 = Value
        End Set
    End Property

    <Bindable(True), _
    Category("Appearance"), _
    DefaultValue(""), _
    Browsable(True)> _
       Public Property FirstFieldText() As String
        Get
            If Nothing = Me.firstFieldText1 Then
                Me.firstFieldText1 = ""
            End If

            Return firstFieldText1
        End Get
        Set(ByVal Value As String)
            firstFieldText1 = Value
        End Set
    End Property

    <Bindable(True), Category("Appearance"), DefaultValue(""), Browsable(True)> _
    Public Property FirstFieldValue() As String
        Get
            If Nothing = Me.firstFieldValue1 Then
                Me.firstFieldValue1 = ""
            End If

            Return firstFieldValue1
        End Get
        Set(ByVal Value As String)
            firstFieldValue1 = Value
        End Set
    End Property

    <Bindable(True), Category("Appearance"), DefaultValue(""), Browsable(False)> _
    Public Overrides Property DataSource() As Object
        Get
            Return MyBase.DataSource
        End Get
        Set(ByVal Value As Object)
            '
        End Set
    End Property


    <Bindable(True), Category("Appearance"), DefaultValue(""), Browsable(False)> _
    Public Overrides Property DataTextField() As String
        Get
            Return MyBase.DataTextField
        End Get
        Set(ByVal Value As String)
            '
        End Set
    End Property

    <Bindable(True), _
    Category("Appearance"), _
    DefaultValue(""), _
    Browsable(False)> _
     Public Overrides Property DataValueField() As String
        Get
            Return MyBase.DataValueField
        End Get
        Set(ByVal Value As String)
            '
        End Set
    End Property

    <Bindable(True), Category("Appearance"), DefaultValue(""), Browsable(False)> _
    Public Overrides Property DataMember() As String
        Get
            Return MyBase.DataMember
        End Get
        Set(ByVal Value As String)
            '
        End Set
    End Property


    Private Function DropDownListDB()
    End Function

    Private Sub LoadDataInListItem()
        Try
            Me.valueColumn1 = Me.valueColumn1.Replace(",", " ||'" + Me.valueColumnSeperator1 + "'|| ")
            Me.displayColumn1 = Me.displayColumn1.Replace(",", " ||'" + Me.displayColumn1Seperator + "'|| ")

            Dim strQry As String = "SELECT " + Me.valueColumn1 + " VALUE_FIELD, " + Me.displayColumn1 + " DISPLAY_FIELD FROM " + Me.tableName1 + " " + Me.whereClause1 + " ORDER BY " + Me.displayColumn1
            'Dim strQry As String = "Select NTUser DISPLAY_FIELD , User_ID VALUE_FIELD From OfficeUsers"

            Dim ds As New DataSet()
            Dim da As New SqlDataAdapter(strQry, Me.CONN_STRING)
            da.Fill(ds)

            dt = ds.Tables(0)
            dt.TableName = Me.tableName1

            Dim i As Integer
            For i = 0 To dt.Rows.Count - 1 Step i + 1
                Me.Items.Add(New ListItem(dt.Rows(i)("DISPLAY_FIELD").ToString(), dt.Rows(i)("VALUE_FIELD").ToString()))
            Next

            If Me.firstField1 = True Then
                If (Me.Items(0).Text <> "") Then
                    Me.Items.Insert(0, New ListItem(Me.firstFieldText1, ""))
                End If
            End If
        Catch ex As Exception

            '
        End Try

    End Sub

    Protected Overrides Function SaveViewState() As Object
        Dim objArr() As Object = New Object(Me.Items.Count + 1) {}

        Try
            If Not Page.IsPostBack Then
                Me.LoadDataInListItem()
            End If

            Dim baseState As Object = MyBase.SaveViewState()
            objArr(0) = baseState
        Catch ex As Exception
            '
        End Try
        Return objArr
    End Function


    Protected Overrides Sub LoadViewState(ByVal savedState As Object)
        Try
            If Not savedState Is Nothing Then
                Dim objSaveStateArr() As Object = CType(savedState, Object())

                If Not objSaveStateArr(0) Is Nothing Then
                    MyBase.LoadViewState(objSaveStateArr(0))
                End If
            End If
        Catch ex As Exception
            '
        End Try
    End Sub

    ' Overrides the RenderContents.
    ' Renders the Error message if error is raised while rendering.
    ' Else, renders nothing as the Content between the open and close tag of IMG Control
    Protected Overrides Sub RenderContents(ByVal htw As HtmlTextWriter)
        Try
            Dim li As ListItem
            For Each li In Me.Items
                If li.Attributes.Count > 0 Then
                    htw.WriteBeginTag("option")

                    If li.Selected Then
                        htw.WriteAttribute("selected", "selected", False)
                    End If

                    li.Attributes.Render(htw)

                    htw.WriteAttribute("value", li.Value.ToString())
                    htw.Write(HtmlTextWriter.TagRightChar)
                    htw.Write(li.Text)
                    htw.WriteEndTag("option")
                    htw.WriteLine()
                End If
            Next

        Catch ex As Exception
            htw.WriteBeginTag("option")
            htw.WriteAttribute("value", "Error")
            htw.Write(HtmlTextWriter.TagRightChar)
            htw.Write(ex.Message)
            htw.WriteEndTag("option")
            htw.WriteLine()
        End Try

        Page.DataBind()
        MyBase.RenderContents(htw)
    End Sub
End Class