Populating a DataTable from a DataReader

[gregburningham]

Please can anyone help ?

I am trying to populate a DataTable from a DataReader and not having much luck .....

 

[gregburningham]

I am using the following code by the way:

Public Function GetItemPermissions(ByVal oraConnectionStr As String, ByVal sqlStr As String) As DataTable

Dim DBException As Exception
Dim DBCommand As New OleDb.OleDbCommand()
Dim oraConnection As New OleDb.OleDbConnection()
Dim DBDataReader As OleDb.OleDbDataReader

'Create the DataTable
Dim vDataTable As New DataTable()
Dim vDataRow As DataRow
'Create the columns
Dim vItemName As New DataColumn("ItemName", GetType(String))
Dim vUpdateAllowed As New DataColumn("UpdateAllowed", GetType(String))
Dim myCommand As New OleDb.OleDbCommand(sqlStr, oraConnection)

'Add the columns to the DataTable's Columns collection
vDataTable.Columns.Add(vItemName)
vDataTable.Columns.Add(vUpdateAllowed)

Try
'Get the Oracle connection string.
oraConnection.ConnectionString = oraConnectionStr
DBCommand.Connection = oraConnection

'Open the Oracle connection.
oraConnection.Open()

'Build the Status LOV from a SQL statement.
DBCommand.CommandText = sqlStr
DBCommand.CommandType = CommandType.Text


DBDataReader = DBCommand.ExecuteReader()

While DBDataReader.Read

' add rows from query

vDataRow = vDataTable.NewRow()
vDataRow("ItemName&quot = DBDataReader.Item("IPE_ITEM_NAME&quot
vDataRow("UpdateAllowed&quot = DBDataReader.Item("IPE_UPD_ALLOWED&quot
vDataTable.Rows.Add(vDataRow)

End While

DBDataReader.Close()

Return vDataTable

Catch DBException
exceptionMessageStr = DBException.Message.ToString()

Finally
'Close the Oracle connection.
oraConnection.Close()
End Try


End Function

 

[Custom24]

The normal way to fill a datatable is to use the fill method of a data adapter, which you just set the select command for.

If you need to change the field names in the data table, as you have done in your example, then I think there is a mappings property or something.

I don't see anything wrong with the code you have posted, though. You need to be a bit more specific than "not having much luck"

Mark

 

[gregburningham]

Thanks for the advice - I'll try it -

Just to clarify - this previous code chunk didn't populate any of the rows in the DataTable even though there were rows retrieved in the query ....

 

[gregburningham]

Thanks Custom24

I have resolved it - you were right - The code that i posted did work OK .....

When I loop through the DataTable to read the data back I had <= instead of < and I was getting an error but as you can see I have amended the offending line !


While vRowCount < vDataTable.Rows.Count
vDataRow = vDataTable.Rows(vRowCount)
lblMessage.Text = lblMessage.Text & vDataRow(&quot;UpdateAllowed&quot
If vDataRow(&quot;UpdateAllowed&quot.ToString = &quot;Y&quot; Then
lblMessage.Text = lblMessage.Text & &quot;UPD!&quot;
If vDataRow(&quot;ItemName&quot.ToString = ManagerDropDownList.ID Then
ManagerDropDownList.Enabled = True
End If
End If
vRowCount = vRowCount + 1
End While