How to Add a New Record

Adding a new record is slightly more complex. First, you have to add a new Row to the DataSet, then commit the new Row to the Database. 

But the Add New Record button on our form is quite simple. The only thing it does is to switch off other buttons, and clear the textboxes, ready for a new entry. Here's the code for your Add New Record button:

btnCommit.Enabled = True
btnAddNew.Enabled = False
btnUpdate.Enabled = False
btnDelete.Enabled = False

txtFirstName.Clear()
txtSurname.Clear()

So three buttons are switched off when the Add New Record button is clicked, and one is switched on. The button that gets switched on is the Commit Changes button. The Enabled property of btnCommit gets set to True. But, for this to work, you need to set it to False when the form loads. So return to your Form. Click btnCommit to select it. Then locate the Enabled Property in the Properties box. Set it to False. When the Form starts up, the button will be switched off.

The Clear/Cancel button can be used to switch it back on again. 

So add this code to your btnClear:

btnCommit.Enabled = False
btnAddNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True

inc = 0
NavigateRecords()

We're switching the Commit Changes button off, and the other three back on. The other two lines just make sure that we display the first record again, after the Cancel button is clicked. Otherwise the textboxes will all be blank.

To add a new record to the database, we'll use the Commit Changes button. So double click your btnCommit to access its code. Add the following:

If inc <> -1 Then

Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow

dsNewRow = ds.Tables("AddressBook").NewRow()

dsNewRow.Item("FirstName") = txtFirstName.Text
dsNewRow.Item("Surname") = txtSurname.Text

ds.Tables("AddressBook").Rows.Add(dsNewRow)

da.Update(ds, "AddressBook")

MsgBox("New Record added to the Database")

btnCommit.Enabled = False
btnAddNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True

End If

The code is somewhat longer than usual, but we'll go through it.

The first line is an If Statement. We're just checking that there is a valid record to add. If there's not, the inc variable will be on minus 1. 

Inside of the If Statement, we first set up a Command Builder, as before. The next line is this:

Dim dsNewRow As DataRow

If you want to add a new row to your DataSet, you need a DataRow object. This line just sets up a variable called dsNewRow. The type of variable is a DataRow.

To create the new DataRow object, this line comes next:

dsNewRow = ds.Tables("AddressBook").NewRow()

We're just saying, "Create a New Row object in the AddressBook DataSet, and store this in the variable called dsNewRow." As you can see, NewRow() is a method of ds.Tables. Use this method to add rows to your DataSet.

The actual values we want to store in the rows are coming from the textboxes. So we have these two lines:

dsNewRow.Item("FirstName") = txtFirstName.Text
dsNewRow.Item("Surname") = txtSurname.Text

The dsNewRow object we created has a Property called Item. This is like the Item property you used earlier. It represents a column in your DataSet. We could have said this instead:

dsNewRow.Item(1) = txtFirstName.Text
dsNewRow.Item(2) = txtSurname.Text

The Item property is now using the index number of the DataSet columns, rather than the names. The results is the same, though: to store new values in these properties. We're storing the text from the textboxes to our new Row.

We now only need to call the Method that actually adds the Row to the DataSet:

ds.Tables("AddressBook").Rows.Add(dsNewRow)

To add the Row, you use the Add method of the Rows property of the DataSet. In between the round brackets, you need the name of your DataRow (the variable dsNewRow, in our case).

You should know what the rest of the code does. Here's the next line:

da.Update(ds, "AddressBook")

Again, we're just using the Update method of the Data Adapter, just like last time. The rest of the code just displays a message box, and resets the button.

But to add a new Row to a DataSet, here's a recap on what to do:

• Create a DataRow variable
• Cretae an Object from this variable by using the NewRow() method of the DataSet Tables property
• Assign values to the Items in the new Row
• Use the Add method of the DataSet to add the new row

A little more complicated, but it does work! Try your programme out. Click your Add New Record button. The textboxes should go blank, and three of the buttons will be switched off. Enter a new First Name and Surname, and then click the Commit Changes button. You should see the message box telling you that a new record has been added to the database. 

To see the new record, close down your programme, and run it again. The new record will be there.

In the next part, you'll learn how to delete a record from the database.