Binding DataSet to GridView

Question

I'm having a problem and I've got the feeling it is something simple that I'm not doing right.

I've got a simple ASP.NET project going. I've got a DataSet that I populate from an Oracle database. The table in the DataSet has two columns: "account_code" and "account_descr".

The user enters in a search term into a textbox. I use LINQ to query to get the results from the DataSet and put them into another variable.

Then I try to bind the variable to the gridview.

Code:

Dim results = AccountsDataSet.Tables("Results")

Dim filteredResults = From q In results Where q.Item("account_code").ToString.StartsWith(search) Select q.Item("account_code")

GridView1.DataSource = filteredResults
GridView1.DataBind()

The code as above works. The only thing is it only shows the one column. I need it to show both columns. But if I change the LINQ line to this:

Dim filteredResults = From q In results Where q.Item("account_code").ToString.StartsWith(search) Select q.Item("account_code"), q.Item("account_descr")

it throws the error "Range variable name can be inferred only from a simple or qualified name with no arguments."

Any ideas?

Answer 1

Correct me if I'm wrong, but shouldn't the LINQ line be like this:

Dim filteredResults = From q In results Where q.Item("account_code").ToString.StartsWith(search) Select New With { .AccountCode = q.Item("account_code"), .AccountDesc = q.Item("account_descr") }

Instead of selecting a single property, select the whole entity.

Answer 2

Your select statement explicitly asks for one column. The following info is relevant to you, hope it makes sense

There's a rule to watch out for which is a LINQ rule, not LINQ to Entities. When you create an on the fly property in a projection and your are doing so with a function, LINQ cannot figure out what to call that property, so you must name it as I have done here by using the variable name "fullname".

Dim custs = From cust In .Customer  By cust.FullNameAlpha 
SELECT cust.ContactID, fullname = cust.LastName.Trim & "," & cust.FirstName

Answer 3

You need to return anonymous type to get all the properties you need or else it will return all the public properties which will also include RowError and HasErrors. Here is the code that I have used:

 gvCustomers.DataSource = from c in ds.Tables[0].AsEnumerable()
                                         where c["FirstName"].ToString().StartsWith("J")
                                         select new { FirstName = c["FirstName"].ToString(), LastName = c["LastName"].ToString() }; 
                gvCustomers.DataBind();

Since, DataTable is not IEnumerable I used the AsEnumerable() method which will return a strongly type collection of DataRow. You can also explicitly specify the type as shown in the following code:

  gvCustomers.DataSource = from DataRow c in ds.Tables[0].Rows
                                             where c["FirstName"].ToString().StartsWith("J")
                                             select new { FirstName = c["FirstName"].ToString(), LastName = c["LastName"].ToString() }; 
                    gvCustomers.DataBind();

Hope it helps!