Create a new Web Form Page named AddCategory.aspx.

From the Server Explorer window, drag and drop a database connection to the Northwind database onto the Designer surface (adding the connection will add an instance of the SqlConnection object named sqlConnection1).

Drag and drop an SqlCommand object from beneath the Data tab in the Toolbox onto the Designer surface. Adding the SqlCommand object will create a new SqlCommand named sqlCommand1 (SqlCommand1 in the case of VB.NET).

In the Properties window, select the sqlCommand1 object and assign the value sqlConnection1 to its Connection property.

In the Properties window, assign the following SQL command to the CommandText property of the SqlCommand1 object:

Insert Into Categories (CategoryName) Values ('Books')

After you enter the value for the CommandText property, the Regenerate Parameters dialog box appears. Click No to close the dialog box.

Add a Button control to the AddCategories.aspx page.

Double-click the Button control. This will switch you to the code-behind file for AddCategories.aspx.

Enter the following code for the Button1_Click() handler:

C#. 

private void Button1_Click(object sender, System.EventArgs e)
{
  sqlConnection1.Open();
  sqlCommand1.ExecuteNonQuery();
  sqlConnection1.Close();
}

VB.NET. 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
 Handles Button1.Click
  SqlConnection1.Open()
  SqlCommand1.ExecuteNonQuery()
  SqlConnection1.Close()
End Sub

Right-click AddCategories.aspx in the Solution Explorer window and select Build and Browse.

Create a new Web Form Page named AddCategoryAccess.aspx.

From the Server Explorer window, drag and drop a database connection to the MyDB.mdb database onto the Designer surface. Adding the connection will add an instance of the OleDbConnection object named oleDbConnection1 (OleDbConnection1 in VB.NET). If a database connection to the MyDB.mdb database does not already exist, create one by right-clicking the Database Connections folder in the Server Explorer window and selecting Add Connection.

Drag and drop an OleDbCommand object from beneath the Data tab on the Toolbox onto the Designer surface. Adding the OleDbCommand object will create a new OleDbCommand named oleDbCommand1 (OleDbCommand1 in VB.NET).

In the Properties window, select the oleDbCommand1 object and assign the value oleDbConnection1 to its Connection property.

In the Properties window, assign the following SQL command to the CommandText property of the oleDbCommand1 object:

Insert Into Categories (CategoryName) Values (‘Books’)

After you enter the value for the CommandText property, the Regenerate Parameters dialog box appears. Click No to close the dialog box.

Add a Button control to AddCategoriesAccess.aspx.

Double-click the Button control. This will switch you to the code-behind file.

Enter the following code for the Button1_Click handler:

C#. 

private void Button1_Click(object sender, System.EventArgs e)
{
  oleDbConnection1.Open();
  oleDbCommand1.ExecuteNonQuery();
  oleDbConnection1.Close();
}

VB.NET. 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
 Handles Button1.Click
        OleDbConnection1.Open()
        OleDbCommand1.ExecuteNonQuery()
        OleDbConnection1.Close()
    End Sub

Right-click AddCategoriesAccess.aspx in the Solution Explorer window and select Build and Browse.

Create a new database table named Survey. Select a database (for example, Northwind) in the Server Explorer window and expand the database folder. Right-click the Tables folder and select New Table. Add the following columns to the table:

When you create the survey_id column, mark the column as an Identity column by assigning the value Yes to the column’s Identity property.

Save the table with the name Survey (click the icon of the floppy on the toolbar).

Create a new Web Form Page named Survey.aspx.

Add two HTML Label controls to the page by dragging them from under the HTML tab in the Toolbox onto the Designer surface. Enter the text Your Name for the first label and the text Comments for the second label.

Add a TextBox control to the page and assign the value txtUsername to its ID property.

Add a second TextBox control to the page and assign the value txtComments to its ID property and the value MultiLine to its TextMode property.

Add a Button control to the page.

Add a data connection to the database that contains the Survey table to the Web Form Page. Drag the data connection from the Server Explorer window onto the Designer surface. Adding the data connection automatically creates an instance of the SqlConnection object.

Add an SqlCommand object to the page by dragging the SqlCommand object from beneath the Data tab in the Toolbox onto the Designer surface. Adding the SqlCommand creates an instance of the SqlCommand object named sqlCommand1 (SqlCommand1 in VB.NET).

Associate the SqlCommand object with the SqlConnection object. In the Properties window, assign the value sqlConnection1 to the SqlCommand object’s Connection property.

Assign the following value to the SqlCommand object’s CommandText property:

Insert Into Survey (survey_name,survey_comments) values (@survey_name,
@survey_comments)

