locked
LINQ - Column Value not Fetching properly RRS feed

  • Question

  • User-1024101449 posted

    Hi,

    I am trying finding the column in table and fetching the data in a table.

    But, value is not coming.

    what is the problem in my code.

    DETAILED CODE

    ------------------

     protected void Button1_Click(object sender, EventArgs e)
        {
            string Studentno = null;
            double Sub1 = 0;
            double Sub2 = 0;
            double Sub3 = 0;

            List<string> StuMarks = GetStudentMarks(Studentno,Sub1,Sub2,Sub3); // Check CONDITION value
        }

        public static List<string> GetStudentMarks(string Studentno, double Sub1, double Sub2, double Sub3)
        {
              

            List<string> TempMarks = new List<string>();
            string str = "Sub1#Sub2";
            string StudentNo = "101";
            double SubMark = 0;
            string[] words1 = str.Split('#');
            for (int i = 0; i < words1.Length; i++)
            {
                int n = 0;

                DataTable dt = new DataTable();
                dt.Columns.AddRange(new[] { new DataColumn("StudentNo"),new DataColumn("Sub1"),new DataColumn("Sub2"),new DataColumn("Sub3"),
                new DataColumn("StudentName") });
                dt.Rows.Add("101", "100", "80", null, "AAA");
                dt.Rows.Add("102", "100", null, "100", "BBB");
                dt.Rows.Add("103", null, "100", "100", "CCC");
                dt.Rows.Add("104", "100", null, "100", "DDD");
                dt.Rows.Add("105", "100", "100", "100", "EEE");
                dt.Rows.Add("106", "100", "100", "100", "FFF");


                // Find the column (sub1,sub2) in dt table
                var QryIn = (from r in dt.AsEnumerable()
                                from c in dt.Columns.Cast<DataColumn>()
                                from t in str
                             where r[c.ColumnName] != DBNull.Value && !string.IsNullOrEmpty((string)r[c.ColumnName]) &&
                             r.Field<string>("StudentNo") == StudentNo // Filter only student no "101"
                             select new 
                                { 
                                    SubMark = words1[i], //Get the marks of sub1 and sub2
                                    
                                });

                foreach (var n1 in QryIn)
                {
                    SubMark = Convert.ToDouble(n1.SubMark); //Get the marks of sub1 and sub2 based for each loop
                   
                }
               
                TempMarks.Add(words1[0] + "=" + n); //
            }

            // Final Result will be Sub1=100,Sub2=80

            return TempMarks;
        }

    Result should come : Sub1=100,Sub2=80

    Thursday, July 20, 2017 6:59 AM

Answers

  • User-271186128 posted

    Hi gani7787,

    Based on your code and description, I’ve created a demo which could get the corresponding columns’ value based on the str’s content for student 101. For example, when str is “Sub1#Sub2” the result will be “Sub1=100Sub2=80”, when str is “Sub1#Sub2#Sub3” the result will be “Sub1=100Sub2=80Sub3=0”......

    Please refer to the following code:

    public static List<string> GetStudentMarks(string Studentno, double Sub1, double Sub2, double Sub3)
    {
        List<string> TempMarks = new List<string>();
        string str = "Sub1#Sub2";
        string StudentNo = "101";
        double SubMark = 0;
        string[] words1 = str.Split('#');
    
        for (int i = 0; i < words1.Length; i++)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new[] { new DataColumn("StudentNo"),new DataColumn("Sub1"),new DataColumn("Sub2"),new DataColumn("Sub3"),
    new DataColumn("StudentName") });
            dt.Rows.Add("101", "100", "80", null, "AAA");
            dt.Rows.Add("102", "100", null, "100", "BBB");
            dt.Rows.Add("103", null, "100", "100", "CCC");
            dt.Rows.Add("104", "100", null, "100", "DDD");
            dt.Rows.Add("105", "100", "100", "100", "EEE");
            dt.Rows.Add("106", "100", "100", "100", "FFF");
    
            // Find the column (sub1,sub2) in dt table
            var QryIn = from r in dt.AsEnumerable()
                        where r.Field<string>("StudentNo") == StudentNo
                        select new
                        {
                            SubMark = r.Field<string>(words1[i])
                        };
    
            foreach (var n1 in QryIn)
            {
                SubMark = Convert.ToDouble(n1.SubMark); //Get the marks of sub1 and sub2 based for each loop
            }
    
            TempMarks.Add(words1[i] + "=" + SubMark); 
        }
    
        // Final Result will be Sub1=100,Sub2=80
        return TempMarks;
    }
    

    If you have any other questions, please feel free to contact me any time.

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 21, 2017 6:44 AM

