locked
DataRow Error RRS feed

  • Question

  • User-561514082 posted

    I am getting an error in the following code; how do I fix it?  Bit1, Bit2, etc are the fields in MyTable.

    DataRow[] does not contain defination for Bit1, Bit2, Bit3, etc...

                using (SqlConnection conn = new SqlConnection(con))
                {
                    conn.Open();
                    var dt = new DataTable();
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "Select * from MyTable WHERE [ID] in ('7','8','9')";
                        cmd.CommandType = CommandType.Text;
                        using (var reader = cmd.ExecuteReader())
                        {
                            dt.Load(reader);
                        }
                    }
                    string expression;
                    expression = "ID = 27";
    
                    DataRow[] foundRows;
    
                    foundRows = dt.Select(expression);
                    lists = new List<List<bool>>
                    {
                        new List<bool>{ foundRows.Bit1, foundRows.Bit2, foundRows.Bit3, foundRows.Bit4, foundRows.Bit5, foundRows.Bit6, foundRows.Bit7 },
                    };
                }

    Thursday, July 6, 2017 1:16 PM

All replies

  • User753101303 posted

    Hi,

    foundRows is an array of rows and those rows are not strongly typed. Assuming you expect to find a single row it would be for each field :

    (bool)foundRows[0]["Bit1"]

    ie taking the first row and the column named "Bit1" and casting that to a bool.

    If you want to work in a strongly type manner you could use Entity Framework (or Linq to DataSet ?). You could also check that you found exactly one line( here you try to find ID 27 which is not selected by your initial query so you won't find any row).

    Thursday, July 6, 2017 1:44 PM
  • User-561514082 posted

    basically, I want to do the following code but having trouble creating EF.

            protected List<List<bool>> lists;
            RecordContext db = new RecordContext();
            protected void Page_Load(object sender, EventArgs e)
            {
                Record record1 = db.Records.Find(1);
                Record record2 = db.Records.Find(2);
                Record record3 = db.Records.Find(3);
    
                lists = new List<List<bool>>
                {
                    new List<bool>{ record1.Bit1, record1.Bit2, record1.Bit3, record1.Bit4,record1.Bit5, ... , record1.Bit52 },
                    new List<bool>{ record2.Bit1, record2.Bit2, record2.Bit3, record2.Bit4,record2.Bit5, ... , record2.Bit52 },
                    new List<bool>{ record3.Bit1, record3.Bit2, record3.Bit3, record3.Bit4,record3.Bit5, ... , record3.Bit52 },
                };
            }

    Thursday, July 6, 2017 1:55 PM
  • User1068175894 posted

    you have to iterate over the rows:

        foundRows = dt.Select(expression);
        var lists = new List<List<bool>>();
        for(int i = 0; i < foundRows.Length; i ++)
        {
            lists.Add( new List<bool>{foundRows[i][0],foundRows[i][1],foundRows[i][2]})
        }

    Thursday, July 6, 2017 2:02 PM
  • User-561514082 posted

    Error:  cannot convert from object to boolean:

    foundRows[i][0],foundRows[i][1],foundRows[i][2]}
    Thursday, July 6, 2017 2:09 PM
  • User753101303 posted

    You have to cast the object to a bool as shown earlier.

    (bool)foundRows[i][0]

    Not related but you can use column 0 or the "Bit1" column name. My personal preference is to use column names else you depend on the order in which the query retrieve each column (and it could be better to use SELECT Bit1,Bit2,Bit3 etc... rather than SELECT *)

    Thursday, July 6, 2017 2:44 PM
  • User-561514082 posted

    An exception of type 'System.InvalidCastException' occurred in MyProject.dll but was not handled in user code

    Additional information: Specified cast is not valid.

    lists.Add(new List<bool> { (bool)foundRows[i][0], (bool)foundRows[i][1], (bool)foundRows[i][2] });

    Thursday, July 6, 2017 2:55 PM
  • User753101303 posted

    You likely run into the issue I warned about previously. My guess is that foundRows[i][0] is your primary key ID column which is likely an integer rather than a boolean.

    What if you try :

    lists.Add(new List<bool> { (bool)foundRows[i]["Bit1"], (bool)foundRows[i]["Bit2"], (bool)foundRows[i]["Bit3"] });

    If you still have the same message are you 100% sure they are BIT columns ?

    Thursday, July 6, 2017 3:15 PM
  • User-561514082 posted

    I am still getting the same error. And, yes, all Bit fields are bit data type.

    I am trying to do the same as code below but not using the Entity Framework. My Visual Studio throws a bunch of errors and missing references when using EF; adding missing references generate a whole bunch of other error and compiler goes off the track.

    public partial class WebForm4 : System.Web.UI.Page
    {
        protected List<List<bool>> lists;
    
        RecordContext db = new RecordContext();//here I use Entity Framework, you could change to ADO.NET
    
        protected void Page_Load(object sender, EventArgs e)
        {
            Record record1 = db.Records.Find(1);
            Record record2 = db.Records.Find(2);
            Record record3 = db.Records.Find(3);
    
            lists = new List<List<bool>>
            {
                new List<bool>{ record1.Bit1, record1.Bit2, record1.Bit3, record1.Bit4,record1.Bit5, ... , record1.Bit52 },
                new List<bool>{ record2.Bit1, record2.Bit2, record2.Bit3, record2.Bit4,record2.Bit5, ... , record2.Bit52 },
                new List<bool>{ record3.Bit1, record3.Bit2, record3.Bit3, record3.Bit4,record3.Bit5, ... , record3.Bit52 },
            };
        }
    }

    Thursday, July 6, 2017 3:41 PM
  • User753101303 posted

    Ah and they are nullable? In this case they would be nullable bools (see https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/nullable-types/) and you would have to use something such as https://stackoverflow.com/questions/12507948/best-way-to-handle-datarow-dbnull to check for DBNull.

    Thursday, July 6, 2017 4:14 PM
  • User-561514082 posted

    No, they are not nullable.

    Thursday, July 6, 2017 4:20 PM
  • User753101303 posted

    What if using https://msdn.microsoft.com/en-us/library/f177hahy.aspx to show foundRows[i]["Bit1"],.GetType().Name  when it happens? It should show "Boolean". If not it will reveal why it fails.

    Edit: I took a shortcut. Don't look at the DataTable column type which is NOT a nullable type. Look at the database side to see if those columns are NULL or NOT NULL columns (or as I just told at the type of the value that you can't cast).

    Thursday, July 6, 2017 4:48 PM
  • User2053451246 posted

    You are only confusing yourself and other with your double-posting:

    https://forums.asp.net/t/2124620.aspx?Need+help+converting+EF+code+to+ADO

    Thursday, July 6, 2017 5:19 PM
  • User-561514082 posted

    I kind of agree with you but they are two different types of questions; one is to find the reason of an error and the other one to know how to convert Entity Framework code in to ADO.Net.

    Thursday, July 6, 2017 5:32 PM
  • User1068175894 posted

    instead of doing:

    (bool)foundRows[i]["Bit1"]

    do

    (foundRows[i]["Bit1"] as boolean)

    the second approach returns null if it cannot convert

    Thursday, July 6, 2017 6:53 PM
  • User753101303 posted

    I tend this dislike this kind of code at least as a fix rather than for a real purpose.

    If Bit1 is really a not null bit column the first line will always work fine and will fail otherwise. For now I suspect we have at least one of those BitX values is NULL while they are supposed to be NOT NULL columns.

    The second line will return null if this bit column is NULL on the db side (which doesn't seems to be expected) BUT ALSO if this is a string etc... Plus if the code is not prepared to handle a possible null value, it will fail anyway later possibly obscuring the real root cause.

    So IMHO it's best to code for the exact situation you expect and avoid to write your code so that it will handle fine an unexpected situation failing later or maybe even worse not failing at all while it doesn't do what you actually want..

    Thursday, July 6, 2017 7:34 PM
  • User1068175894 posted

    You can write this code and call that for every property 

    private bool? ToBool(Object value)
    {
       if (value == null)
          return null;
       return (bool)value;
    }
    ToBool(foundRows[i]["Bit1"])

    , but it will have the exact same effect as

    (foundRows[i]["Bit1"] as boolean)
    Thursday, July 6, 2017 8:31 PM
  • User-832373396 posted

    Hi moquamar,

    string expression; expression = "ID = 27"; DataRow[] foundRows; foundRows = dt.Select(expression); lists = new List<List<bool>> { new List<bool>{ foundRows.Bit1, foundRows.Bit2, foundRows.Bit3, foundRows.Bit4, foundRows.Bit5, foundRows.Bit6, foundRows.Bit7 }, };

    Please refer to this code:


    var dt = new DataTable();
    ...
    var a= from r in dt.AsEnumerable() where r.Field<int>("ID") == 27 select new List<bool> { r.Field<bool>("Bit1") , r.Field<bool>("Bit2") , r.Field<bool>("Bit3") } .ToList();

    We could use linq statement  to query directly .

    With Regards,

    Angelina Jolie

    Friday, July 7, 2017 9:08 AM