How to Modify Data Using ExecuteNonQuery
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)
- ' Open connection
- 'create command
- Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, conn)
- 'execute non query command
- '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
- Catch ex As SqlException
- txtStatus.Text = "Error: " & ex.ToString & vbCrLf
- ' Close connection
- 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