Database Application
10/03/01

Sorry, guys.  I missed the beginning because I was late.

,, is null fields in SQL

"" means en empty record

The problem in this exercise is in the data file – not the constraints.

It's easy to mess with a data file with only a few records.
To fix a lot of records, contact the author or write a little program to fix it.

In the real world, there is DTS – SQL feature that automatically reviews and transforms data files

Here, change the phone # that has letters in it and fix the null problem.
Use find-and-replace feature in Word.  Be sure to save it back to a txt file, though.

If you want to use vanity phone numbers, you'll have to re-think which mask you're going to use.

There's always a tendency to want to fix the constraints as opposed to fixing the data.

Lab 1 is about learning the data definition language
The other thing is learning about constraints on the data attributes
We didn't do this last time.
Some DB clients may already have lots of constraints set up, and some may not.

You can't catch everything, but you can catch a lot.

Data has to be loaded in some particular order because of integrity.

Lab 1 – hand in …
Diagram
Script

You can do a lot in the diagramming tool in Enterprise Manager
This is the most convenient place to do a lot of stuff.
Unlike Access, the diagram is dynamic (alive).

You can see the page breaks.  No need to print over several pages.

Write click and select "View Page Breaks"
Look at blue boundary
Place tables where you want them

"New text annotation" – you can put text in the diagram where ever you want it.

Right click on the header of the table in the diagram
Table view – standard

Right click on zip code
Check constraints, etc.
You can edit it right here.

This would be instead of using the alter table function.

This properties box is available from a number of places.
You can change data types here.

The diagramming tool can change the tables, too.

Now we need to talk about Lab 2

We're building 3-0
VB front end
Middle components logic
DB that handles data

Lab 1 built the database

In Lab 2, we'll work on front end

Ch. 10 talks about data validation
Lab 2 – do data validation in the front end (in VB)

You can do data validation in all three places, and that's not a bad idea because we might have VB, but 
the end-user may not.

Surprise …
There's a lot of code.
Validation code often accounts for more of the code than anything that actually gets worked on.

All the validation code falls into some common patterns.
We'll work with common validation techniques that we can use again and again in other databases.

We can't enter data in the DB yet.  We haven't hooked up to it yet.

Use the same names he uses.

The other two forms use the same techniques as the one we build together.
There's nothing new to learn on the other two.  We just need to do it ourselves.

We'll have 3 forms when we're done.  None of them will work yet.

SQL stored procedures
Components in the middle

We'll have to work on each tier separately and test each tier separately.

Normally there would be three groups of people doing the three different things.

All of the techniques we'll need are talked about in this handout.

First Name is required.
Pop-up message box is okay unless you have people who don't look at their screen as they do data 
entry.
In the real world, we would make sure the add button doesn't work for people like this.

How do we make sure the user enters stuff.
See "Required Fields/Attributes" section on page 4 of Lab 2.



Anything in a text box is a string.

He likes to check for the length of the string.
Check to see if the length is 0.

Code needs to be generated in:
Lost focus event
When you tab out of a field

Lost focus event – probably not a lot
Validate event – a lot
Key press – a lot

We'll put a lot of code in the validate event

Sometimes we want to control individual keystrokes – like when you want to make sure it's a # and not a 
letter.

Lost Focus ? Len
If it's 0, there's nothing there.  If there's nothing there, I want to do several things.
Message box.
His code also uses cancel parameter
Cancel=true sends cursor back to where it came from

If there's some text already in there, I want to highlight it so you'll type over it.
Txtprice.SelStart = 0

If I select something, I have to know how many characters to select.
TxtPrice.SelLength = Len(txtPrice.Text)

This selects how ever many characters are there.

This works fine until somebody throws blanks in there.

When checking strings for length, get rid of the spaces.
Use trim.  Takes it off front and back.  It assumes the middle is okay.
Removes leading and trailing blanks.

Here you have a template for any field requiring data.

Every field in here where I require data, I have something like this.

It looks like a lot of code, but it's the same code repeated over and over.

First name, last name, address, city, state, zip

More things you can do …

Sometimes I want to control how data is formatted.

Names should be initial caps.
Canotical form is how we want to see it in the DB.
It makes no sense to tell the data entry person to do this.

Look at a field and use the key press event to control what goes in there.

See page 5

KeyAscii
You can find the ascii code in programming text

If it's the first key, it's in upper case.  If it's not the first key, I want it in lower case.

chr  takes an integer and turns it into a character
Now you can feed this character into the Ucase function
Asc function changes letter back to integer
Change my letter back to ascii

In this one, you're not expecting a name like St. Petersburg

We can fix this

Look at the other code on page 5, look at city

Right function says, I'm asking for the rightmost characters of the string
This code asks if this rightmost character is a blank

Brad's question on bulletin board
Technically there should be code to keep from entering phone #'s in the name field
The solution has to do with controlling the keys as they go in there.

