Programming with Microsoft Visual Basic 2017 Chapter 11

Programming with Microsoft Visual Basic 2017 Chapter 11 SQL server databases 1

Focus on the Concepts Lesson Concepts covered in this lesson: • Basic database terminology • Creating a SQL Server database • Adding a table to a database • Adding records to a table • Data Source Configuration Wizard • Binding GUI controls to the database objects in a dataset • Data. Grid. View control • Copy to Output Directory property • Try. . . Catch statement • Two-table databases 2

Basic Database Terminology (1 of 3) • A computer database is an electronic file that contains an organized collection (集合) of related information. • Relational databases can be related in different ways. • Field – A single item of information about a person, place, or thing. ID field a record 3

Basic Database Terminology (2 of 3) • Record – A group of related fields that contain all the necessary data about a specific person, place, or thing • Table: A group of related records – Each record in a table pertains to the same topic and contains the same type of information • Primary key: – A field uniquely identifying a record • Foreign key – A field that links a child record to a parent record example 4

Basic Database Terminology (3 of 3) 5

Creating a SQL Server Database (1 of 4) Comparison of Data Types Visual Basic SQL Boolean bit Decimal decimal(p, s) Double Integer String float int char(n), varchar(n) 6

Creating a SQL Server Database (2 of 4) To create a SQL Server database and add a table to it: • Open the Course Info Solution. sln file contained in the Course Info Solution folder (注意 : 檔案夾路徑名稱只能含英 文、路徑名稱不過長、不在桌面). Open the designer window. • Click Project on the menu bar and then click Add New Item. If necessary, expand the Installed node and then expand the Common Items node. • Click Data. • Click Service-based Database in the middle column of the Add New Item dialog box. Change the name in the Name box to My. Courses. mdf. Click Add button. 練習 p. 478 -479. 1~4 7

Creating a SQL Server Database (Add New Item) 8

Creating a SQL Server Database (4 of 4) • Right-click My. Courses. mdf in the Solution Explorer window, and then click Open to open the Server Explorer window. • Permanently display the Server Explorer window, expand the Data Connections node, and then expand the My. Courses. mdf node. 練習 p. 479 -480. 5~6 9

Adding a Table to a Database (1 of 7) To add a table to the database: • Right-click Tables in the Server Explorer window and then click Add New Table. – The database table designer window opens. • When the design surface has finished loading, change [Table] in the T-SQL pane to [Courses]. • The key icon that appears next to Id in the Name column indicates that the Id field is the primary key. – The keywords PRIMARY KEY also appear in the T-SQL pane. • You will change the field name to ID and then have the database automatically number the field whenever a new record is added to the table. – Change Id in the Name column to ID. 練習 p. 480 -481. 1~2 10

Database Table Designer Window - 1 3 1 2 4 11

Adding a Table to a Database (2 of 7) • • Temporarily display the Properties window. Expand the Identity Specification (識別規格) property, click (Is Identity), and then change the setting to True. – The keyword IDENTITY now appears in the T-SQL pane. • The Identity Seed property in the Properties window indicates the starting number for the first record’s ID. – in this case, the starting number will be 1. • The Identity Increment property indicates the number by which each subsequent ID will be increased. – In this case, each subsequent ID will be one number more than the previous ID. 練習 p. 481. 3 12

Database Table Designer Window - 2 1 2 4 3 13

Adding a Table to a Database (3 of 7) • The ID field will contain integers, so its data type should be left at the default value, int. – • A field that is a primary key should never be empty, therefore, leave the Allow Nulls check box unselected for this field. Next, you will add the Code field to the table. – – – Click the blank line below ID in the designer window. Type Code and then press Tab. The course codes will contain six to eight characters. Click the list arrow, scroll down the list, and then click varchar(50) in the list. Change 50 in the data type to 8. Each record should always have a course code, so uncheck the Allow Nulls check box for this field. 練習 p. 481. 5 14

Database Table Designer Window - 3 1 2 3 4 5 11 6 8 7 10 9 15

