Connecting to a Database Tutorial: Learn to Connect Program with Database and Populate Controls

1. Programming in Visual Basic 2010:The Very Beginner’s Guide Chapter 14.2 LINQ to SQL Databases – Part 2 by Jim McKeown

2. Let’s Review

3. Connecting to a Database Tutorial What Just Happened? Wizard sets up connection between program and database Temporaryconnection established long enough to pass the data back and forth when needed Handles the tricky stuff Solution Explorer has an icon for the NEC.mdb database Double click to open the file in Access (I did not see this)

4. Connecting to a Database Tutorial What Just Happened? Select Database Explorer:  Go to View, Other Windows, Select Database Explorer Tabs at the bottom toggle between Toolbox and Database Explorer Database Explorer lists the data connections in your project Database Explorer Window lists the database connections for a project

6. Connecting to a Database Tutorial Expand the view of the database in DB Explorer Lists tables and other contents for NECfile Note: we only have a single table (relation) No views, stored procedures, or functions Serves has a handy reference Remember: DataSet stores data from the database in the computer’s memory for processing, updating, manipulation, queries, etc.

7. Connecting to a Database Tutorial NEC Screen

8. Connecting to a Database Tutorial Component Tray: Three Items: NecDataSet Stores the data from the database into computer’s memory while program is running. Database are usually on a database server on the network and it’s hard to maintain that connection By creating a dataset in memory from the database itself, this frees the server to handle other requests Faster access when the data is in memory Changes to the data are stored in the DataSet and then updated in the database all at once

9. Connecting to a Database Tutorial BindingSource Stores connection information and Provides navigation, filtering, sorting, updating capabilities Pulls together data from various tables in a database (We always have more than a single table!!) TableAdapter Takes data from the tables and passes them back and forth between the program and the database. All three needed to create a connection, get the data and manage it while the program runs

10. Closer Look: Basic Operations Established connection to NEC file, Established DataSet from NEC file. Look at DataGrideView. (see next slide) DataSource of this DataGridView is the NecDataSet Look at DataGridView: Populate it? DataGridViewpopulated when app is run (See two slides down) Most of the time only some of the data are needed

13. Closer Look: Editing the View Click on the DataGridViewcontrol Click EditColumns Link (bot of Properties window) We can now change our view! (what we are interested in) Can add, remove, change order…. of columns Can remove a field (remove a few) Can add: select add button and all available fields are listed so you can add buttons you’ve removed. Can reorder fields: select and use up / down arrows Can also see the bound column properties descriptions on the right.

14. Closer Look: the HeaderText This property under Appearance in the Properties Window contains the columnheadings; (next two slides together) Used it to renamecolumns in DataGridView (Change HeaderText for First to F-Name; press OK Changed it in column names but DataPropertyName is still First) Nice for renaming columns in a report. Does not appear to change the attribute (field) name!!

15. Connecting to a Database Tutorial Edit Columns Dialog

16. Closer Look: Table Adaptor Properties Select the NECTableAdapter in component tray. See links in properties window. Click on PreviewData... And then Preview Displays all columns / rows currently in DataSet Note: from this view you may select a Headertext items and the rows (records) are sorted based on ascending / descending features. Note Also: number of columns / rows at left bottom; tells number of records (rows) and fields (attributes). Note: I changed ID to Identification in HeaderText using Edit Columns when selecting the Grid. Did not change overall attribute name; did change headertext in Grid itself when displayed.

17. VB Quiz 01 What is the name of the database used in this example? How many fields (columns) are in the database? Does the number of records (rows) in the database in this example matter? What is the name of the DataSet? Why is the connection to the database a temporary one? Think about how changes to the records would end up in the database. Did you move attributes around in the DataSetGrid) Add, Remove, Change order? What specifically did you do? Results?

18. Wages Tutorial – Exciting Stuff!! Now we get to the good stuff: populating controls from the dataset! We want to connect controls to our database! Once done, we can access (for our example coming up) employee data, calculate wages, and update records (data from our databases into our programs)! Want to populatecontrols (textboxes, labels, …) from our database. What a deal!!

19. Tutorial to Populate Controls Access the Wages database file from my webpage. Wages.mdb Open in MicrosoftAccess to see what it looks like. Of course, you can view it from VB as well. Be sure to close it before you start the tutorial.

20. Wages Tutorial Create a form that looks like the following and name the controls Done Create a new BindingSource from Toolbox and name it bdsWages Done; BindingSource then appears in the component tray Select DataSource property for bdsWages BindingSource In the dropdown select Add Project Data Source... Recall: Window opens a wizard to select a database and determine the settings and connection

21. Wages Tutorial Wages Tutorial Screen

22. Wages Tutorial – Selecting and Connecting to our Database Select Database as source type and Next Select DataSet and Next (this step and next five are the same as the first tutorial) Select New Connection... button to create connection to a database SelectChange... and change Data source option to Microsoft Access Database File Click OK to establish Access as the database type for use in this program

23. Wages Tutorial (Add Connection dialog set up to work with Access) Select Browse... and locate the Wages.mdb file Select it and click Open to add filepath to to Add Connection Dialog box Test Connection button; OK to proceed. Next Save Connection: Next

