SqlDataReader can not be loaded into table

Question

I have a SqlDataReader from a SQL Server command and want I to show in a DataDridView with this code:

SqlDataReader dr=product.RetriveDetailsOfOneProduct();
DataTable dt = new DataTable();
dt.Load(dr);
DataGrid.DataSource = dr;

I checked it using breakpoints that my SqlDataReader has rows but when loading it into DataTable, it is empty (with no rows)

How can I fix this?

And it is function of my DataAccess class where SqlDataReader is returned:

internal SqlDataReader RetriveDetailsOfOneProduct(product product)
{
     string RetriveQuery = string.Format("SElect person.F_Name as N'Name ',person.L_Name as N'LastName',borrow.Person_Identity as N'Identity',borrow.T_Date as 'Date1', borrow.B_Date as 'Date2' from BorrowTable borrow Inner join PersonTable person on borrow.Product_Name=N'{0}' and person.Person_identity=borrow.Person_Identity ", product.Productname);

     SqlCommand cmd = new SqlCommand(RetriveQuery, conn);
     // conn.Close();  //if i close the Connection(conn) it runs into error
     return(cmd.ExecuteReader());
}

you need to make sure you bind the data to the DataGrid as well just setting the Source is not good enough — MethodMan
– MethodMan, Commented Feb 4, 2015 at 20:23
I would suggest porting the query over to a stored procedure as well. it will be easier to maintain vs trying to edit dynamic sql in your code. also wrap your SqlCommand Objects around a using(){} for auto disposing — MethodMan
– MethodMan, Commented Feb 4, 2015 at 20:30
thanks for all of you worked good.i made a mistake with assigning dt to DataGrid,now it works good — ako
– ako, Commented Feb 4, 2015 at 20:32
please select one of the answers as the acceptable answer thanks — MethodMan
– MethodMan, Commented Feb 4, 2015 at 20:33

mason · Accepted Answer · 2015-02-04 20:44:07Z

3

Your issue is that you are setting the data source to the data reader instead of the data table.

DataGrid.DataSource = dr;

should be

DataGrid.DataSource = dt;
DataGrid.DataBind();

edited Feb 4, 2015 at 20:44

mason

32.8k12 gold badges83 silver badges127 bronze badges

answered Feb 4, 2015 at 20:25

MethodMan

18.9k6 gold badges41 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jan Köhler · Accepted Answer · 2015-02-04 20:24:36Z

2

Assign the datatable, not the datareader...

DataGrid.DataSource = dt;

answered Feb 4, 2015 at 20:24

Jan Köhler

6,1205 gold badges28 silver badges38 bronze badges

Collectives™ on Stack Overflow

SqlDataReader can not be loaded into table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related