I have a datagrid (not a gridview) that contains a few columns and a text box for updating Sales amount as an Itemtemplate column. Here is a short version:
Customer Number --Customer Name-- Sales Amount
111-------------- MSFT----------- 100.00
222-------------- IBM ------------ 200.00
Users are supposed to update the sales amount in this text box in each row and finally click on a button called Update Amount located under the datagrid. Users may update the sales amount in one or more rows. I am then supposed to loop through all the
rows and get the customer number in each row as a key and write the sales amount in that row to a SQL database. I am having hard time understanding how will I loop through the rows available and write to SQL. Normally I have an update button on each row but
in this case a single button will update all of the records in SQL.
Can some one please explain the coding process. Thanks
SqlConnection connection = new SqlConnection("server=test;database=master;user id=user1;passsword=user@1");
SqlCommand commandUpdate = new SqlCommand("update customers set SalesAmount=@SalesAmount where CustomerNumber=@CustomerNumber", connection);
commandUpdate.Parameters.Add("@CustomerNumber", SqlDbType.Int).Value = 0;
commandUpdate.Parameters.Add("@SalesAmount", SqlDbType.Float).Value = 0;
connection.Open();
for (int i = 0; i < dataGrid.Items.Count; i++)
{
//to acess customer no if you have use boudfield
int custNo = Convert.ToInt32(dataGrid.Items[i].Cells[0].Text);
//if you have used templatecolumn with label for customer no
custNo = Convert.ToInt32((dataGrid.Items[i].FindControl("lblCustomerNo") as Label).Text);
Single amount = Convert.ToSingle((dataGrid.Items[i].FindControl("txtSalesAmount") as TextBox).Text);
commandUpdate.Parameters["@CustomerNumber"].Value = custNo;
commandUpdate.Parameters["@SalesAmount"].Value = amount;
commandUpdate.ExecuteNonQuery();
}
connection.Close();
johnzee
Member
100 Points
139 Posts
Loop through datagrid rows and update SQL
May 03, 2012 12:43 AM|LINK
Hi,
I have a datagrid (not a gridview) that contains a few columns and a text box for updating Sales amount as an Itemtemplate column. Here is a short version:
Customer Number --Customer Name-- Sales Amount
111-------------- MSFT----------- 100.00
222-------------- IBM ------------ 200.00
Users are supposed to update the sales amount in this text box in each row and finally click on a button called Update Amount located under the datagrid. Users may update the sales amount in one or more rows. I am then supposed to loop through all the rows and get the customer number in each row as a key and write the sales amount in that row to a SQL database. I am having hard time understanding how will I loop through the rows available and write to SQL. Normally I have an update button on each row but in this case a single button will update all of the records in SQL.
Can some one please explain the coding process. Thanks
karthicks
All-Star
31378 Points
5422 Posts
Re: Loop through datagrid rows and update SQL
May 03, 2012 05:34 AM|LINK
hi, there are so may ways
i) Using for loop
SqlConnection connection = new SqlConnection("server=test;database=master;user id=user1;passsword=user@1"); SqlCommand commandUpdate = new SqlCommand("update customers set SalesAmount=@SalesAmount where CustomerNumber=@CustomerNumber", connection); commandUpdate.Parameters.Add("@CustomerNumber", SqlDbType.Int).Value = 0; commandUpdate.Parameters.Add("@SalesAmount", SqlDbType.Float).Value = 0; connection.Open(); for (int i = 0; i < dataGrid.Items.Count; i++) { //to acess customer no if you have use boudfield int custNo = Convert.ToInt32(dataGrid.Items[i].Cells[0].Text); //if you have used templatecolumn with label for customer no custNo = Convert.ToInt32((dataGrid.Items[i].FindControl("lblCustomerNo") as Label).Text); Single amount = Convert.ToSingle((dataGrid.Items[i].FindControl("txtSalesAmount") as TextBox).Text); commandUpdate.Parameters["@CustomerNumber"].Value = custNo; commandUpdate.Parameters["@SalesAmount"].Value = amount; commandUpdate.ExecuteNonQuery(); } connection.Close();OR also refer : http://geekswithblogs.net/thanigai/archive/2009/05/21/bulk-insert-update-and-delete-with-asp.net-listview-control.aspx
http://www.codeproject.com/Articles/26973/Bulk-Edit-with-GridView-without-xxxDataSource-SqlD
http://www.codeproject.com/Articles/4469/Insert-Data-into-SQL-2000-Using-OpenXML-ADO-NET
http://www.dotnetfunda.com/interview/exam299-what-is-openxml-in-sql-server.aspx
Karthick S