Adding a Table to a Database (4 of 7) • The Title field will contain strings of variable lengths, so you will use varchar(40) as the data type. – This data type will allow the field to store up to 40 characters, which should be more than enough for a course title. – Click the blank line below Code. – Type Title, press Tab, click the list arrow, and then click varchar(50) in the list. § Change 50 in the data type to 40. – The course title should always be completed in each record. § so uncheck the Allow Nulls check box for this field. 練習 p. 481. 6 16

Adding a Table to a Database (5 of 7) • • The Hours field will contain an integer and should never contain a null value. – Click the blank line below Title. – Type Hours, press Tab, click the list arrow, click int in the list, and then uncheck the Allow Nulls check box for this field. The Grade field is last. – This field will contain one character, so you will use char(1) as its data type. – You will allow nulls in this field in case the student wants to add the course to the table before receiving his or her grade. 練習 p. 481. 7~8 17

Adding a Table to a Database (6 of 7) • Click the Update button, which appears below the dbo. Courses [Design] tab. – The Preview Database Updates dialog box opens. • Click the Update Database button. – The “Update Completed successfully” message appears in the Data Tools Operations window. • Close the Data Tools Operations window. 練習 p. 482 -483. 9~10 18

Adding a Table to a Database (7 of 7) 19

Adding Records to a Table (1 of 3) To add records to the Courses table: • Expand the Tables node in the Server Explorer window and then expand the Courses node. – (If the Courses node does not appear below the Tables node, click the Server Explorer window’s Refresh button. ) • Right-click Courses in the Server Explorer window and then click Show Table Data. • Auto-hide the Server Explorer window. 練習 p. 483. 1~2 20

Adding Records to a Table (1 of 3) expand right-click 21

Adding Records to a Table (2 of 3) • • • The first record you will add is for the Accounting Procedures course. You do not need to enter the course’s ID because the database will automatically enter it each time a record is added to the table. Click NULL in the Code field, type ACCOU 110 and then press Tab. Type Accounting Procedures, press Tab. Type 3, press Tab, type A, and press Tab. – Notice that the number 1 now appears in this record’s ID field. • Use the figure as a guide when entering the additional five records. 練習 p. 483 -484. 3~4 22

Adding Records to a Table (3 of 3) • Save the solution and then close the dbo. Courses [Data] and dbo. Courses [Design] windows. 23

Data Source Configuration Wizard (1 of 6) • Dataset : a copy of data to be accessed To create a dataset and then view its contents: • Click View on the menu bar, point to Other Windows, and then click Data Sources. • If necessary, permanently display the Data Sources window. • Click Add New Data Source in the Data Sources window to start the Data Source Configuration Wizard. • If necessary, click Database on the Choose a Data Source Type screen. 練習 p. 484 -485. 1~2 24

Data Source Configuration Wizard 1 2 25

Data Source Configuration Wizard (2 of 6) • Click the Next button to display the Choose a Database Model screen. – If necessary, click Dataset. • Click the Next button to display the Choose Your Data Connection screen. – My. Courses. mdf appears as the data connection. Click the Next button. • The Save the Connection String to the Application Configuration File screen appears and displays the name of the connection string, My. Courses. Connection. String. – Verify that the “Yes, save the connection as” check box is selected. 練習 p. 485 -486. 3~5 26

Choose Your Data Connection Screen 1 2 27

Data Source Configuration Wizard (3 of 6) • Click the Next button to display the Choose Your Database Objects screen. – You use this screen to select the table and/or fields, each of which is considered an object, to include in the dataset. – The default name for the dataset is My. Courses. Data. Set. • Expand the Tables node and then expand the Courses node. • Click the check box next to Courses. – Doing this selects the table object and its field objects. 練習 p. 486. 6~7 28

Choose Your Database Objects Screen 1 2 29

Data Source Configuration Wizard (4 of 6) • • Click the Finish button. The computer adds the My. Courses. Data. Set to the Data Sources window. Expand the Courses node. The dataset contains one table object and five field objects. Right-click 1 練習 p. 486. 8 30

Data Source Configuration Wizard (5 of 6) • • • Right-click My. Courses. Data. Set in the Data Sources window, click Preview Data, and then click the Preview button. The dataset contains six records (rows), each having five fields (columns). Notice the information in “Select an object to preview” box. • • • My. Courses. Data. Set is the name of the dataset in the application. Courses is the name of the table included in the dataset. Fill and Get. Data are methods. The Fill method populates an existing table with data, while the Get. Data method creates a new table and populates it with data. Click the Close button to close the Preview Data dialog box. 練習 p. 487. 9~10 31

