LINQ Query not working

I have a SQL Query that put into a datatable.  
I have a manually configured datatable.
I'm trying to merge the data using LINQ but I get no data and/or errors.

The SQL Query returns:
logHour      logDate
00               2/1/2011
02              2/1/2011
05            2/1/2011

The Datatable returns:
00
01
02
03
04
05

I want to combine to make:
00               2/1/2011
01             <null>
02              2/1/2011
03             <null>
04             <null>
05            2/1/2011

I'm using the following LINQ but I get an error when binding it to the gridview:
Error: "The data source for GridView with id 'gv' did not have any properties or attributes from which to generate columns. Ensure that your data source has content. "

 Query = (From h In dt2 _
                Group Join data In dt _
                On h.Field(Of String)("logHour") Equals _
                   data(0) _
              Into children = Group _
        From child In children.DefaultIfEmpty())

            gv.DataSource = Query
            gv.DataBind()
'Code to GET SQL
da = New SqlDataAdapter(GetSelect(), con)
Dim ds As New DataSet, dt As New DataTable("data")   
da.Fill(dt)

'Code for manual datatable
 dt2 As New DataTable()
 dt2 = dtHours()

'LINQ
Dim Query = (From h In dt2 _
                Group Join data In dt _
                On h.Field(Of String)("logHour") Equals _
                   data(0) _
              Into children = Group _
        From child In children.DefaultIfEmpty())

            gv.DataSource = Query
            gv.DataBind()

Private Function dtHours() As DataTable
        Dim dt As New DataTable, dr As DataRow
        Dim i As Integer = 0
        dt.Columns.Add("logHour", Type.GetType("System.String"))
        While i <= 5
            dr = dt.NewRow
            If i < 10 Then
                dr("logHour") = "0" & i.ToString
            Else
                dr("logHour") = i.ToString
            End If

            dt.Rows.Add(dr)
            i = i + 1
        End While
        Return dt
    End Function

Open in new window

robnhood00Asked:
Who is Participating?
 
PryratesCommented:
Here you go:

 public string result;
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable dtH = new DataTable("HList");
        dtH.Columns.Add("hourList", typeof(string));
        for (int i = 0; i <= 5; i++)
        {
            DataRow dr1 = dtH.NewRow();
            dr1["hourList"] = i.ToString("00");
            dtH.Rows.Add(dr1);            
        }


        DataTable dt = new DataTable("data");
        dt.Columns.Add("logHour", typeof(string));
        dt.Columns.Add("logDate", typeof(string));

        DataRow dr = dt.NewRow();
        dr["logHour"] = "00";
        dr["logDate"] =  "2/1/2011";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr["logHour"] = "02";
        dr["logDate"] = "2/1/2011";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr["logHour"] = "05";
        dr["logDate"] = "2/1/2011";
        dt.Rows.Add(dr);

        var x = (from h in dtH.AsEnumerable()
                 join p in dt.AsEnumerable() on h.Field<string>("hourList") equals p.Field<string>("logHour") into j
                 from subset in j.DefaultIfEmpty()
                 select new { logDate = (subset == null ? string.Empty : subset.Field<string>("logDate")), h });

        foreach (var v in x)
        {
            result += "<br />" + v.h["hourList"] + " " + (string.IsNullOrEmpty(v.logDate) ? "<null>" : v.logDate);
        }
    }

Open in new window


If this will make things more easy - that is up to you ;). At least it works on DataTables.
I created 2 Datasets from scratch, but it will be nearly your structure apart from the fact that you load it from a db and I don't.
Hope that helps
0
 
PryratesCommented:
I made a litte C# sample.
Sorry not so into vb-net, but i think the idea is the same:

 
public string result;
        protected void Page_Load(object sender, EventArgs e)
        {
            List<string> lstH = new List<string>();
            for (int i = 0; i <= 5; i++)
            {
                lstH.Add (i.ToString("00"));
            }

            List<SqlVals> lst = new List<SqlVals>();
            lst.Add(new SqlVals("00", "2/1/2011"));
            lst.Add(new SqlVals("02", "2/1/2011"));
            lst.Add(new SqlVals("05", "2/1/2011"));


            var x = (from h in lstH
                      join p in lst on h equals p.logHour into j 
                      from subset in j.DefaultIfEmpty()
                     select new { logDate = (subset == null ? string.Empty : subset.logDate) , h  });

            foreach (var v in x)
            {
                result += "<br />" + v.h + " " + (string.IsNullOrEmpty(v.logDate) ? "<null>" : v.logDate);
            }
        }

        class SqlVals
        {
            public string logHour { get; set; }
            public string logDate { get; set; }
            public SqlVals(string lh, string ld)
            {
                this.logHour = lh;
                this.logDate = ld;
            }
        }

Open in new window


Hope that helps.
Maybe you will need to "convert" the datatables into the structures I use (one List of string and one class containing  logHours and date)

I embedded it into a website an displayed "result" on it. It looks exacly the wayyou want it to be :)
0
 
robnhood00Author Commented:
Thanks Pryrates.  I think that would work fine, however, I have many more fields and multiple reports that I need to make.  LINQ seems like the best option for managing the data.  Anyway to make the LINQ query work?
0
 
robnhood00Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.