Updating Data in a Data Source (ADO.NET)

.NET Framework 4

Other Versions

SQL statements that modify data (such as INSERT, UPDATE, or DELETE) do not return rows. Similarly, many stored procedures perform an action but do not return rows. To execute commands that do not return rows, create a Command object with the appropriate SQL command and a Connection, including any required Parameters. Execute the command with the ExecuteNonQuery method of the Command object.

The ExecuteNonQuery method returns an integer that represents the number of rows affected by the statement or stored procedure that was executed. If multiple statements are executed, the value returned is the sum of the records affected by all of the statements executed.

Example

---

The following code example executes an INSERT statement to insert a record into a database using ExecuteNonQuery.

VB

C#

C++

F#

JScript

' Assumes connection is a valid SqlConnection.
connection.Open()

Dim queryString As String = "INSERT INTO Customers " & _
  "(CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')"

Dim command As SqlCommand = New SqlCommand(queryString, connection)
Dim recordsAffected As Int32 = command.ExecuteNonQuery()

The following code example executes the stored procedure created by the sample code in Performing Catalog Operations. No rows are returned by the stored procedure, so the ExecuteNonQuery method is used, but the stored procedure does receive an input parameter and returns an output parameter and a return value.

For the OleDbCommand object, the ReturnValue parameter must be added to the Parameters collection first.

VB

C#

C++

F#

JScript

' Assumes connection is a valid SqlConnection.
Dim command As SqlCommand = _
   New SqlCommand("InsertCategory" , connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As SqlParameter = _
 command.Parameters.Add("@RowCount", SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@CategoryName", SqlDbType.NChar, 15)

parameter = command.Parameters.Add("@Identity", SqlDbType.Int)
parameter.Direction = ParameterDirection.Output

command.Parameters("@CategoryName").Value = "New Category"
command.ExecuteNonQuery()

Dim categoryID As Int32 = CInt(command.Parameters("@Identity").Value)
Dim rowCount As Int32 = CInt(command.Parameters("@RowCount").Value) 

See Also

---

Concepts

Updating Data Sources with DataAdapters (ADO.NET)