Member Avatar for cmstoner

Hi all,
I have a form on my program, and I have two comboboxes that I want to fill with data from columns in my database. I've tried three or four different ways that I've found searching around the web, but none of them seem to be working. They don't produce errors, but they don't load the combos either. For clarity, I've tried to bind them to the database, I've tried using datasets, and tried datatables. The code I've been working with is below. Can anyone offer a suggestion to get this working for me? Thanks!

con.Open()

'load hotel dropdown
Dim cmd As OleDbCommand = New OleDbCommand("SELECT Hotel FROM Hotels ORDER BY Hotel", con)
Dim dt As New DataTable()
Dim da As New OleDbDataAdapter()
da.SelectCommand = cmd
da.Fill(dt)

Dim r As DataRow
For Each r In dt.Rows
    cmb_hotel.Items.Add(r(1).ToString)
Next

'load restaurant dropdown
cmd = New OleDbCommand("SELECT Rest_Name FROM Restaurants ORDER BY Rest_Name", con)
Dim dt1 As New DataTable()
Dim da1 As New OleDbDataAdapter()
da1.SelectCommand = cmd
da1.Fill(dt)

Dim r1 As DataRow
For Each r1 In dt.Rows
    cmb_restaurant.Items.Add(r1(1).ToString)
Next
con.Close()
  • 3 Contributors
  • 4 Replies
  • 5K Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by poojavb
Member Avatar for Begginnerdev

It may seem like alot of work, but this will pay off in the long run.

When working with databases, I always try to create strongly typed classes.

(Check the file attached for an example)

So you can do the following to retreive a list:

Dim hHotel as New Hotel
Dim lstHotel as New List(Of Hotel)

lstHotel = hHotel.SelectAll()

For i = 0 To lstHotel.Count - 1
    cmb_hotel.Items.Add(lstHotel(i).Hotel)
Next

Once you tackle classing the objects, the rest is SUPER easy.

Public Class Hotel
#Region "Local Variable Delcaration"
    Private sHotel As String
    Private sAddress As String
    Private sPhone As String
    Private sUnique As String
    Private db As New Connection
#End Region

#Region "Public Property Declaration"
    Public Property Hotel As String
        Get
            Return Me.sHotel
        End Get
        Set(value As String)
            Me.sHotel = value
        End Set
    End Property

    Public Property Address As String
        Get
            Return Me.sAddress
        End Get
        Set(value As String)
            Me.sAddress = value
        End Set
    End Property

    Public Property Phone As String
        Get
            Return Me.sPhone
        End Get
        Set(value As String)
            Me.sPhone = value
        End Set
    End Property

    Public Property Unique As String
        Get
            Return Me.sUnique
        End Get
        Set(value As String)
            Me.sUnique = value
        End Set
    End Property
#End Region

