can't update in datagrid button

Question

I am using Northwest DB and a datagrid.This is the error I keep getting when I select the update button on the datagrid. "Specified argument was out of the range of valid values. Parameter name: index" Here is my code: using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; namespace WebSub1 { ///

/// Summary description for WebForm1. ///

public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.Label Label1; protected System.Data.SqlClient.SqlConnection sqlConnection1; protected System.Web.UI.WebControls.Label ErrorMessage; protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1; protected System.Data.SqlClient.SqlCommand sqlSelectCommand1; protected System.Data.SqlClient.SqlCommand sqlInsertCommand1; protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1; protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1; protected WebSub1.dsWebSub2 dsWebSub21; protected System.Web.UI.WebControls.DataGrid DataGrid1; protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter2; protected System.Data.SqlClient.SqlCommand sqlSelectCommand2; protected System.Data.SqlClient.SqlCommand sqlInsertCommand2; protected System.Data.SqlClient.SqlCommand sqlUpdateCommand2; protected System.Data.SqlClient.SqlCommand sqlDeleteCommand2; protected System.Data.SqlClient.SqlConnection sqlConnection2; protected WebSub1.DataSet2 dataSet21; protected System.Web.UI.WebControls.TextBox TextBox1; private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here if (!IsPostBack) { bindGrid(); } } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } ///

/// Required method for Designer support - do not modify /// the contents of this method with the code editor. ///

