• Use INSERT to add records to a database
• Use DELETE to delete records from a database
• Assemble a SQL statement from entries in textboxes

Command Object

• Use the Command object to execute non-query SQL statements on a connection
  • INSERT 
  • DELETE
  • UPDATE
• Example
  • Assume conX has been declared as a Connection
  • Assume sSQL is a string with a SQL statement

     conX.Open()
     Dim cmdX As New OleDb.OleDbCommand(sSQL, conX)
     cmdX.ExecuteNonQuery()  

Assemble SQL Statement

• Insert a new record into a table called tblNumbers with two fields: name (text field) and number(number field).  The name is in a text box called txtname and the number is in a text box called txtnumber.

Dim sSQL as String

sSQL = "INSERT INTO tblNumbers (name, number) "

sSQL = sSQL & "VALUES ('"

sSQL = sSQL & txtname.text & "', "

sSQL = sSQL & txtnumber.text & ");"

Back to top

In this demo we will add code to INSERT and DELETE records.  We are not specifically covering the UPDATE command because it is very similar to the INSERT and DELETE commands.

Please be aware that there are many different ways to INSERT and DELETE data.  We are showing just one way.  Also, VS provides a DataForm Wizard that essentially creates an entire form for you.  However, you should understand the basics before you use the wizard so that you can modify the code if needed.

1.  Open your Unit 9 project.  We are going to modify the current form to allow you to add and remove records.  Add one button (btnAdd) and  four textboxes to the form (txtID, txtFName, txtLName, txtNumber). The user will enter the data for the new person in these textboxes and click the button to actually insert the information into the database.  While we are dealing with multiple textboxes it may be worth discussing tab order.  When a program runs, the user can usually use the tab key to move from control to control.  You can set the sequence by modifying the tabindex property of the controls.

2.  Add the following code to the click event for btnAdd:

Try
  Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
  sConn = sConn & "Data Source= " & Application.StartupPath & "\contacts.mdb;"
  Dim conX As New OleDb.OleDbConnection(sConn)
  Dim sSQL As String
  sSQL = "INSERT INTO Contacts (ID, FirstName, LastName, PhoneNumber) "
  sSQL = sSQL & "VALUES ("
  sSQL = sSQL & txtID.Text & ", '"
  sSQL = sSQL & txtFName.Text & "','"
  sSQL = sSQL & txtLName.Text & "','"
  sSQL = sSQL & txtNumber.Text & "');"
  MessageBox.Show(sSQL)
  conX.Open()
  Dim cmdX As New OleDb.OleDbCommand(sSQL, conX)
  cmdX.ExecuteNonQuery()
  conX.Close()
Catch er As Exception
  MessageBox.Show(er.Message)
End Try

3.  Test your code.  When you insert a new person you must ensure that the ID is unique since it is the primary key. 

4.  Study the code.  Notice the following.  First we create a connection.  Then we assemble the SQL statement from the information in the textboxes.  Next we display the SQL statement in a messagebox. We really don't need to use this line of code.  However, it can be useful to insert this line when we are trying to debug our program and get the SQL statement correct.  It can be difficult to write the code to assemble the SQL statement.  If you have problems creating a SQL statement it can be helpful to display it so that you can see what is wrong. Finally we create a command object, relate it to the connection and SQL statement and then execute it.  Finally we close the connection.  Generally we should close connections when we are done with them.

5.  Now we will add code to delete a record based on an ID entered by the user into an InputBox.  Add a button (btnDelete).  Add the following code to the click event for the button:

Dim i As Int16
i = InputBox("Enter the ID of the person you want to delete")
Try
  Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
  sConn = sConn & "Data Source= " & Application.StartupPath & "\contacts.mdb;"
  Dim conX As New OleDb.OleDbConnection(sConn)
  Dim sSQL As String
  sSQL = "DELETE FROM Contacts WHERE ID = " & i
  conX.Open()
  Dim cmdX As New OleDb.OleDbCommand(sSQL, conX)
  cmdX.ExecuteNonQuery()
  conX.Close()
Catch er As Exception
  MessageBox.Show(er.Message)
End Try

6.  Test your code.  Study it.  It is the same as the code for adding the record except for the SQL statement. 

 Back to top

1.  What should you usually do to a connection after you have used it?

2.  Assume that you have a table in a database named tblBirthDates with a text field Name and a date field Birthdate.  You have a form with two textboxes txtName and txtBirthdate.  Write the code needed to assemble the SQL statement needed to insert the new person into tblBirthDates where the Name is from txtName and the Birthdate is from txtBirthdate.

2.  Add a feature to your program to allow the user to change (update) the phone number for a person.

• Search for "Introduction to DataCommand Objects in Visual Studio"
• Search for "Executing Updates or Database Commands"