Fill a Combo box with a data reader

[Vayse]

Has anyone an example of filling a combo box with a data reader?
Thanks
Vayse

 

[KitWest]

Here's one; you'll need at the top of your class code:
Imports System.Data.OleDb

Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As EventArgs) Handles MyBase.Load
Try
Dim dbpath As String = "c:\t\db1.mdb"
Dim dbConn As New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & dbpath)
dbConn.Open()
Dim dbCommand As New OleDbCommand( _
"SELECT * FROM Customers", dbConn)
Dim dbDR As OleDb.OleDbDataReader = dbCommand.ExecuteReader
Do While dbDR.Read
ComboBox1.Items.Add(dbDR("CUSCODE"))
Loop
Catch ex As Exception
MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace, "Error")
End Try
End Sub

 

[Greg Burns]

Just to add to KitWest's excellent code, I would create a "ListBoxItem"
class so that you can store both a value and displayitem.

Greg

Imports System.Data.OleDb
Public Class Form1

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


Try
Dim dbpath As String = "c:\t\db1.mdb"
Dim dbConn As New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & dbpath)
dbConn.Open()
Dim dbCommand As New OleDbCommand( _
"SELECT * FROM Customers", dbConn)
Dim dbDR As OleDb.OleDbDataReader = dbCommand.ExecuteReader

ComboBox1.BeginUpdate()

Do While dbDR.Read
ComboBox1.Items.Add(New ListBoxItem(Cint(dbDR("CUSCODE")),
dbDR("CUSNAME").ToString))
Loop

ComboBox1.EndUpdate()

Catch ex As Exception
MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace, "Error")
End Try
End Sub

End Class

Public Class ListBoxItem

Private listItemData As Object
Private listItemText As String

' This is what is displayed in the ComboBox drop-down
Public Overrides Function ToString() As String
Return listItemText
End Function

Public Sub New(ByVal itemData As Object, ByVal itemText As String)

listItemData = itemData
listItemText = itemText
End Sub

Public ReadOnly Property Data() As Object
Get
Data = listItemData
End Get

End Property

Public ReadOnly Property Text() As String
Get
Text = listItemText
End Get

End Property

End Class

 

[Vayse]

Thanks guys.
Another question on this.
Theres several combo boxes on this form, so for speed reasons I'd like to
remove the table adapters and binding sources, and just use Data readers.

Me.CostCentresTableAdapter.Fill(Me.AssetsDataSet.CostCentres)

So I have a databound comCostCentres with these properties
DateSource = CostCentresBindingSource
Display Member = CostName
Display Value = CostCode
Selectecd Value = AssetsBindingSource - CostCentre

I have the combo box filling correctly, thanks to your code. But it doesn't
display the select CostCentre correctly, and its not updating the Asset.
What step am I missing?
Thanks
Vayse


Try

' Fill the combo boxes
Me.comCostCentre.DisplayMember = "CostName"
Me.comCostCentre.ValueMember = "CostCode"

Dim dbConn As New OleDbConnection(conCONNECT)
dbConn.Open()

Dim dbCommand As New OleDbCommand("SELECT * FROM CostCentres",
dbConn)
Dim dbDR As OleDb.OleDbDataReader = dbCommand.ExecuteReader

comCostCentre.BeginUpdate()

Do While dbDR.Read
comCostCentre.Items.Add(New ListBoxItem(dbDR("CostCode"),
dbDR("CostName")))
Loop

comCostCentre.EndUpdate()

dbDR.Close()
dbConn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace, "Error")
End Try

 

[Vayse]

Just to make it clear - I've removed the CostCentresTableAdapter and those
databound properties. Thats as they were before I used the Datareader.

 

[Greg Burns]

Selectecd Value = AssetsBindingSource - CostCentre

I have the combo box filling correctly, thanks to your code. But it
doesn't display the select CostCentre correctly, and its not updating the
Asset.
What step am I missing?

I am not sure I follow, but here is some more code I use:

' set the combobox to a default display text
' this seems to automagically set the selected value as well
comCostCentre.Text = "a CostName is the list"

' how to retrieve the value
Dim value as string = CType(comCostCentre.SelectedItem,
ListBoxItem).Data.ToString

HTH,
Greg

 

[Greg Burns]

Just to make it clear - I've removed the CostCentresTableAdapter and those
databound properties. Thats as they were before I used the Datareader.

I guess I don't know your problem domain well enough. I see you have a
combobox name comConstCentres (I assume this contain costcenters, with
Name/Value pairs; CostName/CostCode). What do you mean when you say "its
not updating the Asset". I would take it to mean, setting the selected
value on your combobox is not sticking.?

I know you are not using the above code anymore, but what in the world did
the below line do?

Selectecd Value = AssetsBindingSource - CostCentre

