Last post Jul 15, 2020 06:53 AM by yij sun
Jul 14, 2020 11:00 AM|N1ZAM|LINK
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?
Jul 14, 2020 11:35 AM|mgebhard|LINK
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.
You can also test different indexes to see what happens. I'm not sure what index "FirstName" but you should get the idea.
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
Jul 14, 2020 04:38 PM|N1ZAM|LINK
type like GetInt32(0) or GetString(1).
For this I get the same exception. The output from SP is as as show below
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.
Jul 15, 2020 06:53 AM|yij sun|LINK
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: