Programming in Visual Basic 2010: The Very Beginner’s Guide

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

2. Customer 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 query Query search or searches in a database; returnspecifiedfields from selected records and displaythem in a specific order

3. Database File for this Part We need the Customers database file See my web page. Download and open and look at it. Close it.

4. Customer Queries Create a new form Name the controls Add a DataGridView (I used a form)

5. Tasks – 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 NameProperty Add DataSource property via drop down…. Goto next slide

6. (except this one)If using DataGridView in lieu of Form If using DataGridView in lieu of form, there are a few differences. Here are a few… select DataGridView control. Click on play Choose DataSource can select WagesDataSetBinding Links WagesDataSet and WagesDataSetBinding in component tray Select WagesDataSetBindingSource See properties DataSource(linked to WagesDataSet) Select DataMember; select tblWages Note: grid modified and has fields. Expand. Fill form space up with grid…

7. Tasks Add 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, OK Next, Yes, Next, Goto next slide

8. Tasks Step 10 in book, p. 588 Select Database Objects for the Data Set. we do this by expanding the Tables Checkbox click on tblCustomers. Leave checked. Finish Component Tray now includes A Customers Data Set

9. Tasks SelectDataMemberproperty of bindingsource (bdsCustomer) and set to tblCustomers. Adds TBLCustomersTable Adaptor to Component tray. Save Project. Onto Step 13 in book…

10. Tasks Bind 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.

11. Tasks Add Binding Navigator drag from toolbox to top of form Name it bdsCustomers via property window Link your binding navigator to your data by changing BindingSourceproperty of the binding navigator to bdsCustomer (drop down) Run your program!!

12. On to Customer Queries Select TblCustomersTableAdapter in component tray. SelectAdd Query... at the bottom of the Properties window Search Criteria Builder wizard displays Select a New query name: and enter Outdoors to build a query of customers interested in the outdoors (see next slide)

13. Query Wizard looks like: Customers Search Criteria Builder Added query name

14. Customer Queries Select Query Builder button to open the Query Builder dialog (three slides down) Top section has the fields listed for tblCustomers and all fields are selected Leave all the fields selected All fields must be selected when using the DataGridView

15. Customer Queries Second section has a list of the fields and the search criteria for it (two slides down) Build your query in this section Scroll down to the Interests field Change the Filter to = ‘Outdoors’ Modifies a query template (next slide) that looks through all the records and returns those with Outdoors as an Interest And adds the query being built in the third section See SQL next slide:

16. Customer Queries Third section now reads SELECT ID, LastName, FirstName, CustNum, Address, City, State, Zip, Since, [Last], Preferred, AcctBalance, Interests FROM tblCustomers WHERE (Interests = 'Outdoors') ADDED THE Where! WHERE clause added to the SQL statement being build Statement says to select all fields from tblCustomers and only those records where ‘Outdoors’ is in the Interests field

17. Customer Queries Customers Query Builder

18. Customer Queries Select OK to close the Query Builder (last slide) SelectOKto 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 Navigator Runprogram; all records available as output SelectOutdoors runs the query and it displays only those records with Outdoors listed in the Interests field

19. Customer Queries Note the Code added to the click event for the Outdoors button on the ToolStrip Try Me.TblCustomersTableAdapter.Outdoors(Me.CustomersDataSet.tblCustomers) Catch ex As System.Exception System.Windows.Forms.MessageBox.Show(ex.Message) End Try

20. Query: Select All Records. (new) SelectTblCustomersTableAdapter in component tray Select Add Query... bottom of the Properties window Displays Search Criteria Builder wizard Data source table already set Select a New query name: (I named it AllQuery) and Select Query Builder Button. (Want all, so no changes) Select OK forQueryBuilder(see window title too) Select OK forSearchCriteriaBuilder(see window title)

21. Query: AllQuery SQL stays the same. SELECT ID, LastName, FirstName, CustNum, Address, City, State, Zip, Since, [Last], Preferred, AcctBalance, Interests FROM tblCustomers ToolStrip named All added to the component try and underneath BindingNavigator strip Run the program Click Outdoors to run that query and it displays only those records in the DataGridView Click All to display all the records in your view.

22. Query: AllQuery Code added to the click event for the All button in the new ToolStrip Also added another subroutine in the code window for this query: Private Sub AllQueryToolStripButton_Click…. Try Me.TblCustomersTableAdapter.All(Me.CustomersDataSet.tblCustomers) Catch ex As System.Exception System.Windows.Forms.MessageBox.Show(ex.Message) End Try

23. More If you want to play with these, you can get rid of a set of query toolstrips. See book.

24. Data Base Terminology - Important When a DataSet is created it generates an .xsd file View it by selecting the TableAdapter and EditQueries in the DataSet Designer at the bottom of the Properties window Contains the XML schema XML (Extensible Markup Language): Is a specification for creating content and structure for data; XML is an open standard specification widely used as a specification language for data and the web

25. Customer Queries These documents define and validate the content and structure of your data Use to exchange data between applications Schema a diagram or plan detailing the structure of your content; for VB, it contains the structure for your DataSet

26. Customer Queries Customers DataSet1 Existing Queries

27. VB Quiz 03 What happens if you don't select all the fields for a query? An Exception will be generated. What makes the Query Builder useful? You don’t have to know SQL. But this is limiting… How were the records sorted in the Payable query? Last Name What SQL code was needed to do it? Ordered by…

28. Potential Problems Your program won’t work if the database is corrupt Database file cannot be in use by another program Don’t delete or change code that’s automatically generated Bind your DataSet to a DataGridView or to individual controls such as a TextBox or Label Include a BindingNavigator so you can move between records Be careful when deleting controls from the component tray and Solution Explorer

29. On Your Own Take a look at Appendix G -- Structured Query Language (SQL) Basics Add queries to the Customer program Customers in your state Customers without Preferred status Customers name Ecks, Ray Customers since 2008

30. On Your Own Create a program with the Customers file that displays data in TextBoxes and CheckBoxes Add controls and code to allow the user to update the database

31. Part 5; Project 4. 50 points Create a program with the KidsFirst file that displays data in a DataGridView Add queries to find all students in Miss Brooks class all students without a pet (look for ‘none’) all seven-year-old students all seven-year-old students in Mr. Moore’s class all students in order by LastName all students in descending order by Locker

32. If you are smart…. You will go over the Review questions, the terms, the multiple choice questions, etc. at the end of the chapter!

33. Summary Visual Basic has controls that can link it to a database Chapter contains tutorials to demonstrate how to connect to an Access file and manipulate records in a file Database controls were introduced Simple SQL examples were introduced