Data Source Configuration Wizard (6 of 6) 32

Binding the Objects in a Dataset (1 of 5) • Binding : connecting a dataset object to a GUI control • Bound control : GUI control connected with a dataset object 33

Binding the Objects in a Dataset (2 of 5) Having the Computer Create a Bound Control 34

Binding the Objects in a Dataset (3 of 5) To bind the Courses object to a Data. Grid. View control: • Click Courses in the Data Sources window to select the Courses object. • Drag the object from the Data Sources window to the middle of the form and then release the mouse button. – The computer adds a Data. Grid. View control to the form, and it binds the Courses object to the control. • Save the solution and then start the application. • The six records appear in the Data. Grid. View control. • Use the scroll bar to view the remaining fields. 練習 p. 489. 1~3 35

Binding the Objects in a Dataset (4 of 5) • Click the Code header to display the records in ascending order by the Code field. • Then, click the Code header again to display the records in descending order by the Code field. • Close the application by clicking the Close button on the form’s title bar. 練習 p. 490. 4~5 36

Binding the Objects in a Dataset (5 of 5) The Objects in Component Tray 37

The Objects in Component Tray • Binding. Navigator control is used to move from one record to the next in the dataset. • Table. Adapter object connects the database to the Data. Set object which stores the information you want to access from the database. • Any changes made to the Data. Set are saved to the database by the Table. Adapter. Manager object. • The Binding. Source object provides the connection between the Data. Set and the bound controls on the form. 38

Data. Grid. View Control (1 of 7) • The Data. Grid. View control is one of the GUI controls for displaying table data. • The intersection of a row and a column is called a cell. • The control’s Auto. Size. Columns. Mode property determines the way the column widths are sized in the control. 39

Data. Grid. View Control (2 of 7) To improve the appearance of Courses. Data. Grid. View control: • Temporarily display the Properties window for the Courses. Data. Grid. View control. – Click Auto. Size. Columns. Mode in the Properties list and then set the property to Fill. • Click the Courses. Data. Grid. View control to close the Properties window. • Click the control’s task box and then click Dock in Parent Container. – The Dock in Parent Container setting anchors the control’s borders to the borders of its container, which is the form. 練習 p. 492. 1~2 40

Data. Grid. View Control (3 of 7) • • Click Edit Columns in the task list. ID is selected in the Selected Columns box. – Recall that the ID field is an auto-numbered field. • • • Click the Alphabetical button to display the property names in alphabetical order. Locate the column’s Read. Only property and verify that the property is set to True. Click Code in the Selected Columns box. – Change the column’s Auto. Size. Mode property to All Cells. • Click Title in the Selected Columns box and then change its Auto. Size. Mode property to All Cells. 練習 p. 492. 3~5 41

Data. Grid. View Control (4 of 7) Task Box of Data. Grid. View 42

Data. Grid. View Control (5 of 7) • • Click Hours in the Selected Columns box. Click Default. Cell. Style, and then click the. . . (ellipsis) button to open the Cell. Style Builder dialog box. – You can use this dialog box to format a column’s numbers and also to specify its alignment. – Click Alignment, click the list arrow, click Middle. Center, and then click the OK button. • Click Grade in the Selected Columns box, click Default. Cell. Style, and then click the. . . (ellipsis) button. – Use the Cell. Style Builder dialog box to change the Grade field’s Alignment property to Middle. Center. 練習 p. 493. 6~8 43

Cell. Style Builder 44

Data. Grid. View Control (6 of 7) • • Click the OK button to close the Cell. Style Builder dialog box, and then click the OK button to close the Edit Columns dialog box. Click the Courses. Data. Grid. View control to close its task list. – Auto-hide the Data Sources window. • • • Save the solution and then start the application. The six records in the dataset appear in the Courses. Data. Grid. View control, with the first record’s ID highlighted. Close the application. 練習 p. 493 -494. 10~12 45

Data. Grid. View Control (7 of 7) 46

