Question Populate a Combobox from sql

[elianeasmar]

Hello. Need your help in something.
I am trying to populate the fields in a table that i created in Sql into a Combobox.
Table name: Vehicule
fields : Immatric-Marque-Modele-annee-etat-compteurkm-remarque-typv


i want this in my form, so when the user wants informations about a car,he just select a field from the combobox and the result would be displayed in a list box
i managed to put the infos in the listbox:

Dim _cn As SqlConnection
    Dim _cnstring As String = "Data Source=ELIANE-VAIO\SQLEXPRESS;Initial Catalog=Car;Integrated Security=True;"
        Private Sub btnArray_Click(sender As Object, e As EventArgs) Handles btnArray.Click
        ListBox1.DataSource = Nothing
        ListBox1.Refresh()
        Dim sqlstring As String = "Select Marque, Modele From Vehicule"
        Dim _cmd As New SqlCommand(sqlstring, _cn)
        _cn.Open()
        Dim _dr As SqlDataReader = _cmd.ExecuteReader()
        While _dr.Read()
            ListBox2.BeginUpdate()
            ListBox2.Items.Add(_dr.Item("Marque") & "          " & _dr.Item("Modele"))
            ListBox2.EndUpdate()
        End While
        _dr.Close()
        _cn.Close()
    End Sub

but couldn't populate the fields in the combobox.
I need other than this method:

Me.cmbDropDown.Items.Add("marque")
Me.cmbDropDown.Items.Add("modele")
Me.cmbDropDown.Items.Add("annee")
Me.cmbDropDown.Items.Add("etat")
Me.cmbDropDown.Items.Add("compteurkm")
Me.cmbDropDown.Items.Add("remarque")
Me.cmbDropDown.Items.Add("typev")

Any help would be appreciated.
Thank you

Best regards.

 

[elianeasmar]

I have this code. But this one does fil the combobox with the data entered. not the fields of the table.

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        _cn = New SqlConnection(_cnstring)
        Dim Adp As New SqlDataAdapter("SELECT  * FROM Vehicule", _cn)
        Dim Dt As New DataTable
        Adp.Fill(Dt)
        cmbDropDown.DataSource = Dt
        cmbDropDown.DisplayMember = Dt.Columns(1).ColumnName
End Sub

Thank you

 

[jmcilhinney]

What exactly are you expecting to see in the ComboBox? Is it the names of the columns or the values from the columns for the selected record in the ListBox?

 

[elianeasmar]

The name of the columns.
Immatric-Marque-Modele-annee-etat-compteurkm-remarque-typv

 

[IanRyder]

Hi,

If you want the Column Names of a DataTable to be added to a ListBox then you have to iterate through the Columns of a DataTable and then add the ColumnNames to the ListBox. Have a play with this:-

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Using sqlCon As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
    Using daEmployees As New SqlDataAdapter("Select * From Employees", sqlCon)
      Dim DT As New DataTable
      daEmployees.FillSchema(DT, SchemaType.Source)
      ListBox1.Items.AddRange(DT.Columns.Cast(Of DataColumn).Select(Function(x) x.ColumnName).ToArray)
    End Using
  End Using
End Sub

Hope that helps.

Cheers,

Ian

 

[jmcilhinney]

Hi,

If you want the Column Names of a DataTable to be added to a ListBox then you have to iterate through the Columns of a DataTable and then add the ColumnNames to the ListBox. Have a play with this:-

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Using sqlCon As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
    Using daEmployees As New SqlDataAdapter("Select * From Employees", sqlCon)
      Dim DT As New DataTable
      daEmployees.FillSchema(DT, SchemaType.Source)
      ListBox1.Items.AddRange(DT.Columns.Cast(Of DataColumn).Select(Function(x) x.ColumnName).ToArray)
    End Using
  End Using
End Sub

Hope that helps.

Cheers,

Ian

I tend to always use data-binding as a first choice and only resort to adding items manually if necessary. As such, in this case I'd bind the Columns collection of the DataTable to the control and set the DisplayMember to "ColumnName".

 

[Herman]

I would agree with John, databinding is just much cleaner.

Also try to structure your code a bit. For example you don't want to go have to dig into the form code looking for some obscure button handler to modify a query or a connection string later on. There is also plenty of code that can be re-used elsewhere in there. For example:

    Private _strConn As String = "Data Source=WINDOWS7HOST\SQL2008R2SP2;Initial Catalog=MyCompany;Integrated Security=True;"

    Private _queryComboBox1 As String = "SELECT * FROM Attachments"

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim dtResults As DataTable = SqlReadDatatable(_strConn, _queryComboBox1)

        ComboBox1.DataSource = (From c As DataColumn In dtResults.Columns Select c.ColumnName).ToList
    End Sub

    Friend Shared Function SqlReadDatatable(ByVal strConn As String, ByVal strQuery As String, Optional ByVal intTimeoutSecs As Integer = 30) As DataTable
        SqlReadDatatable = New DataTable
        Try
            Using _cn As New SqlConnection(strConn) : _cn.Open()
                Using _cmd As New SqlCommand(strQuery, _cn) With {.CommandTimeout = intTimeoutSecs}
                    Using _dr As SqlDataReader = _cmd.ExecuteReader(CommandBehavior.CloseConnection)
                        SqlReadDatatable.Load(_dr)
                    End Using
                End Using
            End Using
        Catch ex As SqlException
            HandleSqlException(ex)
        End Try
    End Function

    Friend Shared Sub HandleSqlException(ByVal ex As SqlException)
        ' Handle SQL Exception here...
    End Sub

With this you can store your connection string and/or query in a resource file or application setting along with all the others in your program, and only have to look there if you need to modify a query. The data reading routine can be called anywhere else you need to read from the database. The button handler does its job no matter what comes back from the query, so once it's in there you never have to modify it again. And you have a centralized SQL exception handler that any other SQL routine can use to handle exceptions in a uniform way.

 

[Herman]

I edited the code above, as you can only bind an IList or IListSource to a combobox. The problem is easily solved using Linq. I tried with a BindingSource too, but instead of binding the actual column name it bound every character in the first name as separate items for some reason.

 

[elianeasmar]

Thank you guys. I've been a bit sick. That's why i couldn't reply earlier.