Create Listbox Values from Database

Question

This is my second experience with VB.net so I am not sure about the language. I am trying to create the listbox values from my table column titled "category". There are duplicates so I am trying to only display each category once. I have to do it this way because the user's can add more categories which means my listbox values need to dynamically update.

I am not sure I am doing this process right:

Protected Sub CategoryListBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CategoryListBox.SelectedIndexChanged

    Dim conn As OleDbConnection = New OleDbConnection("Provider=""*******"";user id=" & strUserID & ";data source=" & strDatabase & ";password=" & strPssWd)

    Dim ClassifiedStr As OleDbCommand = New OleDbCommand("SELECT classid, Addate, username, category, description FROM t_classifieds", conn)
    Dim OracleDataAdapterAds As OleDbDataAdapter = New OleDbDataAdapter
    OracleDataAdapterAds.SelectCommand = ClassifiedStr
    Dim DsAds As DataSet = New DataSet
    DsAds.Clear()
    OracleDataAdapterAds.Fill(DsAds, "t_classifieds")

    CategoryListBox.DataTextField = "category"
    CategoryListBox.DataValueField = "category"
    CategoryListBox.DataSource = DsAds
    CategoryListBox.DataMember = "t_classifieds"
    CategoryListBox.DataBind()

    Dim Categories As String
    Dim CategoryID

    For Each dr As DataRow In DsAds.Tables("t_classifieds").Rows
        Categories = dr("category").ToString()
        CategoryID = dr("classid").ToString()

        CategoryListBox.Items.Add(Categories)

    Next dr

    conn.Close()
End Sub

I have looked at other examples and I have tried to mirror them but nothing prints in my listbox.

My second version of my code works if I use a predefined Dim value but for some reason I am not getting the database connection to display the database Category listing.

Second Version:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    FillList()

End Sub

Private Sub FillList()

    Dim conn As OleDbConnection = New OleDbConnection("Provider=""MSDAORA.1"";user id=" & strUserID & ";data source=" & strDatabase & ";password=" & strPssWd)
    Dim data_reader As OleDbDataReader
    Dim ClassifiedStr As OleDbCommand = New OleDbCommand("SELECT * FROM t_classifieds ", conn)
    Dim Categories
    conn.Open()
    data_reader = ClassifiedStr.ExecuteReader()
    CategoryListBox.Items.Clear()
    If data_reader.HasRows Then
        Do While data_reader.Read()
            Categories = data_reader.Item("category")
            'CategoryListBox.Items.Add(New ListItem(Categories))
            CategoryListBox.Items.Add(Categories.ToString())
        Loop
    End If
    data_reader.Close()
    data_reader = Nothing
    ClassifiedStr.Dispose()
    ClassifiedStr = Nothing
    conn.Close()
    conn.Dispose()


    'Dim dWorkDate As Date = CDate("01.01.2014")
    'While dWorkDate < Date.Today
    'CategoryListBox.Items.Add(dWorkDate.ToString("dd.MM.yyyy"))
    'dWorkDate = dWorkDate.AddDays(1)
    'End While
End Sub

Keith · Accepted Answer · 2015-06-15 15:13:54Z

1

Select DISTINCT values for the category:

Dim ClassifiedStr As OleDbCommand = New OleDbCommand("SELECT DISTINCT category FROM t_classifieds", conn)

'...

CategoryListBox.DataSource = DsAds.Tables("t_classifieds")
CategoryListBox.DataTextField = "category"
CategoryListBox.DataValueField = "category"

By the way, you don't need to call Clear on a New DataSet.

edited Jun 15, 2015 at 15:13

answered Jun 15, 2015 at 14:55

Keith

1,3212 gold badges14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

narue1992 Over a year ago

I did what you suggested and DisplayMember gave me an error stating it can't be used for a listBox. Also, nothing displays still for my listbox

Keith Over a year ago

Oh, ASP. NET. How did I miss those tags. See update.

narue1992 Over a year ago

I see the update but nothing prints. Not sure what is the issue :/

Keith Over a year ago

Set a breakpoint, debug, and make sure you're actually getting data and it's being stored in your DataSet.

narue1992 Over a year ago

I added my answer because my old code actually worked but your Sql was my fix to my issue. Just out of curiousity, I have one space after the first category listing. Do you know what would cause it?

|

narue1992 · Accepted Answer · 2015-06-15 15:36:12Z

Realized my old code worked but I needed @Keith Sql code.

If Not IsPostBack Then
        If Not CategoryListBox Is Nothing Then


            Dim conn As OleDbConnection = New OleDbConnection("Provider=""****"";user id=" & strUserID & ";data source=" & strDatabase & ";password=" & strPssWd)

            'Display All Classified Ads listed in the Database based on the following format/order
            'Date    Name    Home Phone Number     Description

            Dim ClassifiedStr As OleDbCommand = New OleDbCommand("SELECT DISTINCT category FROM t_classifieds", conn)
            Dim OracleDataAdapterAds As OleDbDataAdapter = New OleDbDataAdapter
            OracleDataAdapterAds.SelectCommand = ClassifiedStr
            Dim DsAds As DataSet = New DataSet
            DsAds.Clear()
            OracleDataAdapterAds.Fill(DsAds, "t_classifieds")
            CategoryListBox.DataSource = DsAds
            CategoryListBox.DataMember = "t_classifieds"
            CategoryListBox.DataBind()
        End If
    End If

Only Issue I have is a random space :/ enter image description here

Collectives™ on Stack Overflow

Create Listbox Values from Database

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related