SQL Server Ad Hoc Queries Page - 2

Page 2 of 2

Author : Exforsys Inc. Published on: 9th Jul 2005 | Last Updated on: 24th Dec 2007

SQL Server Ad Hoc Queries

Now press F5 to execute the program. You will see the form like the screenshot below:

Enter a product category and press Insert. The console window will appear and show all the products including the one just inserted. The display will be like the one in the screenshot below.

The UPDATE Statement

UPDATES may be required whenever an user commits an error or wishes to enter data in a field where data was omitted to be entered or wishes to merely change the value of a field. Let us assume that the value “ship” was wrongly entered in place of “Maritime Equipments”. Now the user wishes to update this field with the correct information. Let us work out this example and learn how we can do this.

1.2.The window will look like the screenshot shown below:

To update the field the user will create a code as below:

Click here to view sample code

Note that this code is similar to the code we wrote for INSERT. The difference being that we use the UPDATE command instead of the INSERT command in the code.

Caution: Please note that using a column like name which can contain duplicates will cause more rows than you originally intended to be deleted or updated.

The output for the program in the Quick Console is shown below. Please note that ProductCategory Name for ID No: 5, has been changed to Maritime Equipments.

The DELETE Statement

Sometimes data entry errors can occur due to entry of duplicate values or completely wrong entries. The user will need to delete such entries. The DELETE command is used for this purpose. In the illustration above, we added Maritime Equipments as a product category. Now we find that we do not need this category. We shall use the DELETE statement to delete the category. However, it should be noted that if there are other constraints it may be difficult to delete a particular item, row or column.

Create a new Windows Application in Visual Basic Express 2005 Beta2 IDE and
Add two labels, a TextBox and two Command Buttons as shown below:

Ads

The user will have to replace the UPDATE statement of the previous code with the DELETE statement in the code. Now press F5 to execute the program. In the window that pops up, enter the category name that you want to delete. Please note that using a column like name which can contain duplicates, will cause more rows than you originally intended, to be deleted or updated. The Quick Console will show that the row has been deleted: