Programming in Visual Basic 2010: The Very Beginner’s GuideCustomer QueriesDatabase File for this PartSlide 4Tasks – Build the Connections, etc.(except this one) If using DataGridView in lieu of FormTasksSlide 8Slide 9Slide 10Slide 11On to Customer QueriesQuery Wizard looks like:Slide 14Slide 15Slide 16Slide 17Slide 18Slide 19Query: Select All Records. (new)Query: AllQuerySlide 22MoreData Base Terminology - ImportantSlide 25Slide 26VB Quiz 03Potential ProblemsOn Your OwnSlide 30Part 5; Project 4. 50 pointsIf you are smart….SummaryChapter 14.3LINQ to SQLProgramming in Visual Basic 2010:The Very Beginner’s Guideby Jim McKeown Databases – Part 3Customer Queries•Sometimes there’s just too much data•Queries look through a database and retrieve data that matches the search criteria•Queries find records that match the search, display selected fields in the results and order the records for easy reference•Use a DataGridView to demonstrate a queryQuerysearch or searches in a database; return specified fields from selected records and display them in a specific order2Database File for this PartWe need the Customers database file See my web page.Download and open and look at it. Close it.3Customer QueriesCreate a new formName the controlsAdd a DataGridView (I used a form)4Tasks – Build the Connections, etc.See grid in book and create form from it, if you wish.Or use the DataGridView control from the toolbox in lieu of the form and controls we’re used to.If you use controls, name Controls and Labels,…Either will be okay.Electing to do the form is a bit more work; when electing the datagridview, some things are done for you…The slides ahead are for Forms: Add Binding Source from Toolbox (Category: Data)Name it bdsCustomer in Name PropertyAdd DataSource propertyvia drop down….Goto next slide5(except this one)If using DataGridView in lieu of FormIf using DataGridView in lieu of form, there are a few differences. Here are a few…select DataGridView control.Click on playChoose Data Sourcecan select WagesDataSetBindingLinks WagesDataSet and WagesDataSetBinding in component traySelect WagesDataSetBindingSourceSee properties DataSource (linked to WagesDataSet)Select DataMember; select tblWagesNote: grid modified and has fields. Expand. Fill form space up with grid…6TasksAdd Project Data Source items…Database, dataset, New Connection, Change, (Microsoft Access Database File), Browse for Database File (from desktop), Select Customers.mdb, (book says Wages. Wrong)Test Connection, OKNext, Yes, Next, Goto next slide7TasksStep 10 in book, p. 588Select Database Objects for the Data Set. we do this by expanding the Tables Checkboxclick on tblCustomers.Leave checked. FinishComponent Tray now includes A Customers Data Set8TasksSelect DataMember property of binding source (bdsCustomer) and set to tblCustomers.Adds TBLCustomersTable Adaptor to Component tray.Save Project.Onto Step 13 in book…9TasksBind the Controls to the Dataset (if using controls / form).For each control, Go to DataBindings in property window, Text properties, bdsCustomer, and associate controls to dataset objects. 10Tasks Add Binding Navigatordrag from toolbox to top of formName it bdsCustomers via property window Link your binding navigator to your data by changing BindingSource property of the binding navigator to bdsCustomer (drop down)Run your program!!11On to Customer QueriesSelect TblCustomersTableAdapter in component tray.Select Add Query... at the bottom of the Properties windowSearch Criteria Builder wizard displaysSelect a New query name: and enter Outdoors to build a query of customers interested in the outdoors (see next slide)12Query Wizard looks like:13Customers Search Criteria BuilderAdded query nameCustomer QueriesSelect Query Builder button to open the Query Builder dialog (three slides down)Top section has the fields listed for tblCustomers and all fields are selectedLeave all the fields selectedAll fields must be selected when using the DataGridView14Customer QueriesSecond section has a list of the fields and the search criteria for it (two slides down)Build your query in this sectionScroll down to the Interests fieldChange the Filter to = ‘Outdoors’Modifies a query template (next slide) that looks through all the records and returns those with Outdoors as an InterestAnd adds the query being built in the third sectionSee SQL next slide:15Customer QueriesThird section now readsSELECT ID, LastName, FirstName, CustNum, Address, City, State, Zip, Since, [Last], Preferred, AcctBalance, InterestsFROM tblCustomersWHERE (Interests = 'Outdoors') ADDED THE Where!WHERE clause added to the SQL statement being buildStatement says to select all fields from tblCustomers and only those records where ‘Outdoors’ is in the Interests field16Customer Queries17Customers Query BuilderCustomer QueriesSelect OK to close the Query Builder (last slide)Select OK to close the Search Criteria Builder and create your query (slide not shown)•ToolStrip named OutdoorsToolStrip is added to component tray and thus your query is added!•underneath Binding NavigatorRun program; all records available as output Select Outdoors runs the query and it displays only those records with Outdoors listed in the Interests field18Customer QueriesNote the Code added to the click event for the Outdoors button on the ToolStripTryMe.TblCustomersTableAdapter.Outdoors(Me.CustomersDataSet.tblCustomers)Catch ex As System.Exception System.Windows.Forms.MessageBox.Show(ex.Message)End Try19Select TblCustomersTableAdapter in component traySelect Add Query... bottom of the Properties windowDisplays Search Criteria Builder wizardData source table already setSelect a New query name: (I named it AllQuery) and Select Query Builder Button.(Want all, so no changes)Select OK for Query Builder (see window title too)Select OK for Search Criteria Builder (see window title)20Query: Select All Records. (new)Query: AllQuerySQL stays the same.SELECT ID, LastName, FirstName, CustNum, Address, City, State, Zip, Since, [Last], Preferred, AcctBalance, InterestsFROM tblCustomersToolStrip named All added to the component try and underneath Binding Navigator stripRun the programClick Outdoors to run that query and it displays only those records in the DataGridViewClick All to display all the records in your view.21Code added to the click event for the All button in the new ToolStripAlso added another