After you modify the CommandText property, the Regenerate Parameters dialog box appears. Click Yes to create the necessary parameters.

Double-click the Button control. This will switch you to the Code editor.

Enter the following code for the Button1_Click handler:

C#. 

private void Button1_Click(object sender, System.EventArgs e)
{
  sqlCommand1.Parameters["@survey_name"].Value = txtUsername.Text;
  sqlCommand1.Parameters["@survey_comments"].Value = txtComments.Text;
  sqlConnection1.Open();
  sqlCommand1.ExecuteNonQuery();
  sqlConnection1.Close();
}

VB.NET. 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
 Handles Button1.Click
  SqlCommand1.Parameters("@survey_name").Value = txtUsername.Text
  SqlCommand1.Parameters("@survey_comments").Value = txtComments.Text
  SqlConnection1.Open()
  SqlCommand1.ExecuteNonQuery()
  SqlConnection1.Close()
End Sub

Right-click the Survey.aspx page in the Solution Explorer window and select Build and Browse.

Create a new Web Form Page named SurveyAccess.aspx.

Add two HTML Label controls to the page and enter the text Your Name for the first label and the text Comments for the second label.

Add a TextBox control to the page and assign the value txtUsername to its ID property.

Add a second TextBox control to the page and assign the value txtComments to its ID property and the value MultiLine to its TextMode property.

Add a Button control to the page.

Create a new data connection to the MyDB.mdb database by right-clicking the Data Connections folder in the Server Explorer window and selecting Add Connection. Enter the connection information for the MyDB.mdb database and click OK.

Drag the new database connection from the Server Explorer window onto the Designer surface. Adding the data connection automatically creates an instance of the OleDbConnection object.

Add an OleDbCommand object to the page by dragging the OleDbCommand object from beneath the Data tab in the Toolbox onto the Designer surface. Adding the OleDbCommand creates an instance of the OleDbCommand object named oleDbCommand1 (OleDbCommand1 in VB.NET).

Associate the OleDbCommand object with the OleDbConnection object. In the Properties window, assign the value oleDbConnection1 to the OleDbCommand object’s Connection property.

Assign the following value to the OleDbCommand object’s CommandText property:

Insert Into Survey (survey_name,survey_comments) values (?,?)

After you modify the CommandText property, the Regenerate Parameters dialog box appears. Click Yes to create the necessary parameters.

Double-click the Button control. This will switch you to the Code editor.

Enter the following code for the Button1_Click handler:

C#. 

private void Button1_Click(object sender, System.EventArgs e)
{
  oleDbCommand1.Parameters["survey_name"].Value = txtUsername.Text;
  oleDbCommand1.Parameters["survey_comments"].Value = txtComments.Text;
  oleDbConnection1.Open();
  oleDbCommand1.ExecuteNonQuery();
  oleDbConnection1.Close();
}

VB.NET. 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
 Handles Button1.Click
  OleDbCommand1.Parameters("survey_name").Value = txtUsername.Text
  OleDbCommand1.Parameters("survey_comments").Value = txtComments.Text
  OleDbConnection1.Open()
  OleDbCommand1.ExecuteNonQuery()
  OleDbConnection1.Close()
End Sub

Right-click the SurveyAccess.aspx page in the Solution Explorer window and select Build and Browse.

Create a new database table named Books. In the Server Explorer window, select a database (for example, Northwind), right-click the Tables folder, and select New Table. Enter the following columns for the Books table:

Make the book_id column an identity column by assigning the value Yes to its Identity property.

Save the table with the name Books.

Right-click the Stored Procedures folder in the database that contains the Books table and select New Stored Procedure (see Figure 9.6).

Figure 9.6. Adding a new stored procedure.

Enter the following code for the stored procedure:

Create PROCEDURE dbo.InsertBooks
    (
      @title varchar(50),
      @price money
    )
AS
Insert Books
(
  book_title,
  book_price
) Values (
  @title,
  @price
)

Save the new stored procedure by selecting File, Save StoredProcedure1.

Create a new Web Form Page named InsertBooks.aspx.

Add two HTML labels to the InsertBooks.aspx page and enter the text Title into the first label and the text Price into the second label.

Add a TextBox control to the page and assign the value txtTitle to its ID property.

Add a second TextBox control to the page and assign the value txtPrice to its ID property.

Add a Button control to the page.

Double-click the Button control. This will switch you to the Code editor.

Enter the following code for the Button1_Click handler:

C#. 

