Working with an Access DB in VB.Net

Part 3 - Data tables and rows

Editing rows in a dataset is quite straight forward once you've done it a few times. First you need to declare a row.

Dim CustomerRow() As Data.DataRow

Notice the lack of the 'new' keyword here. We are not creating a new row, just creating a reference to a row which in a minute will equal the row we want to edit. Now make that row equal the row we want to edit.

CustomerRow = ds.Tables("Customers").find("1")

This sets the row to equal the row in the "Customers" table in the dataset that has a primary key equal to "1". Notice that in the test database the data type for the primary key column is set to text, hence why the value is enclosed in quotes which signifies a string (text).

Now that we have a reference to the row in our dataset that we want to edit we can start to change its values, for example.. CustomerRow(0)("first_name") = "Florance" CustomerRow(0)("Last_name") = "Smith" And lastly, and very importantly we need to call the 'Endedit' method of the row.

CustomerRow.Endedit()

This signifies that we have finished editing the row and that it is ready to be written back to the database. We'll cover applying these to the database in a minute.

Deleting rows...

This is quite similar to the above up to a point, and again we first create a reference to a data row.

Dim CustomerRow() As Data.DataRow

And again we make it equal the row we are looking for

CustomerRow = ds.Tables("Customers").find("1")

But now we have to do something different.

CustomerRow.delete()

This calls the delete method on the data row and marks it for deletion in the dataset.

CustomerRow.Endedit()

Then again we must call the Endedit method on the row Editing the data in a dataset - Adding new rows Once more we need to create a new row, but this time we do it slightly differently..

Dim newrow As Data.DataRow = ds.Tables("Customers").NewRow

This creates a new data row called newrow and makes it equal to a new row in the 'Customers' table in our ds dataset.

Now we can fill in some of the fields in the new row.. newrow.Item(0) = "2" This will set the first field of the new row to equal '2'. Alternately you can fill in the fields by specifying the name of each field like this.. newrow.Item("id") = "2" Once you have populated all the fields you then need to add the new row to the dataset.

ds.Tables("Customers").Rows.add(newrow)

And last but not least, the ever important...

newrow.Endedit()

Next we'll show you how to apply the changes in the dataset back to the live database.

Part 4 : Committing changes

Part 2 : Creating a dataset and data adapter