Visual Basic .NET Nightmare or Guide to upgrading a Guru.

Page 10

The Data Monster

  1. Click on connect to database.  When the data link properties window comes up it automatically jumps to the connection tab with a pre selected Provider. It's as if they don't think we would use a different one.  It used to be ODBC now it's SQL Server. Let's select MSJet.  You can modify your connection type when you work on your production project versus this lesson.

  2. Now click the connections tab or you can click next progressively and select the Northwind DB from wherever it is hiding on your computer.

  3. Click Test connection, make sure it's working. Then click okay.

SELECT ContactName, Phone FROM Customers

  1. The ConnectionString belonging to the DBConnection component.  This is similar to our SQL statement, in that it very generically sends the information pertaining to our DBConnection in a standard format.  It carries with it progressively more and more options and settings to fire up our connection.  Such things as username and passwords.  Write and read privileges.  All manner of things for use to peruse through and set.

  2. The CommandText belonging to the OleDbDataAdapter component. All this is is our SQL statement.  But a command string is not limited to a select statement.  It handles other commands sent to the database as a string.

  1. SelectCommand - As seen with our SQL query.

  2. UpdateCommand - An auto generated command that when used updates the database with new data.

  3. DeleteCommand - Used to delete data.

  4. InsertCommand - Used add new data, such as a new record.

Private Function StartData()

Me.OleDbConnection1.Open()

Me.OleDbDataAdapter1.Fill(Me.DataSet11)

End Function

