VBDB

1. 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.

12. 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 Continued So: 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. SQL It works! For comparison, here’s the original table: And here’s the result! 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.)

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.