Results 1 to 3 of 3

Thread: [RESOLVED] Cast MySQL boolean / TTinyint(1) to dot net standard boolean (Invalid cast exception

  1. #1

    Thread Starter
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,205

    Resolved [RESOLVED] Cast MySQL boolean / TTinyint(1) to dot net standard boolean (Invalid cast exception

    here is the MySQL query
    Code:
    SELECT TableName.Filed IS NOT NULL AS Cancelled FROM TableName
    the MySQL query returns 0 or 1
    Now while i fetching the Data table value, i am getting invalid cast exception from Boolean to Int32

    vb.net Code:
    1. var x = Dr.Field<Boolean>("Cancelled");
    the Col Type is displaying as Int32, Why not boolean
    vb.net Code:
    1. var ColType = Dr.Table.Columns[FieldName].DataType.Name.ToString();
    if i start converting the Int32 to Boolean then i will loose the power of Boolean.
    why it happens
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,895

    Re: Cast MySQL boolean / TTinyint(1) to dot net standard boolean (Invalid cast excep

    The whole point of a cast is that you change the way you access an object. not the object itself. You cannot cast something as a type that it is not. In order to cast as type Boolean, you have to already have an actual Boolean, e.g.
    vb.net Code:
    1. object o = true;
    2. var b = (bool) o;
    If what you have is an Int32 or some other integral type than that is the only type you can cast it as. If you want a different type then you have no choice but to convert, not cast.

    One option might be to define your own method that will use Field internally to get the value as the current type and then convert it to the type you want, e.g.
    csharp Code:
    1. public static class DataRowExtensions
    2. {
    3.     public static TFinal Field<TFinal, TCurrent>(this DataRow source, string columnName)
    4.     {
    5.         var currentValue = source.Field<TCurrent>(columnName);
    6.         var finalValue = (TFinal) Convert.ChangeType(currentValue, typeof(TFinal));
    7.  
    8.         return finalValue;
    9.     }
    10. }
    You could then change your code to this:
    csharp Code:
    1. var x = Dr.Field<bool, int>("Cancelled");
    As for why you don't get Boolean values in C#, that's because the data is not Boolean. It's numeric. In MySql, BOOL and BOOLEAN are just aliases for TINYINT(1) and that data type can hold any value from 0 to 9. It is used to store Boolean data but it is not restricted to doing so. You need to account for that fact in your application.

    Apparently, MySQL has plans for implementing a genuine Boolean data type but it hasn't happened yet.

  3. #3

    Thread Starter
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,205

    Re: Cast MySQL boolean / TTinyint(1) to dot net standard boolean (Invalid cast excep

    If what you have is an Int32 or some other integral type than that is the only type you can cast it as. If you want a different type then you have no choice but to convert, not cast.
    Thanks again sir
    i got it right,
    Code:
    /*Create a test table in MySQL server 
      to Check what the field type of the Boolean out put is*/
    DROP TABLE IF EXISTS test ;
    
     CREATE TABLE test AS
     SELECT TableName.Filed IS NOT NULL AS Cancelled FROM TableName
     ;
     
     /* Terrible *****
     */
    It's Terrible The data type of the field Cancelled in table is INT(11).

    there fore now i created a function to convert the query to TINYINT(1)
    Code:
    CREATE DEFINER=`root`@`localhost` FUNCTION `Fn_ConvertToBoolean`(IP_Int int(11)) RETURNS tinyint(1)
    BEGIN
        #ConvertsM Int to boolean
        
        DECLARE TrueVal TINYINT(1) ;
        
        IF IP_Int <= 0 || IP_int IS NULL then
        SET TrueVal = 0 ; -- Return false TINYINT(1) = 0
        ELSE
        SET TrueVal = 1 ;
        end if;
        
        RETURN TrueVal ;
        
    END
    Now
    Code:
    SELECT Fn_ConvertToBoolean(TableName.Filed IS NOT NULL) AS Cancelled FROM TableName
     ;
    the Table field type of column Cancelled is TINYINT(1)

    Thanks for the guidance
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width