Copy to Output Directory Property (1 of 4) To change the My. Courses. mdf file’s Copy to Output Directory property: • Display the Solution Explorer window. • Right-click My. Courses. mdf and then click Properties. • Change the file’s Copy to Output Directory property to Copy if newer. • Save the solution and then start the application. • Click the empty cell below CIS 112. • Type PSYCH 100, press Tab, type General Psychology, press Tab, and then type 3. Leave the Grade field empty. • Click the Save Data button on the Binding. Navigator control to save the new record. • Notice that the – 1 in the ID column changes to number 7. 練習 p. 495. 1~2 47

Copy to Output Directory Property (2 of 4) 48

Copy to Output Directory Property (3 of 4) • • • Now, enter the following record below the PSYCH 100 record: ART 100, Ceramics, 2, B. Click the Save Data button. Close the application by clicking the Close button on the form’s title bar. Then, start the application again. The new records appear in the Data. Grid. View control. Type C in the PSYCH 100 course’s Grade field. Click ART 100 in the Code column and then click the Delete button (the red X) on the Binding. Navigator control. Click the Save Data button to save the changes. 49

Copy to Output Directory Property (4 of 4) • Close the application and then start it again to verify that the changes you made were saved. • The PSYCH 100 grade (C) appears in the course’s Grade field, and the ART 100 record was removed from the dataset. • Close the application. 練習 p. 495 -496. 3~6 50

Try. . . Catch Statement (1 of 6) To view the code automatically entered in the Code Editor window: • Open the Code Editor window. • The two procedures shown in figure were automatically entered when you dragged the Courses table object to the form. 練習 p. 497. 1 51

Try. . . Catch Statement (2 of 6) • Exception : an error that occurs while an application is running. • The abnormal behavior of an application can be prevented by using Try. . . Catch statement to control exception handling. • Exception message is stored in the Message property of the Catch block’s ex parameter. 52

Try. . . Catch Statement (3 of 6) 53

Try. . . Catch Statement (4 of 6) To include a Try. . . Catch statement in the Save Data button’s Click event Procedure: • Insert a blank line above the Me. Validate() statement in the Courses. Binding. Navigator. Save. Item_Click procedure. • Type Try and press Enter. The Code Editor automatically enters the Catch ex As Exception and End Try clauses for you. • Move three statements that appear below the End Try clause, as well as the blank line below the statements, into the Try block. 54

Try. . . Catch Statement (5 of 6) • • If the three statements in the Try block do not produce (throw) an exception, the Try block should display the “Changes saved. ” message; otherwise, the Catch block should display a description of the exception. Enter the two Message. Box. Show methods indicated in the figure and then delete the blank line below the End Try clause. Save the solution and then start the application. The statement in the frm. Main_Load procedure retrieves the appropriate data from the My. Courses. mdf database file and loads the data into the My. Courses. Data. Set. The data are displayed in the Data. Grid. View control. 練習 p. 499. 1~4 55

Try. . . Catch Statement (6 of 6) • Delete the PSYCH 100 record and then click the Save Data button. The “Changes saved. ” message appears in a message box. Close the message box. • Stop the application, and then start it again to verify that the PSYCH 100 record is no longer in the dataset. • Stop the application. Close the Code Editor window and then close the solution. 練習 p. 499. 5~6 56

Two-Table Databases (1 of 14) To open the Charleston Sales application and connect it to the Charleston. mdf database file: • Open the Charleston Sales Solution. sln file contained in the Charleston Sales Solution folder. • Open the designer window. • Display the Data Sources window. (If the window is not open, click View on the menu bar, point to Other Windows, and then click Data Sources. ) • Click Add New Data Source in the Data Sources window to start the Data Source Configuration Wizard. • If necessary, click Database on the Choose a Data Source Type screen. 練習 p. 500. 1~3 57

Two-Table Databases (2 of 14) • • Click the Next button to display the Choose a Database Model screen. If necessary, click Dataset. Click the Next button to display the Choose Your Data Connection screen. Click the New Connection button. Verify that Microsoft SQL Server Database File (Sql. Client) appears in the Data source box. (If it does not, click the Change button, click Microsoft SQL Server Database File, and then click the OK button. ) Click the Browse button in the Add Connection dialog box. Open the Databases folder, click Charleson. mdf, and then click the Open button. 練習 p. 500. 4~6 58

