Programming in Visual Basic 2010: The Very Beginner’s GuideLet’s ReviewConnecting to a Database TutorialSlide 4PowerPoint PresentationSlide 6Slide 7Slide 8Slide 9Closer Look: Basic OperationsSlide 11Slide 12Closer Look: Editing the ViewCloser Look: the HeaderTextSlide 15Closer Look: Table Adaptor PropertiesVB Quiz 01Wages Tutorial – Exciting Stuff!!Tutorial to Populate ControlsWages TutorialSlide 21Wages Tutorial – Selecting and Connecting to our DatabaseWages Tutorial (Add Connection dialog set up to work with Access)Slide 24Wages Tutorial – Bind Controls to DataSlide 26Slide 27Slide 28Wages Tutorial – Binding NavagatorWages Tutorial- AppearanceWages Tutorial – go through datasetWages Tutorial - Navigator Options.Wages Tutorial – Adding OptionsWages Tutorial – Save ButtonSlide 35Wages Tutorial – Delete ButtonWages Tutorial – Clearing Add ButtonWages Tutorial – Adding WagesWages Tutorial – Calculating ResultsSlide 40Slide 41Potential ProblemsSlide 43Chapter 14.2LINQ to SQLProgramming in Visual Basic 2010:The Very Beginner’s Guideby Jim McKeown Databases – Part 2Let’s Review2Connecting to a Database TutorialWhat Just Happened?•Wizard sets up connection between program and database•Temporary connection 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)3Connecting to a Database TutorialWhat 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 projectDatabase Explorer•Window lists the database connections for a project45Connecting to a Database TutorialExpand the view of the database in DB ExplorerLists tables and other contents for NEC fileNote: we only have a single table (relation) No views, stored procedures, or functionsServes has a handy referenceRemember: DataSet stores data from the database in the computer’s memory for processing, updating, manipulation, queries, etc.6Connecting to a Database Tutorial7NEC ScreenConnecting to a Database TutorialComponent 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 8Connecting to a Database TutorialBindingSource •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 runs9Closer Look: Basic OperationsEstablished connection to NEC file, Established DataSet from NEC file.Look at DataGrideView. (see next slide)DataSource of this DataGridView is the NecDataSetLook at DataGridView: Populate it? DataGridView populated when app is run(See two slides down)Most of the time only some of the data are needed101112Closer Look: Editing the ViewClick on the DataGridView controlClick Edit Columns Link (bot of Properties window)We can now change our view! (what we are interested in)Can add, remove, change order…. of columnsCan 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.13This property under Appearance in the Properties Window contains the column headings; (next two slides together)Used it to rename columns 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!! 14Closer Look: the HeaderTextConnecting to a Database Tutorial15Edit Columns DialogSelect the NECTableAdapter in component tray. See links in properties window.Click on Preview Data... 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.16Closer Look: Table Adaptor PropertiesVB Quiz 01What 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?17Wages 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 populate controls (textboxes, labels, …) from our database.What a deal!!18Tutorial to Populate ControlsAccess the Wages database file from my webpage. Wages.mdbOpen in Microsoft Access 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.19Wages TutorialCreate a form that looks like the following and name the controlsDoneCreate a new BindingSource from Toolbox and name it bdsWagesDone; BindingSource then appears in the component traySelect DataSource property for bdsWages BindingSourceIn the dropdown select Add Project Data Source...Recall: Window opens a wizard