How to fix System.InvalidCastException on DataReader.GetString()

Question

Problem is that: I run this code (CODE 1), and thne gain System.InvalidCastException in browser (ASP.NET). The very same code (in my case I use this function from my another project), running in another project and working with the same data works fine and doesn't throw any exceptions.

I tried to catch this exception and look inside of problem object (CODE 2), but then I was surprised that no exceptions were caught (I placed a break point on second line of a catch code block to be sure) and I got right output in browser

CODE 1

private static List<Dictionary<string, object>> GetResultRows(SQLiteDataReader Reader)
            {
                List<Dictionary<string, object>> Result = new List<Dictionary<string, object>>();

                if (!Reader.HasRows) return Result;

                while (Reader.Read())
                {
                    var CurrentRow = new Dictionary<string, object>();
                    Result.Add(CurrentRow);
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        CurrentRow.Add(Reader.GetName(i), Reader.GetString(i));
                    }
                }

                return Result;
            }

CODE 2

private static List<Dictionary<string, object>> GetResultRows(SQLiteDataReader Reader)
            {
                List<Dictionary<string, object>> Result = new List<Dictionary<string, object>>();

                if (!Reader.HasRows) return Result;

                while (Reader.Read())
                {
                    var CurrentRow = new Dictionary<string, object>();
                    Result.Add(CurrentRow);
                    for (int i = 0; i < Reader.FieldCount; i++)
                    {
                        //CurrentRow.Add(Reader.GetName(i), Reader.GetString(i));
                        try
                        {
                            CurrentRow.Add(Reader.GetName(i), Reader.GetString(i));
                        }
                        catch
                        {
                            var temp = Reader.GetValue(i);
                            System.Diagnostics.Debug.WriteLine($"{Reader.GetName(i)} = {Reader.GetValue(i)}");
                        }
                    }
                }

                return Result;
            }

I want this function to be as quick as it can be, so try-catch can't solve my problem. Any ideas?

UPD: Browser exception: image

You should include the exception in your question to make it easier to find the problem. Also, change `catch` to `catch(Exception e)` and see which type of exception it is. — Shehab Ellithy, Aug 14 '19 at 01:12
@CPerkins, you see, I don't get any exceptions in case of try-catch statement. I changed catch to catch(Exception e) according to your advice, but nothing happened — Денис Саенко, Aug 14 '19 at 02:07

score 0 · Answer 1 · answered Aug 14 '19 at 03:03

0

The basic answer I think is that Reader.GetString(i) will throw an exception if the value is null, so you have to use IsDBNull to check. For more information, see here:

SQL Data Reader - handling Null column values

answered Aug 14 '19 at 03:03

MPost

535
2
7

Are there any ways to clear result set from "null" values using sqlite? I think to change default value for every single column in my database, but maybe I can do it in query – Денис Саенко Aug 14 '19 at 11:57
I did IsDBNull check, exception when IsDBNull is false – Денис Саенко Aug 14 '19 at 13:04
Change GetString() to GetObject().ToString() and it will work regardless of the data type, as long as you verified first that the value isn't DBNull. – MPost Aug 14 '19 at 15:35
I did, but these are not the same things. For example date storing in next format "yyyy-MM-dd", when date.ToString() returns different value. It would be ok, if only one table controller in my program use this method, but many controllers use it – Денис Саенко Aug 16 '19 at 13:58

Денис Саенко · Accepted Answer · 2019-08-18T18:53:26.660

0

Reader.GetString() is not the same thing as object.ToString(). image

UPD: From comment to C Perkins comment: "It appears because of SQLiteDataReader checking the column data type of returned from db file data set. For example: if column data type is integer and we run GetString() method, SQLiteDataReader throw exception."

edited Aug 18 '19 at 18:53

answered Aug 14 '19 at 13:11

Денис Саенко

11
4

@CPerkins null values are not the reason of exception. If you look on question again, you will see, that exception appears on SQLiteDataReader attempt to execute GetString() method. It appears because of SQLiteDataReader checking the column data type of returned from db file data set. For example: if column data type is integer and we run GetString() method, SQLiteDataReader throw exception. You can see it on image, I've pinned to my answer. The explanation why the error occurred is shown on image. I found it's informative enougth, but ok, I will update it – Денис Саенко Aug 18 '19 at 18:48
The edited answer is much more useful and helps to interpret the image. Sometimes an image is useful, but the image you shared is very busy and focus is drawn to the exception details. The small dark-grey popup showing a single-digit integer is very small and subtle and was easily overlooked. In the end, the image is unnecessary if the answer explains the details well enough. – C Perkins Aug 18 '19 at 19:11
@CPerkins your right, sorry, I was hurrying writing that answer – Денис Саенко Aug 18 '19 at 19:36

How to fix System.InvalidCastException on DataReader.GetString()

2 Answers2