A data from a database is deleted by using the SQL Delete statement. The syntax for a Delete statement is as follows: -

DELETE table_name WHERE condition

If, for example, you want to delete all the rows from a table named Books where the bookName column has the value ASP.NET; the following statement would be used.

DELETE Books WHERE bookName ='ASP.NET'

For the use of DELETE command follow these steps: -

1.Create and open a database connection.

2.Create a database command that represents the SQL Delete statement to execute.

3.Execute the command by calling the ExecuteNonQuery () method.

Look at the following example for executing DELETE command: -

 

Example 33 SqlDeleteDemo.aspx

<%@ Import Namespace="System.Data.SqlClient" %> <% Dim conNorthwind As SqlConnection Dim strDelete As String Dim cmdDelete As SqlCommand conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;database=Northwind" ) strDelete = "Delete Books Where bookName='ASP.NET'" cmdDelete = New SqlCommand( strDelete, conNorthwind ) conNorthwind. Open () cmdDelete. ExecuteNonQuery () conNorthwind. Close () %>

Record Deleted from the table Books!

The output of this example is shown below:

 

The first line in the above example imports the necessary namespace to work with SQL Server.

Next, a connection to the SQL Server running on the local machine is initialized.

The SqlCommand class is initialized with two parameters: a SQL Delete statement and an instance of the SqlConnection class. Next, the connection is opened, the command is executed by calling ExecuteNonQuery (), and the connection is closed.

Example 34 OleDbDeleteDemo.aspx

<%@ Import Namespace="System.Data.OleDb" %> <% Dim conNorthwind As OleDbConnection Dim strDelete As String Dim cmdDelete As OleDbCommand   conNorthwind = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Books.mdb") strDelete = "Delete from Books Where bookName='ASP.NET'" cmdDelete = New OleDbCommand( strDelete, conNorthwind ) conNorthwind. Open () cmdDelete. ExecuteNonQuery () conNorthwind. Close () %> Records Deleted from the table Books!

 

The output is shown as below:-

The first line in above example imports the necessary namespace to work with MS Access.

Next, a connection to the MS ACCESS running on the local machine is initialized.

The OleDbCommand class is initialized with two parameters: a SQL Delete statement and an instance of the OleDbConnection class.

Next, the connection is opened, the command is executed by calling ExecuteNonQuery (), and the connection is closed.

Important: - You must use Delete From rather than just Delete when working with a Microsoft Access database.

Parametric Delete

Practically we use Parametric Delete in the form of a web i.e. the parameters are passed from outside. The following example demonstrates it: -

 

ExpertRating ASP.NET Certification - Get Certified Now!!