Me.TrackBar1.Maximum = Me.DataSet11.Customers.Rows.Count
Private Sub TrackBar1_Scroll(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TrackBar1.Scroll

Me.BindingContext(Me.DataSet11, "Customers").Position = Me.TrackBar1.Value

End Sub

Me.DataSet11.Customers.ToString
  • Saves you a little bit off typing anyway! Okay now try that out, slide through your data and see how that feels.
  • Are you unhappy yet?  Need some more data to populate your form?  Well we can do that and we can start the nair beginnings of a more powerful adaptation, without starting over from scratch!
  • The first thing we want to do is provide some more order to the way we are viewing our records. Such as names in alphabetical order.  So what we need is a sorted view of our data.  Without going back over our work we can easily add this to our app.
  • Go back into your data section of the toolbox and drag out into your project a DataView component.
  • Plop, okay now all we need to do is point it's .Table property to our Dataset and select the customer's table.  Now in order to view our data in order we need to then set the .Sort property to ContactName.  This will put the table in ASC order, a k a alphabetical order according to the ContactName's list.
  • Then we need to change our textboxes to point to the DataView instead of the Dataset. With that done we are going to add another way to get to different rows in our data.  Under dot net there is some more power in this area.  We are going to drag a listbox onto our form from the toolbox. Push it all the way to the far left of your form, we are going to need some more room.
  • So our textboxes and labels will be across the top our TrackBar along the bottom and our list to the left side.
  • Now we are going to bind that listbox to a whole column in our DataView and the list is going to fill up with the whole column's data. We do this by setting it's .DataSource property then it's .DisplayMember.  These properties are mixed in with the rest of properties of the control as opposed to being under the data binding section.  The data bind section only handles single field display binding.
  • Set the DataSource=DataView component. Then select ContactName as the DisplayMember.
  • Okay then run it, see now that our list shows all the names in alphabetical order.  But that's not all, try selecting names in the list.  This now acts as a pointer to the rows of our DataView.
  • Okay now we are going to add our last thing to our form, but we need to do something else first.  Consider this a means and a way of skipping through the steps we took to get here, to edit part of the path from our data base to our end user form.  We now have 4 steps.
    1. Connection
    2. SQL Statement
    3. Data Storage
    4. And a custom view.
  • Now we are going to add back our customer ID.  In fact we are also going to add the orders table. So click on your data adaptor go back into the select command properties and click on the ellipse next to command text. Select the check next to customer ID. Click Ok.
  • Okay go back into the data section of your toolbox and drag out another data adaptor.  When the wizard comes up click cancel. Now go into the select command section and add our existing connection to our database.  This will allow you to run the wizard on the existing connection instead of it generating a new one.
  • Now click configure adaptor, click along until your in query builder.  At this point we want to add the customer's table and select the All option at the top of the table, click next and finally finish.
  • Now let's go back to our DataSet.xsd file and open it up.  Remember we added the orders table already.  But look, our Customer's table does not have the CustomerID in here.  This is because we did not generate the dataset a new since we changed the select command.  Although the two steps interact they can be focused on Separately.  So we are simply going to click in there and type it in.
  • Okay not bad, but there is something different about it than the other table. That would be that little gold key.  Remember this was the column for the table's primary key.  So we want to add that, too. Right click on the element and then click add ---> key.  Make sure it's pointing at CustomerID then check Nullable and Primary Key. Then click Ok.
  • So what do we need the key for?  In order to link tables together in a relationship they need some kind of primary key to index the data and cause one to update the other in a Parent/Child relationship, in this case our Customer's table is going to be the parent and orders the child. Now right click the table and go to Add again, this time select relationship.  Add the relationship from CustomerID in Customers to CustomerID in orders. then click OK.
  • Alright we are done here let's go back to our form.  Inside the toolbox under windows forms look for the data grid control and drag it out onto your form. Fit it in nicely between the top and bottom.
  • Now we are probably going to need more room for viewing this and we want it to resize with our form.  But we don't need to write any resize code!!!
  • Under the properties for each windows forms control is the .Anchor property. it does two things, it keeps control in a general area and defines how they resize when the form is resized.  For resize in all direction, click all four anchors, do this for the data grid.  For stretching top to bottom, click the top to bottom anchors.  If you want your control do that and stay to the left, like our listbox, set 3 anchors, top left and bottom.
  • For our textboxes and labels we can select all 4 controls and set their anchor properties all at once,  We simply want them to stay at the top and centered to the position they start in, so select the top anchor.  That just leaves our track bar, we want it on the bottom and to stretch with the width of the form, so anchor it left, right and bottom.
  • With that accomplished we want to finally bind our data grid.  We do this similar to the listbox, We set our DataSource to our DataView and then we set our DataMember to the CustomersOrders  inside of the view.
  • Now for some more code.  We want to fill the orders table from our second data Adaptor.
Private Function StartData()

Me.OleDbConnection1.Open()

Me.OleDbDataAdapter1.Fill(Me.DataSet11)

Me.OleDbDataAdapter2.Fill(Me.DataSet11)

Me.TrackBar1.Maximum = Me.DataView1.Count

End Function

  • Now run it.

  • By creating our relationship between the two tables, the Data Grid automatically shows us just the orders pertaining to the current customer.

  • Okay so now we are retrieving all the data we want from the database, but what about when the end user makes changes?  Do we want them to be able to make changes? Yes, but not to everything, let's say our end user in this lesson only needs to add new orders. They are not allowed to edit existing orders nor are they responsible for adding any new customer's, so with this as their tool we only need to code for one event, New row added.

  • The first thing we want to do is set the Grid's read only property to true.  This will stop editing of any existing data.  Now we want a row addition method that best suits our needs.  Since most items in the rows are duplicates of information, and some are missing, see the (null) {That's because we haven't pulled any information from the linked tables into our grid not a problem, though.} we are just going to work with the columns that will be different. So let's say our user only needs to set the Freight and and the dates.  Their employee number will be set by their login, let's pretend we coded for that for now :).

  • Now we want a nice little quickie form to fill in these bits of information and return them to our main form's code and add a new row and update the database. we need 5 labels and 4 textboxes.  Then add a button to enter the new data. Also we want to populate one label with the Contact Name so the user knows they are adding to the correct table.

  • First we will put some code in our order form for our enter validation.  In this case we are not going to be to specific about what they enter, just want to make sure they filled all the entries. If they fail to do so we will simply signify to them that their work is not complete with a simple Beep.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

If Me.tbFrieght.Text <> "" And _

Me.tbOrderDate.Text <> "" And _

Me.tbShipBy.Text <> "" And _

Me.tbShippedOn.Text <> "" Then

Me.DialogResult = DialogResult.OK

Else

Beep()

End If

End Sub

  • With a compound IF statement, we check to see that all the textboxes have something in them.  If so we are going to close out form as a dialog box and send back OK as a results. This statement will close the form.

  • Next we are going into some more code in our main form by adding a button and setting it's Text property to Order.  Then we will do all of our work in it's click event.  Almost sounds to easy doesn't it?  Well I admit it wasn't that easy for me to figure it out.  So better to present you with working code and techniques your sure to use along the way. Like adding a DataView at run time and binding textboxes to it.  From there we will open our form as a dialog and this will give it focus and our code in our main form will wait for the called form to be closed before it accepts any new interaction from the user and finishes our code in the sub procedure.

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

Dim OrderForm As New Form2()

Dim MyDataView As New DataView()

Dim MyRow As DataRowView

MyDataView.Table = DataSet11.Orders

MyRow = MyDataView.AddNew()

MyRow("CustomerID") = Me.DataView1(Me.BindingContext(Me.DataView1).Position)("CustomerID")

OrderForm.BindingContext(MyDataView).Position = MyDataView.Count - 1

OrderForm.tbFrieght.DataBindings.Add("Text", MyDataView, "Freight")

OrderForm.tbOrderDate.DataBindings.Add("Text", MyDataView, "OrderDate")

OrderForm.tbShipBy.DataBindings.Add("Text", MyDataView, "RequiredDate")

OrderForm.tbShippedOn.DataBindings.Add("Text", MyDataView, "ShippedDate")

OrderForm.lblContactName.Text = Me.DataView1(Me.BindingContext(Me.DataView1).Position)("ContactName")

If OrderForm.ShowDialog(Me) = DialogResult.OK Then

MyRow.EndEdit()

Me.OleDbDataAdapter2.Update(Me.DataSet11)

Else

MyRow.CancelEdit()

End If

End Sub

  • We start off by creating 3 objects, our form, our DataView, and a row object as our new row.  First we set our DataView to get it's information from the orders table from the dataset. Then we create our new row from the DataView.Add() method which returns our new row as an object.

  • Next we are going to set the CustomerID to our currently selected row from our original DataView, we do this by using the BindingContext position property to point to our current location in the DataView.

  • Then we are setting our position in the new view to the new row.  As of yet, I haven't found a property from the row object itself, apparently it doesn't keep track of where it in the view.  So we count off all the rows and then take one away to indicate the last row, which should be the one we just added.

  • We then bind all of our text boxes to the columns we want our end user to work with.  Then we put our current contact name in our top label for our order form.

  • Showing the form as a dialog the end user can now enter only the information they are responsible for.  Granted this was not the grand design for Northwind, since no products have been actually ordered, but it's working and that's all we need to know for right now.

  • Should the dialog end gracefully and the ok response is returned, we are going to end the edit on the new row, and then update our database.  Depending on how you wish for these events to happen you could wait to update the database itself at a later time, either giving over that last responsibility to the end user or set it to happen at certain intervals.

  • Should the end use click on the X on the order form, their current edit is canceled.

This took some time to finish, with lots of pouring over information pertaining to many things.  Lots of tricks here, dialog form, Anchoring form elements,  Drag and drop components and straight up coding for them, Finally mastering a simple database project.  You can be sure the battle with the beast is only beginning, but just getting a foot in is a start.

VB Home