Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  .NET Newbie  »  ADO.NET for Beginners Part Two  »  Editing and Deleting data from the Contacts table
 »  Home  »  Data Programming  »  ADO.NET  »  ADO.NET for Beginners Part Two  »  Editing and Deleting data from the Contacts table
ADO.NET for Beginners Part Two
by David Jeavons | Published  07/18/2006 | .NET Newbie ADO.NET | Rating:
Editing and Deleting data from the Contacts table

Now that we know how to add data to the Contacts table, we can look at the SQL syntax for updating an existing record. When updating records we use the UPDATE syntax which takes the form of:

 UPDATE tableName SET fieldName=Value, secondFieldName=Value WHERE fieldName=Criteria

The above would update two fields in a specific table to specific values using a WHERE clause to limit the number of rows affected. If you wanted to update the entire table then you would omit the WHERE clause.

So, to put this into the context of our AddressBook application, the SQL we will need to add to our Save button on the ManageData form for performing an update would be:

The entire save routine should therefore look like:

As you can see in the above, the code has not changed that much from the previous version that simply creates a new record. This is because the ExecuteNonQuery method of the OleDbCommand object executes action queries.   Both the INSERT INTO and UPDATE satatements are action queries, so we need only supply the correct SQL statement to the ExecuteNonQuery method for it to function correctly.

Before we can test the new update routine, we again have to attach the code to the Edit button on our Main form to show the ManageData form. This time however, we also need to use the ContactID property on the ManageData form to notify the save routine that

a) it is editing a record and

b) which record to update.

Add the following code to the Edit button of your main form:

The above code is very similar to that found in the Add button's click event with the difference being that in Line 54 we are updating the ContactID property of the ManageData form to the value selected in the dgContacts grid.   One point to make about the syntax above is that the dgContacts.Item method requires that you pass it a row index and a column index. The row index is easily obtained by using the dgContacts.CurrentRowIndex property and for the purpose of this tutorial the ContactID column is the first column in the returned data so is referred to by its index of zero. Also the dgContacts.Item property has a return type of object so in order to use it properly, it has to be cast to an Integer value; hence the CType function.

If you launch the program and select a row in the dgContacts grid that you want to edit and make some changes before then pressing the Save button, you should then see your changes reflected in the dgContacts grid. Now there is a slight problem with the current setup.  Currently, when you press the Edit button, the ManageData form is displayed but the fields on the ManageData form are blank.

This is not an ideal solution for the user as they may only want to edit one field, but with the current implementation, they would have to supply values for all fields otherwise they will be updated to empty strings. So we need to add a method in our ManageData form that will retrieve the data from the Contacts table based on the value of the _contactID variable that was passed from the main form. The method that we will create will be very similar to that we used to retrieve all Contacts for our DataGrid, the only difference being that we only need to retrieve the record that matches the _contactID passed. Add the following method to the ManageData form:

The first thing to note with the above routine is that it is marked Private. This is because I don't see a reason for the main form to ever need to invoke this method. The second thing to note is the inclusion of a new object that we haven't discussed yet, namely the OleDbDataReader.

There are many ways of reading data from a database, such as using an OleDbDataAdapter and using the Fill method to populate a DataSet/DataTable as we have already seen.  We can then enumerate through the DataTable should we wish to retrieve specific information and show the values in controls on the form.  However, the overhead required to create a DataSet or DataTable is relatively large compared to using a simple OleDbDataReader to access a forward only cursor to the data.

What do I mean by forward only cursor? Well, the OleDbDataReader can only be used in a forward only fashion, which means that you can enumerate through the OleDbDataDataReader using a Do Loop but once you have read a record, you cannot go back to a previous record. This is different to a DataTable as you have access to its Rows property which allows you to specify any particular row of data at any given time. The OleDbDataReader however, is much quicker in this scenario as all we are interested in is a single row of data.

Now that we have the RetrieveContactDetails method in place, we need to call it from somewhere. I think the best place to call this for our example is in the Set method of the ContactID property as the Set method is only called when we are about to edit an existing record. You will need to modify the Set method of the ContactID property to the following:

Now if you launch the program and select a row to edit, you will see that the values from the Contacts table are populated into the fields on the ManageData form. This is a more desirable interface for the user.

Deleting Records

This section is the shortest of all of the sections as you now have enough knowledge of executing action queries that I need not explain in too much detail how to execute a DELETE query. The syntax of the DELETE query takes the form:

 DELETE FROM tableName WHERE fieldName=fieldValue

It goes without saying that when you write your DELETE statement, don't forget to add the WHERE clause, otherwise you will delete every record from the table. If this is your intention then fine, but I have on occasion found myself going to a backup of a database because I forgot to add the WHERE clause to limit the number of rows deleted.

Add the following code to your Delete button:

The code above is similar to the Edit button's code in that we grab the ContactID value from the dgContacts grid by use of it's Item property.

The code we have just looked at in the previous section and this section is now enough for you to be able to fully manage your database table. You can retrieve data, update and add data and delete data from the table.

Sponsored Links