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]

Re: Fill a Combo box with a data reader

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

Private Sub Form1_Load(ByVa l sender As Object, _
ByVal e As EventArgs) Handles MyBase.Load
Try
Dim dbpath As String = "c:\t\db1.m db"
Dim dbConn As New OleDbConnection ( _
"Provider=Micro soft.Jet.OLEDB. 4.0;DATA SOURCE=" & dbpath)
dbConn.Open()
Dim dbCommand As New OleDbCommand( _
"SELECT * FROM Customers", dbConn)
Dim dbDR As OleDb.OleDbData Reader = dbCommand.Execu teReader
Do While dbDR.Read
ComboBox1.Items .Add(dbDR("CUSC ODE"))
Loop
Catch ex As Exception
MessageBox.Show (ex.Message & vbCrLf & ex.StackTrace, "Error")
End Try
End Sub

[Greg Burns]

Re: Fill a Combo box with a data reader

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

Greg

Imports System.Data.Ole Db
Public Class Form1

Private Sub Form1_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load


Try
Dim dbpath As String = "c:\t\db1.m db"
Dim dbConn As New OleDbConnection ( _
"Provider=Micro soft.Jet.OLEDB. 4.0;DATA SOURCE=" & dbpath)
dbConn.Open()
Dim dbCommand As New OleDbCommand( _
"SELECT * FROM Customers", dbConn)
Dim dbDR As OleDb.OleDbData Reader = dbCommand.Execu teReader

ComboBox1.Begin Update()

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

ComboBox1.EndUp date()

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]

Re: Fill a Combo box with a data reader

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.CostCentresT ableAdapter.Fil l(Me.AssetsData Set.CostCentres )

So I have a databound comCostCentres with these properties
DateSource = CostCentresBind ingSource
Display Member = CostName
Display Value = CostCode
Selectecd Value = AssetsBindingSo urce - 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.comCostCentr e.DisplayMember = "CostName"
Me.comCostCentr e.ValueMember = "CostCode"

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

Dim dbCommand As New OleDbCommand("S ELECT * FROM CostCentres",
dbConn)
Dim dbDR As OleDb.OleDbData Reader = dbCommand.Execu teReader

comCostCentre.B eginUpdate()

Do While dbDR.Read
comCostCentre.I tems.Add(New ListBoxItem(dbD R("CostCode") ,
dbDR("CostName" )))
Loop

comCostCentre.E ndUpdate()

dbDR.Close()
dbConn.Close()

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




"Greg Burns" <bluebunny@news groups.nospam> wrote in message
news:e6nCLGi5FH A.3876@TK2MSFTN GP09.phx.gbl...[color=blue]
> Just to add to KitWest's excellent code, I would create a "ListBoxIte m"
> class so that you can store both a value and displayitem.
>
> Greg[/color]

[Vayse]

Re: Fill a Combo box with a data reader

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

"Vayse" <Vayse@nospam.n ospam> wrote in message
news:%23$taYWr5 FHA.3960@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> 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.CostCentresT ableAdapter.Fil l(Me.AssetsData Set.CostCentres )
>
> So I have a databound comCostCentres with these properties
> DateSource = CostCentresBind ingSource
> Display Member = CostName
> Display Value = CostCode
> Selectecd Value = AssetsBindingSo urce - 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.comCostCentr e.DisplayMember = "CostName"
> Me.comCostCentr e.ValueMember = "CostCode"
>
> Dim dbConn As New OleDbConnection (conCONNECT)
> dbConn.Open()
>
> Dim dbCommand As New OleDbCommand("S ELECT * FROM CostCentres",
> dbConn)
> Dim dbDR As OleDb.OleDbData Reader = dbCommand.Execu teReader
>
> comCostCentre.B eginUpdate()
>
> Do While dbDR.Read
> comCostCentre.I tems.Add(New ListBoxItem(dbD R("CostCode") ,
> dbDR("CostName" )))
> Loop
>
> comCostCentre.E ndUpdate()
>
> dbDR.Close()
> dbConn.Close()
>
> Catch ex As Exception
> MessageBox.Show (ex.Message & vbCrLf & ex.StackTrace, "Error")
> End Try
>
>
>
>
> "Greg Burns" <bluebunny@news groups.nospam> wrote in message
> news:e6nCLGi5FH A.3876@TK2MSFTN GP09.phx.gbl...[color=green]
>> Just to add to KitWest's excellent code, I would create a "ListBoxIte m"
>> class so that you can store both a value and displayitem.
>>
>> Greg[/color]
>
>[/color]

[Greg Burns]

Re: Fill a Combo box with a data reader

"Vayse" <Vayse@nospam.n ospam> wrote in message
news:%23$taYWr5 FHA.3960@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> Selectecd Value = AssetsBindingSo urce - 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?[/color]

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.T ext = "a CostName is the list"