Brad asked:
 The txtFirstName field will accept numbers. How do we keep that from happening?
 Answer: In the keypress event check to see that each character is between Ascii 65 (A) and 
Ascii 90 (Z) or between Ascii 97 (a) and Ascii 122 (z). If it's not, assign 0 to the KeyAscii 
parameter. Note, you can find the Ascii code in any programming book..
Private Sub txtFirstName_KeyPress(KeyAscii As Integer)
  Dim ValidKey As Boolean
  ValidKey = ((KeyAscii >= 65 And KeyAscii <= 90) Or _
              (KeyAscii >= 97 And KeyAscii <= 122))
  If ValidKey Then
    If (Len(txtFirstName.Text) = 0) Then
      KeyAscii = Asc(UCase(Chr(KeyAscii)))
    Else
      KeyAscii = Asc(LCase(Chr(KeyAscii)))
    End If
  Else
    KeyAscii = 0
  End If
End Sub










In the state, zip code, and phone fields
They all have special patterns that we want them to look like.

There's a mask edit control in VB.  You have to add it through the menus.
It tells in the handout how to edit it.
You supply the mask.

See mask on p. 7 of handout

This doesn't take care of making sure we get a valid state.

You have a lot of fields here that have length constraints here.
Set the max number of characters so that no matter what happens (for example, on state), you get no 
more than 2.

Zip
.text includes the literal
Doesn't include the dash in the zip code

.cliptext includes the dash

How do you get them to enter either no zip code extensions or a total of 4 (not 1, 2, or 3)
Check the length.
You want 5 or you want 10.
If it's the wrong length, it's an invalid zip code.
You'll see that in the code he wrote.
Just having the mask is not enough.

Phone # is even worse.
You can:
Not have it at all (not required)
With an area code
Without an area code

999 ###-####

Phone # has been divided up into 2 fields.  You can tab out of the area code field if you don't want to add 
one.

He used txt prefix on mask edit boxes by mistake.
Don't let this confuse you.
Where ever you see a mask in a field, that has to be a mask edit box.
You have to concatenate the two fields before putting them in the database.

You want to make sure you don't end up with an area code without a phone #.

Use a yes-no message box

Yes – cursor needs to end up in phone # field
No – get rid of the area code

You'll see this code.  There's a lot of code there to get that stuff done.

The stuff in the mask edit boxes is the more complicated stuff even though you would hope these would 
be easier.

We have a mask on state that says you need two capital letters.
How do you make sure you only put valid states in there?

You COULD use an if statement.  This is not good.
You could do long code.

The usual way to do this is to look it up in the table.
If it finds it, we know it's okay.  If not, we know it's not.

Use lookup table.

Too expensive to make it go out to the server every time.
Make ourselves a local state table.  States aren't going to change.

When we open the application in the morning, go get the tax table and bring them over and make a local 
table on our client and look them up.

Set up state table code in Global module (Global.bas) so any form who wants to use it can.

You will see this code as well.

Option Explicit
Private strConnect As String
Public conDB As ADODB.Connection
Public StateTable As ADODB.Recordset


Public Function ConnectToDatabase() As Boolean
If conDB Is Nothing Then
  Set conDB = New ADODB.Connection
End If
 
If conDB.State <> adStateOpen Then
  strConnect = "Provider=SQLOLEDB.1;" & _
              "UID=sa;" & _
              "PWD=;" & _
              "Initial Catalog=MusicDiscounters;Data Source=(local)"
  Set conDB = New ADODB.Connection
  conDB.ConnectionString = strConnect
  conDB.Open
End If
 
If (conDB.State = adStateOpen) Then
  ConnectToDatabase = True
Else
  ConnectToDatabase = False
End If
End Function
Public Sub MakeStateTable()
  'Use a disconnected recordset to set up a local state table
  If ConnectToDatabase Then
    Set StateTable = New ADODB.Recordset
    StateTable.CursorType = adOpenStatic
    StateTable.CursorLocation = adUseClient
    StateTable.Open "Select StateCode, StateName, TaxRate From tblStates", conDB
    StateTable.ActiveConnection = Nothing
    Set conDB = Nothing
  End If
End Sub
Public Function StateLookUp(ByVal strState As String) As Boolean
  StateTable.MoveFirst
  StateTable.Find "StateCode = '" & strState & "'"
  StateLookUp = Not StateTable.EOF
End Function


As long as your table is called Music Discounters and as long as you don't have a password on your DB, 
this code will work for you.

Somewhere in your code, you're going to have to create a state table.
You could do it in the form load event.
Put it in the main procedure.
Add main into the global module and then tell it to run main first.

Main is a way to run a procedure before running anything else.
We only load when we open the application.

If you're confused about where to put it, you COULD put it in your form load event.
Although wanting to "move it later" is not a good practice, it's okay here.
We WILL move it later.

String functions
Len
Trim
UCase
LCase
Chr
Asc

Events
Lost focus
Validate
Key press
Database Applications Programming
10/03/01
Page 1 of 7