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;
}
}
}
}