' how to retrieve the value
Dim value as string = CType(comCostCe ntre.SelectedIt em,
ListBoxItem).Da ta.ToString

HTH,
Greg

[Greg Burns]

Re: Fill a Combo box with a data reader


"Vayse" <Vayse@nospam.n ospam> wrote in message
news:e1sAQgr5FH A.444@TK2MSFTNG P11.phx.gbl...[color=blue]
> Just to make it clear - I've removed the CostCentresTabl eAdapter and those
> databound properties. Thats as they were before I used the Datareader.
>
> "Vayse" <Vayse@nospam.n ospam> wrote in message
> news:%23$taYWr5 FHA.3960@TK2MSF TNGP10.phx.gbl. ..[color=green]
>> 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.CostCentresT ableAdapter.Fil l(Me.AssetsData Set.CostCentres )
>>
>> So I have a databound comCostCentres with these properties
>> DateSource = CostCentresBind ingSource
>> Display Member = CostName
>> Display Value = CostCode
>> Selectecd Value = AssetsBindingSo urce - 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?[/color][/color]

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 = AssetsBindingSo urce - CostCentre

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

Greg

[Vayse]

Re: Fill a Combo box with a data reader

Selected Value = AssetsBindingSo urce - CostCentre

If you look at the property, thats the way its displayed! Not a calculation
or anything, just means Assets.CostCent re
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" <bluebunny@news groups.nospam> wrote in message
news:%23i0XRsr5 FHA.2576@TK2MSF TNGP09.phx.gbl. ..[color=blue]
>
> "Vayse" <Vayse@nospam.n ospam> wrote in message
> news:e1sAQgr5FH A.444@TK2MSFTNG P11.phx.gbl...[color=green]
>> Just to make it clear - I've removed the CostCentresTabl eAdapter and
>> those databound properties. Thats as they were before I used the
>> Datareader.
>>
>> "Vayse" <Vayse@nospam.n ospam> wrote in message
>> news:%23$taYWr5 FHA.3960@TK2MSF TNGP10.phx.gbl. ..[color=darkred]
>>> 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.CostCentresT ableAdapter.Fil l(Me.AssetsData Set.CostCentres )
>>>
>>> So I have a databound comCostCentres with these properties
>>> DateSource = CostCentresBind ingSource
>>> Display Member = CostName
>>> Display Value = CostCode
>>> Selectecd Value = AssetsBindingSo urce - 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?[/color][/color]
>
> 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 = AssetsBindingSo urce - CostCentre
>
> What does it mean to be using a subtractation between objects here?
>
> Greg
>
>[/color]

[Greg Burns]

Re: Fill a Combo box with a data reader

"Vayse" <Vayse@nospam.n ospam> wrote in message
news:OPrdAet5FH A.744@TK2MSFTNG P10.phx.gbl...[color=blue]
> Selected Value = AssetsBindingSo urce - CostCentre
>
> If you look at the property, thats the way its displayed! Not a
> calculation or anything, just means Assets.CostCent re
> 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.[/color]

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]

Re: Fill a Combo box with a data reader

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" <bluebunny@news groups.nospam> wrote in message
news:eYSZalt5FH A.1864@TK2MSFTN GP12.phx.gbl...[color=blue]
> "Vayse" <Vayse@nospam.n ospam> wrote in message
> news:OPrdAet5FH A.744@TK2MSFTNG P10.phx.gbl...[color=green]
>> Selected Value = AssetsBindingSo urce - CostCentre
>>
>> If you look at the property, thats the way its displayed! Not a
>> calculation or anything, just means Assets.CostCent re
>> 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.[/color]
>
> 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
>
>
>
>[/color]

[Jeffrey Tan[MSFT]]

Re: Fill a Combo box with a data reader

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(Data Set) and
the control(ComboBo x), 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]

Re: Fill a Combo box with a data reader

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
CategoriesTable Adapter
CategoriesBindi ngSource

comCatgIDBindin g is based on these.
Instead I'd like to use comCatgDataRead er, based on a Data Reader.

thanks for your help
Vayse



""Jeffrey Tan[MSFT]"" <v-jetan@online.mi crosoft.com> wrote in message
news:i3b5BWP6FH A.3796@TK2MSFTN GXA02.phx.gbl.. .[color=blue]
> 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(Data Set) and
> the control(ComboBo x), 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.
>[/color]

[Cor Ligthert [MVP]]

Re: Fill a Combo box with a data reader

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]]

Re: Fill a Combo box with a data reader

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]

Re: Fill a Combo box with a data reader

""Jeffrey Tan[MSFT]"" <v-jetan@online.mi crosoft.com> wrote in message
news:tuvahNb6FH A.3788@TK2MSFTN GXA02.phx.gbl.. .
Also, can you tell us what is your current problem? The current information[color=blue]
> I can get really makes me confused. Thanks[/color]

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.