Create function to binding data into dropdownlist in ASP.Net using VB.NET

 In this article show how to create function to load data from database into dropdownlist  on page load in ASP.NET using VB.NET. In this post, I create three functions: function execute query, function connect to database and function for loading data into dropdownlist on event page load.

Description:
      I want to load data from a table field (Country)  in Tbl_Country from Microsoft SQL Server to dropdownlist in webform of Microsoft visual studio 2010.

Sample table 

Code:
  Form Design:

<asp:DropDownList ID="ddlcate" runat="server" Width="200px" Height="20px" AutoPostBack="true"></asp:DropDownList>

 Code Behind: 

'add these two namespaces

Imports System.Data
Imports System.Data.SqlClient

'function open connection connect to database 

Public Sub openconnection(ByVal sqlcon As SqlConnection)

 Try

     sqlcon.ConnectionString = "server=THAROTH_UBEE; database=dbtest; user=sa; password = 123"

     sqlcon.Open()

 Catch ex As Exception

     MsgBox(ex.Message)

 End Try 

End Sub

'function execute query to load data into data table

Public Function getquery(ByVal query As String, ByVal sqlcon As SqlConnection) As DataTable

     Dim dt As New DataTable

     Try

        Dim da As New SqlDataAdapter(query, sqlcon)

        da.Fill(dt)

        If (dt.Rows.Count > 0) Then

           Return (dt)

        Else

           dt = Nothing

        End If

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

     Return dt

End Function

 'function load data into dropdownlist 

Public Sub loadddl(ByVal ddl As DropDownList, ByVal query As String, ByVal colname As String, ByVal sqlcon As SqlConnection)

    Dim dt As New DataTable

    dt = getquery(query, sqlcon)

    ddl.DataSource = dt

    ddl.DataValueField = colname

    ddl.DataBind()

    dt.Dispose()

End Sub

'calling function in page load 

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Try 

         Dim sqlcon = New SqlConnection

       openconnection(sqlcon)

       If (Not IsPostBack) Then

          Dim query = " select cate_name from Tbl_category order by cate_name asc"

          loadddl(ddlcate, query, "cate_name", sqlcon)                
          'cate_name is colum name of table Tbl_Country

       End If

       sqlcon.Dispose()

     Catch ex As Exception

       MsgBox(ex.Message)

     End Try

 End Sub

 Done ..... hope you guys enjoy it :D