SqlDataReader not recognizing column ordinal values

Question

I'm trying to access a SQL database through a WCF service for a Silverlight application, and for some reason my SqlDataReader object keeps giving me errors. I'm only attempting to retrieve one single value from the database.

This is my code right now:

using(SqlConnection oConnection = new SqlConnection(ConnectionString))
{
   SqlCommand oCommand = new SqlCommand("SELECT SystemVersNo FROM dbo.CoPolicyPointer WHERE SystemAssignId = '58859700000000'");

   CompositeType oCustomer = new CompositeType();

   SqlDataReader oReader = null;
   oConnection.Open();
   oCommand.Connection = oConnection;
   oReader = oCommand.ExecuteReader();

   if (oReader.HasRows)
   {
      oCustomer.SRVNo = oReader.GetValue(0).ToString();
   }
   else
   {
      oCustomer.SRVNo = "No rows returned.";
   }

   oConnection.Close();

   return oCustomer;
}

oReader.HasRows returns true, but oReader.GetValue(0) (or oReader.GetAnythingElse, for that matter) throws an error:

System.InvalidOperationException: Invalid attempt to read when no data is present.

The thing that's really stumping me though, is that if I call oReader.GetOrdinal("SystemVersNo"), or oReader.GetName(0), they both return with the right values. How is it that the ordinal position returns with zero, but it can't find a value to read at that position? If I run the same query in SQL Server Management Studio, the value comes right up no problem, so I know it's there.

I'm brand new to this all, am I missing something fundamental? Or is there something funky going on.

Thanks!

mlorbetske · Accepted Answer · 2013-06-26 18:13:12Z

5

The problem is that you haven't moved to the first record in the set at that time.

if (oReader.HasRows)
{
    oCustomer.SRVNo = oReader.GetValue(0).ToString();
}

Should be

if (oReader.Read())
{
    oCustomer.SRVNo = oReader.GetValue(0).ToString();
}

To assert that there is at least one record to read and, at the same time, move to that record to get the data from.

answered Jun 26, 2013 at 18:13

mlorbetske

5,6592 gold badges32 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kyle Breton Over a year ago

Ahh I see, I didn't realize the Read() method actually moved the reader forward rather than just return whether or not it could, which I didn't think was necessary since I don't need a loop. Thanks!

kostas ch. · Accepted Answer · 2013-06-26 18:10:56Z

1

You have forgotten

while(oReader.Read())
{ // do stuff}

answered Jun 26, 2013 at 18:10

kostas ch.

2,0451 gold badge18 silver badges32 bronze badges

Collectives™ on Stack Overflow

SqlDataReader not recognizing column ordinal values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related