ankita alung
ankita alung

Reputation: 327

Difficulty with linq and data table

I am trying to filter records via Linq on my datatable, but somehow The result is displaying a table with no errors but a line saying "row has errors", in a column of table. My code is as following: Any suggestions?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;


namespace linq_app
{
    public partial class admins : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbcon"].ToString());

            con.Open();

            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.CommandText = "select * from user_info";
            sqlcmd.CommandType = CommandType.Text;

            sqlcmd.Connection = con;
            SqlDataReader sqlreader = sqlcmd.ExecuteReader();

            DataTable dt = new DataTable();
            dt.Columns.Add("first_name", typeof(string));
            dt.Columns.Add("last_name", typeof(string));
            dt.Columns.Add("Email_ID", typeof(string));
            dt.Columns.Add("Contact", typeof(string));
            dt.Columns.Add("Address", typeof(string));
            dt.Columns.Add("Role", typeof(string));


            while (sqlreader.Read())
            {
                DataRow dr = dt.NewRow();
                dr["first_name"] = sqlreader["first_name"];
                dr["last_name"] = sqlreader["last_name"];
                dr["Email_ID"] = sqlreader["email"];
                dr["Contact"] = sqlreader["contact1"];
                dr["Address"] = sqlreader["address"];
                dr["Role"] = sqlreader["role"];
                dt.Rows.Add(dr);
            }


            var results = from myRow in dt.AsEnumerable()
                          where myRow.Field<string>("Role").ToString() == "admin".ToString()
                          select myRow;

            //dataGridView1.DataSource = dt;


            //DataClasses1DataContext dc = new DataClasses1DataContext();
            //var admin = from records in dc.GetTable<user_info>() 
            //              where records.role =="admin"
            //              select records;
            GridView1.DataSource = results;
            GridView1.DataBind();
        }
    }
}

Upvotes: 2

Views: 459

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236328

Try to remove ToString call. It will throw exception if there is null in roles column. And create new datatable from filtered rows:

var results = from myRow in dt.AsEnumerable()
              where myRow.Field<string>("Role") == "admin"
              select myRow;

GridView1.DataSource = results.CopyToDataTable(); // create datatable
GridView1.DataBind();

BTW why not to use Linq to Entities instead? You will be able to do filtering on database side.

Upvotes: 3

Related Questions