#Region "Sub Procedutes"
    Public Sub New()
        'Blank constructor
    End Sub

    Public Sub New(ByVal sUnique As String)
        Try
            If sUnique <> "" Then
                Unique = sUnique
                GetHotelInformation(db.fNew)
                'Close connection afterward
                db.Close()
            End If
        Catch ex As Exception
            MsgBox("There was a problem instatiating the class!" & vbCrLf & ex.Message)
        End Try
    End Sub

    Private Sub GetHotelInformation(ByVal dbCon As OleDb.OleDbConnection)
        Try
            'Make sure a connection was passed in
            If IsNothing(dbCon) = False Then
                Dim cmd As New OleDb.OleDbCommand("SELECT * FROM Hotels WHERE uniquecolumn='" & Unique & "'", dbCon)
                Dim dat As New OleDb.OleDbDataAdapter(cmd)
                Dim ds As New DataSet
                dat.Fill(ds, "Hotels")

                If ds.Tables("Hotels").Rows.Count > 0 Then
                    With ds.Tables("Hotels")
                        'Your column names will be pulled from the database.
                        'Use the column names that are in the database.
                        Hotel = .Rows(0)("hotelName").ToString
                        Address = .Rows(0)("hotelAddress").ToString
                        Phone = .Rows(0)("hotelPhone").ToString
                    End With
                Else
                    MsgBox("No records returned." & vbCrLf & "Unable to retreive hotel information.")
                End If
            End If
        Catch ex As Exception
            MsgBox("There was a problem retreiving the information for the record!" & vbCrLf & ex.Message)
        End Try
    End Sub

    Public Sub Insert()
        Try
            Dim cmd As New OleDb.OleDbCommand("INSERT INTO Hotel(hotelName,hotelAddress,hotelPhone) VALUES " & _
                                              "('" & Hotel & "'," & _
                                              "'" & Address & "'," & _
                                              "'" & Phone & "')", db.fNew)

            cmd.ExecuteNonQuery()

            db.Close()
        Catch ex As Exception
            MsgBox("There was a problem interting the Hotel!" & vbCrLf & ex.Message)
        End Try
    End Sub

    Public Sub Update()
        Try
            Dim cmd As New OleDb.OleDbCommand("UPDATE Hotel SET " & _
                                              "hotelName='" & Hotel & "'," & _
                                              "hotelAddress='" & Address & "'," & _
                                              "hotelPhone='" & Phone & "'" & _
                                              " WHERE uniquecolumn='" & Unique & "'", db.fNew)
            cmd.ExecuteNonQuery()

            db.Close()
        Catch ex As Exception
            MsgBox("There was a problem updating the Hotel!" & vbCrLf & ex.Message)
        End Try
    End Sub

    Public Sub Delete()
        Try
            Dim cmd As New OleDb.OleDbCommand("DELETE FROM Hotel WHERE uniquecolumn='" & Unique & "'", db.fNew)
            cmd.ExecuteNonQuery()

            db.Close()
        Catch ex As Exception
            MsgBox("There was a problem deleting the Hotel!" & vbCrLf & ex.Message)
        End Try
    End Sub

    Public Function SelectAll() As List(Of Hotel)
        Try
            Dim cmd As New OleDb.OleDbCommand("SELECT * FROM Hotels", db.fNew)
            Dim dat As New OleDb.OleDbDataAdapter(cmd)
            Dim ds As New DataSet
            dat.Fill(ds, "Hotels")

            Dim lstHotels As New List(Of Hotel)
            Dim htl As New Hotel

            If ds.Tables("Hotels").Rows.Count > 0 Then
                For i = 0 To ds.Tables("Hotels").Rows.Count - 1
                    With ds.Tables("Hotels")
                        'Your column names will be pulled from the database.
                        'Use the column names that are in the database.
                        htl.Unique = .Rows(i)("uniquecolumn").ToString
                        htl.Hotel = .Rows(i)("hotelName").ToString
                        htl.Address = .Rows(i)("hotelAddress").ToString
                        htl.Phone = .Rows(i)("hotelPhone").ToString
                    End With

                    lstHotels.Add(htl)
                Next

                Return lstHotels
            Else
                MsgBox("No records returned." & vbCrLf & "Unable to retreive hotels.")
                Return Nothing
            End If
        Catch ex As Exception
            MsgBox("There was a problem retreiving the list of hotels!" & vbCrLf & ex.Message)
            Return Nothing
        End Try
    End Function
#End Region
End Class

Public Class Connection
    'Create a connection class, so you will not have to retype it every time, just create a new instance.
    'Example:
    ' Dim db As New Connection
    Dim con As New OleDb.OleDbConnection("ConnectionStringHere")

    Public Function fNew() As OleDb.OleDbConnection
        Try
            con.Open()
            Return con
        Catch ex As Exception
            MsgBox("There was a problem opening the connection" & vbCrLf & ex.Message)
            Return Nothing
        End Try
    End Function

    Public Sub Close()
        con.Close()
    End Sub

    Public Sub Dispose()
        con.Dispose()
    End Sub
End Class
Edited by Begginnerdev
Member Avatar for poojavb

Check if this will be fine for u

Dim cmd As New OleDbCommand("SELECT colname FROM Tablename", Connection)
Dim reader As OleDbDataReader = cmd.ExecuteReader
combobox1.Items.Clear()
While reader.Read
      combobox1.Items.Add(reader("colname"))
End While
reader.close()
Member Avatar for cmstoner

Thanks for the suggestions, but for whatever reason, I still can't fill the combos. After poojavb's suggestion, the code I have is:

con.Open()
'load hotel dropdown
Dim cmd As OleDbCommand = New OleDbCommand("SELECT Hotel FROM Hotels ORDER BY Hotel", con)
rdr = cmd.ExecuteReader
cmb_hotel.Items.Clear()
While rdr.Read
    cmb_hotel.Items.Add(rdr("Hotel"))
End While

'load restaurant dropdown
cmd = New OleDbCommand("SELECT Rest_Name FROM Restaurants ORDER BY Rest_Name", con)
rdr = cmd.ExecuteReader
cmb_restaurant.Items.Clear()
While rdr.Read
    cmb_restaurant.Items.Add(rdr("Rest_Name"))
End While
con.Close()

I don't see any reason why this shouldn't work. I've double checked to make sure I don't inadvertantly clear the combos elsewhere in the code, but I'm okay there. I honestly don't know what else to try. Is there anything else I can try?

Member Avatar for poojavb

Debug ur code and check the flow of ur code...u will come to know where u are facing problem....by clicking F8

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.