private void InitializeComponent() { this.sqlConnection1 = new System.Data.SqlClient.SqlConnection(); this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter(); this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand(); this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand(); this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand(); this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand(); this.dsWebSub21 = new WebSub1.dsWebSub2(); this.sqlDataAdapter2 = new System.Data.SqlClient.SqlDataAdapter(); this.sqlSelectCommand2 = new System.Data.SqlClient.SqlCommand(); this.sqlInsertCommand2 = new System.Data.SqlClient.SqlCommand(); this.sqlUpdateCommand2 = new System.Data.SqlClient.SqlCommand(); this.sqlDeleteCommand2 = new System.Data.SqlClient.SqlCommand(); this.sqlConnection2 = new System.Data.SqlClient.SqlConnection(); this.dataSet21 = new WebSub1.DataSet2(); ((System.ComponentModel.ISupportInitialize)(this.dsWebSub21)).BeginInit(); ((System.ComponentModel.ISupportInitialize)(this.dataSet21)).BeginInit(); this.DataGrid1.CancelCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.cancelChange); this.DataGrid1.EditCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.editRow); this.DataGrid1.UpdateCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.updateRow); this.DataGrid1.DeleteCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.deleteRow); // // sqlConnection1 // this.sqlConnection1.ConnectionString = "workstation id=DADNOTE;packet size=4096;integrated security=SSPI;data source=DADN" + "OTE;persist security info=False;initial catalog=subjob4SQL"; // // sqlDataAdapter1 // this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1; this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1; this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1; this.sqlDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "TeacherSubs", new System.Data.Common.DataColumnMapping[] { new System.Data.Common.DataColumnMapping("LastName", "LastName"), new System.Data.Common.DataColumnMapping("FirstName", "FirstName"), new System.Data.Common.DataColumnMapping("JobID", "JobID")})}); this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1; // // sqlDeleteCommand1 // this.sqlDeleteCommand1.CommandText = "DELETE FROM TeacherSubs WHERE (JobID = @Original_JobID)"; this.sqlDeleteCommand1.Connection = this.sqlConnection1; this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_JobID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "JobID", System.Data.DataRowVersion.Original, null)); // // sqlInsertCommand1 // this.sqlInsertCommand1.CommandText = "INSERT INTO TeacherSubs(LastName, FirstName, JobID) VALUES (@LastName, @FirstName" + ", @JobID); SELECT LastName, FirstName, JobID FROM TeacherSubs WHERE (JobID = @Jo" + "bID)"; this.sqlInsertCommand1.Connection = this.sqlConnection1; this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", System.Data.SqlDbType.VarChar, 2147483647, "LastName")); this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", System.Data.SqlDbType.VarChar, 2147483647, "FirstName")); this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@JobID", System.Data.SqlDbType.Int, 4, "JobID")); // // sqlSelectCommand1 // this.sqlSelectCommand1.CommandText = "SELECT LastName, FirstName, JobID FROM TeacherSubs"; this.sqlSelectCommand1.Connection = this.sqlConnection1; // // sqlUpdateCommand1 // this.sqlUpdateCommand1.CommandText = "UPDATE TeacherSubs SET LastName = @LastName, FirstName = @FirstName, JobID = @Job" + "ID WHERE (JobID = @Original_JobID); SELECT LastName, FirstName, JobID FROM Teach" + "erSubs WHERE (JobID = @JobID)"; this.sqlUpdateCommand1.Connection = this.sqlConnection1; this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", System.Data.SqlDbType.VarChar, 2147483647, "LastName")); this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", System.Data.SqlDbType.VarChar, 2147483647, "FirstName")); this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@JobID", System.Data.SqlDbType.Int, 4, "JobID")); this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_JobID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "JobID", System.Data.DataRowVersion.Original, null)); // // dsWebSub21 // this.dsWebSub21.DataSetName = "dsWebSub2"; this.dsWebSub21.Locale = new System.Globalization.CultureInfo("en-US"); // // sqlDataAdapter2 // this.sqlDataAdapter2.DeleteCommand = this.sqlDeleteCommand2; this.sqlDataAdapter2.InsertCommand = this.sqlInsertCommand2; this.sqlDataAdapter2.SelectCommand = this.sqlSelectCommand2; this.sqlDataAdapter2.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "Employees", new System.Data.Common.DataColumnMapping[] { new System.Data.Common.DataColumnMapping("LastName", "LastName"), new System.Data.Common.DataColumnMapping("FirstName", "FirstName"), new System.Data.Common.DataColumnMapping("EmployeeID", "EmployeeID")})}); this.sqlDataAdapter2.UpdateCommand = this.sqlUpdateCommand2; // // sqlSelectCommand2 // this.sqlSelectCommand2.CommandText = "SELECT LastName, FirstName, EmployeeID FROM Employees"; this.sqlSelectCommand2.Connection = this.sqlConnection2; // // sqlInsertCommand2 // this.sqlInsertCommand2.CommandText = "INSERT INTO Employees(LastName, FirstName) VALUES (@LastName, @FirstName); SELECT" + " LastName, FirstName, EmployeeID FROM Employees WHERE (EmployeeID = @@IDENTITY)"; this.sqlInsertCommand2.Connection = this.sqlConnection2; this.sqlInsertCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", System.Data.SqlDbType.NVarChar, 20, "LastName")); this.sqlInsertCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", System.Data.SqlDbType.NVarChar, 10, "FirstName")); // // sqlUpdateCommand2 // this.sqlUpdateCommand2.CommandText = @"UPDATE Employees SET LastName = @LastName, FirstName = @FirstName WHERE (EmployeeID = @Original_EmployeeID) AND (FirstName = @Original_FirstName) AND (LastName = @Original_LastName); SELECT LastName, FirstName, EmployeeID FROM Employees WHERE (EmployeeID = @EmployeeID)"; this.sqlUpdateCommand2.Connection = this.sqlConnection2; this.sqlUpdateCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@LastName", System.Data.SqlDbType.NVarChar, 20, "LastName")); this.sqlUpdateCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FirstName", System.Data.SqlDbType.NVarChar, 10, "FirstName")); this.sqlUpdateCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_EmployeeID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "EmployeeID", System.Data.DataRowVersion.Original, null)); this.sqlUpdateCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_FirstName", System.Data.SqlDbType.NVarChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirstName", System.Data.DataRowVersion.Original, null)); this.sqlUpdateCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_LastName", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "LastName", System.Data.DataRowVersion.Original, null)); this.sqlUpdateCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EmployeeID", System.Data.SqlDbType.Int, 4, "EmployeeID")); // // sqlDeleteCommand2 // this.sqlDeleteCommand2.CommandText = "DELETE FROM Employees WHERE (EmployeeID = @Original_EmployeeID) AND (FirstName = " + "@Original_FirstName) AND (LastName = @Original_LastName)"; this.sqlDeleteCommand2.Connection = this.sqlConnection2; this.sqlDeleteCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_EmployeeID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "EmployeeID", System.Data.DataRowVersion.Original, null)); this.sqlDeleteCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_FirstName", System.Data.SqlDbType.NVarChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "FirstName", System.Data.DataRowVersion.Original, null)); this.sqlDeleteCommand2.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_LastName", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "LastName", System.Data.DataRowVersion.Original, null)); // // sqlConnection2 // this.sqlConnection2.ConnectionString = "workstation id=DADNOTE;packet size=4096;integrated security=SSPI;data source=\"DAD" + "NOTE\\SQLSUB\";persist security info=False;initial catalog=Northwind"; // // dataSet21 // this.dataSet21.DataSetName = "DataSet2"; this.dataSet21.Locale = new System.Globalization.CultureInfo("en-US"); this.Load += new System.EventHandler(this.Page_Load); ((System.ComponentModel.ISupportInitialize)(this.dsWebSub21)).EndInit(); ((System.ComponentModel.ISupportInitialize)(this.dataSet21)).EndInit(); } #endregion private void bindGrid() { sqlConnection2.Open(); sqlDataAdapter2.Fill(dataSet21); DataGrid1.DataBind(); //TextBox1.Text = dsWebSub21.TeacherSubs.LastNameColumn; sqlConnection1.Close(); } private void sqlDataAdapter1_RowUpdated(object sender, System.Data.SqlClient.SqlRowUpdatedEventArgs e) { } private void sqlConnection1_InfoMessage(object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e) { } private void deleteRow(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { try { ErrorMessage.Text = ""; SqlCommand delCommand = new SqlCommand(); delCommand.Connection = sqlConnection1; delCommand.CommandText = "DELETE FROM TeacherSubs WHERE LastName = '" + e.Item.Cells[0].Text + "'"; delCommand.CommandType = CommandType.Text; sqlConnection1.Open(); delCommand.ExecuteNonQuery(); sqlDataAdapter1.Fill(dsWebSub21); //Cache["CustomerData"] = dataSet1; bindGrid(); sqlConnection1.Close(); } catch (Exception ex) { ErrorMessage.Text = ex.Message; } // Code to delete the item from the data source. //DataTable aTable = (DataTable)DataList1.DataSource; //aTable.Rows[e.Item.ItemIndex].Delete(); // Bind the data after the item is deleted. //DataList1.DataBind(); } private void editRow(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = e.Item.ItemIndex; bindGrid(); } private void cancelChange(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = -1; bindGrid(); } private void DataGrid1_SelectedIndexChanged(object sender, System.EventArgs e) { } private void updateRow(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { try { ErrorMessage.Text = ""; SqlCommand updCommand = new SqlCommand(); updCommand.Connection = sqlConnection2; updCommand.CommandText = " UPDATE Employees SET LastName = '" + ((TextBox)e.Item.Cells[1].Controls[0]).Text + " WHERE EmployeeID = '" + ((TextBox)e.Item.Cells[3].Controls[0]).Text + "'"; updCommand.CommandType = CommandType.Text; sqlConnection2.Open(); updCommand.ExecuteNonQuery(); sqlDataAdapter1.Fill(dsWebSub21); //Cache["CustomerData"] = dataSet1; DataGrid1.EditItemIndex = -1; bindGrid(); sqlConnection1.Close(); } catch (Exception ex) { ErrorMessage.Text = ex.Message; } } } }