Updating with SQL

The simplest way to update the database is to generate a SQL insert, update, or delete statement, and execute it using the Command object's ExecuteNonQuery method. For example, you can insert a few records into one or more tables, edit existing rows, and delete rows, all with the appropriate SQL statements.

To illustrate the use of the ExecuteNonQuery statement, you'll use Visual Studio .NET to create a simple form that will display the current records in a listbox. This will be a very simple user interface to keep the focus on SQL rather than on interaction with the control.

Choose whichever language you feel most comfortable using, and name the project BugHistoryHandEdits. Drag a listbox onto the form and make it wide. Add a textbox below and three buttons to the right, as shown in Figure 20-1.

Hand-edits form

Figure 20-1. Hand-edits form

Name the ListBox lbBugs and the textbox txtDescription. Clear the Text property of the TextBox. Name the three buttons btnAdd, btnEdit, and btnDelete. Stretch the three buttons and modify their text fields to say Add Record, Edit Record, and Delete Record, respectively. You may want to set their backColor to pale green, yellow, and red. Add a textbox and be sure to set its text field to blank.

Fill the listbox with a stored procedure: spBugsNoHistory, as shown in Example 20-1. (See Sidebar 20-1.)

Example 20-1. SpBugsNoHistory

CREATE PROCEDURE spBugsNoHistory ...

Get Programming .NET Windows Applications now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.