OT? Why is my SafeDataReader empty?

Q Johnson posted on Tuesday, August 28, 2007

My parent ERO is populating children in its DP_Fetch.  Rather than building a sProc to generate multiple result sets, I'm running a sProc to populate Parent's properties, closing its SafeDataReader, and then using the same command object to populate another SafeDataReader(SDR).

The ERO's properties get populated normally.  But the second SDR is alway empty.  Yet when I stop the code both before and after the call to the sqlcommand object's Execute method, I can execute the sProc in Query Analyzer (using the same value for the parameter being supplied in my code) and it executes perfectly, generating a well-populated row of data.  But it leaves the SDR empty in the ERO's fetch, so that when I pass the empty dr to the child object (not a collection), the child's Fetch event bombs when it tries to read the data that isn't there.  It might as well bomb.  There should ALWAYS be data present.  The fact that a parameter value was available means that there should be child info.

Also please note, there is no run time error (e.g. no warning that there is already a datareader open or such)

FYI, the relevant code looks like the following (lots is missing of course):

with cmd
  .commandtext = "spGetWarrant"
  .parameters.add("@WarrantID", crit.WarrantID)
  dim dr as New SafeDataReader(.ExecuteReader)
  WarrantID = crit.WarrantID
  <otherProps = dr.Get<type>(index)>
  .... blah blah to populate properties
  dr.Close

  .commandtext = "spGetEmployee"
  .parameters.clear
  .parameters.add("@WarrantID", crit.WarrantID)
  dim drEmp as New SafeDataReader(.ExecuteReader)

  <drEmp is empty>
end with

Why??? The Emp sProc is over 30 lines of SQL code joining three tables, and I'd really prefer not to pack it into the sProc with all the other SQL just to use the .NextResultset technique with a single SafeDataReader if I don't have to. 

Thanks for any help you can offer.

Q

skagen00 replied on Tuesday, August 28, 2007

This may be an obvious suggestion but I've had this simple piece of code trip me up before...

Are you doing a dr.Read before trying to get data from the SafeDataReader?

Chris

Q Johnson replied on Tuesday, August 28, 2007

Yes, I guess it was obvious... and yes it was my mistake.  I'm an idiot, but a happy idiot !!!!  Thanks.

I'm so used to only having to use the Read command once (because I'm so used to using only one data reader), that I just skipped this. 

Thanks so much!  Back to productive work on that project again.

skagen00 replied on Tuesday, August 28, 2007

This little bug has caused me problems and wasted time before, so I'm glad I could help.