All replies

  • User753101303 posted

    Hi, Coud it be because of your r[c.ColumnName] != DBNull.Value test ? What if you drop this test for now.

    Not sure to get your intent. Do you want to select this row based only on the student number or do you want as well to select this row only if it doesn't have any null values ??? (in which case it seems correct that this row is not selected).

    Thursday, July 20, 2017 7:06 AM
  • User-1024101449 posted

    yes. i want to get row based.

    First i want to find the columns Sub1,Sub2 in dt table.

    If it is there, then second find the values of the columns for student 101.

    Thursday, July 20, 2017 7:39 AM
  • User753101303 posted

    Ah in your real app you won't be sure if those columns are present ?  Could it be pivoted data ? It might be best to expose them as rows ie :

    StudentNo,SubNo,Value
    101 1 100
    101 2 80
    102 1 100
    103 2 100
    103 3 100
    etc..

    You can always pivot later (IMO it's best to do that at the very last moment, pivoted data is a good way to show data but makes them harder to process).

    For now you do check the value for all columns and you ignore the row if one of those column is null.

    Else for now my understanding is that you actually want :
    - check if sub1 and sub2 columns (or at least one column whose name starts with "sub" ?) are found in your data table (without caring about their value)
    - and if found, retrieve the row and the values for those columns (and perhaps ignoring null values ???)

    Ah in short you want to "unpivot" the sub columns for a student ???


    Thursday, July 20, 2017 9:48 AM
  • User-271186128 posted

    Hi gani7787,

    Based on your code and description, I’ve created a demo which could get the corresponding columns’ value based on the str’s content for student 101. For example, when str is “Sub1#Sub2” the result will be “Sub1=100Sub2=80”, when str is “Sub1#Sub2#Sub3” the result will be “Sub1=100Sub2=80Sub3=0”......

    Please refer to the following code:

    public static List<string> GetStudentMarks(string Studentno, double Sub1, double Sub2, double Sub3)
    {
        List<string> TempMarks = new List<string>();
        string str = "Sub1#Sub2";
        string StudentNo = "101";
        double SubMark = 0;
        string[] words1 = str.Split('#');
    
        for (int i = 0; i < words1.Length; i++)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new[] { new DataColumn("StudentNo"),new DataColumn("Sub1"),new DataColumn("Sub2"),new DataColumn("Sub3"),
    new DataColumn("StudentName") });
            dt.Rows.Add("101", "100", "80", null, "AAA");
            dt.Rows.Add("102", "100", null, "100", "BBB");
            dt.Rows.Add("103", null, "100", "100", "CCC");
            dt.Rows.Add("104", "100", null, "100", "DDD");
            dt.Rows.Add("105", "100", "100", "100", "EEE");
            dt.Rows.Add("106", "100", "100", "100", "FFF");
    
            // Find the column (sub1,sub2) in dt table
            var QryIn = from r in dt.AsEnumerable()
                        where r.Field<string>("StudentNo") == StudentNo
                        select new
                        {
                            SubMark = r.Field<string>(words1[i])
                        };
    
            foreach (var n1 in QryIn)
            {
                SubMark = Convert.ToDouble(n1.SubMark); //Get the marks of sub1 and sub2 based for each loop
            }
    
            TempMarks.Add(words1[i] + "=" + SubMark); 
        }
    
        // Final Result will be Sub1=100,Sub2=80
        return TempMarks;
    }
    

    If you have any other questions, please feel free to contact me any time.

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 21, 2017 6:44 AM
  • User-1024101449 posted

    yes. exact matching. i done same already.

    anyhow thanks for your great help.

    Friday, July 21, 2017 7:04 AM