Although the DataGrid does not have built in support for INSERTs, the DataSet and SqlDataAdapter can help automate the process. The SqlDataAdapter will automatically generate the necessary SQL statement to INSERT a new row into the underlying database table. In this project, text boxes are used to gather information about the new row. This information is then added to the dataset and and the dataset is then updated, automatically inserting the new row into the underlying database table. Note that the same text boxes are also used to filter the data grid.
SqlDataAdapter Generates the INSERT Statement
You don't need to write the SQL INSERT statement. It is generated automatically by the SqlDataAdapter wizard. If you expand the hidden wizard generated code in WebForm1.aspx.cs you can see the auto generated SQL statements:
private void InitializeComponent()
{
...
this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
...
// sqlInsertCommand1
//
this.sqlInsertCommand1.CommandText = @"INSERT INTO authors(au_id, au_lname, au_fname,
phone, address, city, state, zip, contract)
VALUES (@au_id, @au_lname, @au_fname, @phone, @address, @city, @state, @zip,
@contract);
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract
FROM authors WHERE (au_id = @au_id) ORDER BY au_id";
this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_id",
System.Data.SqlDbType.VarChar, 11, "au_id"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_lname",
System.Data.SqlDbType.VarChar, 40, "au_lname"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_fname",
System.Data.SqlDbType.VarChar, 20, "au_fname"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@phone",
System.Data.SqlDbType.VarChar, 12, "phone"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@address",
System.Data.SqlDbType.VarChar, 40, "address"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@city",
System.Data.SqlDbType.VarChar, 20, "city"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@state",
System.Data.SqlDbType.VarChar, 2, "state"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@zip",
System.Data.SqlDbType.VarChar, 5, "zip"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@contract",
System.Data.SqlDbType.Bit, 1, "contract"));
//
...
}
Adding the INSERT Event Handler
Use the IDE to drag and drop the appropriate text boxes and a button onto the form. Then rename the text boxes and buttons. For example:
textBoxLast
buttonAdd
If you double click on the "Add" button in the design view, the IDE will automatically register the button with an event handler and create a new empty event handler in the file WebForm1.aspx.cs. Here is the automatically generated registration code in the hidden "InitializeComponent" method:
this.buttonAdd.Click += new System.EventHandler(this.buttonAdd_Click);
The IDE generates an empty "buttonAdd_Click" event handler. Here is our fully implemented event handler:
protected void buttonAdd_Click(object sender, System.EventArgs
e)
{
string debug= "No errors on INSERT.";
// Insert new row into the dataset table
try
{
DataRow dr= dataSet11.authors.NewRow();
dr["au_id"]= textBoxID.Text;
dr["au_lname"]= textBoxLast.Text;
dr["au_fname"]= textBoxFirst.Text;
dr["address"]= textBoxAddress.Text;
dr["city"]= textBoxCity.Text;
dr["state"]= textBoxState.Text;
dr["phone"]= textBoxPhone.Text;
dr["zip"]= textBoxZip.Text;
dr["contract"]= textBoxContract.Text;
dataSet11.Tables[0].Rows.Add(dr);
sqlDataAdapter1.Update(dataSet11);
}
catch (Exception exc)
{
debug= exc.Message;
}
// Refresh the grid
DataGrid1.EditItemIndex = -1;
ResetPageIndex(DataGrid1, view);
DataGrid1.DataBind();
textBoxMessage.Text= debug;
}
This code simply creates a new DataRow and then fills the row with data from the text boxes. The data set is typed so that the appropriate conversion is attempted for each data assignment. Note that the data assignments are wrapped in a try catch construct. If the user enters an illegal Boolean value such as "no" in the "contract" text box, an exception will be thrown on the line:
dr["contract"]= textBoxContract.Text;
Here is the exception message:
"System.FormatException: String was not recognized as a valid Boolean.
at System.Boolean.Parse(String value)"
Wrapping the call in try catch allows you to exit the function without calling "Update". The exception message is then returned to the user in a multi-line text box. If the data assignments succeed, the row is then added to the data set:
dataSet11.Tables[0].Rows.Add(dr);
Finally, the changes to the data set are written back to the database by calling:
sqlDataAdapter1.Update(dataSet11);
Once a new row has been added to the data set, the auto generated INSERT statement will be used to insert the new row into the database. Again, note that you should wrap the Update() call in try catch, and notify the user if the Insert failed. At the end of the event handler, the data grid is refreshed form the data set by calling "DataBind" so that any successful insert is reflected in the data grid:
// Refresh the grid
DataGrid1.EditItemIndex = -1;
ResetPageIndex(DataGrid1, view);
DataGrid1.DataBind();
Note: An INSERT could fail for a number of reasons. The primary key may already exist. The data may conflict with a column constraint. The data may be missing a required value (Column NOT NULL).
Comments