ComboBox - How do you stop the user from clicking a blank area?

[emaduddeen]

Hi Everyone,

Can you tell me how to stop a ComboBox from displaying blank data?

The query I'm using does not allow nulls but the ComboBox is showing black areas and the user can click in those areas.

The PureComponents ComboBox did not show the blank areas but when I started to use the ComboBox from VS 2008 the blank areas are showing up.

The attachment shows me clicking in a blank area.

Thanks.

Truly,
Emad

 

[jmcilhinney]

A ComboBox is only going to display the items it contains. If your ComboBox is displaying blank items then it contains blank items. Presumably you have bound it to a Datatable or some other list. Have you tried looping through that list in code to see exactly what it contains?

 

[emaduddeen]

Hi jmcilhinney,

Since I'm still new to VB I never did that before. Can you show me what coding is needed to do that?

Here's the query I'm using to populate it and the coding I used:

    Private Sub PopulateTeacherNameComboBox()
        ' Declare strings to hold SQL Statements.
        '----------------------------------------
        strSqlStatement = _
            "SELECT LTrim(FirstName) & ' ' & LTrim(LastName) AS [Teacher Name],  " & _
                   "TeacherID " & _
              "FROM Teacher " & _
             "WHERE LastName IS NOT NULL " & _
             "ORDER BY 1"

        ' Set up a command to execute a query.
        '-------------------------------------
        objCommand = New OleDbCommand(strSqlStatement, objConnection)
        objCommand.CommandType = CommandType.Text

        ' Load the data into the DataAdapter then into the ComboBox.
        '-----------------------------------------------------------
        Try
            objDataAdapter.SelectCommand = New OleDbCommand(strSqlStatement, objConnection)

            objDataAdapter.Fill(objDataSet)

            With ComboBoxTeacherName
                .DataSource = objDataSet.Tables(0)
                .DisplayMember = "Teacher Name"
                .ValueMember = "TeacherID"
            End With

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Sub

Truly,
Emad

 

[jmcilhinney]

My guess is that you have records where the FirstName and LastName are both blank, so the [Teacher Name] ends up just a single space character. You could first of all remove the ORDER BY clause from your query and see what happens. If the blank items end up interspersed with the others then you know that it's not the control doing something funky. You can also test the value returned by Fill, which is the number of records retrieved. According to your theory it should 5 as that's the number of names in the ComboBox, but I'll wager that it's more than that.

 

[emaduddeen]

Hi jmcilhinney,

I removed the ORDER BY statement and it only changed the order of data.

I also added the LTRIM AND RTRIM functions to the WHERE statement in case there were blank spaces in the data but there wasn't any.

I also tried the query in MS Access and the query does not show any blank rows so I'm assuming there must be a property that needs to be set on the ComboBox control but I'm not sure which one to set.

I changed the DropDownHight to a small number to cause a scrollbar to be displayed and you can still scroll down to the blank rows. I also changed the MaxDropDownItems to a smaller number and both of these changes did not help.

Could you show some coding that loops through the ComboBox and deletes the blank rows? At least that can be a work-around for now until I learn more about VB 2008.

Truly,
Emad

 

[jmcilhinney]

There is no code to delete the blank rows. Either they are items or they are not. If they not then there's nothing to delete. If they are then you wouldn't delete them, you just wouldn't put them in in the first place. How about you try what I've already suggested so we can diagnose the problem properly?

You can also test the value returned by Fill, which is the number of records retrieved.

 

[emaduddeen]

Hi,

It returns 5 rows.

Here's some of the code I used to find out.

        Dim intTotalRowsReturned As Integer

        ' Declare strings to hold SQL Statements.
        '----------------------------------------
        strSqlStatement = _
            "SELECT LTrim(FirstName) & ' ' & LTrim(LastName) AS [Teacher Name],  " & _
                   "TeacherID " & _
              "FROM Teacher " & _
             "WHERE LTrim(RTrim(LastName)) IS NOT NULL " & _
             "ORDER BY 1"

        ' Set up a command to execute a query.
        '-------------------------------------
        objCommand = New OleDbCommand(strSqlStatement, objConnection)
        objCommand.CommandType = CommandType.Text

        ' Load the data into the DataAdapter then into the ComboBox.
        '-----------------------------------------------------------
        Try
            objDataAdapter.SelectCommand = New OleDbCommand(strSqlStatement, objConnection)

            intTotalRowsReturned = objDataAdapter.Fill(objDataSet)

            MessageBox.Show(intTotalRowsReturned)

I appreciate all the help you are giving us.

Truly,
Emad

 

[jmcilhinney]

Something funky is going on there. I've never seen a ComboBox display extra items and I just tested with VS 2008/.NET 3.5 and VS 2010/.NET 4.0 and in both case a ComboBox only displayed the items I added to the DataTable it was bound to.

I suggest that you check the Items.Count property of your ComboBox and the Rows.Count property of your DataTable. Don't do it straight after binding. Add a dummy button to your form and test those properties in its Click event handler. Also, try creating a new project with just a ComboBox on the form and the following code:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim table As New DataTable

        With table.Columns
            .Add("ID", GetType(Integer))
            .Add("Name", GetType(String))
        End With

        With table.Rows
            .Add(1, "Peter")
            .Add(2, "Paul")
            .Add(3, "Mary")
        End With

        With Me.ComboBox1
            .DisplayMember = "Name"
            .DataSource = table
        End With
    End Sub

End Class

Let's see how that behaves on your machine because on mine it just shows three items, as you'd expect.

 

[emaduddeen]

Gday my friend.

Thanks for all of your help.

With your inspiration and hints I placed a message box in the Teacher ComboBox filling procedure after filling of the DataAdapter and also in a button as you described.

Here is what was returned:
After the filling of the DataAdapter the ComboBox count was 5 which is what I really wanted but when the button was clicked the number jumped to 18.

This particular form has 2 ComboBoxes and after running the debugger and placing a watch on: ComboBoxTeacherName.Items.Count I found where the number jumped to 18.

It jumped to 18 when this line of code in the wax executed in the procedure that fills the other ComboBox: objDataAdapter.Fill(objDataSet)

I didn't know that there were other ways of filling the DataAdapter but under curiosity I placed a comma next to objDataSet and changed it to this after finding out I can create a table name to use later in code: objDataAdapter.Fill(objDataSet, "Teacher")

I did the same for the other ComboBox and called it "Grade"

I also find out I can change the 0 in .DataSource = objDataSet.Tables(0) as well because I had no idea what the 0 was for anyway. I changed it to this:
.DataSource = objDataSet.Tables("Teacher")

I did this also for the "Grade" ComboBox.

When I ran the app, I got my 5 rows in the teacher ComboBox and got the correct number of rows in the grade ComboBox.

Well, that's a valuable lesson I just learned and I hope your efforts and mine will help others as new to VB 2008 as I am.

Truly,
Emad