private void Button1_Click(object sender, System.EventArgs e)
{
  sqlCommand1.Parameters["@title"].Value = txtTitle.Text;
  sqlCommand1.Parameters["@price"].Value = txtPrice.Text;
  sqlConnection1.Open();
  sqlCommand1.ExecuteNonQuery();
  sqlConnection1.Close();
}

VB.NET. 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
 Handles Button1.Click
  SqlCommand1.Parameters("@title").Value = txtTitle.Text
  SqlCommand1.Parameters("@price").Value = txtPrice.Text
  SqlConnection1.Open()
  SqlCommand1.ExecuteNonQuery()
  SqlConnection1.Close()
End Sub

Right-click the InsertBooks.aspx page in the Solution Explorer window and select Build and Browse.

Right-click the InsertBooks stored procedure in the Server Explorer window and select Edit Stored Procedure. Modify the procedure by adding the following Return statement:

ALTER PROCEDURE dbo.InsertBooks
    (
      @title varchar(50),
      @price money
    )
AS
Insert Books
(
  book_title,
  book_price
) Values (
  @title,
  @price
)

Return @@Identity

Save the InsertBooks stored procedure by selecting Save InsertBooks from the File menu.

Add a Label control to the InsertBooks.aspx page and clear its Text property to an empty string.

Switch to the code-behind file for InsertBooks.aspx by double-clicking the Button control.

Modify the Button1_Click handler by assigning the return value to the Label control as follows:

C#. 

private void Button1_Click(object sender, System.EventArgs e)
{
  sqlCommand1.Parameters["@title"].Value = txtTitle.Text;
  sqlCommand1.Parameters["@price"].Value = txtPrice.Text;
  sqlConnection1.Open();
  sqlCommand1.ExecuteNonQuery();
  Label1.Text = sqlCommand1.Parameters["@RETURN_VALUE"].Value.ToString();
  sqlConnection1.Close();
}

VB.NET. 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
 Handles Button1.Click
  SqlCommand1.Parameters("@title").Value = txtTitle.Text
  SqlCommand1.Parameters("@price").Value = txtPrice.Text
  SqlConnection1.Open()
  SqlCommand1.ExecuteNonQuery()
  Label1.Text = SqlCommand1.Parameters("@RETURN_VALUE").Value
  SqlConnection1.Close()
End Sub

Right-click the InsertBooks.aspx page in the Solution Explorer window and select Build and Browse.

Create a new Web Form Page named SearchBookID.aspx.

Add a TextBox control to the page.

Add a Button control to the page.

Add two Label controls to the page. Set the EnableViewState property of both Label controls to the value False, and set the Text property of both Label controls to an empty string.

Right-click the Stored Procedures folder beneath your database in the Server Explorer window and select New Stored Procedure.

Enter the following code for the body of the stored procedure:

CREATE PROCEDURE dbo.SearchBookID
    (
      @bookID int,
      @title varchar (50) OUTPUT,
      @price money OUTPUT
    )
AS
Select
  @title = book_title,
  @price = book_price
From Books
Where book_id = @bookID

Save the SearchBookID stored procedure by selecting Save StoredProcedure1 from the File menu.

Drag the SearchBookID stored procedure from the Server Explorer window onto the Designer surface. This will create an SqlConnection and SqlCommand object.

Double-click the Button control. This will switch you to the code-behind file for the SearchBookID.aspx page.

Enter the following code for the Button1_Click handler:

C#. 

private void Button1_Click(object sender, System.EventArgs e)
{
  if (TextBox1.Text != String.Empty)
  {
    sqlCommand1.Parameters["@BookID"].Value = TextBox1.Text;
    sqlConnection1.Open();
    sqlCommand1.ExecuteNonQuery();
    if (sqlCommand1.Parameters["@title"].Value != DBNull.Value )
    {
      Label1.Text = (string)sqlCommand1.Parameters["@title"].Value;
      Label2.Text = String.Format("{0:c}",
sqlCommand1.Parameters["@price"].Value);
     }
     sqlConnection1.Close();
  }
}

VB.NET. 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
  If TextBox1.Text <> String.Empty Then
    SqlCommand1.Parameters("@BookID").Value = TextBox1.Text
    SqlConnection1.Open()
    SqlCommand1.ExecuteNonQuery()
    If Not IsDBNull(SqlCommand1.Parameters("@title").Value) Then
      Label1.Text = SqlCommand1.Parameters("@title").Value
      Label2.Text = String.Format("{0:c}",
SqlCommand1.Parameters("@price").Value)
    End If
    SqlConnection1.Close()
  End If
End Sub

Right-click the SearchBookID.aspx page in the Solution Explorer window and select Build and Browse.