SqlDataReader Problem

  • Thread starter Thread starter Cameron Frasnelly
  • Start date Start date
C

Cameron Frasnelly

I emulated the code from the .Net Framework help (Titled "Using Stored
Procedures with a Command") and I still receive and error...

Error Received = "Invalid attempt to read when no data is present."

BUT when I manually run the Stored Procedure and manually type in '3333' it
works just fine by returning the single row for that particular work
order????????

*****More Clues****
- Returning the column name via .GetName works fine
- I am attempting to do this within the class (I don't see why this would be
an issue)
- I have been working on this one single problem for MORE THAN THREE FRI@#%$
HOURS! :) Just wanted to vent...

*****CODE BELOW*******
'Setup the connection

Dim connObj As New SqlConnection(OpsDB_Module.ConnectionString())

'Setup the command to execute

Dim cmdObj As SqlCommand = New SqlCommand("spWorkOrderFull_byKEY", connObj)

cmdObj.CommandType = CommandType.StoredProcedure

'----add parameter to stored procedure

Dim myParm As SqlParameter = cmdObj.Parameters.Add("@WorkOrderKEY",
SqlDbType.Int)

myParm.Value = 3333

'Open the connection

connObj.Open()

'Grab the data into a reader object

Dim sqlDR As SqlDataReader = cmdObj.ExecuteReader()

'Assign returned values to class properties

Dim tmpString As String = sqlDR.GetName(0) '!!! This Works Just Fine and
returns the column name!!!

Me._WorkOrderKEY = sqlDR.GetInt16(0)

Me._Problem = sqlDR.GetString(sqlDR.GetOrdinal("Problem"))

Me._Priority = sqlDR.GetInt16(sqlDR.GetOrdinal("Priority"))
 
You didn't issue a SqlDataReader.Read to move to the first row.

Dim sqlDR As SqlDataReader = cmdObj.ExecuteReader()
'Assign returned values to class properties
Dim tmpString As String = sqlDR.GetName(0)
sqlDR.Read()
Me._WorkOrderKEY = sqlDR.GetInt16(0)


Also make sure you have
set rowcount off
at the beginning of your sp.

David
 
Don't worry, that's not a big deal--it's progress. Is it
on the DataReader.Read() line or is it afterward. If it's
on that line, can you show me what else is on it.


If it's below, I'm guessing taht _WorkOrderID or _Priority
is a different type than what's in the reader.

What are the first three fields in your Proc?
 
Thanks a million for your time...

it's thrown on the sqlDR.Read() line..

the first three are fields returned in the SP are:

WorkOrderKEY, FKEYProject, Priority

each of which are int's.

Thanks!
 
I lied it's thrown on the
Me._WorkOrderKEY = sqlDR.GetValue(0)

I changed it a bit to see if that would help!
 
I'm sorry more info I keep leaving out...

it is giving me : sqlDR.GetValue(0) Run-time exception thrown :
System.InvalidOperationException - Invalid attempt to read when no data is
present.
 
So _WorkOrderKey and WorkOrderKey (DB) are both Int values?

Just to get sure, try using .GetType.ToString on the
sqlDR.GetValue[0] and see what happens

Also, try commenting out this line and see if everything
else is cool. We are casting something invalidly from
that exception and I'm guessing it's a type mismatch.
Another thing, for future reference to watch out for is
nulls...but we'll deal with that later, let's get this
fixed for you now. We're close!
 
Make sure that its .HasRows property is True.

Try a while dr.Read() and see what happens then. I'll be in early tomorrow,
so let me know.

Bill
 
Back
Top