Deleting SQL Server Data, ASP.net

If you have code on your ASP.NET pages that allows visitors to enter a record, or where you add records through code without their input, you will likely need to delete records from your pages. This technique shows you how to use a SQL Delete query to delete records from an SQL Server table.

This technique displays a list of employees from an SQL Server table called Employees. Visitors select a name and then click the button. When they do that the employee selected is deleted from the database.

Defined on the page is this DropDownList control that will display the employee names:

dropdownlist id="ddlEmps" datatextfield="EmpName" datavaluefield="EmpID" runat="server"

and this Button control is defined:

button
id="butDelete" text="Delete" onclick="SubmitBtn_Click"
runat="server"
/ >

When the page first loads, the following code will populate the DropDownList control:

Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
If Not IsPostBack Then
Dim DBConn as SQLConnection
Dim DBCommand As SQLDataAdapter Dim DSPageData as New DataSet
DBConn = New SQLConnection("server=localhost;" _
& "Initial Catalog=TT;" _
& "User Id=sa;" _
"Password=yourpassword;") DBCommand = New SQLDataAdapter _ ("Select LastName + ' + FirstName "
& "as EmpName, EmpID " _
& "From Employees " _
& "Order By LastName, FirstName", DBConn)
DBCommand.Fill(DSPageData, _ "Employees")
ddlEmps.DataSource = _
DSPageData.Tables("Employees").DefaultView
ddlEmps.DataBind()
End If
End Sub

and this procedure fires when the Button control is clicked:

Sub SubmitBtn_Click(Sender As Object, E As EventArgs)

When that happens, you need a Connection and a Command object:

Dim DBConn as SQLConnection Dim DBDelete As New SQLCommand

You start by connecting to the SQL Server database:

DBConn = New SQLConnection("server=localhost;"
& "Initial Catalog=TT;" _
& "User Id=sa;"
"Password=yourpassword;")

Then the SQL Delete statement that will delete the record selected in the DropDownList control is placed into the CommandText property of the Command object:

DBDelete.CommandText = "Delete From Employees " _
& "Where EmpID = " & ddlEmps.SelectedItem.Value

The connection to the database for the Command object is made though the Connection object:

DBDelete.Connection = DBConn DBDelete.Connection.Open

and the offending record is deleted:

DBDelete.ExecuteNonQuery()