Add Connection Dialog Box 59

Two-Table Databases (3 of 14) • • • Click the Test Connection button. The “Test connection succeeded. ” message appears in a message box. Close the message box. Click the OK button in the Add Connection dialog box. Charleston. mdf appears next to the New Connection button. Click the Next button. The message box shown in figure 11 -37 opens. The message asks whether you want to include the database file in the current project. – By including the file in the project, you can more easily copy the application and its database to another computer. 練習 p. 500 -501. 7~8 60

61

Two-Table Databases (4 of 14) • • Click the Yes button to add the Charleston. mdf file to the application’s project folder. The Save the Connection String to the Application Configuration File screen appears next and displays the name of the connection string, Charleston. Connection. String. Verify that the “Yes, save the connection as” check box is selected. Click the Next button to display the Choose Your Database Objects screen. Use this screen to select the table and/or field objects to include in the dataset. Expand the Tables node and then expand the Location and Salesperson nodes. – Click the check box next to Tables. 練習 p. 501. 9~11 62

Choose Your Database Objects screen 63

Two-Table Databases (5 of 14) • Click the Finish button. • The computer adds the Charleston. Data. Set to the Data Sources window. • The dataset contains two table objects. Expand the Location and Salesperson nodes in the Data Sources window. 練習 p. 502. 12 64

Two-Table Databases (6 of 14) To relate both tables by their Country. Code field: • Right-click Charleston. Data. Set in the Data Sources window and then click Edit Data. Set with Designer. The Data. Set Designer window opens. • Right-click an empty area of the window, point to Add, and then click Relation to open the Relation dialog box. • The Name box shows the names of the two tables you want to relate. – The Location table is the parent table because the Country. Code field is the primary key in that table. – The Salesperson table is the child table because the Country. Code field is the foreign key in that table. 練習 p. 502. 1~2 65

Data. Set Designer Window 66

Relation Dialog Box 67

Two-Table Databases (7 of 14) • Click the OK button. • The line between both tables in the Data. Set Designer window indicates that a one-to-many relationship exists between the tables. • Save the solution. Slide 69使用 練習 p. 503. 3~4 68

Two-Table Databases (8 of 14) To create a query: • Click Salesperson. Table. Adapter in the Data. Set Designer window. • Right-click Fill, Get. Data(), and then click Configure to open the Table. Adapter Configuration Wizard. • Click the Query Builder button to open the Query Builder dialog box. • The Salesperson table’s primary key (Sales. Id) appears boldfaced in the Diagram pane. 練習 p. 504. 1~2 69

Query Builder Dialog Box 70

Two-Table Databases (9 of 14) • Right-click an empty area in the Diagram pane and then click Add Table to open the Add Table dialog box. – – • The Location table appears next to the Salesperson table in the Diagram pane. – • • The Location table is selected on the Tables tab. Click the Add button and then click the Close button. Notice that its primary key (Country. Code) appears boldfaced. In the Location table, click the Country check box to select the Country field. Then, in the Salesperson table, click the Country. Code check box to deselect the Country. Code field. 練習 p. 505. 3~4 71

Building Query and Query Result 72

F-10 Two-Table Databases (10 of 14) • Click the Execute Query button. – • • • The Results pane displays the Sales. Id, First. Name, and Last. Name fields for each record in the Salesperson table, as well as each record’s corresponding Country field from the Location table. Click the OK button to close the Query Builder Dialog box, and then click the Finish button to close the Table. Adapter Configuration Wizard. Save the solution and then close the Data. Set Designer window. Expand the Location and Salesperson nodes in the Data Sources window. – Notice that in this dataset, the Salesperson entry contains the Sales. Id, First. Name, and Last. Name fields from the Salesperson table, as well as the Country field from the Location table. 練習 p. 505 -506. 5~8 73

Two-Table Databases (11 of 14) (Data Sources Window) 74

Two-Table Databases (12 of 14) To display the results of the query in a Data. Grid. View control: • Drag the Salesperson table from the Data Sources window to the middle of the form. • Set the Salesperson. Data. Grid. View control’s Auto. Size. Columns. Mode property to Fill. • Click the Salesperson. Data. Grid. View control’s task box and then click Dock in Parent Container. • In this application, the Data. Grid. View control will only display the records. • • The user will not be allowed to add, edit, or delete records. Uncheck the Enable Adding, Enable Editing, and Enable Deleting check boxes. 練習 p. 506. 1~3 75

