datareader threw an exception of type 'system.indexoutofrangeexception'

Question

User739135361 posted

Hi,

I have a ado.net code which was fetching user details from the db and displaying. It was working fine. Now its decided to enrypt them. Once the encryption is done, I get an error 

datareader threw an exception of type 'system.indexoutofrangeexception

at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)

at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)

at System.Data.SqlClient.SqlDataReader.get_Item(String name)

Note: No sp change, No datatype changes, just that we are getting the encrypted values, decrypting it and sending to the ui. 

Initiall trouble shooting says that the colum dosent exists, however, there is no difference in column names.  FirstName, LastName ,Email ,etc are the column names. 

user = new User();
user.FirstName = Decrypt(dr["FirstName"].ToString());
user.LastName = Decrypt(dr["LastName"].ToString());
user.Email = Decrypt(dr["Email"].ToString());

Further searches suggest that number of columns returned by sp should be equal to properties in model. Infac, the columns returned are less than that in the model. Inspite of all that, I could not resolve the issue. Any suggestions? 

Answer 1

User475983607 posted

Any suggestions? 

I recommend basic debugging.  Place a break point and use the "Locals" or "Watch" window to drill into dr["FirstName'] to see if it has a value.  Use the "Immediate" window to execute code like GetOrdinal() to see if the column exists.

?dr.GetOrdinal("FirstName")

You can also test different indexes to see what happens.  I'm not sure what index "FirstName" but you should get the idea. 

dr.GetString(0);

IMHO, dr["FirstName'] is a poor approach because dr collection returns an object.  You are forcing the an implicit cast which can be a problem in a strongly typed language.  The recommended programming pattern found in the documentation is using the ordinal to get a specific type like GetInt32(0) or GetString(1).  

Lastly, SQL server has an encryption feature which might be a better option than creating a custom C# solution; https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-encryption?view=sql-server-ver15

Answer 2

User739135361 posted

type like GetInt32(0) or GetString(1).  

For this I get the same exception. The output from SP  is as as show below

UserID	FirstName	LastName	Email
1	UIgVZkkykzpD+MWwS5c9ntj2	31iSr4r7//QLMEJpWuz	yORD4g3I1JJiACNbiDWnFK1iw48Bd

Sometime if I copy past  the column name from the colum above in dr["FirstName"].ToString(), it works though technically its one and the same. Later again this issue aries.

Answer 3

User1535942433 posted

Hi N1ZAM,

Accroding to your descriprion,as far as I think,You could   check your select statement. Somehow column names are not what you expected. Use FieldCount to check number of columns in datareader. Use GetName method to check real name of column with specified index in watch window.

Another may is your other codes in the same process that is not safe can corrupt the state of SqlConnections in the pool. 

More details,you could refer to below articles:

https://forums.asp.net/t/744673.aspx?data+reader+System+IndexOutOfRangeException

https://stackoverflow.com/questions/2590893/nhibernate-fieldnamelookup-throws-indexoutofrangeexception

Best regards,

Yijing Sun