|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer|
How to: Create and Execute an SQL Statement that Returns No Value
To execute an SQL statement that returns no value, you can run a TableAdapter query that is configured to run an SQL statement (for example, CustomersTableAdapter.UpdateTableData(CustomersDataTable)).
If your application does not use TableAdapters, call the ExecuteNonQuery method on a command object, setting its CommandType property to .NET Framework Data Provider your application is using. For example, if your application is using the .NET Framework Data Provider for SQL Server, the command object would be .). ("Command object" refers to the specific command for the
The following examples show how to execute SQL statements that return no value from a database using either TableAdapters or command objects. For more information on querying with TableAdapters and commands, see.
Executing SQL Statements that Return No Values Using a TableAdapter
This example shows how to create a TableAdapter query using the, and then it provides information on how to declare an instance of the TableAdapter and execute the query.
The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see.
To create an SQL statement that returns no value using a TableAdapter
Open a dataset in the Dataset Designer. For more information, see.
If you do not already have one, create a TableAdapter. For more information on creating TableAdapters, see.
If you already have a query on your TableAdapter that uses an SQL statement that returns no value, then skip to the next procedure, "To declare an instance of the TableAdapter and execute the query." Otherwise, continue with step 4 to create a new query that returns no value.
Right-click the TableAdapter that you want, and use the shortcut menu to add a query.
The TableAdapter Query Configuration Wizard opens.
Leave the default value of Use SQL statements, and then click Next.
Choose the UPDATE, INSERT or DELETE option, and then click Next.
Type your SQL statement, or use the Query Builder to assist with creating one, and then click Next.
Provide a name for the query.
Complete the wizard; the query is added to the TableAdapter.
Build your project.
To declare an instance of the TableAdapter and execute the query
Declare an instance of the TableAdapter that contains the query you want to execute.
To create an instance using design-time tools, drag the TableAdapter that you want from the Toolbox. (Components in your project now appear in the Toolbox under a heading that matches your project name.) If the TableAdapter does not appear in the Toolbox, then you may need to build your project.
To create an instance in code, replace the following code with the names of yourand TableAdapter.
Dim tableAdapter As New DataSetTableAdapters.TableAdapter
TableAdapters are not actually located inside their associated dataset classes. Each dataset has a corresponding collection of TableAdapters in its own namespace. For example, if you have a dataset named SalesDataSet, there would be a SalesDataSetTableAdapters namespace that contains its TableAdapters.
Call your query as you would call any other method in code. Your query is a method on the TableAdapter. Replace the following code with the names of your TableAdapter and query. You also need to pass in any parameters required by your query. If you are not sure if your query requires parameters, or what parameters it requires, then check IntelliSense for the required signature of the query. Depending on whether your query takes parameters or not, the code would look similar to one of the following examples:
Queries that we think of as returning no value actually do return a value — an integer containing the number of rows affected by the query. The complete code to declare an instance of a TableAdapter and execute a query should look similar to the following:
NorthwindDataSetTableAdapters.CustomersTableAdapter tableAdapter = new NorthwindDataSetTableAdapters.CustomersTableAdapter(); int rowsAffected = tableAdapter.UpdateContactTitle("Sales Manager", "ALFKI");
Executing SQL Statements that Return No Value Using a Command Object
The following example shows how to create a command and execute an SQL statement that returns no value. For information on setting and getting parameter values for a command, see.
This example uses the SqlCommand object and requires:
References to the, , and namespaces.
A data connection named SqlConnection1.
A table named Customers in the data source that SqlConnection1 connects to. (Otherwise, you need a valid SQL statement for your data source).
To execute an SQL statement that returns no value using a DataCommand
Add the following code to a method that you want to execute the SQL statement from. Call the ExecuteNonQuery method of a command to return no value (for example,).
SqlConnection sqlConnection1 = new SqlConnection("Your Connection String"); SqlCommand cmd = new SqlCommand(); Int32 rowsAffected; cmd.CommandText = "UPDATE Customers SET ContactTitle = 'Sales Manager' WHERE CustomerID = 'ALFKI'"; cmd.CommandType = CommandType.Text; cmd.Connection = sqlConnection1; sqlConnection1.Open(); rowsAffected = cmd.ExecuteNonQuery(); sqlConnection1.Close();
SqlConnection sqlConnection1 = new SqlConnection("Your Connection String"); SqlCommand cmd = new SqlCommand(); int rowsAffected; cmd.set_CommandText("UPDATE Customers SET ContactTitle = 'Sales Manager' WHERE CustomerID = 'ALFKI'"); cmd.set_CommandType(CommandType.Text); cmd.set_Connection(sqlConnection1); sqlConnection1.Open(); rowsAffected = cmd.ExecuteNonQuery(); sqlConnection1.Close();
The application requires permission to access the database and execute the SQL statement.