chapter fourteen access databases and sql programming with microsoft visual basic 2010 5 th edition

Chapter FourteenAccess Databases and SQL

Programming with Microsoft Visual Basic 2010

5th Edition

Programming with Microsoft Visual Basic 2010, 5th Edition

Previewing the Academy Award Winners Application

2

Open the Award.exe file Application displays records from the

Movies databaseMovies database stores information on

Academy Award winners for Best PictureMovie titleYearName of production company

Application allows user to add or delete database records


Figure 14-1 Academy Award Winners application

3


Previewing the Academy Award Winners Application (cont’d.)

4

Add missing record to databaseClick Year won text box

In Add new record section of the interfaceType 2002Enter Chicago as movie nameEnter Miramax as production companyClick the Add button


Figure 14-2 Result of adding the missing record

5


Previewing the Academy Award Winners Application (cont’d.)

6

Delete the record from the databaseClick 2002 in first column of DataGridView

controlClick the Delete buttonClick Yes to confirm the deletion


Lesson A Objectives

7

After studying Lesson A, you should be able to:

Add records to a datasetDelete records from a datasetSort the records in a dataset


Adding Records to a Dataset

8

Add and delete records:Without using a BindingNavigator control

Microsoft database is stored in Movies.accdb fileContains one table, tblMoviesContains nine records, each with three fields

YearWon field is numericTitle field contains textProductionCo field contains text


Adding Records to a Dataset (cont’d.)

9

Figure 14-3 Data contained in the tblMovies table



10

Figure 14-4 Interface for the Academy Award Winners application



11

Figure 14-5 Records displayed in the TblMoviesDataGridView control



12

Figure 14-6 Syntax and examples of adding a record to a dataset



13

TableAdapter object’s Update methodUsed to save the changes to the database

associated with the datasetGood practice:

Place Update method within the Try block of a Try….Catch statementBecause errors can occur when saving data


Figure 14-8 Syntax and examples of saving dataset changes to a database (continues)

14


Figure 14-8 Syntax and examples of saving dataset changes to a database (cont’d.)

15


Figure 14-9 Add button’s Click event procedure

16


Sorting the Records in a Dataset

17

BindingSource object’s Sort methodUsed to sort records

To have records sorted when application is started:Place Sort method in the form’s Load event

procedure


Figure 14-11 Syntax and examples of sorting the records in a dataset

18


Deleting Records from a Dataset

19

Code Delete button’s Click event procedureDeletes record whose YearWon field contains

the value entered in the txtDeleteYear controlBefore deleting record, display a message

that asks user for confirmationUse MessageBox.Show method


Figure 14-13 MessageBox.Show method entered in the btnDelete control’s Click event procedure

20


Figure 14-14 Syntax and examples of locating a record in a dataset

21


Figure 14-15 Syntax and an example of deleting a record from a dataset

22


Figure 14-16 Additional code entered in the btnDelete control’s Click event procedure

23


Lesson A Summary

24

To add a record to a dataset, use the syntax:dataSetName.tableName.AddtableRow(valueField1[,valueField2…, valueFieldN])

Use the TableAdapter object’s Update method to save dataset changes to a database

Use the BindingSource object’s Sort method to sort the records in a dataset

To locate a record in a dataset, use the syntax:dataRowVariable=dataSetName.tableName.FindByfieldName(value)


Lesson A Summary (cont’d.)

25

Use the DataRow variable’s Delete method to delete a record from a dataset


Lesson B Objectives

26

After studying Lesson B, you should be able to:

Query a database using the SQL SELECT statement

Create queries using the Query Builder dialog box


Structured Query Language

27

SQL (Structured Query Language)Set of statements to perform common

database tasksExamples: Storing, retrieving, updating, deleting,

and sorting dataCan be used with a variety of database

management systems and computers of all sizes


The SELECT Statement

28

Most commonly used statement in SQLAllows you to specify fields and records you

want to viewCan control the order in which records

appear when displayedWHERE and ORDER BY clauses

Optional parts of the syntaxWHERE is used to limit records displayedORDER BY is used to sort records


Figure 14-20 Syntax and examples of the SELECT statement (continues)

29


Figure 14-20 Syntax and examples of the SELECT statement (cont’d.)

30


Creating a Query

31

Open the DataSet Designer windowPoint to Add on the shortcut menu and

click QueryChoose SQL statements as the Command

typeChoose a Query TypeTwo ways to build a query using the SELECT

statementType the statement yourselfOr use the Query Builder button

Opens the Query Builder dialog box


Creating a Query (cont’d.)

32

Figure 14-22 DataSet Designer window



33

Figure 14-23 Choose a Command Type screen in the TableAdapter Query Configuration Wizard



34

Figure 14-24 Choose a Query Type screen

Programming with Microsoft Visual Basic 2010, 5th Edition35

Figure 14-25 Specify a SQL SELECT statement screen


