Out of range error on SqlDataReader

Question

I'm using a SqlDataReader and get this exception when trying to read a column...

System.IndexOutOfRangeException: record

Here is the code...

SqlCommand select = new SqlCommand("SELECT RTRIM(LTRIM(PART_NO)) AS PART_NO, record AS CUSTOMER_NO FROM [RMAData].[dbo].[IMPORTING_ORDER_EDI] WHERE sessionID = '" + Session.SessionID + "'", connection);
SqlDataReader reader = select.ExecuteReader();

if (reader.HasRows)
{
   while (reader.Read())
   {
       lblWebMasterMessage.Text += "record " + reader["record"].ToString() + "<br />";
...

If I change the lblWebMasterMessage.Text to the following it works just fine...

lblWebMasterMessage.Text += "record " + reader["PART_NO"].ToString() + "<br />";

The difference between record and PART_NO in the SQL Server table is that 'record' is a primary key and an int, PART_NO is a varchar(100).

The trouble is, I need the 'record' to identify the record to update it later...

I really can't see why it can return one field and not the other?

Stuart I would recommend that you read up on SQL Aliasing when ever you use the AS key word that's the field that you need to reference in your results set not the actual name that's in the Database itself — MethodMan
– MethodMan, Commented Feb 21, 2013 at 15:07
i know about sql aliasing... this was a daft mistake that i just couldn't see. woods and trees and all that!! — Stuart
– Stuart, Commented Feb 21, 2013 at 15:11
Not a problem Stuart I guess I could see that.. I was just offering an opinion no harm no foul — MethodMan
– MethodMan, Commented Feb 21, 2013 at 15:12

user447356 · Accepted Answer · 2013-02-21 15:04:50Z

11

That's because you have no field named "record", you aliased it to "CUSTOMER_NO" so change the code to:

lblWebMasterMessage.Text += "record " + reader["CUSTOMER_NO"].ToString() + "<br />";

That said, you can also use index instead of name so to read the second column:

lblWebMasterMessage.Text += "record " + reader[1] + "<br />";

answered Feb 21, 2013 at 15:04

user447356

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

4 Comments

Michael Minton Over a year ago

I'd warn against using the index to reference columns because changes to the SQL Statement can result in unexpected behavior if you forget to change the index.

user447356 Over a year ago

@Michael true but on the other hand, changing the alias name will lead to the error our OP here faced when using name to reference the column.

Stuart Over a year ago

ahh.... i am sorry! i didn't see that!! why did i do that?! i wish people at work would leave me alone so i can concentrate properly and stop wasting peoples time with my daft mistakes!! THANK YOU!! :)

MethodMan Over a year ago

Michael you are close that would only pertain if you were to reference the Column based on the reader[1].ToString() for example because the column lengths may change but if you are doing it by Column Nae you would never find this to be an issue

user1326628 · Accepted Answer · 2016-10-27 06:08:52Z

2

In my case this error was because I was accidentally executing two select statements in my command text. I had two separate output results and the first output didn't contain the column name I was reading and hence the error.

answered Oct 27, 2016 at 6:08

user1326628

Collectives™ on Stack Overflow

Out of range error on SqlDataReader

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related