Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

LINQ Query not working

Posted on 2011-10-21
4
Medium Priority
?
307 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:robnhood00
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Pryrates
ID: 37010283
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
 

Author Comment

by:robnhood00
ID: 37026513
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
 
LVL 13

Accepted Solution

by:
Pryrates earned 2000 total points
ID: 37029337
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
 

Author Closing Comment

by:robnhood00
ID: 37155179
Thanks!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Screencast - Getting to Know the Pipeline
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month20 days, 20 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question