How to Modify Data Using ExecuteNonQuery
This tutorial is part of Database Programming Made Easy Series.
[inline:ExecuteNonQuery.jpg=Modify Data using ExecuteNonQuery]
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