By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,081 Members | 861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,081 IT Pros & Developers. It's quick & easy.

Fill a Combo box with a data reader

P: n/a
Has anyone an example of filling a combo box with a data reader?
Thanks
Vayse
Nov 23 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
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
Nov 23 '05 #3

P: n/a
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.C ostCentres)

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


"Greg Burns" <bl*******@newsgroups.nospam> wrote in message
news:e6**************@TK2MSFTNGP09.phx.gbl...
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

Nov 23 '05 #4

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

"Vayse" <Va***@nospam.nospam> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.C ostCentres)

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


"Greg Burns" <bl*******@newsgroups.nospam> wrote in message
news:e6**************@TK2MSFTNGP09.phx.gbl...
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


Nov 23 '05 #5

P: n/a
"Vayse" <Va***@nospam.nospam> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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
Nov 23 '05 #6

P: n/a

"Vayse" <Va***@nospam.nospam> wrote in message
news:e1*************@TK2MSFTNGP11.phx.gbl...
Just to make it clear - I've removed the CostCentresTableAdapter and those
databound properties. Thats as they were before I used the Datareader.

"Vayse" <Va***@nospam.nospam> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.C ostCentres)

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?


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
Nov 23 '05 #7

P: n/a
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" <bl*******@newsgroups.nospam> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

"Vayse" <Va***@nospam.nospam> wrote in message
news:e1*************@TK2MSFTNGP11.phx.gbl...
Just to make it clear - I've removed the CostCentresTableAdapter and
those databound properties. Thats as they were before I used the
Datareader.

"Vayse" <Va***@nospam.nospam> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.C ostCentres)

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?


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

Nov 23 '05 #8

P: n/a
"Vayse" <Va***@nospam.nospam> wrote in message
news:OP*************@TK2MSFTNGP10.phx.gbl...
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


Nov 23 '05 #9

P: n/a
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!

"Greg Burns" <bl*******@newsgroups.nospam> wrote in message
news:eY**************@TK2MSFTNGP12.phx.gbl...
"Vayse" <Va***@nospam.nospam> wrote in message
news:OP*************@TK2MSFTNGP10.phx.gbl...
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

Nov 23 '05 #10

P: n/a
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.

Nov 23 '05 #11

P: n/a
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

""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
news:i3**************@TK2MSFTNGXA02.phx.gbl...
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.

Nov 23 '05 #12

P: n/a
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
Nov 23 '05 #13

P: n/a
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.

Nov 23 '05 #14

P: n/a
""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
news:tu**************@TK2MSFTNGXA02.phx.gbl...
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.
Nov 23 '05 #15

P: n/a
Hi Vayse,

Thanks for your feedback, see inline.
1) On a databound form, must the combo boxes be databound? Of cause, we can use combobox without databinding. We can just use
ComboBox.Items.Add method to add the display items programmatically.
2) If they don't have to be databound, how do I get the combo box to

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.

Nov 23 '05 #16

P: n/a
Ah, that changes things. Thanks Cor.

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:ee**************@TK2MSFTNGP15.phx.gbl...
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

Nov 23 '05 #17

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

""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
news:$0**************@TK2MSFTNGXA02.phx.gbl...
Hi Vayse,

Thanks for your feedback, see inline.
1) On a databound form, must the combo boxes be databound? Of cause, we can use combobox without databinding. We can just use
ComboBox.Items.Add method to add the display items programmatically.
2) If they don't have to be databound, how do I get the combo box to

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.

Nov 23 '05 #18

P: n/a
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.

Nov 23 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.