Article Options
Premium Sponsor
Premium Sponsor

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

Before we look at the code to add new data to our Contacts table, we will need to add some logic to the ManageData form to allow us to know whether we are adding a new record or editing an existing record. The simplest way of achieving this would be to add a property to the ManageData form that will accept a ContactID value. Then in our Save button code, we can determine if we have a ContactID or not (new record or existing record).   This also serves another purpose as when we are editing an existing record we will need to have the ContactID in order to update the correct record in the Contacts table.

Adding the ContactID Property

Add the following code to the ManageData form:

If you are unfamiliar with the above syntax or properties in general then the following is a quick overview.

The above property has a name of ContactID and works very much like a function>   There is a Get Method which allows you to retrieve the value of the _contactID variable and a Set method which takes a Value parameter which is assigned to the _contactID variable.

You will notice that the _contactID variable is marked as Private.   This is to ensure that it is not visible outside of the form.   Of course this could have been marked as Public but by doing so will mean that you have no method whereby you could validate the data coming in before then assigning it to the variable.

By using properties, if you wanted to limit the ContactID value to a number between 1 and 100 then you could add this validation within the Set method.    If it fell within the range then the value could be assigned to the _contactID variable, otherwise you could throw an exception or set the _contactID variable to a default value.

OleDbCommand Object

In order to execute Action queries (Action queries being those that modify data) we need to use the OleDbCommand object. If you open the Object Browser and drill down to the System.Data object and then further down to the System.Data.OleDb object, you will see that the first object listed is the OleDbCommand object. As you can see from its Summary field, it states that the OleDbCommand object "Represents an SQL statement or stored procedure to execute against a data source".  This is exactly what we will be doing in a moment.

Firstly, however, take a moment to look at the ExecuteNonQuery() method. This method will execute an SQL Statement or stored procedure against the data source and return the number of rows affected by the query. This is the method that we will be using in the code that follows.

Creating the code to Add new data to the Contacts table

Add the following code to the Save button on the ManageData form:

The above code creates a new connection to the AddressBook database and declares an empty string variable called sql. Then in line 41, the _contactID variable is checked to determine if we are adding a new record (later you will see that we also use this check to determine if we are editing an existing record). Once it is determined that we are adding a new record, the sql string variable is then assigned an INSERT INTO sql statement (more on this in a moment). Line 52 then opens the database connection as you have seen in the previous part of this article.

Now line 54 and 55 are the most important parts of this code block as they are responsible for doing the work of modifying the database. First, line 54 declares a new command object and passes to it's constructor the SQL statement that we want the command object to work with and the connection object that it will work against. Line 55 then calls the command object's ExecuteNonQuery method which all going well will then execute the query and write the data to the Contacts table. As previously stated, the ExecuteNonQuery method returns the number of rows affected by the query, so assuming that there were no problems with the above code, the returned value should be equal to 1 as one row of data should have been added to the Contacts table. You can assign the returned value to an Integer variable should you wish to notify users of how many rows were affected. Another important point is that if no rows were affected by the query then the return value will be equal to -1. Finally, Lines 57 and 60 simply close the connection and the form.

In the above code, we used an INSERT INTO SQL statement to write the contents of our three text boxes to the Contacts table. The INSERT INTO statement can be wrote in a number of ways, for example, if you were writing to every single field in the Contacts table then you could have simply wrote:

 INSERT INTO Contacts (fieldvalue, fieldvalue, fieldvalue etc..)

However, two reasons why we are not doing this in the above example. The first and most obvious reason is that we are only concentrating on three fields for this example so we must explicitly state which fields we want to write to, hence the syntax:

 INSERT INTO Contacts (Title, FirstName, LastName) VALUES (etc.)

The other reason however, is that the Contacts table contains a primary key field called ContactID which is an AutoNumber field (meaning that it increments by one each time a new record is added) and this AutoNumber field should be handled by the database, not us. For this reason, if we did not include the list of fields to write to, the database would expect us to supply a field value for the ContactID field which we certainly do not want to do.

You will also notice with the above INSERT INTO syntax that each text box is enclosed within single quotes. This is because the desired output of the SQL string should look like:

 INSERT INTO Contacts (Title, FirstName, LastName) VALUES ('Mr', 'Joe', 'Bloggs')

However, depending on the data type of field you are writing to, you may not need to enclose the field value in quotes. The following table shows the generic Data Types and the characters that should be used to enclose the field values. The table also includes the characters to be used when working with SQL Server data:

Data Type Access Character SQL Server Character 
     
String Apostraphe (') Apostraphe (')
Numeric No character No character
Date Hash (#) Apostraphe (')

So assuming that we had a table containing three fields called FirstName, Age and DOB, to write to these fields our SQL syntax (for Access) would need to look like:

 INSERT INTO TableName (FirstName, Age, DOB) VALUES ('Dave', 18, #01/01/1998#)

These rules apply throughout all SQL queries, not just the INSERT INTO statement.

A further tip that catches many people out is to ensure that your string values are properly formatted to avoid breaking the SQL string. By this I refer to values that contain an apostraphe such as the Surname O'Reilly. If you imagine that you are writing to a field with this value the SQL String will look like:

 INSERT INTO TableName (LastName) VALUES ('O'Reilly')

which will cause a syntax error as the query will stop after the O' and treat Reilly as an improperly formatted string value. In order to avoid this, you must escape any apostrophe characters within the string value by doubling them up so that the SQL string would look like:

 INSERT INTO TableName (LastName) VALUES ('O''Reilly')

this is easily achieved by using the strings Replace method and replacing all single apostrophes with double apostrophes. For example:

 

Trying out the new Add Data code

Before we can try the new functionality out, we need to modify our main form so that we can load the ManageData window. Place the following code into the Add button's click event:

You should now be good to go. Launch the program and press the Add button and enter some information into the three fields. Once you press the Save button, you should then see the new data refreshed in the grid.

Sponsored Links