1

I'm playing around with SQlite and sql commands. I'm trying to make a quizz program and I have a loop, that reads the questions and answers from my database and adds them to a list. I also have a bool that defines if the answer picked from the database is right or wrong.

My problem is that all this works fine the first time my loop executes the code and adds the true and false to my array of bools, but the 2'nd time my loop executes it throws the exception: SPECIFIED CAST NOT VALID. The method that fails looks like this: I made a comment where the code fails:

    public void GetQuestion(int categoryRef)
    {
        Console.Clear();

        int arrayIndex = 0;

        int qListIndex = 0;

        int idListIndex = 0;

        List<string> qList = new List<string>();

        List<int> idList = new List<int>();

        int ansNr = 1;

        bool[] isTrue = new bool[3];


        SQLiteDataReader sqReader;

        SQLiteCommand sqCommand = new SQLiteCommand(sqConnection);

        try
        {
            sqCommand.CommandText = "SELECT Question, ID FROM Questions WHERE CategoryRef=" + categoryRef.ToString();
            sqCommand.Connection.Open();
            sqReader = sqCommand.ExecuteReader();
            foreach (var item in sqReader)
            {
                qList.Add(sqReader.GetString(0));
                idList.Add(sqReader.GetInt32(1));
            }
            sqReader.Close();
        }
        finally
        {
            sqConnection.Close();    
        }

        for (int i = 0; i < qList.Count; i++)
        {   
            try
            {
                sqCommand.CommandText = "SELECT Answer FROM Answers WHERE QuestionRef=" + idList[idListIndex].ToString();
                sqConnection.Open();
                sqReader = sqCommand.ExecuteReader();
                Console.WriteLine(qList[qListIndex]);
                foreach (var answer in sqReader)
                {
                    Console.WriteLine(ansNr + ":" + sqReader.GetString(0));

                    ansNr++;
                }
                sqReader.Close();
            }
            finally
            {
                sqConnection.Close();
            }

            try
            {   
                //THIS CODE FAILS 2'nd TIME IT LOOPS THROUGH
                sqCommand.CommandText = "SELECT IsTrue FROM Answers WHERE QuestionRef=" + idList[idListIndex].ToString();
                sqConnection.Open();
                sqReader = sqCommand.ExecuteReader();
                foreach (var item in sqReader)
                {

                    isTrue[arrayIndex] = sqReader.GetBoolean(0); //<-- Specified cast is not valid.
                    arrayIndex++;


                }
                sqReader.Close();
            }
            finally
            {

                sqConnection.Close();
            }

            string input = Console.ReadLine();
            int number = Convert.ToInt32(input);

            switch (number)
            {
                case 1:
                    if (isTrue[0] == true)
                    {
                        Console.WriteLine("Correct");
                    }
                    if (isTrue[0] == false)
                    {
                        Console.WriteLine("False");
                    }
                    break;

                case 2:
                    if (isTrue[1] == true)
                    {
                        Console.WriteLine("Correct");
                    }
                    if (isTrue[1] == false)
                    {
                        Console.WriteLine("False");
                    }
                    break;

                case 3:
                    if (isTrue[2] == true)
                    {
                        Console.WriteLine("Correct");
                    }
                    if (isTrue[2] == false)
                    {
                        Console.WriteLine("False");
                    }
                    break;
            }
            Console.ReadLine();
            idListIndex++;
            qListIndex++;
            arrayIndex = 0;
            ansNr = 1;
        }
    }
Improve this question

4 Answers 4

Reset to default
6

Mostly likely, that you read DbNull from database, which cannot be cast to bool.

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

1 Comment

This is so helpful. I had FALSE in my BOOL field and that wasn't understood by Entity Framework. Once I changed it to an integer value (0 for false) then things started working properly.
1

I was getting the same error because SQLite was returning a NULL.

Check for null: if (!sqlite_datareader.IsDBNull(4)) {// then do what is required}

Improve this answer

Comments

0

SQLite does not guarantee that the contents of any column will match the cast.
The GetBoolean is failing. You have 3 choices.

  1. Try Catch
  2. Test for Null

  3. Replace your select with a query guaranteed to return true/false

    SQLite version 3.7.8 2011-09-19 14:49:19
    sqlite> CREATE TABLE Q (A);
    sqlite> INSERT INTO Q VALUES (0);
    sqlite> INSERT INTO Q VALUES (1);
    sqlite> INSERT INTO Q VALUES ('T');
    sqlite> INSERT INTO Q VALUES ('F');
    sqlite> INSERT INTO Q VALUES ('Y');
    sqlite> INSERT INTO Q VALUES (NULL);
    sqlite> SELECT not ifnull(A==0 OR A=='F' OR A=='N',1) FROM Q;
    0
    1
    1
    0
    1
    0

Improve this answer

Comments

0

In my case, EF was throwing the same error ("Specified cast not valid") because it couldnt resolve "1234" that SQLite was throwing at it.

SQLite type-affinity The solution was to change the type of Name field from STRING to TEXT.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.