Two-Table Databases (13 of 14) • Click Edit Columns and then click the Alphabetical button in the Edit Columns dialog box. • Use the Header. Text property to change the first three column headings to Sales ID, First Name, and Last Name. • Click the OK button to close the Edit Columns dialog box. • Change the form’s Size property to 575, 235. • Right-click the Save Data button (the disk) on the Salesperson. Binding. Navigator control and then click Delete. – Use the same process to remove both the Add new button (the plus sign) and the Delete button. 練習 p. 506 -507. 4~7 76

Two-Table Databases (14 of 14) • Save the solution and then start the application. • Stop the application and then close the solution. 練習 p. 507. 8~9 77

APPLY THE CONCEPTS LESSON After studying this lesson, you should be able to: • A-1 Create a data form • A-2 Bind field objects to existing controls • A-3 Perform calculations on the fields in a dataset 78

A-1 Create a Data Form (1 of 10) To create a data form: • Open the Course Info Solution. sln file contained in the Course Info Solution-Data Form folder. • • • Open the Data Sources window by clicking View on the menu bar, pointing to Other Windows, and then clicking Data Sources. Click Add New Data Source to display the Choose a Data Source Type screen. • • Open the designer window. If necessary, click Database. Click the Next button to display the Choose a Database Model screen. • If necessary, click Dataset. 練習 p. 508. 1~4 79

A-1 Create a Data Form (2 of 10) • • • Click the Next button to display the Choose Your Data Connection screen. Click the New Connection button. Verify that Microsoft SQL Server Database File (Sql. Client) appears in the Data source box. Add Connection Dialog – • • • (If it does not, click the Change button, click Microsoft SQL Server Database File, and then click the OK button. ) Click the Browse button. Open the Databases folder, click My. Courses. mdf, and then click the Open button. Click the Test Connection button. The “Test connection succeeded. ” message appears in a message box. Close the message box. 練習 p. 508. 5~7 80

A-1 Create a Data Form (3 of 10) • • • Click the OK button to close the Add Connection dialog box. My. Courses. mdf appears in the box next to the New Connection button in the Choose Your Data Connection screen. Click the Next button. Click the Yes button to add the My. Courses. mdf file to the application’s project folder. Then, click the Next button to display the Choose Your Database Objects screen. Expand the Tables node and then click the check box next to Courses. Click the Finish button. The computer adds the My. Courses. Data. Set to the Data Sources window. – Expand the Courses node in the Data Sources window. The dataset contains one table object and five field objects. 練習 p. 508. 8~10 81

A-1 Create a Data Form (4 of 10) • • Click the Courses list arrow and then click Details. Recall that the ID field is an auto-numbered field, which means that the database will take care of completing the field for each new record. – – • Therefore, you will display the ID field in a label control. Click ID in the Data Sources window, click the list arrow, and then click Label. Now, drag the Courses table to the middle of the form and then release the mouse button. – The computer adds 10 controls to the form: six labels and four text boxes. Each label and text box is associated with a field object in the dataset. 練習 p. 508 -509. 11~12 82

A-1 Create a Data Form (4 of 10) 83

A-1 Create a Data Form (5 of 10) • • Click the form to deselect the selected controls. Modify the interface as shown in Figure 11 -47. – • • Be sure to add the access keys to the labels and also change the names of the text boxes as indicated. Lock the controls on the form. Click the txt. Code control and then set its Max. Length property to 8. Set the Max. Length properties for the txt. Hours and txt. Grade controls to 1. Set the Character. Casing properties for the txt. Code and txt. Grade controls to Upper. 練習 p. 509 -510. 13~16 84

A-1 Create a Data Form (6 of 10) • • The application will allow the user to add, edit, and delete records, so you will need to set the database file’s Copy to Output Directory property. Display the Solution Explorer window. Right-click My. Courses. mdf and then click Properties. Set the Copy to Output Directory property to Copy if newer. Save the solution and then start the application. – • • The first record appears in the data form. Use the Move next, Move last, Move previous, and Move first buttons on the Binding. Navigator control to view the remaining records. Close the application. 練習 p. 510. 17~20 85

