VBDB

Connecting VB Programs to a Database

2

Writing VB Code for INSERT INTO

• Creating user-friendly data-entry forms is easy in VB.• A typical data-entry form is associated with a

particular table in a database.• It will have several controls in which the user can

select or type a value for a particular field in a table. • Some of these controls may have descriptive labels

associated with them.• The form will have a “Submit” or “Save” button,

usually on the bottom right, which runs code to insert the new row into the table.

3

A typical data-entry problem• You are a data-entry clerk at the Planetary System’s annual conference.• Arriving Beings present their ID chips to you.• The chips contain the Being’s BeingID, BeingName, and BirthYear.• You must ask them for the name of their home planets.• You need to create an easy-to-use form for entering this information into the

Beings table.

4

The Data-Entry Form

• This simple form has controls for entering all of the required data.

5

Connecting to the database• As you know, Access stores an entire

database in a single file.• VB uses several fairly complicated lines of

code to connect a program to an Access database file.

• I have encapsulated this code into a Class called DbConn.

• DbConn is available in Resources/DbConn. It is a single VB file.

• Add it to your project so you can use it.

6

Adding DbConn to a Project• Create a new project, and save it with an

appropriate name. Note the location where the project has been saved.

• Download DbConn.vb into the same directory as the VB project file (which will also contain Form1.vb and other files).

• Back in VisualStudio, with the project open, go to the Project menu and select “Add Existing Item…”

• Choose “DbConn.vb”. The class will now be a part of your project.

7

DbConn

• You can look inside DbConn to see the complicated code that it encapsulates, but I do not recommend modifying it.

• If you get an error inside DbConn, it is generally because there is an error in your query. Use the Call Stack to find the source of the error.

• The next slide shows the code which allows DbConn to connect to your database.

8

• Discussion:– Why would I declare my DB object variable outside of a subroutine?– Why do I create the object (DB = New DbConn…) inside a

subroutine?• “Try” and “Catch” are used to keep errors from causing the

program to crash.• The code first assumes that the database file is in the same

place as the EXE file.• If it isn’t, it lets the user use an open file dialog to try and find

it.

9

The DbConn Constructor

• Note that when I call the DbConn constructor, it has two overloads.

• The second overload is for SQL Server, which I use at UMTRI. Here, we will only use the first overload, which is for Access.

• This constructor takes only one parameter: Filename As String. This is the path to the Access database file.

10

What can DbConn do?• DbConn runs queries.• For action queries like INSERT INTO, we use Sub ExecuteSQL.• As you can see, Intellisense will help you to remember the names of

the methods and properties of DbConn objects, and may even provide hints about each one.

• As you can see, Sub ExecuteSQL takes one parameter: sql As String.• If you pass the sub a valid SQL action query, the query will be executed

on the connected database.

11

Filling the ComboBox• Note that Form1_Load calls a sub called “LoadComboBox.”

Why would I write all of this code, including creating a Planet class, when I could have just typed the names of the planets into the Items collection at design time?

Answer: Because the data entry person will want to select the name of the planet, but we want to enter only the PlanetID number into the database.

Therefore, since we need multiple pieces of data about Planets, we put Planet objects into the ComboBox, not just Strings.

This is just like we did with the Propty Class in assignment 2.

The Planet Class• We’ll learn a lot about object-oriented programming later.• For now, you can use this Planet class as a model for entities that you will use in your assignment.• For each attribute, include a private variable and public property.• The reason we use a class here is because it allows us to put lots of information into the ComboBox, even though we only see the name of the planet.

13

A better way to fill the ComboBox

• This code is more complicated, but much more versatile.• Instead of hard-coding in the names of the planets, we

read them in from the database.• Here is the same code modified for the softball database.

14

Creating the SQL statement• Here is the code that creates the INSERT INTO

query and tries to run it.

In this case, we are creating a SQL statement as a VB String called “sql”.

DB.ExecuteSQL(sql) is inside a “Try” block since there is still a chance that the query will be invalid.

One possible error would be if the Being’s name had a single quote character in it, since single quotes are used around string values in SQL.

15

Use the best control!• A good data-entry form guides the user

to correct answers.• A common error is to use Textboxes for

all input, and then display a MessageBox when invalid data is entered.

• By using the best control for each data type, you can make data entry easier and less annoying.

In this program, only one TextBox is used, for BeingName. Home Planet is entered using a ComboBox using the DropDownList

style—meaning that the user can only select from one of the five planets.

ComboBox items can be loaded from a table, as you’ll see next week. BeingID and BirthYear use NumericUpDown controls.

16

NumericUpDowns• The NumericUpDown control allows precise control

over values that a user can enter.• It only allows valid numbers to be entered, and the

programmer can also set the Minimum, Maximum, and DecimalPlaces properties to prevent invalid entries.

• The little arrows can be used to adjust the value, which can be distracting. I prefer to use NumericUpDowns like a Textbox which can only accept valid numerical values.

• The Value property a NumericUpDown is a Decimal; use an appropriate conversion (Cint, CDbl, ToString) to convert it the the type that you need.

17

DELETE FROM and UPDATE forms• Given the danger of destroying large amounts of data, it is less

common to see simple forms used for deletes and updates.• Frequently, these operations are performed by database

programmers and administrators skilled in SQL.• However, it is not unusual to have forms which make it simple to

delete or update a single record based on its primary key value.• For example, you might call a utility company to tell them that

you have moved. If you have moved to a new location they serve, they might update your address. If you have moved out of their service area, they might delete you from their database.

• In either case, they would ask for your account number—the primary key in their Customers table.

