Solved

Populate Combos from DB and leave empty item

Posted on 2006-10-27
10
178 Views
Last Modified: 2010-04-23
Hi

I am populating some combos from an access db but when they are populated the first record of the table is visible.  How can i make this blank or have text such as [Enter User Name].

Thanks

Code for populating combos:

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\ITAssets.mdb")

        Dim UserAdapter As New OleDbDataAdapter("SELECT Name FROM tblUsers", conn)
       
        Dim UserDT As New DataTable("tblUsers")
       

        Call UserAdapter.Fill(UserDT)   'Execute SQL above and fill data table with records.
       

        'Populate Combo Boxes
        cboUser.DataSource = UserDT


I have tried adding a blank record to the DB and that sort of works but leaves a blank record as a choice.


Thanks
       
0
Comment
Question by:weight01
  • 5
  • 3
  • 2
10 Comments
 
LVL 4

Expert Comment

by:davehunt00
ID: 17822055
Try something like this:

    Private Sub FillList()
        mylist.DataSource = UserDT
        mylist.DataTextField = "Name"
        mylist.DataValueField = "ID"
        mylist.DataBind()
        mylist.Items.Insert(0, "--- Select a name ---")      
        mylist.SelectedIndex() = 0
    End Sub

hth
Dave
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17826167
Does

     cboUser.SelectedIndex = -1

not produce the effect you want?

Roger
0
 
LVL 1

Author Comment

by:weight01
ID: 17829405
Roger

Thanks did not work

Thanks anyway
0
 
LVL 1

Author Comment

by:weight01
ID: 17829412
Dave

Did not work

thanks anyway
0
 
LVL 4

Expert Comment

by:davehunt00
ID: 17829513
What error or manifestation are you seeing?  Are you sure the data table is populated?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:weight01
ID: 17829769
The combo box is being populated with the data, however, it always shows the first record.  I would like that to be either blank or some text such as [Select a User]
0
 
LVL 1

Author Comment

by:weight01
ID: 17829805
Just to clarify, I recieve no errors with my code and all combos are populated.  I use the following to poplulate all the combos on a form:

Public Sub FillCombos()

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\ITAssets.mdb")

        Dim UserAdapter As New OleDbDataAdapter("SELECT Name FROM tblUsers", conn)
        Dim DeptAdapter As New OleDbDataAdapter("SELECT Department FROM tblDepartment", conn)
        Dim EFCTAGAdapter As New OleDbDataAdapter("SELECT EFCTAG FROM tblEFCTags", conn)
        Dim ModelAdapter As New OleDbDataAdapter("SELECT Model FROM tblModel", conn)


        Dim UserDT As New DataTable("tblUsers")
        Dim DepartmentDT As New DataTable("tblDepartment")
        Dim EFCTagDT As New DataTable("tblEFCTags")
        Dim ModelDT As New DataTable("tblModel")


        Call UserAdapter.Fill(UserDT)   'Execute SQL above and fill data table with records.
        Call DeptAdapter.Fill(DepartmentDT)
        Call EFCTAGAdapter.Fill(EFCTagDT)
        Call ModelAdapter.Fill(ModelDT)


        'Populate Combo Boxes

        cboUser.DataSource = UserDT
        cboUser.DisplayMember = ("Name")
        cboDepartment.DataSource = DepartmentDT
        cboDepartment.DisplayMember = ("Department")
        cboEFCRef.DataSource = EFCTagDT
        cboEFCRef.DisplayMember = ("EFCTAG")
        cboModel.DataSource = ModelDT
        cboModel.DisplayMember = ("Model")

        'Types
        With cboType
            .Items.Add("Laptop")
            .Items.Add("Desktop")
            .Items.Add("Printer")
            .Items.Add("Server")
            .Items.Add("Switch")
            .Items.Add("Hub")

        End With

        'Manufacturer
        With cboManufacturer
            .Items.Add("HP")
            .Items.Add("Dell")
            .Items.Add("Cisco")
            .Items.Add("IBM")
            .Items.Add("AMD")
            .Items.Add("CMS")
            .Items.Add("ZooStorm")

        End With

        'Supplier
        With cboSupplier
            .Items.Add("Supplier A")
            .Items.Add("Supplier B")

        End With

0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17830586
The "in principle" problem here is that when a combobox is bound to a datatable it will only display what it is in that datatable and it will treat everything in the datatable as a "record".  As you've discovered, if you add a "record" to the datatable to give a blank entry or some alternative such as "[please select]", a user can then select that AS A RECORD.  Normally, if you force a combobox (even when it's bound) to have NO selection, by setting it's .SelectedIndex = -1, it will show a blank entry.  But I suggested that and you say it didn't work.

The ways round this "in principle" problem are (1) not to BIND the combobox to the datatable but - like you have done with your other comboboxes - FILL it with the data from the datatable.  On these lines

        With cboType
            .Items.Add("--- Select a name ---")
            For Each dr As DataRow in UserDT
                .Items.Add(dr("Name"))
            Next
        End With

or (2) to add a "record" to the datatable, but to put code (probably in the .SelectedIndexChanged event) to treat THAT selection differently from the selection of any valid "record".

Roger
0
 
LVL 1

Author Comment

by:weight01
ID: 17830691
Thanks Roger, you have confirmed to me that there is no staight forward solution.

As a solution I have already created a record in the DB user table called -- Select a User -- and included this code as part of the validation when clicking a save button to prevent a user saving that record :

ElseIf cboUser.Text = "" Or cboUser.Text = "-- Select a User --" Then
            MessageBox.Show("Please enter a User.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
            cboUser.Focus()

Thanks
0
 
LVL 4

Expert Comment

by:davehunt00
ID: 17831030
Yes, here is an extension of what Roger is talking about that I should have given you. What you do is disable the Submit button or the NextList until myList.SelectedIndex <> 0

    Private Sub mylist_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mylist.SelectedIndexChanged

        If mylist.SelectedIndex = 0 Then
            Submit1.Disabled = True
            nextList.Enabled = False
        Else
            Submit1.Disabled = False
            nextList.Enabled = True
        End If
    End Sub

Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Set form below another form 3 35
Format column on datatable 7 28
Copy/Clone an object. 9 29
Error "String or binary data would be truncated" in an update on a datatable 7 27
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now