none
Specified cast is not valid

    Question

  • I'm trying to get data from SQLite database, but I'm getting a Specified cast is not valid exception.

    Here is the table creation:

    CREATE TABLE ViewList (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, parentID INTEGER, child BOOL, imageIndex INTEGER)

    I am 100% there is data in the table.

    The children arg is true.

            public List<TaskContainer> getContainerList(bool children) 
            { 
                SQLiteCommand command = _connection.CreateCommand(); 
     
                if(children) 
                    command.CommandText = "SELECT * FROM ViewList"
                else 
                    command.CommandText = "SELECT * FROM ViewList WHERE child=0"
                 
                SQLiteDataReader reader = command.ExecuteReader(); 
     
                List<TaskContainer> list = new List<TaskContainer>(); 
     
                while (reader.Read()) 
                { 
                    //**The line below has the error**
                    TaskContainer item = new TaskContainer(reader.GetString(1), reader.GetInt32(2), reader.GetInt32(0), reader.GetBoolean(3), reader.GetInt32(4)); 
                    list.Add(item); 
                } 
     
                return list; 
            } 



    System.InvalidCastException was unhandled
      Message="Specified cast is not valid."
      Source="System.Data.SQLite"
      StackTrace:
           at System.Data.SQLite.SQLiteDataReader.VerifyType(Int32 i, DbType typ)
           at System.Data.SQLite.SQLiteDataReader.GetInt32(Int32 i)
           at GED.TaskManager.getContainerList(Boolean children) in C:\Documents and Settings\ryanc\My Documents\Visual Studio 2008\Projects\GED\GED\TaskManager.cs:line 142
           at GED.frmMain.frmMain_Load(Object sender, EventArgs e) in C:\Documents and Settings\ryanc\My Documents\Visual Studio 2008\Projects\GED\GED\frmMain.cs:line 26
           at System.Windows.Forms.Form.OnLoad(EventArgs e)
           at System.Windows.Forms.Form.OnCreateControl()
           at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
           at System.Windows.Forms.Control.CreateControl()
           at System.Windows.Forms.Control.WmShowWindow(Message& m)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
           at System.Windows.Forms.ContainerControl.WndProc(Message& m)
           at System.Windows.Forms.Form.WmShowWindow(Message& m)
           at System.Windows.Forms.Form.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
           at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
           at System.Windows.Forms.Control.set_Visible(Boolean value)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.Run(Form mainForm)
           at GED.Program.Main() in C:\Documents and Settings\ryanc\My Documents\Visual Studio 2008\Projects\GED\GED\Program.cs:line 18
           at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException:

    Tuesday, July 01, 2008 12:37 AM

Answers

  • It's possible you're getting that exception because one of your columns in the current row being evaluated has a DBNull value.

    Try setting a breakpoint on that line then in debug mode watch (add each column to your watch window, view your locals etc.) the values of each column. Are any coming up as DBNull? You'll probably have to come up with a few checks to call on each of those columns where you use GetBoolean, GetInt32 etc. before using their values by using IsDBNull then return the value if false, otherwise return some value if true.

    Roughly something such as:

    if (reader.IsDBNull(1)) 
        return ""
    else 
        return reader.GetString(1); 


    Document my code? Why do you think it's called "code"?
    Tuesday, July 01, 2008 1:12 AM

All replies

  • It's possible you're getting that exception because one of your columns in the current row being evaluated has a DBNull value.

    Try setting a breakpoint on that line then in debug mode watch (add each column to your watch window, view your locals etc.) the values of each column. Are any coming up as DBNull? You'll probably have to come up with a few checks to call on each of those columns where you use GetBoolean, GetInt32 etc. before using their values by using IsDBNull then return the value if false, otherwise return some value if true.

    Roughly something such as:

    if (reader.IsDBNull(1)) 
        return ""
    else 
        return reader.GetString(1); 


    Document my code? Why do you think it's called "code"?
    Tuesday, July 01, 2008 1:12 AM
  •  
    TaskContainer item = new TaskContainer(reader.GetString(1), reader.GetInt32(2), reader.GetInt32(0), reader.GetBoolean(3), reader.GetInt32(4)); 
                    list.Add(item); 

    Aside from what Ahmad said (most likely possibility) there is a chance that you messed up you columntypes a well. make sure they match.

    To avoid problems with System.DBNull(s) it is a much better idea to use SqlietDatareader in a different way.
    using ( OleDbConnection conn = new OleDbConnection ( Globals.connStr ) )  
                {  
                    conn.Open ( );  
                    OleDbCommand cmdm = new OleDbCommand ( );  
                    cmdm.Connection = conn;  
                    cmdm.CommandType = CommandType.Text;  
                    cmdm.CommandText = "SELECT * FROM sessionLog ";  
                    using ( OleDbDataReader rdr = cmdm.ExecuteReader ( CommandBehavior.CloseConnection ) )  
                    {  
                        if ( rdr.HasRows == true )  
                        {  
                            foreach ( System.Data.Common.DbDataRecord row in rdr )  
                            {  
                                if ( row[ 0 ] != System.DBNull.Value )  
                                {  
                                    acc = ( int )row[ 0 ];  
                                }  
                                if ( row[ 1 ] != System.DBNull.Value )  
                                {  
                                    cat = ( int )row[ 1 ];  
                                }  
                                if ( row[ 2 ] != System.DBNull.Value )  
                                {  
                                    cheb = ( int )row[ 2 ];  
                                }  
                                if ( row[ 3 ] != System.DBNull.Value )  
                                {  
                                    comp = ( int )row[ 3 ];  
                                }  
                                if ( row[ 4 ] != System.DBNull.Value )  
                                {  
                                    diaries = ( int )row[ 4 ];  
                                }  
                                if ( row[ 5 ] != System.DBNull.Value )  
                                {  
                                    emails = ( int )row[ 5 ];  
                                }  
                                if ( row[ 6 ] != System.DBNull.Value )  
                                {  
                                    files = ( int )row[ 6 ];  
                                }  
                                if ( row[ 7 ] != System.DBNull.Value )  
                                {  
                                    notes = ( int )row[ 7 ];  
                                }  
                                if ( row[ 8 ] != System.DBNull.Value )  
                                {  
                                    paymt = ( int )row[ 8 ];  
                                }  
                                if ( row[ 9 ] != System.DBNull.Value )  
                                {  
                                    pers = ( int )row[ 9 ];  
                                }  
                                if ( row[ 10 ] != System.DBNull.Value )  
                                {  
                                    phones = ( int )row[ 10 ];  
                                }  
                                if ( row[ 11 ] != System.DBNull.Value )  
                                {  
                                    products = ( int )row[ 11 ];  
                                }  
                                if ( row[ 12 ] != System.DBNull.Value )  
                                {  
                                    urls = ( int )row[ 12 ];  
                                }  
                                if ( row[ 13 ] != System.DBNull.Value )  
                                {  
                                    dated = ( DateTime )row[ 13 ];  
                                }  
                                if ( row[ 14 ] != System.DBNull.Value )  
                                {  
                                    dateTimed = ( DateTime )row[ 14 ];  
                                }  
                                break;  
                            } 
    }
    Of course I had to give all those fields highlighted in red some default values which are not posted.

    Using 2 nested using statements assures me that the Reader will be closed and the connection will be shut down as soon as thelast record is read.
    AlexB
    Tuesday, July 01, 2008 1:24 AM