How to Modify Data Using ExecuteNonQuery

This tutorial is part of Database Programming Made Easy Series. 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) 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

Add new comment