none
Population Combo box from sql, reusing code RRS feed

  • Question

  • Hi,

    I have a sub that populates a combo box from sql, works well

    I would like to "reuse" the code on the sub, because i have a few combo boxes.

    Here is the sub

                app_cmd.CommandText = "select sett_value from qcn_sett where sett_combo='Brief Specific Description' order by sett_value;"
                Dim da As New SqlDataAdapter(app_cmd)
                Dim dt As New DataTable
                da.Fill(dt)
                Me.combo_dshort.ValueMember = "sett_value"
                Me.combo_dshort.DisplayMember = "sett_value"
                Me.combo_dshort.DataSource = dt

    I dont want to create a sub for each combo nor use my_sub(my_combo as control, my_sql as string)

    Is there a way? i dont want do dim da and dt again, and again

    thanks

    John

    Friday, June 16, 2017 4:56 PM

Answers

  • Hello,

    The first key for re-use is to place the code into a class rather than in a single form. The class should only retrieve data to the caller who then uses the data.

    Here I'm using a class to get data from SQL-Server. There are two versions, one hard coded to locate customers by country e.g. in Mexico while the other accepts a country.

    Imports System.Data.SqlClient
    
    Public Class Operations
        ''' <summary>
        ''' Replace with your SQL Server name
        ''' </summary>
        Private Server As String = "KARENS-PC"
        ''' <summary>
        ''' Database in which data resides, see SQL_Script.sql
        ''' </summary>
        Private Catalog As String = "NorthWindAzure"
        ''' <summary>
        ''' Connection string for connecting to the database
        ''' </summary>
        Private ConnectionString As String = ""
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        Public Function ForComboBox() As DataTable
            Dim dt = New DataTable()
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT CustomerIdentifier,CompanyName,ContactName " &
                                       "FROM Customers WHERE Country = 'Mexico'"
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using
            Return dt
        End Function
        Public Function ForComboBox(ByVal Country As String) As DataTable
            Dim dt = New DataTable()
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT CustomerIdentifier,CompanyName,ContactName " &
                                      "FROM Customers WHERE Country = @Country"
                    cmd.Parameters.AddWithValue("@Country", Country)
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using
            Return dt
        End Function
    End Class
    

    Usage

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As Operations = New Operations
            ComboBox1.DataSource = ops.ForComboBox("Sweden")
            ComboBox1.DisplayMember = "CompanyName"
            ComboBox1.ValueMember = "CustomerIdentifier"
        End Sub
    End Class
    

    Now we can go farther, have another combo box to select (in this case countries from)

    Imports System.Data.SqlClient
    
    Public Class Operations
        ''' <summary>
        ''' Replace with your SQL Server name
        ''' </summary>
        Private Server As String = "KARENS-PC"
        ''' <summary>
        ''' Database in which data resides, see SQL_Script.sql
        ''' </summary>
        Private Catalog As String = "NorthWindAzure"
        ''' <summary>
        ''' Connection string for connecting to the database
        ''' </summary>
        Private ConnectionString As String = ""
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        Public Function ForComboBox() As DataTable
            Dim dt = New DataTable()
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT CustomerIdentifier,CompanyName,ContactName " &
                                       "FROM Customers WHERE Country = 'Mexico'"
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using
            Return dt
        End Function
        Public Function ForComboBox(ByVal Country As String) As DataTable
            Dim dt = New DataTable()
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT CustomerIdentifier,CompanyName,ContactName " &
                                      "FROM Customers WHERE Country = @Country"
                    cmd.Parameters.AddWithValue("@Country", Country)
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using
            Return dt
        End Function
        Public Function Countries() As List(Of String)
            Dim CountryNames As New List(Of String)
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT Country  FROM Customers GROUP BY Country"
                    cn.Open()
                    Dim reader As SqlDataReader = cmd.ExecuteReader
                    While reader.Read
                        CountryNames.Add(reader.GetString(0))
                    End While
                End Using
            End Using
            Return CountryNames
        End Function
    End Class
    

    Final example form code

    Public Class Form1
        Private ops As Operations = New Operations
        Private CustomerTable As DataTable
        Private CountryNames As List(Of String)
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            ResultComboBox.DataSource = ops.ForComboBox(CountryComboBox.Text)
            ResultComboBox.DisplayMember = "CompanyName"
            ResultComboBox.ValueMember = "CustomerIdentifier"
        End Sub
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            CountryNames = ops.Countries
            CountryComboBox.DataSource = CountryNames
        End Sub
    End Class
    

    If the above form is the main form and you need data in child forms you can now pass them to the child forms.

    Note there is no need for a SqlDataAdapter, much cleaner code.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Bre-x Friday, June 16, 2017 6:47 PM
    Friday, June 16, 2017 6:01 PM
    Moderator

