How to: Delete Records in a Database
This article applies to Visual Studio 2015. If you're looking for Visual Studio 2017 documentation, use the version selector at the top left. We recommend upgrading to Visual Studio 2017. Download it here.
To delete records from a database, use the
TableAdapter.Update method or the
TableAdapter.Delete method. Or, if your application does not use TableAdapters, you can use command objects to delete records from a database (for example, ExecuteNonQuery).
TableAdapter.Update method is typically used when your application uses datasets to store data, whereas the
TableAdapter.Delete method is typically used when your application uses objects to store data.
If your TableAdapter does not have a
Delete method, it means either the TableAdapter is configured to use stored procedures, or its
GenerateDBDirectMethods property is set to
false. Try setting the TableAdapter's
GenerateDBDirectMethods property to
true from within the Dataset Designer and then save the dataset to regenerate the TableAdapter. If the TableAdapter still does not have a
Delete method, then the table probably does not provide enough schema information to distinguish between individual rows (for example, no primary key is set on the table).
Delete Records Using TableAdapters
TableAdapters provide different ways to delete records from a database depending on the requirements of your application.
If your application uses datasets to store data you can simply delete records from the desired DataTable in the DataSet, and then call the
TableAdapter.Update method. The
TableAdapter.Update method takes any changes in the data table and sends those changes to the database (including inserted, updated, and deleted records).
To delete records from a database using the TableAdapter.Update method
Delete records from the desired DataTable by deleting DataRow objects from the table. For more information, see How to: Delete Rows in a DataTable. After the rows are deleted from the DataTable, call the
TableAdapter.Updatemethod. You can control the amount of data to update by passing in an entire DataSet, a DataTable, an array of DataRows, or a single DataRow. The following code shows how to delete a record from a DataTable and then call the
TableAdapter.Updatemethod to communicate the change and delete the row from the database. (This example uses the Northwind database's
// Locate the row to delete. NorthwindDataSet.RegionRow oldRegionRow; oldRegionRow = northwindDataSet.Region.FindByRegionID(5); // Delete the row from the dataset oldRegionRow.Delete(); // Delete the row from the database this.regionTableAdapter.Update(this.northwindDataSet.Region);
' Locate the row to delete. Dim oldRegionRow As NorthwindDataSet.RegionRow oldRegionRow = NorthwindDataSet._Region.FindByRegionID(5) ' Delete the row from the dataset oldRegionRow.Delete() ' Delete the row from the database Me.RegionTableAdapter.Update(Me.NorthwindDataSet._Region)
If your application uses objects to store the data in your application, you can use the TableAdapter's DBDirect methods to delete data directly from the database. Calling the
Deletemethod removes records from the database based on the parameter values passed in.
To delete records from a database using the TableAdapter.Delete method
Call the TableAdapter's
Deletemethod, passing in the values for each column as parameters of the
Deletemethod. (This example uses the Northwind database's
If you do not have an instance available, instantiate the TableAdapter you want to use.
NorthwindDataSetTableAdapters.RegionTableAdapter regionTableAdapter = new NorthwindDataSetTableAdapters.RegionTableAdapter(); regionTableAdapter.Delete(5, "NorthWestern");
Dim regionTableAdapter As New NorthwindDataSetTableAdapters.RegionTableAdapter regionTableAdapter.Delete(5, "NorthWestern")
Delete Records Using Command Objects
The following example deletes records directly from a database using command objects. For more information on using command objects to execute commands and stored procedures, see Fetching Data into Your Application.
To delete records from a database using command objects
Create a new command object, set its
CommandTextproperties. (This example uses the Northwind database's
System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING "); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "DELETE Region WHERE RegionID = 5 AND RegionDescription = 'NorthWestern'"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close();
Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("YOUR CONNECTION STRING") Dim cmd As New System.Data.SqlClient.SqlCommand cmd.CommandType = System.Data.CommandType.Text cmd.CommandText = "DELETE Region WHERE RegionID = 5 AND RegionDescription = 'NorthWestern'" cmd.Connection = sqlConnection1 sqlConnection1.Open() cmd.ExecuteNonQuery() sqlConnection1.Close()
.NET Framework Security
You must have access to the database you are trying to connect to, as well as permission to delete records from the desired table.
Insert new records into a database
How to: Update Records in a Database
Save data from an object to a database
Overview of Data Applications in Visual Studio
Connecting to Data in Visual Studio
Preparing Your Application to Receive Data
Fetching Data into Your Application
Bind controls to data in Visual Studio
Editing Data in Your Application