datareader questions

[William]

Can someone please explain this to me. I am not understanding why this
works the way it does. The commented lines are the problems

Dim oleEmp as OleDbCommand
Dim myFinds as OleDataReader
Dim strSearch as String
Dim strFound as String

strSearch = "select * from employee where [first name]='" &
txtName.Text.ToString & "'"

oleEmp = New OleDbCommand(strSearch, oleConn)

Try

'*********************************
' Why does the next line of code not fill the datareader
'*********************************
myFinds = oleEmp.ExecuteReader

'*********************************
' in order for it to work I had to perform a read
'myFinds.Read()
'as-is the code throws up an exception
'
' No Data exists for the row/column.
'*********************************
strFound = myFinds.Item(2)
MsgBox(txtName.Text.ToString + "'s last name is " + strFound

Catch prob as Exception

MsgBox(prob.Message)

End Try

If I uncomment the myFinds.Read() line then everything works fine but I'm
just not understand why it needs to read. It seems to me that when the
myFinds = oleEmp.ExecuteReader executes the datareader should have info to
use. Like I said this is more of a curiousity than a problem.

And now for my question: Does the datareader return a count of the records
that were returned? From the above code you can see why I want to know if
there is more than one record returned.

Thanks

[Tom Shelton]

In article <Zo******************@bignews4.bellsouth.net>, William wrote:

Can someone please explain this to me. I am not understanding why this
works the way it does. The commented lines are the problems

Dim oleEmp as OleDbCommand
Dim myFinds as OleDataReader
Dim strSearch as String
Dim strFound as String

strSearch = "select * from employee where [first name]='" &
txtName.Text.ToString & "'"

oleEmp = New OleDbCommand(strSearch, oleConn)

Try

'*********************************
' Why does the next line of code not fill the datareader
'*********************************
myFinds = oleEmp.ExecuteReader

'*********************************
' in order for it to work I had to perform a read
'myFinds.Read()
'as-is the code throws up an exception
'
' No Data exists for the row/column.
'*********************************
strFound = myFinds.Item(2)
MsgBox(txtName.Text.ToString + "'s last name is " + strFound

Catch prob as Exception

MsgBox(prob.Message)

End Try

If I uncomment the myFinds.Read() line then everything works fine but I'm
just not understand why it needs to read. It seems to me that when the
myFinds = oleEmp.ExecuteReader executes the datareader should have info to
use. Like I said this is more of a curiousity than a problem.

And now for my question: Does the datareader return a count of the records
that were returned? From the above code you can see why I want to know if
there is more than one record returned.

Thanks

The reason your seeing the behavior you are is that a DataReader (class
that implements IDataReader) is a forward-only stream based object. In
other words, unlike using a DataAdpater, you are using a connected
datasource. All the ExecuteReader method does, is construct a handle to
this datasource. The usual way of using a reader is something like:

myReader = myCommand.ExecuteReader()

Do While (myReader.Read())
' do stuff with the data
Loop

This can execute 0 to n Times depending on the number of rows
returned... As for getting the row count - that is not supported by the
IDataReader interface. I suppose it would be possible for a individual
datasource to implement an extension to the actual object returned (but,
I don't believe the OleDB provider does this). But, it would not be
part of the generic interface and would be datasource dependent... The
closest thing is the RecordsAffected property - and that is the number
of rows inserted, changed, or deleted by an SQL statement. Not at all
what your looking for :)

--
Tom Shelton [MVP]

[Richard Myers]

"William" <an******@microsoft.com> wrote in message
news:Zo******************@bignews4.bellsouth.net.. .

Can someone please explain this to me. I am not understanding why this
works the way it does. The commented lines are the problems

Dim oleEmp as OleDbCommand
Dim myFinds as OleDataReader
Dim strSearch as String
Dim strFound as String

strSearch = "select * from employee where [first name]='" &
txtName.Text.ToString & "'"

oleEmp = New OleDbCommand(strSearch, oleConn)

Try

'*********************************
' Why does the next line of code not fill the datareader
'*********************************
myFinds = oleEmp.ExecuteReader

'*********************************
' in order for it to work I had to perform a read
'myFinds.Read()
'as-is the code throws up an exception
'
' No Data exists for the row/column.
'*********************************
strFound = myFinds.Item(2)
MsgBox(txtName.Text.ToString + "'s last name is " + strFound

Catch prob as Exception

MsgBox(prob.Message)

End Try

If I uncomment the myFinds.Read() line then everything works fine but I'm
just not understand why it needs to read. It seems to me that when the
myFinds = oleEmp.ExecuteReader executes the datareader should have info to use. Like I said this is more of a curiousity than a problem.

And now for my question: Does the datareader return a count of the records that were returned? From the above code you can see why I want to know if
there is more than one record returned.

As Tomn said no. If you need to determine how many records are affected by a
given SELECT statement you should try ExecuteScaler with a count statement.
If you just want to know whether the reader has any records in it you use
the statement

If dr.read then

which returns a boolean.

The reason you need to call .read is that when the datareader is returned
the forward only cursor is set to before the first record... think of it as
being parked. For returning datareaders i use the same statement over and
over which covers most of my basis

dr = myCommand.ExecuteReader(CommandBehaviour.CloseConn ection)

if dr is nothing orelse not dr.read then .... return or whatever

You should also be checking for a Null condition from the field you are
trying to read before you read it i.e

If not IsDbNull(myFinds.Item(2)) then
strFindFind = cstr(myFinds.Items(2))
End if
hth
Richard

[Greg Burns]

Don't forget that version 1.1 of .NET Framework added the HasRows property
to the datareader.

Greg

If you just want to know whether the reader has any records in it you use
the statement

If dr.read then

which returns a boolean.