• The update or delete would then be performed on your records only, without endangering the data for the rest of the customers.SQL & VB

18

Sample UPDATE/DELETE form• Here is an example of a combination update and delete form.• The data-entry user enters a BeingID. If there is a matching record

in the Beings table, the form will display the current values of BirthYear, Name, and Planet.

• The user can then either Delete the record, or change the Being’s Name or Planet.

• This form is a part of the SampleDataEntry program.

19

Responding to the NumericUpDown

• NumericUpDowns are trick controls, since the number can be typed in OR chosen using the arrows.

• To get data to update with either method, you need to respond to two types of events:

20

Display Data• The controls are set to the current values:

21

Setting the right planet in the ComboBox

22

Updating

23

Deleting

24

Using Dates• Not much details here; just enough to get

started.• VB includes two date-selection controls:– MonthCalendar– DateTimePicker

• Both controls make entering a date easy for the user; both return a Date value.

• I recommend the DateTimePicker, since it takes up less room (like a Combobox).

25

The DateTime Picker Clicking on the DateTime Picker’s arrow causes a month

calendar to appear. The left and right arrows navigate to different months.

26

Reading the Date• Here’s the code for entering the selected date

into the database.• Note that date strings (Like “February 17, 2010”),

go inside pound signs (#February 17, 2010#).

27

INSERTs and UPDATEs with Relationships

• When you have a one-to-many relationship between two tables with referential integrity checked, the foreign key field in one table must have a matching record in the other table.

• If there is no matching record, your VB program can ask the user if she wants to create one.

SQL & VB

28

A Last Word on UPDATES• You can use UPDATE to make mass edits to

data. • For example, you decide that the position

should be called “First Base” instead of “1st Base”.

• This query accomplishes that:• UPDATE Players SET

PlayerPosition=‘FirstBase’ WHERE PlayerPosition = ‘1st Base’

29

INSERT INTO (multiple rows)• INSERT INTO can be combined with SELECT to add multiple

rows to a table at once.• In OLTP databases, this isn’t usually good practice unless you

are restructuring the database (splitting up or combining tables, or normalizing a database that isn’t in 3NF).

• You may find this most useful when redesign your project database. You find that you need to redefine your tables, but you don’t want to have to re-enter all of the data in your existing tables. You can write a multi-row insert query that will transfer the data for you.

• Multi-row inserts are an important method for taking data out of an OLTP database and transferring it to an OLAP database.

SQL

30

INSERT INTO Example

• An example: Birth records in the planetary system have become more refined. They include the month now. In fact, the whole planetary system is switching to a monthly basis; in the future, years will not be used.

• You want to be able to record this more refined data in a modified Beings table.

• However, you don’t want to lose any of the data from the old Beings table; you’ll convert those BirthYears to BirthMonths by multiplying by 12.

• That part could be done by a select query, but a select query doesn’t store information—there would still be no place to store the new monthly info. SQL

31

Example ContinuedSo: We rename the original Beings table as “BeingsOld”, and create a new Beings table which has a BirthMonth field instead of BirthYear.

How are we going to populate our new table with the old data? Here’s the query:

INSERT INTO Beings (BeingID, BeingName, HomePlanetID, BirthMonth)SELECT BeingID, BeingName, HomePlanetID, 12 * BirthYear FROM BeingsOld

SQL

32

It works!And here’s the result! For comparison, here’s the original table:

INSERT INTO Beings (BeingID, BeingName, HomePlanetID, BirthMonth)SELECT BeingID, BeingName, HomePlanetID, 12 * BirthYear FROM BeingsOld(Note that since the results of the SELECT query are not being displayed directly, I don’t need to alias the calculated field.)

SQL

33

Simulating an AutoNumber

• By default, Access gives your new tables a primary key which is an AutoNumber—it starts at 1 and increments every time you add a record.

• This guarantees that it will be unique, but in some ways defeats the purpose of a primary key, as I showed in an earlier lecture. Therefore, I don’t recommend that you use autonumbers.

• However, you will be creating data-entry forms in later assignments and the project, and you may want to simulate the autonumber functionality.

• The following slide shows how to do this.

SQL

34

Simulating AutoNumber (cont.)

• Here’s the query:INSERT INTO Beings

(BeingID,BeingName,HomePlanetID,BirthMonth) SELECT MAX(BeingID) + 1 ,"Sally Ride",3,100 FROM Beings

• This query will add Sally Ride, from Planet 3, with BirthMonth 100, to the Beings table.

• Using MAX(BeingID) + 1 guarantees that the new BeingID won’t already exist in the table.

• The remaining fields are constant values (no field names), so they don’t have to be included in a GROUP BY. SQL

35

Using Panels to Show/Hide Controls

• I request that once the data-entry person has selected the subtype, you (as programmer) should display ONLY the controls appropriate for that subtype.

• The Panel control is very useful in this case.• Let’s see how we could use a Panel for this

purpose.

36

frmMultiPanels The finished version of this form is also a part of the

SimpleDataEntry project, accessible through the menu. I start by creating three panels on the form, all with the same size. I give them different colors and different controls.

37

• Next, I put them all on top of each other:

And then add a combobox, DropDownList style:

I then added three string items to the ComboBox: Subtype 1, Subtype 2, Subtype 3.

38

• Now, when a different item is selected in the ComboBox, the corresponding panel appears;

• The controls on that panel appear too!

39

frmMultiPanels Code

• This code can be a model for how to do part 4 of assignment 4.• The controls for each subtype can be on separate panels, and you

only show one panel at a time.