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!

score 5 · Accepted Answer · answered Jun 26 '13 at 18:13

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 '13 at 18:13

mlorbetske

5,529
2
28
40

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! – Kyle Breton Jun 26 '13 at 18:27

score 1 · Answer 2 · answered Jun 26 '13 at 18:10

1

You have forgotten

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

answered Jun 26 '13 at 18:10

kostas ch.

1,960
1
17
30

SqlDataReader not recognizing column ordinal values

2 Answers2