How To fetch all rows of a table from SQL database using SQL query in C#

Question

I have a Grid View in which there is a Date column. In database I have one unique id column and a Date column. I want to get all the ids from the SQL query so that I can get all the ids for a particular date. For example if I write "27-06-2013" then all ids 121,123,124 receive. When I am doing using Response.Write I am getting only a single id. and all the ids then I want to show in grid view.

SqlCommand cmd = new SqlCommand("Select * from Scheduleappointment where Doctor_Id=@docid and Convert(varchar(5),Start_Time,108)+'-'+Convert(varchar(5),End_Time,108)=@time and Date=@date", con);
cmd.Parameters.AddWithValue("@docid", Label1.Text);
cmd.Parameters.AddWithValue("@time", timing.Text);
cmd.Parameters.AddWithValue("@date", Convert.ToDateTime(txtdate.Text).ToString("yyyy-MM-dd"));
conopen();
cmd.ExecuteNonQuery();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
    Response.Write(dr["Id"].ToString());
}

I want to show these id values in single row of grid view.Like 121,123 in single row next to date 27-06-2013.

@subhash-bt its not giving all the ids for 27-06-2013.I have tried this. — user2432793
– user2432793, Commented Jun 27, 2013 at 11:49
i m not getting that tolist();..when i m writing list<string> ids=new list<string>(); — user2432793
– user2432793, Commented Jun 27, 2013 at 11:52
ohh sorry my bad, instead of if use While(dr.Read()) { Response.Write(dr["Id"].ToString()); } — Gopesh Sharma
– Gopesh Sharma, Commented Jun 27, 2013 at 11:57

Tevo D · Accepted Answer · 2013-06-27 12:50:44Z

5

The issue is with the last part

if (dr.Read())
{
    Response.Write(dr["Id"].ToString());
}

The if will evaluate and read only once. If there is at least one row, it will be written. You probably need while, to read until there are no more rows.

while (dr.Read())
{
    Response.Write(dr["Id"].ToString());
}

You will probably also want to add some formatting (spaces or line breaks between values) as well as clean up code (close the data reader, etc.) after writing.

answered Jun 27, 2013 at 12:50

Tevo D

3,39123 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

How To fetch all rows of a table from SQL database using SQL query in C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related