Submitted by admin on Sunday, May 3, 2009 - 14:44.
This tutorial is part of Database Programming Made Easy Series.
In my Hotel Reservation System (VB .NET version) I’m using ExecuteNonQuery sometimes if the data to be passed to the data source is small. Like inserting or updating a single row. You can use ExecuteNonQuery even if you want to delete multiple rows.
I recommend modifying a data using Dataset if the statement is too complicated or contains a lot of fields.
An example of using ExecuteNonQuery:
ExecNonQuery("INSERT INTO Customers (CustomerID, LastName, FirstName) Values (" & CustomerID & ", '" & txtLastName.Text & "', '" & txtFirstName.Text & "')")
This code was taken from hotel reservation system under “Check In” form and within the SaveRecord procedure. Take note that ExecNonQuery is just a Function.
Going back from our sample code let us use the code from Data Reader and remove unnecessary line. The code to execute a non query command should look like this:
'Set up connection string
Dim cnString As String
txtStatus.Text = "Connecting to database using MS Access" & vbCrLf & vbCrLf
cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb"
'Sql statement. This will insert record into Customers table
Dim sqlQRY As String = "INSERT INTO Customers (CustomerID, CompanyName, City, Region, PostalCode) Values ('AAAAA', 'ABC Company', 'Seattle', 'WA', '98128')"
'Create connection
Dim conn As OleDbConnection = New OleDbConnection(cnString)
Try
' Open connection
conn.Open()
'create command
Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, conn)
'execute non query command
cmd.ExecuteNonQuery()
'Sql statement. This will delete newly inserted record from Customers table
sqlQRY = "DELETE FROM Customers WHERE CustomerID='AAAAA'"
'execute non query command AGAIN! By this time using DELETE statement
cmd.CommandText = sqlQRY
'execute non query command. To see the changes in insert statement, comment the code below
cmd.ExecuteNonQuery()
Catch ex As SqlException
txtStatus.Text = "Error: " & ex.ToString & vbCrLf
Finally
' Close connection
conn.Close()
txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed."
End Try
As you can see the only difference is the cmd.ExecuteNonQuery() and its SQL Statement above after the connection string “cnString”.
ExecuteNonQuery command is very useful if you want a quick way of modifying a data in your data sources. Just replace the INSERT command to something like UPDATE or DELETE command if you want to execute update or delete statement.
Previous: How to Modify Data
Next: How to Modify Data Using Datasets and Data Adapters