Tutorial on Editing, Inserting and Displaying data using Datasets

[Denaes]

This "tutorial" assumes that you already know how to populate your dataset in some way and is already working with a full dataset.

 

Note: Personally I do my work with Typed Datasets which signifigantly shorten the code and reduce errors when dealing with a dataset. This code works 100% with my Typed DataSet, I hope I didn't introduce any errors by working with an untyped dataset.

 

Displaying Data

 

The easiest way to display data is to set the datasource of a control to the datatable:

 

Listbox.Datasource = DataSet.Tables("TableName") 

 

and choosing a DisplayMember:

 

Dataset.DataMember = DisplayMember = DataSet.Tables("TableName").Columns("ColumnName").ToString

 

The DataSource is pointed to a DataTable to display. In some cases, like a DataGrid, the control can display all the columns and no DisplayMember is required. In a ListBox, however, you can only display one column. The DataMember is the DataColumn you wish to display.

 

Creating a DataBound control is a lot easier for viewing. I personally have learned to not fully trust it for editing and adding new data.

 

Once you databind a control, you loose some of the control's properties. A DataBound ListBox cannot use the .Sorted property to alpabetize the data. You also cannot play with the .Items collection without throwing an exception.

 

When I want to "play" with my data, like adding new rows, editing and deleting (In the work I do, deleting is a four letter word and isn't done lightly, ie only the Admin will do it, not the end user), I use a different method of binding the data to a control.

 

This is the display procedure I'm using currently to display data into the textboxes for viewing:

 

   Private Sub DisplayDataset()
       'Me.BindingContext(DataTable).Position is a Property that returns 
       'the current index of the DataTable. As a property it can also be set
       'to another value and changing the current index. 
       With DataSet.Tables("Customers").Rows(Me.BindingContext(Me.DataSet.Tables("Customer")).Position)
           'See, instead of being databound to a column automatically,
           'we're just giving the textbox the text from a column in the
           'row that is currently selected.
           Me.txtCustLastName.Text = .Item("LastName")
           Me.txtCustFirstName.Text = .Item("FirstName")
           Me.txtCustPhone.Text() = .Item("PhoneNumber")
       End With
   End Sub

 

That shows us how we're going to display our data. Here is how we're going to Load our data:

 

'This goes in the Form Load event (or a button event), which loads the data for the end user to see.

'This sets the current index to 0, the first record.
Me.BindingContext(DataTable).Position = 0
'This calls the procedure to display our data
DisplayDataset()

 

Navigation is just as easy:

 

'First Record
Me.BindingContext(DataTable).Position = 0

'Last Record
Me.BindingContext(DataTable).Position = DataTable.Count-1

'Next Record - gets a little trickier
With Me.BindingContext(DataTable)
           If .Position = .Count - 1 Then
               'Position cannot become greater than the number of records.
           Else
               .Position += 1
               UpdateDisplay()
           End If
       End With

'Previous Record - more of the same, just in the 
'opposite direction
With Me.BindingContext(DataTable)
           If .Position = 0 Then
               'Position cannot become < 0
           Else
               .Position -= 1
               UpdateDisplay()
           End If
       End With

 

Of course after every change of the BindingContext.Position, you need to update your display by calling DisplayDataset

 

Adding a New Record

 

First off, when someone attempts to add a new record, there are normally a few things you want to do.

 

1. Disable Navigation. You normally don't want the user to change the record.

2. Clear out old data. Just a simple procedure to get rid of the data in each of the textboxes will suffice.

3. Possibly a confirmation of some sort, like a MsgBox.

4. Check the data being entered. Obviously you can't enter a 500 character string into a 50character field of a database, you may not want text in the phone number field, etc.

 

Ok, now the meat of the procedure. We have the data we want in the input form. The LastName is in the txtCustLastName textbox, the phone number is in the proper textbox. The data has been checked to make sure it will go into your database properly (if applicable)

 

   Private Sub SaveNewRow()
       'I use a datarow to store the changes.
       'This allows for later uses of verification, 
       'accepting and rejecting a seperate DataRow
       'Rather than adding directly to the DataSet.
       Dim NewRow As DataRow

       'This sets the NewRow to an instance of the 
       '"Customers" table's .NewRow method. 
       NewRow = dDataSet.Tables("Customers").NewRow
       'This is exactly the opposite of what we have
       'done to populate our textboxes. 
       'Instead of setting the textbox to be equal to the 
       'Field in the DataTable, we're setting the Field of
       'the DataRow to be equal to the text in the textbox.
       With NewRow
           .Item("LastName") = Me.txtCustLastName.Text
           .Item("FirstName") = Me.txtCustFirstName.Text
           .Item("PhoneNumber") = Me.txtCustPhone.Text()
       End With

       'If needed, this is were final verification of the data, 
       'checking for errors, a chance to accept/reject the
       'DataRow.

       'And we insert our new DataRow into the Customers
       'DataTable.
       DataSet.Tables("Customers").Rows.Add(NewRow)

       'This would be a good place to update the DataSource
       '(Database) unless you want to make multiple additions
       ' and edits before making a single save. 
   End Sub

Edited March 16, 2004 by Denaes

[Denaes]

Editing the DataTable

 

Editing the DataTable

 

Private Sub EditRow()
       'Notice how this looks nearly identical to 
       'The NewRow Sub? Same idea, but a few minor 
       'tweaks.

       'I use a datarow to store the changes.
       'This allows for later uses of verification, 
       'accepting and rejecting a seperate DataRow
       'Rather than adding directly to the DataSet.
       Dim CurrentRow As DataRow

       'CurrentRow is set to be equal to the current record, 
       'by way of getting its position as we did earlier.
       CurrentRow = DataSet.Tables("Customers").Rows(Me.BindingContext( _
           Me.DataSet.Tables("Customers")).Position)

       'This is exactly the opposite of what we have
       'done to populate our textboxes. 
       'Instead of setting the textbox to be equal to the 
       'Field in the DataTable, we're setting the Field of
       'the DataRow to be equal to the text in the textbox.
       With CurrentRow
           'Because its an edit, you want to start your edit
           'with the .BeginEdit method to tell the datarow that 
           'editing has begun.
           .BeginEdit()
           .Item("LastName") = Me.txtCustLastName.Text
           .Item("FirstName") = Me.txtCustFirstName.Text
           .Item("PhoneNumber") = Me.txtCustPhone.Text()
           'This signifies the end of the edit. 
           'This also, without a doubt, tells the dataset that
           'a change has been made. 
           'As an alternative, .CancelEdit will undo the edit.
           .EndEdit()
       End With

       'If needed, this is were final verification of the data, 
       'checking for errors, a chance to accept/reject the
       'DataRow.

       'And we insert our new DataRow into the Customers
       'DataTable.
       DataSet.Tables("Customers").Rows.Add(NewRow)

       'This would be a good place to update the DataSource
       '(Database) unless you want to make multiple additions
       ' and edits before making a single save. 
   End Sub