Database Application
10/31/01

Sorry, guys.  I didn't take very good notes tonight.

Labs 7 & 8 are gone

Lab 5 & 6 are left.

In Lab 5 …

You take the stuff you've written in VB that does the data validation.
You take the stuff you've done in SQL.
You hang them together with some middle stuff.

We used ADO for this last year.

This year, we're going to do this a little differently.  We're going to do this in Data Access Classes

We'll write ADO code, but we'll put it in a class.  We'll have classes for customer, inventory, supplier, and order.

A class is a data structure, but it has within it data items and methods.
Methods are sub-routines.
It encapsulates them nicely into one object.

Once they're working, we'll do the data access through the classes rather than through VB.

Once they're working, we'll package them into a component (Lab 6).  We'll be doing everything through that 
instead of though VB.

Each class needs a method.  A method is a procedure.  We need an insert or add method that inserts an object (like a 
customer, a supplier, an inventory record, etc.).  We'll need an update, delete, insert, add, get.  That's what we'll 
need for customer, inventory, and supplier.

We'll need a Process New Order for the order class.

Here's how you make a get.
You pass in the key, and then you select the record where the key matches.
You need it for doing updates and deletes because you have to get a copy of the record first.

All of this stuff is ADO code.

He's done the customer one for us in Lab 5.  It looks like  a lot of stuff, but it isn't really.  See handout # 10.

Once you've done one, you've done them all.

If you have one stored procedure that updates something, you've solved a whole class of problems.

If your stuff works for customer, it works for everything else (with a little tiny bit of editing).

Work smart.  Once you've solved one ADO problem, you've solved it for a bunch of things.
Cut and paste.

When you do these, after your SQL stored procedures are there, you want to have a copy of those procedures in 
hand while doing classes.

Each of our classes only have methods – no data.
Look at Customer Class ADO Delete Customer Method.

All of our ADO procedures work like this.

Notice that he's also included the header for the SQL procedure.  Input parameters are passed to VB by value.

Output procedures need to be able to be changed, so they are passed by reference.
ByVal and ByRef

In his case, he's got a customer string, a return code, and a return _____________

I need to send in those parameters in that same order.
Have your stored procedures in front of you.

First thing is to dimension a command object.
The reason is to execute a command.

I connect to the DB.
I create the command object – dimming it.
To create an object, I have to use the "new" operator.

Command object called "comm.".

Set active connection to CONNDB which we get from connect-to-database (in global module).

A command object can have parameters, and they're in a list.
There's some weird syntax here to add items to the list.
Look at "append comm..createparameter, etc.

.item gives it an actual value
I now have 1 parameter set up with a value.

2nd side
Get returns recordset
How do I get a recordset out of this?
Look at record list, I have added "byref" rs customerADO.recordset

I have extra parameter which isn't in the stored procedure

How do I get recordset back.
Database Application Management
10/31/01
Page 1 of 2