How to update the Datagrid data.

[system123]

Hi All,

With the Edit Linkbutton I am able to change the datagrid to Edit mode. After doing some modifictaions/editing the row I need to Update the Datagrid Display view with the Edited values.

Please help me.


Thanks in Advance.

[axas]

Hello my friend. After having add the edit command, do this steps:

1.) make this method for binding
void BindGrid()
{
sqlDataAdapter1.Fill( dataSet1 );
DataGrid1.DataSource = dataSet1;
DataGrid1.DataKeyField = "xxxID";
DataGrid1.DataBind();
}

2.) handle this event

private void DataGrid1_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex = e.Item.ItemIndex;
BindGrid();
}

3.) handle this event

private void DataGrid1_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex = -1;
BindGrid();
}

4.) and this event...

private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.
DataGridCommandEventArgs e)
{
// Retrieve TextBox Controls from DataGrid
TextBox txtProductName = (TextBox)e.Item.Cells[2].Controls[0];
TextBox txtUnitPrice = (TextBox)e.Item.Cells[3].Controls[0];

// Assign Parameters to SqlCommand
sqlCommand1.Parameters["@field1"].Value= txtField1.Text;
sqlCommand1.Parameters["@field2"].Value = txtField2.Text;
sqlCommand1.Parameters["@xxxID"].Value = DataGrid1.DataKeys[ e.Item.ItemIndex ];

// Execute SqlCommand
sqlConnection1.Open();
sqlCommand1.ExecuteNonQuery();

// Deselect Row for Editing
DataGrid1.EditItemIndex = -1;
BindGrid();
sqlConnection1.Close();
}

[axas]

I have made a wrong...

Instead of txtProductName and txtUnitPrice e.g., write txtField1 and txtField2, to not be confused...

[system123]

Hi
Thanq for your quickreply. Actually I am displaying the datagrid value by dynamically importing the excel file so the Fields of the Table are unknown.

My requirement is to hold the chnages made in .aspx page in a dataset and update the datgrid accordingly.

Please give some idea. I hope you have understood my problem.

[axas]

Have you tried this code my friend?
Is this ok?
If you want to keep the changes in a dataset, clear the dataset first, and then use a DataAdapter to fill again the dataset. Is that you mean?

[system123]

Hi Frd,
Its working !!!
hip hip hurry
I made some changes after getting the textbox control text.thanx a million.

One small request. I am importing excel file with file upload control but additionally i am getting blank rows into the datagrid. How to retrive only datafilled rows/columns of excel.
I have given the SQLquery as
Select * FROM [worksheetname$]

Plz give ur valubale suggestions.

Thanks in advance

[axas]

There are three ways in which you can reference Excel workbook data within a SQL statement:

Specify the worksheet name followed by a dollar sign to access the entire range used in the worksheet:

SELECT * FROM [MySheet$]
Specify a range explicitly using cells:

SELECT * FROM [MySheet$A1:E5]
Specify a range with a defined name, as shown in the solution:

SELECT * FROM MyRange

The following sample code is to show how you can have access in a Microsoft Excel Workbook:

// Create the DataAdapter.
da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",
ConfigurationSettings.AppSettings["Excel_0115_ConnectString"]);

// Create the insert command.
String insertSql = "INSERT INTO [Sheet1$] " +
"(CategoryID, CategoryName, Description) " +
"VALUES (?, ?, ?)";
da.InsertCommand =
new OleDbCommand(insertSql, da.SelectCommand.Connection);
da.InsertCommand.Parameters.Add("@CategoryID", OleDbType.Integer, 0,
"CategoryID");
da.InsertCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15,
"CategoryName");
da.InsertCommand.Parameters.Add("@Description", OleDbType.VarChar, 100,
"Description");

// Create the update command.
String updateSql = "UPDATE [Sheet1$] " +
"SET CategoryName=?, Description=? " +
"WHERE CategoryID=?";
da.UpdateCommand =
new OleDbCommand(updateSql, da.SelectCommand.Connection);
da.UpdateCommand.Parameters.Add("@CategoryName", OleDbType.Char, 15,
"CategoryName");
da.UpdateCommand.Parameters.Add("@Description", OleDbType.VarChar, 100,
"Description");
da.UpdateCommand.Parameters.Add("@CategoryID", OleDbType.Integer, 0,
"CategoryID");

// Fill the table from the Excel spreadsheet.
dt = new DataTable( );
da.Fill(dt);
// Define the primary key.
dt.PrimaryKey = new DataColumn[] {dt.Columns[0]};

// Records can only be inserted using this technique.
dt.DefaultView.AllowDelete = false;
dt.DefaultView.AllowEdit = true;
dt.DefaultView.AllowNew = true;
// Bind the default view of the table to the grid.
dataGrid.DataSource = dt.DefaultView;