CODEWITHSUNDEEP
vb.netms-access.net-4.0vb.net-2010
Tahi
Tahi

Reputation: 95

How to fill a ComboBox using records from an Access database

I want to retrieve the data and display in a ComboBox but it's showing blank.

This is my code:

Imports System.Data.OleDb

Public Class frmAirwaybill

    Private Sub frmAirwaybill_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim cm As OleDbCommand = New OleDbCommand("select acc_no from tblshipper order by acc_no")
        cm.Connection = DBconnection()
        Dim dr As OleDbDataReader = cm.ExecuteReader
        Do While (dr.Read())
            txtAccountNo.Items.Add(dr.Item("acc_no"))
        Loop
        dr.Close()
        DBconnection.Close()
    End Sub
End Class*

txtAccountNo is the ComboBox

What I want, when the form loads, is to load the accno from my database. How do I do that?

This is a screenshot showing the ComboBox with blank values:

its blank, i want they show the data in combo box

My database connection is OK.

This is my connection on module file

Public Function DBconnection() As OleDbConnection

        Dim con As New OleDb.OleDbConnection
        Dim constring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\SmartshipG2.mdb"
        con = New OleDb.OleDbConnection(constring)
        con.Open()
        Return con

End Function

Upvotes: 0

Views: 1252

Answers (1)

Bugs
Bugs

Reputation: 4489

The first thing to note is that you haven't opened the connection. This is probably the root cause.

That said, you are better binding to the .DataSource of the ComboBox using a DataTable and setting the .DisplayMember and .ValueMember properties.

I would also consider implementing Using:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

Lastly, consider giving your ComboBox a better prefix. txt is often used for TextBox controls. I use cmb whilst others may use cbx. So in this case cmbAccountNo seems a better fit.

With the changes you code would look something like this:

Dim dt As New DataTable
Using con As OleDbConnection New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\SmartshipG2.mdb"),
      cmd As New OleDbCommand("SELECT [acc_no] FROM [tblshipper] ORDER BY [acc_no]", con)
    con.Open()

    dt.Load(cmd.ExecuteReader())
End Using

cmbAccountNo.DataSource = dt
cmbAccountNo.DisplayMember = "acc_no"
cmbAccountNo.ValueMember = "acc_no"

Upvotes: 1

Related Questions