Loading

How to Modify Data Using ExecuteNonQuery

Submitted by: 
Language: 
Visitors have accessed this post 19555 times.


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:

  1. 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:

  1. 'Set up connection string
  2. Dim cnString As String
  3.  
  4. txtStatus.Text = "Connecting to database using MS Access" & vbCrLf & vbCrLf
  5.  
  6. cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Northwind.mdb"
  7.  
  8. 'Sql statement. This will insert record into Customers table
  9. Dim sqlQRY As String = "INSERT INTO Customers (CustomerID, CompanyName, City, Region, PostalCode) Values ('AAAAA', 'ABC Company', 'Seattle', 'WA', '98128')"
  10.  
  11. 'Create connection
  12. Dim conn As OleDbConnection = New OleDbConnection(cnString)
  13.  
  14. Try
  15. ' Open connection
  16. conn.Open()
  17.  
  18. 'create command
  19. Dim cmd As OleDbCommand = New OleDbCommand(sqlQRY, conn)
  20.  
  21. 'execute non query command
  22. cmd.ExecuteNonQuery()
  23.  
  24. 'Sql statement. This will delete newly inserted record from Customers table
  25. sqlQRY = "DELETE FROM Customers WHERE CustomerID='AAAAA'"
  26.  
  27. 'execute non query command AGAIN! By this time using DELETE statement
  28. cmd.CommandText = sqlQRY
  29.  
  30. 'execute non query command. To see the changes in insert statement, comment the code below
  31. cmd.ExecuteNonQuery()
  32.  
  33. Catch ex As SqlException
  34. txtStatus.Text = "Error: " & ex.ToString & vbCrLf
  35. Finally
  36. ' Close connection
  37. conn.Close()
  38. txtStatus.Text = txtStatus.Text & vbCrLf & "Connection closed."
  39. 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

Filtered HTML

  • You may insert videos with [video:URL]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.