On forum today I saw an interesting post where I had advised the ORPO that a datareader could be binded to combobox, having worked on ASP.NET for the past months now its easier than falling over as you can use the 'DropDownList' object. However, for the Windows Combobox object ( regardless of web based development semantics ) you cannot do this as it will pipe up with the friendly...
"Complex DataBinding accepts as a data souce either an IList or an IListSource"
Anyhoo - fix provided kinda went around the houses somewhat and I thought it might be an idea to show my DataReader to DataTable converter class. Basically, the base DbDataAdapter class exposes a 'Fill' routine that can allow the user to fill an instance of a datatable from a datareader instance. By inheriting this class and exposing this routine, you've got an easy converter object that makes binding to a combobox easy without having to a) create a datatable from scratch or b) creating an array (or other object) to contain the data to bind to.
Note: this code is for .NET 1.*, however, in .NET you might want to strongly type the second overloaded 'FillFromReader' method 'datatables' parameter, e.g. from ArrayList to 'Collections.Generic.List(Of DataTable).
Here's the implementation code...
Imports System.Data
Imports System.Data.Common
Public Class DRToTableConverter
Inherits Data.Common.DbDataAdapter
#Region "Comments"
' Converts a datareader to a datatable
#End Region
Public
Overloads Function FillFromReader(
ByVal dataTableToFill
As DataTable,
ByVal dataReader
As IDataReader)
As Integer' Fills the datatable array with all results from the datareader
Return MyBase.Fill(DataTableToFill, DataReader)
End FunctionPublic
Overloads Function FillFromReader(
ByVal dataTables
As ArrayList,
ByVal dataReader
As IDataReader)
As Integer' Fills the datatable array with all results from the datareader
Dim dt As DataTable
Dim noOfRowsAffected As Integer
' Loop through the datareader reading each resultset into a table object
Do' Create a datatable
dt = New DataTable
' Perform the fill
noOfRowsAffected += MyBase.Fill(dt, dataReader)
' Add to the return collection
dataTables.Add(dt)
Loop While dataReader.NextResult
Return noOfRowsAffected
End Function
#Region "Overridden without implementation"
Protected
Overrides Sub OnRowUpdated(
ByVal value
As System.Data.Common.RowUpdatedEventArgs)
' NOT IMPLEMENTED
End SubProtected
Overrides Sub OnRowUpdating(
ByVal value
As System.Data.Common.RowUpdatingEventArgs)
' NOT IMPLEMENTED
End SubProtected
Overrides Function CreateRowUpdatedEvent(
ByVal dataRow
As System.Data.DataRow,
ByVal command
As System.Data.IDbCommand,
ByVal statementType
As System.Data.StatementType,
ByVal tableMapping
As System.Data.Common.DataTableMapping)
As System.Data.Common.RowUpdatedEventArgs
Return Nothing
End FunctionProtected
Overrides Function CreateRowUpdatingEvent(
ByVal dataRow
As System.Data.DataRow,
ByVal command
As System.Data.IDbCommand,
ByVal statementType
As System.Data.StatementType,
ByVal tableMapping
As System.Data.Common.DataTableMapping)
As System.Data.Common.RowUpdatingEventArgs
Return Nothing
End Function
#End Region
End Class And here's an example of using it
' Generate the connection object
Dim sqlDBConn As New SqlConnection("Network Library=DBMSSOCN;Data Source=10.0.0.10;Initial Catalog=MyDatabase;User ID=Username;Password=Password;")
Dim sqlComm As New SqlCommand("SELECT * FROM MyTable", sqlDBConn)
' Open the connection and execute
sqlDBConn.Open()
Dim dr As SqlDataReader = sqlComm.ExecuteReader
' Invoke the convertor object and create our empty datatable
Dim dtConverter As New DRToTableConverter
Dim dt As New DataTable
' Fill from the datareader
dtConverter.FillFromReader(dt, dr)
dtConverter = Nothing
' Set the source of the combobox
With Me.ComboBox1
.DataSource = dr
.ValueMember = "MyValueField"
.DisplayMember = "MyDisplayField"
End
With' Cleanup
HTH - M