locked
datareader threw an exception of type 'system.indexoutofrangeexception' RRS feed

  • 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? 

    Tuesday, July 14, 2020 11:00 AM

All replies

  • 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

    Tuesday, July 14, 2020 11:35 AM
  • 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.

    Tuesday, July 14, 2020 4:38 PM
  • 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

    Wednesday, July 15, 2020 6:53 AM