Figure 14-26 Query Builder dialog box



37

Click Execute Query button to run queryResults appear in the Results pane

Create query to select all fields for records from year 2006 and later


Figure 14-28 SELECT statement containing a WHERE clause


Figure 14-30 Records displayed in ascending order by the Title field


Lesson B Summary

40

The SELECT statement is used to query a database using SQL

The SELECT statement’s WHERE clause limits the records displayed

The SELECT statement’s ORDER BY clause sorts the selected records in a specified order

To open the DataSet Designer window:Open the dataset’s schema file (.xsd)


Lesson B Summary (cont’d.)

41

To start the TableAdapter Query Configuration Wizard:Right-click table adapter’s name in DataSet

Designer windowPoint to add on shortcut menu and click Query

To open the Query Builder dialog box:Use TableAdapter Query Configuration WizardSpecify a SQL SELECT statement screen

Then click Query Builder button

The % wildcard is used to represent characters in the WHERE clause’s condition


Lesson C Objectives

42

After studying Lesson C, you should be able to:

Create a parameter querySave a queryInvoke a query from codeAdd records to a dataset using the SQL

INSERT statementDelete records from a dataset using the

SQL DELETE statement


Parameter Queries

43

Parameter markerA question mark (?)Used in a parameter queryAllows user to specify query parameters


Parameter Queries (cont’d.)

44

Figure 14-32 Examples of parameter queries


Parameter Queries (cont’d.)

45

Test SELECT statements from Figure 14-32Right-click MoviesDataSet.xsd

Then click Open to open DataSet Designer windowStart the TableAdapter Query Configuration

WizardUse Query Builder dialog to create a query

that selects only the Chicago recordExecute queryType Chicago in the Value column of the

Query Parameters dialog box


Figure 14-34 Query Parameters dialog box


Saving a Query

47

For an application to use a query during run time:Must save query and invoke it from code

Use the TableAdapter Query Configuration WizardSave a query that contains SELECT

statementAssociate the query with one or more

methods


Figure 14-36 Default query in the Specify a SQL SELECT statement screen

Figure 14-37 Parameter query in the Specify a SQL SELECT statement screen


Figure 14-38 Completed Choose Methods to Generate screen


Figure 14-39 Wizard Results screen

Figure 14-40 Method names included in the DataSet Designer window


Invoking a Query from Code

51

Methods associated with a queryCan be used to invoke query during run time

Next exampleEnter appropriate methods in Display

button’s Click event procedure


Invoking a Query from Code (cont’d.)

52

Figure 14-41 If clause and Fill method entered in the procedure


Invoking a Query from Code (cont’d.)

53

Figure 14-43 Display button’s Click event procedure


The INSERT and DELETE Statements

54

INSERT statementUsed to insert records in a database

DELETE statementUsed to delete records from a database

Both statements can be used in a queryKnown as Insert query and Delete query


Figure 14-45 Syntax and examples of the SQL INSERT statement


Figure 14-46 Syntax and examples of the SQL DELETE statement


The INSERT and DELETE Statements (cont’d.)

57

Create an insert queryStart the TableAdapter Query Configuration

WizardVerify Use SQL statements radio button is

selectedClick INSERT radio button

On Choose a Query Type screenDefault INSERT statement appears

Change the function’s name


Figure 14-49 Choose Function Name screen

Figure 14-48 Default INSERT statement for the tblMovies table


Figure 14-51 InsertRecordQuery function

Figure 14-50 Wizard Results screen



60

Create a delete queryStart the TableAdapter Query Configuration

WizardClick DELETE radio button

On Choose a Query Type screenDefault DELETE statement appears

Change the function’s name


Figure 14-53 DeleteRecordQuery function

Figure 14-52 SQL DELETE statement



62

Code Click event procedures for Add and Delete buttonsAdd button uses InsertRecordQuery functionDelete button uses DeleteRecordQuery

functionTest the Add and Delete buttons


Figure 14-56 Selection structure entered in the btnDelete control’s Click event procedure

Figure 14-55 Additional lines of code entered in the btnAdd_Click procedure


Figure 14-57 Nested selection structure entered in the procedure

Figure 14-58 Additional lines of code entered in the btnDelete_Click procedure


Lesson C Summary

65

Parameter queryCreated using a question mark in place of the

criteria’s value in the WHERE clauseThe TableAdapter Query Configuration Wizard

Can be used to save a query that contains the SELECT statement

Can be used to associate a query containing the INSERT or DELETE statement with a function

To invoke a query from code:Enter the query’s method or function in a

procedure


Lesson C Summary (cont’d.)

66

The INSERT statement inserts records into a database

The DELETE statement deletes records from a database

chapter fourteen access databases and sql programming with microsoft visual basic 2010 5 th edition

Documents

microsoft visual basic

editionadding records

th editionprogramming

datasetdelete records

missing record

file application

delete buttonclick

new record section