how to add mutiple rows of values queried from sql into cmd

Question

I have the following piece of code. The SQL query returns me two rows of value. However, when I involve the method cmd.ExecuteReader(); the application only returns the first row of values.

How do I go about resolving this? Is it something wrong with the cmd.Parameters.AddWithValue method? Please help!

sqlQuery = "select name, data,data2 from surgicalfiledemo where id = '2'";
SqlCommand cmd = new SqlCommand(sqlQuery, con);
cmd.Parameters.AddWithValue("id", surgicalGridView.SelectedRow.Cells[1].Text);

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())
{
   HttpContext.Current.Response.ClearContent();
   HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + dr["name"].ToString() + ".xls");
   HttpContext.Current.Response.ContentType = "application/excel";
   Response.Charset = "";
   Response.Cache.SetCacheability(HttpCacheability.NoCache);
   Response.Write(dr["data"] + "\t");
   Response.Write(dr["data2"] + "\t");
   Response.End();
}

If you get multiple rows of data, you need to loop through them using a while (dr.Read()) { ...... } code snippet. Right now - you're only reading a single row from the data reader .... — marc_s
– marc_s, Commented Mar 18, 2013 at 5:42

BizApps · Accepted Answer · 2013-03-18 02:29:21Z

1

If you are using command parameters you must use @ parameter on your query: Change Id='2' to Id=@Id then and on your add with value "id" change it to @Id.

Try this:

sqlQuery = "select name, data,data2 from surgicalfiledemo where id = @id";
SqlCommand cmd = new SqlCommand(sqlQuery, con);
cmd.Parameters.AddWithValue("@id", surgicalGridView.SelectedRow.Cells[1].Text);

//SqlDataReader dr = cmd.ExecuteReader(); use sqldata adapter to load all the data then fill to to Datatable and Bind it on GridView
SqlDataAdapter adapt = new SqlDataAdapter();
DataTable dt = new DataTable();
adapt.SelectCommand = cmd;
adapt.Fill(dt);
GridView GridView1 = new  GridView();
GridView1.DataSource = dt;
GridView1.DataBind();

// if (dr.Read()) {  .. }  this causes you to return single record only, your export to excel execute every time a row was read thats why you can only see one record.

Response.Clear();
Response.Buffer = true;
string filename="MyExportedData.xls"; 
Response.AddHeader("content-disposition","attachment;filename="+filename);
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.RenderControl(hw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();

Check this link : ASP.NET: Export Grid View to Excel

Best Regards

edited Mar 18, 2013 at 2:29

answered Mar 18, 2013 at 1:07

BizApps

6,1409 gold badges43 silver badges65 bronze badges

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

5 Comments

user1781830 Over a year ago

yes! but the application is still returning me one row of values. How do i change the codes to make the application return me rows of values? new to c#!

BizApps Over a year ago

what is the value of surgicalGridView.SelectedRow.Cells[1].Text ? could you add also you dr.read() code

user1781830 Over a year ago

the value of surgicalGridview.SelectedRow.Cells[1].Text = the row where i can download the excel file with a download link

BizApps Over a year ago

Got it. what your code do is when reader reads each row it will really display 1 row only because your export to excel is also inside your read code.

user1781830 Over a year ago

so how should i change at the codes? any examples to illustrate? thanks!

Collectives™ on Stack Overflow

how to add mutiple rows of values queried from sql into cmd

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related