Populate data a Combobox in vb from a access database

Question

0.00/5 (No votes)

See more:

Hi there,
I'm new to vb and I need to know a thing. I've made a simple form by using vb.net. There I entered a combobox and I connected it to a access database. But when I'm running the program, when I click the drop down mark I won't get the details from the connected database. Can somebody tell me what's the wrong with my code?
here is the code
[code]
Imports System.Data
Public Class Form1
Dim inc As Integer
Dim MaxRows As Integer

Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "PROVIDER = Microsoft.ace.oledb.12.0;data source = ItemList.accdb"
con.Open()

sql = "SELECT * FROM Item_List"
da = New OleDb.OleDbDataAdapter(sql, con)

da.Fill(ds, "ItemList")
con.Close()

MaxRows = ds.Tables("ItemList").Rows.Count
inc = -1

End Sub

End Class
[/code]

My vb version is vb 2005 express edition.

Posted 17-Jan-13 1:32am

CrugerBrent

Updated 4-Jul-20 8:06am

Add a Solution

2 solutions

Solution 2

Here is a function that I use to fill an Array with values selected from an Access Table.

To use the function you will need:

Dim larayOfSelectedValues() As String = Nothing
Dim lclsFillTypeSecureUserComboBox As clsFillArrayOfSelectedValues
Dim lsqlCommand As String = "SELECT TypeUser " &
"FROM 10_10_TypeSecureUser " &
"Order By TypeUser"

Statments to use the Function:

' ----  Load the Secure User Combo Box with the Secure User Names from Secure User Name Table
            lclsFillTypeSecureUserComboBox = New clsFillArrayOfSelectedValues
            If lclsFillTypeSecureUserComboBox.FillArrayOfSelectedValues(iclsOLEDBConnectionString:=mclsOLEDBConnectionString,
                                                                        istrSelectCommand:=lsqlCommand,
                                                                        iarayOfSelectedValues:=larayOfSelectedValues) = vbFalse Then
                Throw New Exception("An error occurred wil populating the Type user Combo Box")
            End If
            Me.cmboTypeUser.Items.AddRange(larayOfSelectedValues)

And Finally the Function:

Imports System.Data
Imports System.Data.OleDb
Imports Project_Reporting.Project_ReportingDataSetTableAdapters

''' <summary>
''' Selects all the Secure Users from the Secure User table [10_00_SecureUser]
''' and fills the Secure User Combo Box [cmboSecureUser] with the Secure User
''' Names
''' 
''' </summary>
Public Class clsFillArrayOfSelectedValues
    Private mstrClsTitle As String = "clsFillArrayOfSelectedValues"

    '<PageBreak>
    ''' <summary>
    ''' Function Selects values from a Table in the Access Data base and fills and array with the
    ''' selected values [iarayOfSelectedValues] using the provided OLEDb connection string
    ''' [iclsOLEDBConnectionString] and provided SQL select command [istrSelectCommand].
    '''     *)  Returns True if successful
    '''     *)  Returns False if un-successful
    '''     
    ''' The Parameters:
    '''     *)  iclsOLEDBConnectionString   -   The Database Connection string that is
    '''                                         created by the caller as:
    '''                                         "Provider=Microsoft.ACE.OLEDB.12.0; " +
    '''                                         "Data Source=" + .pstrDataBase + 
    '''                                                      "Project_Reporting.accdb;" +
    '''                                         "Jet OLEDB:Database Password=" + mstrPassword + ";" +
    '''                                         "Persist Security Info=False;"
    '''     *)  istrSelectCommand           -   The SQL Select Statement that will select the values
    '''                                         to be inserted into the array of selected values
    '''                                         [iaraySelectedValue].
    '''     *)  iarayOfSelectedValues          -   An array of the values selected from
    '''                                         the Table that can be inserted into a
    '''                                         Combo Box by the caller using:
    '''                                         [Me.cmboUseName.Items.AddRange(iarayOfSelectedValues)]
    '''    
    ''' </summary>
    ''' <param name="iclsOLEDBConnectionString"></param>
    ''' <param name="istrSelectCommand"></param>
    ''' <param name="iarayOfSelectedValues"></param>
    ''' <returns></returns>
    Public Function FillArrayOfSelectedValues(ByVal iclsOLEDBConnectionString As clsOLEDBConnectionString,
                                              ByVal istrSelectCommand As String,
                                              ByRef iarayOfSelectedValues() As String) As Boolean

        ' ----  Local Data Base Classes 
        Dim lcmdOleDbCommand As New OleDbCommand
        Dim lrdrOleDbDataReader As OleDbDataReader = Nothing

        '<PageBreak>
        Try

            With lcmdOleDbCommand
                .Connection = iclsOLEDBConnectionString.pOleDbConnection
                .Connection.Open()
                .CommandType = CommandType.Text
                .CommandText = istrSelectCommand
            End With

            ReDim iarayOfSelectedValues(0)
            lrdrOleDbDataReader = lcmdOleDbCommand.ExecuteReader
            While lrdrOleDbDataReader.Read
                Debug.Print("lrdrOleDbDataReader.Item(0) [" & lrdrOleDbDataReader.Item(0) & "]")
                iarayOfSelectedValues(UBound(iarayOfSelectedValues)) = lrdrOleDbDataReader.Item(0)
                ReDim Preserve iarayOfSelectedValues(UBound(iarayOfSelectedValues) + 1)
            End While
            lrdrOleDbDataReader.Close()
            ReDim Preserve iarayOfSelectedValues(UBound(iarayOfSelectedValues) - 1)
            Return vbTrue

        Catch ex As Exception
            Throw New Exception(mstrClsTitle & ".FillArrayOfSelectedValues " &
                                "An error occurred while filling the " &
                                "Type Secure User Combo Box " & vbCrLf &
                                "Err.Number [" & Err.Number & "] " & vbCrLf &
                                "Err.Description [" & Err.Description & "]")
            Return False
        Finally
            lcmdOleDbCommand.Connection.Close()
            lcmdOleDbCommand = Nothing
            lrdrOleDbDataReader = Nothing
        End Try
    End Function

End Class

Posted 4-Jul-20 8:06am

Member 14880851

Comments

Dave Kreskowiak 4-Jul-20 14:35pm

Asked and answered seven years ago, with a much simpler piece of code and explanation.

Richard Deeming 6-Jul-20 9:51am

Methods like that will force the user to write code which is vulnerable to SQL Injection[^].

You're also ReDiming the array on every iteration, which is horrendously inefficient. Use a List(Of T) instead.

And you're mixing proper structured exception handling with the old VB6-style Err object, and throwing away the details of any exceptions.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jenitshah · Accepted Answer · 2013-01-17T03:26:00

Try this :

VB

Imports System.Data.OleDb
Public Class homemain
    Dim cnn As New OleDbConnection(<connection string="">)
    Dim cmd As New OleDbCommand

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)handles Button1.Click
        
     cnn.Open()
     cmd.Connection=cnn
     cmd.CommandText="select empname from employee"
     
     Dim dr As OleDbDataReader=cmd.ExecuteReader
     
     While dr.Read
         cmbempname.items.add(dr.item(0))
     End While
     dr.close()
End Sub
End Class

Hope, This will help you.