All replies

  • Hello,

    The first key for re-use is to place the code into a class rather than in a single form. The class should only retrieve data to the caller who then uses the data.

    Here I'm using a class to get data from SQL-Server. There are two versions, one hard coded to locate customers by country e.g. in Mexico while the other accepts a country.

    Imports System.Data.SqlClient
    
    Public Class Operations
        ''' <summary>
        ''' Replace with your SQL Server name
        ''' </summary>
        Private Server As String = "KARENS-PC"
        ''' <summary>
        ''' Database in which data resides, see SQL_Script.sql
        ''' </summary>
        Private Catalog As String = "NorthWindAzure"
        ''' <summary>
        ''' Connection string for connecting to the database
        ''' </summary>
        Private ConnectionString As String = ""
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        Public Function ForComboBox() As DataTable
            Dim dt = New DataTable()
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT CustomerIdentifier,CompanyName,ContactName " &
                                       "FROM Customers WHERE Country = 'Mexico'"
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using
            Return dt
        End Function
        Public Function ForComboBox(ByVal Country As String) As DataTable
            Dim dt = New DataTable()
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT CustomerIdentifier,CompanyName,ContactName " &
                                      "FROM Customers WHERE Country = @Country"
                    cmd.Parameters.AddWithValue("@Country", Country)
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using
            Return dt
        End Function
    End Class
    

    Usage

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As Operations = New Operations
            ComboBox1.DataSource = ops.ForComboBox("Sweden")
            ComboBox1.DisplayMember = "CompanyName"
            ComboBox1.ValueMember = "CustomerIdentifier"
        End Sub
    End Class
    

    Now we can go farther, have another combo box to select (in this case countries from)

    Imports System.Data.SqlClient
    
    Public Class Operations
        ''' <summary>
        ''' Replace with your SQL Server name
        ''' </summary>
        Private Server As String = "KARENS-PC"
        ''' <summary>
        ''' Database in which data resides, see SQL_Script.sql
        ''' </summary>
        Private Catalog As String = "NorthWindAzure"
        ''' <summary>
        ''' Connection string for connecting to the database
        ''' </summary>
        Private ConnectionString As String = ""
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            ConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        Public Function ForComboBox() As DataTable
            Dim dt = New DataTable()
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT CustomerIdentifier,CompanyName,ContactName " &
                                       "FROM Customers WHERE Country = 'Mexico'"
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using
            Return dt
        End Function
        Public Function ForComboBox(ByVal Country As String) As DataTable
            Dim dt = New DataTable()
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT CustomerIdentifier,CompanyName,ContactName " &
                                      "FROM Customers WHERE Country = @Country"
                    cmd.Parameters.AddWithValue("@Country", Country)
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
                End Using
            End Using
            Return dt
        End Function
        Public Function Countries() As List(Of String)
            Dim CountryNames As New List(Of String)
            Using cn As SqlConnection = New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As SqlCommand = New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT Country  FROM Customers GROUP BY Country"
                    cn.Open()
                    Dim reader As SqlDataReader = cmd.ExecuteReader
                    While reader.Read
                        CountryNames.Add(reader.GetString(0))
                    End While
                End Using
            End Using
            Return CountryNames
        End Function
    End Class
    

    Final example form code

    Public Class Form1
        Private ops As Operations = New Operations
        Private CustomerTable As DataTable
        Private CountryNames As List(Of String)
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            ResultComboBox.DataSource = ops.ForComboBox(CountryComboBox.Text)
            ResultComboBox.DisplayMember = "CompanyName"
            ResultComboBox.ValueMember = "CustomerIdentifier"
        End Sub
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            CountryNames = ops.Countries
            CountryComboBox.DataSource = CountryNames
        End Sub
    End Class
    

    If the above form is the main form and you need data in child forms you can now pass them to the child forms.

    Note there is no need for a SqlDataAdapter, much cleaner code.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Bre-x Friday, June 16, 2017 6:47 PM
    Friday, June 16, 2017 6:01 PM
    Moderator
  • Karen

    thank you so much

    I will test it

    :)

    john

    Friday, June 16, 2017 6:20 PM