A-1 Create a Data Form (7 of 10) 86

A-1 Create a Data Form (8 of 10) To code the procedures and then start and test the application: • Auto-hide any open windows except for the form designer window. • Open the Code Editor window and locate the Courses. Binding. Navigator. Save. Item_Click procedure. Modify the procedure as shown in figure. • Open the code template for the txt. Hours_Key. Press procedure. • • Enter the comment and code shown in figure. Save the solution and then start the application. 練習 p. 510 -511. 1~3 87

txt. Hours_Key. Press Procedure 88

A-1 Create a Data Form (9 of 10) • Click the Add new button (the plus sign) on the Binding. Navigator control. – – • Now, click the Add new button and then click the Code box. – • Type psych 100, press Tab, type General Psychology, press Tab, and then type 3. Leave the Grade field empty. Click the Save Data button and then close the message box that confirms that the changes were saved. Enter the following record: art 100, Ceramics, 2, b. Click the Save Data button and then close the message box. 練習 p. 511. 4~5 89

A-1 Create a Data Form (10 of 10) • • Stop the application and then start it again. Use the Binding. Navigator control to locate the PSYCH 100 record, which is record 7 in the dataset. – • Locate the ART 100 record, which is the last record in the dataset. – • • Press the Delete button (the red X), Click the Save Data button, and close the message box. Stop the application and then start it again. – • Type C in the record’s Grade field. Verify that the PSYCH 100 grade was saved and that the ART 100 record is no longer in the dataset. Stop the application and close the solution. 練習 p. 511. 6~8 90

A-2 Bind Field Objects to Existing Controls (1 of 5) To bind objects to existing controls: • Open the Course Info Solution. sln file contained in the Course Info Solution-Labels folder. • Open the designer window and then display the Solution Explorer window. • • The application is already connected to the My. Courses. mdf file, and the My. Courses. Data. Set has already been created. Display the Data Sources window. • • Expand the My. Courses. Data. Set node and then expand the Courses node. The dataset contains one table object and five field objects. 練習 p. 511 -512. 1~2 91

A-2 Bind Field Objects to Existing Controls (2 of 5) • • Click Code in the Data Sources window, drag the field object to the lbl. Code control, and then release the mouse button. The computer binds the field object to the control and also adds the My. Courses. Data. Set, Courses. Binding. Source, Courses. Table. Adapter, and Table. Adapter. Manager objects to the component tray. – Notice that when you drag an object from the Data Sources window to an existing control, the computer does not add a Binding. Navigator object to the component tray, nor does it add a Binding. Navigator control to the form. 練習 p. 512. 3 92

Data Sources Window and Application GUI 93

A-2 Bind Field Objects to Existing Controls (3 of 5) • Click Grade in the Data Sources window and then drag the field object to the lbl. Grade control. – • • Release the mouse button. You can use the Binding. Navigator tool, which is located in the Data section of the toolbox, to add a Binding. Navigator control and object to the application. Temporarily display the Toolbox window. – – – Expand the Data node and then click Binding. Navigator. Drag the Binding. Navigator tool to the top of the form and then release the mouse button. The computer adds the Binding. Navigator 1 control to the form and adds the Binding. Navigator 1 object to the component tray. 練習 p. 512. 4~5 94

A-2 Bind Field Objects to Existing Controls (4 of 5) • • • Temporarily display the Properties window. Set the Binding. Navigator 1 control’s Binding. Source property to Courses. Binding. Source. Right-click the Delete button (the red X) on the Binding. Navigator 1 control and then click Delete. Right-click the Add new button (the plus sign) and then click Delete. Save the solution and then start the application. – • The first record appears in the interface. Use the Move next, Move last, Move previous, and Move first buttons on the Binding. Navigator control to view the remaining records. 練習 p. 512 -513. 6~9 95

A-2 Bind Field Objects to Existing Controls (5 of 5) • Close the application and then close the solution. 96