24. Wages Tutorial Select Tables and Expand Tables Click on tblWages Expand the tblWages table for list of fields ClickFinish Component tray now has a WagesDataSet DataSourceproperty of the BindingSource bdsWages shows it’s linked to the WagesDataSet Please check this out to verify. Select the DataMemberproperty of the BindingSource and set it to tblWages This now adds the tblWages TableAdapter to the component tray Note: component tray has the binding source, the data set and the table adaptor.

25. Wages Tutorial – Bind Controls to Data Select eachcontrolinturnand bind to a field. (next three slides after this one. But this is the one…) Pick a textbox from form (tbxEmpID for me). Go to Properties Window Go to DataBindings property (near top of Window); Expand Select Text property under DataBindings Select Drop Down box (says none) Select bdsWages, expand it (double click) and select the ID property (in this case for me) that I am trying to bind to the control. Be sure to use the Text property in the DataBindings and not the regular Text property Should say bdsWages - ID Regular Text property has a canister in it to indicate the property is bound to a data field

26. Wages Tutorial Later Wages Tutorial Component Tray

27. Wages Tutorial Wages Select ID Popup

28. Wages Tutorial Repeat this for other TextBoxes to bind them to the appropriate data fields Do This. Save your project Database connection established and control are bound to the DataSet It works!!!! Program “works” but only displays the first record No way to navigate through the records Program needs a BindingNavigator

29. Wages Tutorial – Binding Navagator The BindingNavigator is the userinterface to move through the records in a DataSet Go to your Toolbox and AddaBindingNavigator to top of the form; Drag from Toolbox Name it bdnWages (Name property) (Adds bdnWages to Component Tray) BindingNavigator provides a user interface for navigation and manipulation of data bound to controls on a form

30. Wages Tutorial- Appearance Wages Tutorial Toolbar

31. Wages Tutorial – go through dataset SetBindingSourceproperty for BindingNavigator to bdsWages to link it to data Run program. Movethroughrecords using BindingNavigator Each record shows up in the TextBoxes Go to Source Code Window: One line of code in the Load event Me.TblWagesTableAdapter.Fill(Me.WagesDataSet.tblWages) This statement fills the TableAdapter with data when the program starts

32. Wages Tutorial - Navigator Options. Some buttons on BindingNavigator aren’t needed Rightclick on bdnWages BindingNavigator and select Insert Standard Items from drop down. Adds many standard buttons including Cut, Copy, Paste, New, Open, Save, Print and Help Removeall except the Save button Right click on a button and select Delete to remove it

33. Wages Tutorial – Adding Options Insert a new Button RightClick on the bdnWages BindingNavigator to get a dropdown SelectButton from the list AddButton Rightclick on Button SelectSetImage... (or select Image from the Properties) Browse until you find the Exit.gif file Replace the image with it DoubleClick on Exit button Key in End

34. Wages Tutorial – Save Button Save Button must be coded Click on the Save Button and insert this code. Try bdsWages.EndEdit() ‘ attempts to end the editing TblWagesTableAdapter.Update(WagesDataSet.tblWages) ‘ needed to update the database . Catch ex As Exception MessageBox.Show("An error has occurred.", "Save", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try

35. Wages Tutorial Try/Catch prevents a crash if there’s an error and databases are finicky Two lines in Try first line says to EndEdit for the data second line updates the DataSet and the file Until the update, changes to a record are not sent to the file

36. Wages Tutorial – Delete Button Code the Delete Button Try WagesDataSet.tblWages.Rows(bdsWages.Position).Delete() ‘ This line deletes current row in Dataset ‘ current row determined by position in the ‘ binding source. TblWagesTableAdapter.Update(WagesDataSet.tblWages) ‘ Updates the dataset and file. Catch ex As Exception MessageBox.Show("An error has occurred.", "Save", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try

37. Wages Tutorial – Clearing Add Button Add this line to the AddButton lblWages.Text = "" It clears the text from the Label so there’s no confusion when a new record is added

38. Wages Tutorial – Adding Wages Add this code to the Wages Button Dim decWages As Decimal Dim sngHours As Single Dim decPayRate As Decimal sngHours = WagesDataSet.tblWages.Item(bdsWages.Position)(5) decPayRate = WagesDataSet.tblWages.Item(bdsWages.Position)(4) decWages = sngHours * decPayRate lblWages.Text = decWages.ToString("c")

39. Wages Tutorial – Calculating Results First two lines get values from DataSet based on Position Position is the current record in a DataSet Last number is a fieldnumber Similar to how fields were split and assigned to variables using file I/O Field 4 is the PayRate field Field 5 is the number of Hours Next line calculates their wages Last line displays their wages in the Label Position: the current record in a DataSet

40. Wages Tutorial Finished Wages Tutorial

41. Wages Tutorial Wrap-up Connecting a control to a field from a database is an easy way to display and manipulate data BindingNavigator makes navigation easier Little code is needed to add, delete and update records VB interface makes it easy to work with data Interfaces for most databases are hard to work with Easy to customize the interface and make it appealing to users

42. Potential Problems Be sure to select the correct connection type Check DataSet to ensure it includes correct data Be sure to bind proper fields with correct control Be sure to use Text property under DataBindings for your connections

43. Potential Problems Be sure to add the code to the Load event to fill the DataSet Never try to open a database in two programs at once Always keep a backup of your data