Lesson 1

Data in the VirtualBookShelf database will change. Inventory and customers will be added and removed, quantity on hand, prices, customer information, and more will change. Therefore, Web pages are needed to provide these functions to users.
This module discusses how to use the recordset and data-bound Design Time Controls to:

1. Add an Update button to update existing data
2. Add an Insert button to insert new rows in a table
3. Add a Delete button to remove a table row

You will be surprised at just how little work is required to add these features to a Web page using Visual InterDev's drag-and-drop features.
Dragging and dropping Design Time Controls is quick and easy. However, you are not required to use them. In some cases you may want to use script instead. You will also see in this module how to use script directly with the Data Environment rather than using Design Time Controls.

Altering a Record Set in SQL Server 2022 Using Data-Bound Design-Time Controls
In SQL Server 2022, you can alter a record set (update, insert, delete) using data-bound design-time controls in tools like Microsoft Visual Studio or SQL Server Management Studio (SSMS). These controls provide an easy way to interact with the database without writing complex SQL queries.
1. Using Visual Studio with Data-Bound Controls
Steps to Alter a Record Set Using a Data-Bound Control

1. Create a New Windows Forms or ASP.NET Project
   • In Visual Studio, create a WinForms, WPF, or ASP.NET Web Forms project.
2. Connect to SQL Server Database
   • Go to Server Explorer &arr; Right-click Data Connections &arr; Add Connection.
   • Select Microsoft SQL Server, enter server details, and select the database.
3. Add a Data Source
   • Go to Data &arr; Add New Data Source.
   • Choose Database, then Dataset.
   • Select your SQL Server database and tables/views.
4. Drag and Drop Data-Bound Controls
   • Drag a DataGridView, ListView, or DetailsView onto the form.
   • Set the DataSource property to the dataset or a table.
5. Enable Editing, Insertion, and Deletion
   • Click on the DataGridView/DetailsView control.
   • Set Allow Editing, Allow Deleting, and Allow Inserting to True.
6. Bind the Data Source to Update Automatically
   • Open the TableAdapter Configuration Wizard.
   • Ensure that "Generate Insert, Update, and Delete statements" is enabled.
   • Click Finish.
7. Test the Application
   • Run the application and try editing the data in the DataGridView.
   • The changes should be automatically reflected in SQL Server.

2. Using SQL Server Management Studio (SSMS) - Design Mode
If you are working within SQL Server Management Studio (SSMS), you can directly alter records using the table designer.
Steps to Modify Records Using SSMS Table Designer

1. Open SSMS and Connect to SQL Server 2022.
2. Navigate to Your Database
   • Expand Tables.
3. Right-Click the Table
   • Select Edit Top 200 Rows.
4. Modify Data Directly in the Grid.
5. Press Enter or Move to Another Row to Save Changes.
6. For Structural Changes (Adding Columns, Changing Data Types):
   • Right-click the table &arr; Select Design.
   • Modify columns as needed.
   • Click Save.

3. Using SQL DataSource Control in ASP.NET Web Forms
For web applications, you can use the SQLDataSource control for editing record sets.
Steps to Alter a Record Set Using SQLDataSource

1. Drag a `SqlDataSource` Control
   • onto the ASP.NET page.
2. Configure the DataSource
   • Click Configure Data Source.
   • Choose your SQL Server connection.
   • Select Table and Enable Insert, Update, Delete options.
3. Drag a `GridView` Control
   • onto the Page.
4. Bind `GridView` to `SqlDataSource`.
5. Enable Editing in GridView
   • Set AutoGenerateEditButton="True".
6. Run the Page and Modify Records.

Example Markup

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:YourDBConnectionString %>"
    SelectCommand="SELECT * FROM Employees"
    UpdateCommand="UPDATE Employees SET Name=@Name WHERE EmployeeID=@EmployeeID"
    DeleteCommand="DELETE FROM Employees WHERE EmployeeID=@EmployeeID">
</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" 
    AutoGenerateColumns="True" 
    AutoGenerateEditButton="True" 
    AutoGenerateDeleteButton="True" 
    DataSourceID="SqlDataSource1">
</asp:GridView>

Conclusion

• For Windows Apps: Use DataGridView with a Dataset and TableAdapter.
• For Web Apps: Use ASP.NET GridView bound to an SQLDataSource.
• For SSMS: Use Edit Table Mode for quick manual edits.

In the next lesson, you will learn how to update existing data using a recordset object and bound controls.