What does it mean to be using a subtractation between objects here?

Greg

 

[Vayse]

Selected Value = AssetsBindingSource - CostCentre

If you look at the property, thats the way its displayed! Not a calculation
or anything, just means Assets.CostCentre
Also displayed that way if you click the smart tag. Kind of strange really.

And yes, the selected value in the combo box is not sticking. Also, its not
been filled correctly.
So when I navigate to a new record, the combo box is blank.

 

[Greg Burns]

Selected Value = AssetsBindingSource - CostCentre

If you look at the property, thats the way its displayed! Not a
calculation or anything, just means Assets.CostCentre
Also displayed that way if you click the smart tag. Kind of strange
really.

And yes, the selected value in the combo box is not sticking. Also, its
not been filled correctly.
So when I navigate to a new record, the combo box is blank.

That sounds like you are still using DataBinding. I am not sure you can
manually populate a combobox like that and have databinding still function.

Hopefully somebody else has some ideas.

Greg

 

[Vayse]

Yeah, I'm still using databinding on the form. Just wanted to "kind of"
unbind the combo boxes. I thought it would be possible.

Thanks for your help though - very useful for all my other forms, which
aren't data bound. And I'm beginning to regret ever making this form data
bound!

 

[Jeffrey Tan[MSFT]]

Hi Vayse,

I am not sure I understand your problem very well. Based on my knowledge,
we can not use SqlDataReader to do databinding in Winform, can you show me
how do you do this? If you just only use SqlDataReader to populate the
DataSet, then the databinding should have nothing to do with the
SqlDataReader, databinding only related with the datasource(DataSet) and
the control(ComboBox), once these 2 are OK, the databinding should be OK.

Can you provide a little sample project to demonstrate your problem, then I
can understand the context better. Thanks

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Vayse]

Hi Jeffrey
I can't provide a working project, because I can't get it working!
But I've put what I have here
http://h1.ripway.com/vayse/ProductCombo.zip

In the example, I'd like to remove
CategoriesTableAdapter
CategoriesBindingSource

comCatgIDBinding is based on these.
Instead I'd like to use comCatgDataReader, based on a Data Reader.

thanks for your help
Vayse

 

[Cor Ligthert [MVP]]

Vayse,

You know that if you are reading in this way information, that is already in
your datatable, than it will probably consume a lot of time doing it this
way.

By the way changing the reading from a datataadapter for datareading will
probably result in not more than 10 milliseconds. Be aware that for reading
the dataadapter uses just a datareader object.

Just to help you to overthink what you are doing

Cor

 

[Jeffrey Tan[MSFT]]

Hi Vayse,

Thanks for your feedback.

However, only a project is not helpful for us to track this problem, can
you give some problem context description to this issue? This will save
community a lot of time and give the community members a good
understanding.

Also, can you tell us what is your current problem? The current information
I can get really makes me confused. Thanks

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Vayse]

Also, can you tell us what is your current problem? The current information

I can get really makes me confused. Thanks

Heh, I confused myself at this stage!

My questions are these:
1) On a databound form, must the combo boxes be databound?
2) If they don't have to be databound, how do I get the combo box to update
the field.

 

[Jeffrey Tan[MSFT]]

Hi Vayse,

Thanks for your feedback, see inline.
Of cause, we can use combobox without databinding. We can just use
ComboBox.Items.Add method to add the display items programmatically.
update the field.
This depends on what does "field" pointed to, certain field in underlying
DataTable/DataSet or certain field in some table in DataBase.
Normally, after we selected an item, click a update button to update the
underlying datasource, we should use some primary key information(this
primary key information should store with other information in the item of
the combobox) to look the related row in DataTable/DataSet, then change the
corresponding column value in that row to your selected value.
To update the database table, you can use ADO.net with SQL statement to
update the change data into the database.

Hope this helps

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Vayse]

Ah, that changes things. Thanks Cor.

 

[Vayse]

Thanks for your help on this.
Its a lot clearer now.
I have no more questions on this!

Hi Vayse,

Thanks for your feedback, see inline.
Of cause, we can use combobox without databinding. We can just use
ComboBox.Items.Add method to add the display items programmatically.
update the field.
This depends on what does "field" pointed to, certain field in underlying
DataTable/DataSet or certain field in some table in DataBase.
Normally, after we selected an item, click a update button to update the
underlying datasource, we should use some primary key information(this
primary key information should store with other information in the item of
the combobox) to look the related row in DataTable/DataSet, then change
the
corresponding column value in that row to your selected value.
To update the database table, you can use ADO.net with SQL statement to
update the change data into the database.

Hope this helps

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Jeffrey Tan[MSFT]]

Hi Vayse,

Ok, if you have any further question, please feel free to post, we will
help you. Thanks

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.