Visual Basic

Here is a function to populate any combobox with data from a table column.This style wld work for any database. I prefer to use oracle in this instance.

You first have to create the function in a module or class
and call it from anywhere in your project with the required parameters

For instance, drop a combobox(combobox1) and a button(button1) and under the button add

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
 
'when calling from a class      
'className.populateAnyCombo(comboboxName,"sql statment", _ 
'column name, connection string)
 
'---call function from a module----
 
PopulateAnyCombo(combobox1,"SQL(eg Select Column_Name from Table_Name)", _
"column_name", connectionString)
 
End Sub
 
'----------Place in a module or class----------
 
Public Function PopulateAnyCombo(ByVal cBox As ComboBox, ByVal strSQL   _
As String, ByVal tableColumn As String,ByVal connString As String ) As String()
 
            Dim array(0) As String
            Dim cnt As Integer = 0
            Dim conn As New OracleConnection
 
            conn.ConnectionString = connString
            Try
                  conn.Open()
            Catch ex As Exception
                  MessageBox.Show(ex.Message, "Error")            
                  MsgBox("Failed conection to Oracle" & ex.Message)
                  Return array
            End Try
 
            Dim cmd As New OracleCommand
            With cmd
                  .CommandText = strSQL
                  .CommandType = CommandType.Text
                  .Connection = conn
            End With
            'data reader to store recordsets retrieved
            Dim dr As OracleDataReader
 
'Reading an populating records in array
'ReDim preserve increases the array size and preserve old members
            Try
                  dr = cmd.ExecuteReader
                  While dr.Read
                        ReDim Preserve array(cnt)
                        array(cnt) = dr.Item(tableColumn)
                        cnt = cnt + 1
                  End While
             
                  cBox.Items.Clear()
                  cBox.Items.AddRange(array)
 
                  Return array
            Catch ex As Exception
                  MessageBox.Show(ex.Message, "Error")
                  Dim a(0) As String
                  a(0) = 0
                  Return a
            Finally
                  conn.Close()
                  conn.Dispose()
            End Try
      End Function
 
'-----------------place in a module or class-------------

Would be glad to know if i helped solve a problem
Criticisms to this snippet are welcomed
nan492001@yahoo.com

Scott Dorman wrote:

Strictly speaking, no topics don't have to only be questions although I think that is the majority. I think it is much preferred to publish an article for something like this and provide more background into what problem is being solved and why and explanations of how the code works, although this probably doesn't have enough "meat" to be an article. There was talk a while back of a new "article type" for posting code snippets (similar to this) but I don't know what happened with it.