A-3 Perform Calculations on the Fields in a Dataset (1 of 6) To open this version of the Course Information application: • Open the Course Info Solution. sln file contained in the Course Info Solution-Total Hours folder. • Open the designer window. • • The application is connected to the My. Courses. mdf file. The dataset has already been created and is named My. Courses. Data. Set. • Start the application. • • The dataset information appears in the Data. Grid. View control. The dataset contains 14 records and five fields. 練習 p. 513. 1~2 97

A-3 Perform Calculations on the Fields in a Dataset (2 of 6) • Use the scroll bar to view the remaining records. • Notice that the Grade field for the first ENG 101 record contains W, and the Grade field for the SOCIO 100 record is empty. • The Hours field for both of these records should not be included in the total number of credit hours completed. • Click the Exit button. 98

A-3 Perform Calculations on the Fields in a Dataset (3 of 6) pseudocode for the btn. Calc_Click procedure 99

A-3 Perform Calculations on the Fields in a Dataset (4 of 6) To code the btn. Calc_Click procedure: • Open the Code Editor window and locate the btn. Calc_Click procedure. • • The procedure will use an Integer variable named int. Total to accumulate the values in the Hours field, excluding the values stored in the first ENG 101 record and the SOCIO 100 record. Click the blank line above the End Sub clause. Type the following declaration statement and then press Enter twice: Dim int. Total As Integer • The easiest way to access each record (row) in a dataset is by using a For Each. . . Next statement. • • Enter the For Each. . . Next statement shown in figure 11 -57 and then position the insertion point as shown in figure. Be sure to change the Next clause to Next row. 練習 p. 514. 1~2 100

btn. Calc_Click Procedure 101

A-3 Perform Calculations on the Fields in a Dataset (5 of 6) • The loop will use a selection structure to determine whether the Grade field’s value is not NULL and is not the letter W. • You can make the NULL determination by using the Is. Grade. Null method. The method returns a Boolean value: • True if the field contains Null and False if it does not. • If the Grade field does not contain NULL and it does not contain the letter W, the selection structure’s True path should add the number of hours stored in the record’s Hours field to the int. Total accumulator variable. • Enter the selection structure. 練習 p. 514. 3 102

A-3 Perform Calculations on the Fields in a Dataset (6 of 6) • The last step in the procedure’s pseudocode is to display the contents of the int. Total variable in the lbl. Total control. – • Save the solution and then start the application. – • • Insert a blank line below the Next row clause and then type the assignment statement shown in figure. Click the Calculate button. The total number of hours completed is 40. Click the Exit button. Close the Code Editor window and then close the solution. 練習 p. 515. 4~5 103

Summary (1 of 5) • • Most business store information in computer databases. The information in a relational database is stored in one or more tables. – Each table is composed of fields (columns) and records (rows). • Most tables have a primary key that uniquely identifies each record in the table. – Some tables also have a foreign key that is used to relate one table to another table. 104

Summary (2 of 5) • You can use the Data Source Configuration Wizard to connect an application to a database file. – To start the wizard, open the Data Sources window by clicking View, pointing to Other Windows, and then clicking Data Sources. – Then, click Add New Data Source in the Data Sources window. • For the user to view the information contained in a dataset, you must bind one or more of the objects in the dataset to one or more controls. 105

Summary (3 of 5) • A Data. Grid. View control displays data in a row (record) and column (field) format, similar to a spreadsheet. – To have the columns fill the control’s display area, set the control’s Auto. Size. Columns. Mode property to Fill. – To anchor the control to the borders of its container (which is typically the form), click the Dock in Parent Container option on the control’s task list. • You can use the Edit Columns option on the Data. Grid. View control’s task list to add columns, remove columns, and reorder columns. – You can also use it to change the properties of the columns. 106

Summary (4 of 5) • • A database file’s Copy to Output Directory property determines the way Visual Basic saves the changes made to a local database file. You can use the Try. . . Catch statement to handle exceptions (errors) that occur during run time. A database query allows you to retrieve specific information from a database, such as the fields and records you want to display. You can create the query using the Query Builder dialog box and Structured Query Language (SQL). 107

Summary (5 of 5) • • • When inputting data, it is often easier to use a data form, which typically provides text boxes for entering data, instead of a Data. Grid. View control. You can access each record (row) in a dataset using a loop along with the dataset table’s Rows collection. You can use the Isfield. Null method to determine whether the field contains the NULL value. 108