How to Modify Data Using ExecuteNonQuery

Submitted by admin on Sunday, May 3, 2009 - 14:44.

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

69 views