Microsoft has designed SQL Server and Visual Basic to work well together in a happy partnership. VB developers know what to do with their data once they get it. The MCSD tests and courses push the boundaries of VB programmers into the database world of SQL Server. Knowing some of the finer points of database design and administration empowers you, the developer, to use the full potential of SQL Server in your applications and helps you avoid common pitfalls. Microsoft knows how important database knowledge is to good application design—and they will test you on it.

In this chapter you’ll learn a mixed array of very beneficial database information. We’ll cover the most important elements of SQL Server security. You’ll learn what cursors are and how to create and use them in your applications. The discussion of data integrity will help you ensure the long-term success of your work. And finally, you’ll add some real power to your programming arsenal by executing statements on your database server.

This chapter draws its examples from SQL Server and ADO, Microsoft’s primary database and data access technologies. These are the technologies you’ll need to know to pass the MCSD tests.

As a professional developer and test-taker, you will want to learn about database security and use this knowledge in your application design. How does SQL Server recognize the users of applications? What options do we have for handling security? The best way to understand SQL Server security is to start at the table level and work out to the application.

SQL Server assigns permissions on database objects to users and roles. The permissions are select, update, insert, delete, execute, and DRI. Execute permission applies to stored procedures, not tables or views. DRI (Declarative Referential Integrity) applies to tables and allows a user to create a table with a referential integrity relationship to a table where that user has DRI permission . The same objective can be accomplished with select permission, but that also allows the user to view the data in the table. A user with select permission can run a select statement on a table or view, such as SELECT * from Authors, allowing him or her to view the data. Update permission allows changes to the data; insert and delete let the user add and remove rows from a table or view, respectively. For example, take a look at Figure 10-1. GajusR can select from publishers, but not change data. He is also specifically prevented from changing the stores table. Permissions for a role would be assigned in the same way.

SQL Server administers its own sets of groups (called roles) and users at the database level, distinct from NT domain groups and users . SQL roles can be thought of as groups with a purpose, and contain one or more database users. SQL Server defines its own set of built in database roles, such as db_backupoperator and public. The database administrator (DBA) may create new roles and assign members. Each database has its own set of users and roles. Figure 10-2 shows the users for the Pubs database. Names for users and roles can be the same across different databases and may include the same logical users, or the DBA can create completely different groups for each database.

Logins relate SQL Server to application users. Databases use database users and roles to handle permissions. A database user corresponds to one login. Roles are sets of database users. The login is a server-level object that provides authentication for a user. Databases have their own sets of users and roles, but there is only one list of logins for a particular SQL Server . Figure 10-3 shows the logins for my server. In this example, NT user RettigP can gain authentication on the server. This, in itself, does not provide access to any data until the DBA makes a database user for the login RettigP and assigns permissions.

The end user gains authentication with the SQL Server login, but what relationship does this login have with the NT Domain account? The DBA again has a lot of flexibility in setting up security. In short, the SQL Server login may have no relationship to an NT account, it may represent one user account, or it may represent an NT Domain group . SQL 7 has two authentication method options : Windows NT Authentication Mode, and Mixed Mode (see Figure 10-4).

SQL Server can take advantage of trusted connections by using NT user account names for authentication. In this method, SQL trusts the validity of the NT name as it’s passed using Windows NT-based facilities—SQL doesn’t check a password, just matches the username against its list of logins . Figure 10-5 shows how a SQL login relates to an NT account. In this example, MetschT is an NT account in the domain CINSTATE. For convenience, I’ve given this person the same SQL login name, but I could have entered any valid name. An NT Domain group may also have one SQL login. Entering CINSTATE\Programmers in the Name text box would allow anyone in the Programmers group to have the database permissions granted to the one login.

Windows NT Authentication Mode adds the features of NT security to SQL Server. SQL allows NT to handle account validation, password encryption, auditing, and other administrative tasks, while SQL concentrates on the database-related aspects of security without the extra overhead.

SQL Server also has the capability of authenticating users. SQL can store its own set of accounts, separate from NT. With SQL Server authentication, either the user provides a username and password at the time of initial database access, or the application itself can store a username and password, which it uses to establish a connection. The account is administered by SQL Server, password and all, and has nothing to do with a network logon . Mixed mode combines the functionality of standard security, SQL Server authentication, with NT authentication. SQL server can accept an NT account name, assuming a trusted connection exits, or validate a username and password using the server’s login list.

Some cases require SQL Server authentication. SQL 7 can run on Windows 95 or 98, operating systems that do not use NT security . In this case, SQL Server runs in Mixed mode using only SQL Server authentication. Internet clients, as well as any other non-NT client, must provide both a username and password because these clients do not have a trusted connection. Alternately, as the developer you may choose SQL authentication. Security implementation can become almost trouble free if the application connects with a standard account. All users then access the data under the same username and login, with the same permissions. For read-only databases or databases with open access, you can make your life much simpler with this method.

A note on SQL Server changes: SQL 6.5 allowed three security setup configurations: standard, mixed, and integrated. Standard security has fallen away with version 7. With standard security the only method for the user to log on to the server is through SQL Server authentication. This functionality has been retained in Mixed mode. The name "integrated security" has changed to Windows NT Authentication Mode in SQL Server 7.

Once you understand the concepts of SQL Server Security, the implementation becomes easy. Here’s an example of a database connection using SQL Server authentication:

Dim cnn As ADODB.Connection
Dim rstTitles As ADODB.Recordset
Dim strCnn As String

strCnn = "Provider=sqloledb;server=pc6742;Initial Catalog=pubs; User Id=sa;Password=;"
Set cnn = New ADODB.Connection
cnn.Open strCnn

Set rstTitles = New ADODB.Recordset
rstTitles.Open "titles", cnn, adOpenStatic, adLockReadOnly, adCmdTable

rstTitles.MoveFirst
Debug.Print rstTitles!Title
rstTitles.MoveLast
Debug.Print rstTitles!Title
rstTitles.Close
cnn.Close

To implement NT Authentication Mode, change strCnn to

strCnn = "Provider=sqloledb;server=pc6742;Initial Catalog=pubs;Integrated Security=SSPI;"

In this example I use ADO (ActiveX Data Objects) to connect to the SQL Server running on my system. The connection string, strCnn, provides the information ADO uses to find the data . The SQLOLEDB provider has a number of optional parameters beyond those the Connection object exposes. When we set Integrated Security to SSPI (Security Support Provider Interface), the authentication service for Microsoft NT Integrated Security, SQL server uses the trusted connection to obtain a username . If we choose SQL authentication, we provide a username and password with the User Id and Password parameters.

Implementing database security in VB applications is easy, just a matter of a parameter or two. With security, as with other aspects of programming, planning requires more attention and careful thought. SQL Server takes care of the permissions and authentication. VB presents the data to the user, who’s unaware of the cooperation between desktop application, NT networking, and SQL Server just in verifying the user’s right to view or change data.

On the test, you may come across scenarios like the following:

Often in developing applications for Relational Database Management Systems (RDBMS), such as SQL Server, we find that query-type operations fall short of our design goals. In other words, we just can’t get it done without looping through records one at a time . VB uses cursors to give us programmatic control over result sets. Once the application makes a connection to a database, it creates a Recordset object to process rows of data, from a table, view, or query statement . This result set is a cursor. Cursors are not as efficient as ANSI SQL statements for operating on a database, so there are numerous options to consider to get the best performance.

Cursors use resources, both memory space and processor time. By selecting a cursor library, you can choose to build your result set on the client or on the server. Server side cursors use the resources of the server to create the cursor. The server sends only the cursor data to the client. With ADO, you set the CursorLocation property of the recordset to determine the cursor library . The following example creates a server side cursor. The SQL server queries the titles table and returns to the client only those rows with the value "business" in the type column.

Set rstTitles = New ADODB.Recordset
rstTitles.CursorLocation = adUseServer
rstTitles.Open "SELECT * FROM titles WHERE type = ‘business’", cnn, adOpenStatic, adLockReadOnly

By changing the constant adUseServer to adUseClient, we turn the record set into a client side cursor. This means that the server will send all the data needed to process the cursor to the client. In this case, all the rows from the titles table travel over the network to the client. The client then allocates space to hold the temporary data and perform the creation of the cursor, finally selecting titles with a type equal to "business."

SQL Server does support server side cursors, but not all databases do, so client side cursors will be necessary in some cases. Server side cursors are more efficient when network traffic is a limiting factor, but the server must be up to handling the load. For large tables client side cursors may be next to impossible. But client side cursors can provide extra features.

Besides establishing where best to run a cursor, you can further improve efficiency by limiting the its capabilities. Some uses don’t demand as much flexibility as others. For instance, printing a report from cursor data doesn’t require the level of sophistication of an update screen. Table 10-1 shows a list of the primary cursor types.

Static cursors give you a snapshot of the data. Use a static cursor when you don’t need to see changes to the underlying data and when you might need to move back and forth through the set. Static cursors can perform well because the server doesn’t need to keep the data current. A good use for a static cursor would be a search screen, where the user enters a value that the cursor then uses to filter its rows, displaying the matching records.

Forward-only cursors also provide a data snapshot, but limit the record set to forward scrolling. Forward-only cursors are the best choice for reports or any other function that only requires one pass through the data . An identical loop through a static cursor would use more resources and could be noticeably slower. Forward-only cursors may seem very restrictive, but situations where they fit perfectly are in fact quite common.

Dynamic cursors use more resources than any other type, but they also give the most flexibility . Dynamic cursors receive updates to the data from the server whenever another application causes an insert, update, or delete. You can imagine that the overhead needed to maintain this link could be considerable. You might choose a dynamic cursor in cases where not having live data can result in conflicts, such as in an airline reservation system.

Keyset cursors use a saved set of keys to maintain a link to the underlying data. So if another application changes a column that change will bubble through to the keyset cursor. Deletes also appear, but not new records. Keysets work well when you want accurate, but not necessarily the newest data, or if your application gets a lot of new entry.

Before starting to use a cursor, you have to decide on a locking method, also called concurrency . The problem lies with two or more users trying to access the same data concurrently. Locking conflicts cause headaches for DBAs and frustration for users. Planning appropriate concurrency will help your cursors do their job smoothly. In this section I’ll present standard locking options, which are supported by SQL Server, using ADO as the example access technology. Table 10-2 shows the available methods.

Always use read-only concurrency when you know you don’t need to edit the data you request from the server. The server will put shared locks on the pages with the records the cursor needs, which will prevent other users from making exclusive locks, but not shared locks. SQL Server does not lock every row in the cursor, just those that the cursor requests at one time.

With optimistic concurrency, the server does not apply any locks to the data until the time of update. When an update occurs an exclusive lock is made and the change applied . The danger here is that after the cursor reads the records, another user will make a change that can then conflict with the update you want to make. For example, you make an optimistic lock on a customer’s demographic record, then another user locks and updates that same record. When you go to apply your changes, the update will fail. In this case, an error will be produced when the cursor attempts an exclusive lock. Set your locking to batch optimistic when you want to change multiple records in one swoop.

If your application must succeed in its data edits, then pessimistic concurrency is the necessary choice . Pessimistic locking poses the greatest danger of freezing your data. At cursor creation the server locks whatever set of rows it sends to the client. The whole cursor does not receive an exclusive lock, just those records that the cursor processes at one time. Other users cannot obtain either shared or exclusive locks on this data until the cursor moves off the block of records.

Having planned so carefully, what do you do with the cursor once you’ve created it? I’ll give a typical example that uses a cursor to fill a list box. This example also brings together the concepts we’ve covered and illustrates them with a practical application. Figure 10-6 shows the output. Here’s the only code contained in the program, attached to the click event of the command button :

Private Sub cmdFillList_Click()

Dim cnn As ADODB.Connection
Dim rstAuthors As ADODB.Recordset
Dim strCnn As String

'Create Connection
strCnn = "Provider=sqloledb;server=pc6742;Initial Catalog=pubs;Integrated Security=SSPI;"
Set cnn = New ADODB.Connection
cnn.Open strCnn

'Create Recordset
Set rstAuthors = New ADODB.Recordset
rstAuthors.CursorLocation = adUseServer
rstAuthors.Open "SELECT * FROM Authors", cnn, adOpenForwardOnly, adLockReadOnly

'Fill Listbox
lstNames.Clear
rstAuthors.MoveFirst
While Not rstAuthors.BOF And Not rstAuthors.EOF
lstNames.AddItem Trim(rstAuthors!au_fname) & " " & Trim(rstAuthors!au_lname)
rstAuthors.MoveNext
Wend

rstAuthors.Close
cnn.Close

End Sub

When the user clicks the button, the application initiates a connection to the pubs database on SQL Server, using a trusted connection. Then the code creates a new record set, called rstAuthors. This record set contains all the fields for all the rows in the authors table. I’ve made it a forward scrolling cursor with read-only locking, using the resources on the server.

After the program clears the listbox, a loop moves through each record in the set, adding the first and last names of each author to the box. Note the MoveFirst and MoveNext methods of the recordset. These do exactly as their names indicate. There are a number of cursor movement methods:

• Move
• MoveFirst
• MoveLast
• MovePrevious
• MoveNext

The Move method accepts a signed parameter of type Long, which then moves the given number of records forward or back . The EOF (end of file) and BOF (beginning of file) properties work hand in hand with the move methods. Once MoveNext, for example, moves beyond the last record, the EOF property becomes true. Another MoveNext will produce an error. Because the example uses a forward-only cursor, only the Move and MoveNext methods will behave predictably.

By using read-only locks and a forward-scrolling cursor, the example minimizes the impact on the database. One possibility that I didn’t account for is the size of the authors table. If I hadn’t known that the authors table contained just a few rows, setting the MaxRecords property of the record set would prevent problems due to a very large table. Adding a WHERE clause to the SQL statement is a more efficient way to control record set size, if the design allows.

In this exercise we’ll modify the example from the above section to demonstrate the difference between a dynamic and a static cursor. Between creating the record set and filling the listbox, the program will pause and give us a chance to change the data on the authors table.

In my occasional consulting work, I’ve often had the pleasure of working with other people’s data. The companies that call me usually do so because they’re in a mess and need help. For various reasons, the data has become corrupted. Organizations that fail to put adequate planning and design into their business logic end up, over time, with aging and increasingly useless data. Applications can be replaced, but data is the lifeblood of a company. Once it gets tired, making it pure again takes a fearful amount of work. You can stop this drift before it starts by building solid validation and referential integrity rules.

Both VB and SQL Server have an array of tools to help with data validation . Your application validates data by checking it as it’s entered. The tools range from simple to sophisticated. First I’ll discuss what can be done with VB to ensure data integrity, then I’ll cover the powerful SQL Server methods.

Client-side validation happens through the controls of a form. Controls provide validation because of their basic nature, by the use of their properties, and by using events that the user generates in entry. All of these controls, illustrated in Figure 10-7, can be used with validation logic:

• CheckBox
• OptionButton
• Masked Edit
• ListBox
• ComboBox
• TextBox

A CheckBox control forces the user to input a Boolean value. Unlike Jet databases, for instance, SQL Server doesn’t have a Yes/No datatype, though the bit datatype works well. Depending on how you intend to store Boolean data, a checkbox can help limit input.

The OptionButton control allows you to give the user a usually small set of fixed choices, which are written on the form as labels. Each option button has a true or false value, which you must then relate to your data through code.

The Masked Edit control lets the developer limit input in a text box type control though the use of an input mask. The classic example of this is the telephone number . The mask requires that three digits be entered between parentheses, then three more, a dash, and the final four numbers. Input masks help you to establish standards among users. Not everyone enters an Social Security number, for instance, in the same way. Some people like to put dashes, others spaces, others just run the numbers together. Sorting or searching this kind of hodgepodge can become very ineffective. The Masked Edit control helps you unify these personal preference issues.

The ListBox control presents the user with an often numerous list of choices, with many, if not all, visible at one time. ListBoxes can be fully controlled by programming, and they are a great way to limit choices and assure uniform, unambiguous input .

The ComboBox control adds to the functionality of a list box by letting the user type. Sometimes appearance governs the choice between the two, but if you want the user to be able to type in new data, rather than choosing from a fixed list, the combo box is what you need.

By themselves, text boxes do not do much for validation, but the KeyPress and Validate events give you total programmatic control over input. When focus shifts from a text box to another control with the CausesValidation property set to true, the Validate event fires, giving you a change to check the input by whatever standards you choose, and if necessary, hold the focus on the text box until the user meets your validation requirements.

Client-side validation works, but server-side validation has real advantages. A database can be used by many applications, with many more programmers having a hand in development. Also, educated users can learn to access data through their own methods, such as forming links to user-level databases. Server-side validation rules reside with the data. No matter how someone connects to the data, the server can apply its validation logic. Server-side validation centralizes control over the integrity of the data. I’ll present SQL Server’s validation methods in some detail.

SQL Server validates data primarily with constraints . Constraints give you tremendous flexibility in policing data input. SQL Server recognizes five types of ANSI standard constraints: CHECK, DEFAULT, UNIQUE, PRIMARY, FOREIGN KEY. PRIMARY and FOREIGN KEY constraints relate to referential integrity, which we’ll discuss in the next section.

See Figure 10-8 for an example of a CHECK constraint. In this example, the constraint makes sure employees are at least one day old. You can see that a CHECK constraint evaluates a condition that includes the column name. All constraints are saved with the table structure and can be related to multiple columns. Also each column can have more than one constraint.

DEFAULT constraints differ from defaults, below, in that they apply only to one column of one table . The function, though, is the same. It’s not a surprise that defaults supply a value for a column when the user inserts a new record without input for that column. The ANSI standards disallow the use of null, which many RDBMSs nevertheless permit. To avoid null fields, use a default to supply a filler. For example, DBA’s often put a default of "Unknown" for a name, or 000-00-0000 for an SSN.

The UNIQUE constraint assures that a column contains no repeating values. Use this constraint for a column that could function as an alternate primary key. You may, for instance, maintain a list of network users and their usernames. This list could have a key that relates to another employee table, a generic employee ID. In addition, you want to make sure that no two employees have the same username in the table, which would be a data entry mistake.

Rules function very similarly to CHECK constraints, except a rule is independent of a particular table. Rules must be created first and then bound to columns. Rules also make sure that data fits within a set or range of values or has a given format.

Defaults, like rules and unlike DEFAULT constraints, are saved separately from a table. They are then applied to any number of columns. Defaults must conform to all other rules or constraints on a field. Of course, a default must also fit the data type.

Triggers and stored procedures both use more system resources than constraints, and so should be used only when more basic validation methods cannot get the job done. Think of a trigger as the SQL Server equivalent of an AfterUpdate or Change event . Triggers fire when an update, insert, or delete happens on a table. Triggers consist of Transact-SQL and can perform any number of tasks in response to a data change. A trigger may, for instance, keep a sales totals table up to date by adding new sales to the totals after each insert. Triggers can help with validation by performing a more complex check of new values than is possible with constraints.

Stored procedures are the code modules of SQL Server. Also consisting of Transact-SQL, stored procedures give the database designer full programmatic control over data. Stored procedures can create tables, add users, generate output, copy data, perform row-by-row operations, and whatever else you can dream up. (I’ll show you how to run a stored procedure from your VB application and describe their usefulness in more detail later.) They assist in data integrity by providing a single centralized set of logic to perform a task. Instead of writing complex data operations in your application, you can simply call a stored procedure. Anyone else who wants to accomplish the same goal can run the same procedure, instead of coming up with new code.

Referential integrity is a fundamental part of RDBMSs. Instead of storing information in one huge table, the process of normalization splits data out into elemental pieces. The smaller tables then share relationships through their keys. The potential exists for this relationship to break down if an entity does not correspond to a row in another table. Through referential integrity an RDBMS ensures that the proper references exist between key values, thus maintaining the validity of relationships .

The PRIMARY KEY constraint defines the keys in each table . In the products table, SupplierID is the foreign key. When SQL Server enforces referential integrity, it makes sure that every SupplierID in Products has a matching SupplierID in Suppliers. The FOREIGN KEY constraint, created at the same time as the rest of the table, assures that SQL Server performs the referential integrity checks . This script creates the Products table; notice the FOREIGN KEY constraints:

CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductName] [nvarchar] (40) NOT NULL ,
[SupplierID] [int] NULL ,
[CategoryID] [int] NULL ,
[QuantityPerUnit] [nvarchar] (20) NULL ,
[UnitPrice] [money] NULL CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0),
[UnitsInStock] [smallint] NULL CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0),
[UnitsOnOrder] [smallint] NULL CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0),
[ReorderLevel] [smallint] NULL CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0),
[Discontinued] [bit] NOT NULL CONSTRAINT [DF_Products_Discontinued] DEFAULT (0),
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED

(
[ProductID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Products_Categories] FOREIGN KEY

(
[CategoryID]
) REFERENCES [dbo].[Categories] (
[CategoryID]
),
CONSTRAINT [FK_Products_Suppliers] FOREIGN KEY
(
[SupplierID]
) REFERENCES [dbo].[Suppliers] (
[SupplierID]
),
CONSTRAINT [CK_Products_UnitPrice] CHECK ([UnitPrice] >= 0),
CONSTRAINT [CK_ReorderLevel] CHECK ([ReorderLevel] >= 0),
CONSTRAINT [CK_UnitsInStock] CHECK ([UnitsInStock] >= 0),
CONSTRAINT [CK_UnitsOnOrder] CHECK ([UnitsOnOrder] >= 0)
)

A number of operations could cause a trappable error because of the relationship between Products and Suppliers. An insert into the Products table of a row without a SupplierID from Suppliers will produce an error. A delete from Suppliers that tries to remove a SupplierID that’s present in Products will violate referential integrity. Finally, your application will produce an error if it tries to change a SupplierID in either table if the result would be an orphan record.

The goal of this Question and Answer section is to help you understand how referential integrity works to preserve data consistency. Refer to Figure 10-9 and consider the following questions and answers.

So what do you do when your application tries to break the referential integrity relationship on your server? You trap the error, of course, and deal with it. Like any other VB error, SQL Server errors can be trapped with the On Error statement. In addition to the VB Err object, the ADO Connection object has an Errors collection . This collection can be looped through with a For Each, Next construction. Your application can respond not to only referential integrity errors with the Errors collection but to many other SQL Server errors as well, such as locking and connecting errors. This code sample illustrates how to use the Errors collection of the Connection object:

Private Sub IntegrityErrors()

Dim cnn As ADODB.Connection
Dim er As ADODB.Error

On Error GoTo OnError

'Code to Create Connection and violate referential integrity
'
'
'

Exit Sub

OnError:

Debug.Print Err.Number, Err.Description
For Each er In cnn.Errors
Debug.Print er.Number, er.Description
Next
Resume Next

End Sub

Once you’ve trapped the error, you can automate a response, or let the user give input in deciding a course of action. In the case of referential integrity errors, you’ll want to communicate to the user an understandable explanation for why the operation failed. Error handling sophistication is up to you, the developer. SQL Server will never let your application violate referential integrity, but you need to make sure that a failure to insert, update, or delete doesn’t introduce problems into a larger segment of logic.

The Execute method of the ADO Connection object opens up a world of possibilities to you, the developer. With this one method you can :

• Run stored procedures
• Perform action queries
• Return result sets
• Send commands to the database server

Microsoft has optimized SQL Server for running stored procedures and executing statements. As a programmer, you have the choice of performing logic on the client VB application or on the server. Executing statements on the server takes advantage of server resources and reduces network traffic . Let the server do database work whenever possible.

Let’s start with an example. This procedure updates the authors table, changing a last name for one record, then prints the names in the authors table. Both operations use the Execute method:

Private Sub ExecutingStatements()

Dim cnn As ADODB.Connection
Dim rstAuthors As ADODB.Recordset
Dim strCnn As String
Dim sqlUpdate As String

'Create Connection
strCnn = "Provider=sqloledb;server=pc6742;Initial Catalog=pubs;Integrated Security=SSPI;"
Set cnn = New ADODB.Connection
cnn.Open strCnn

'Execute Update Statement
sqlUpdate = "UPDATE authors SET au_lname = 'Kornau' WHERE au_id = 3"
cnn.Execute sqlUpdate, , adCmdText

'Fill Recordset with stored procedure output
Set rstAuthors = cnn.Execute("AuthorList", , adCmdStoredProc)
'Print results
rstAuthors.MoveFirst
While Not rstAuthors.BOF And Not rstAuthors.EOF
Debug.Print rstAuthors!au_fname, rstAuthors!au_lname
rstAuthors.MoveNext
Wend
rstAuthors.Close
cnn.Close

End Sub

The Execute method takes three parameters. The first is a string variable containing an SQL statement or command text. In the above example, sqlUpdate contains the text of an UPDATE action query. Any valid SQL statement will work, including INSERT, UPDATE, and DELETE statements. If your goal is to return a result set from a SELECT statement, use the Open method of the Recordset object. I’ve omitted the second parameter, RecordsAffected, from both Executes, because it’s not useful in this case. This parameter returns the number of records affected by a query or command. Those familiar with ISQL will immediately recognize this standard output from query statements. The third parameter takes a constant that tells SQL Server how to handle the command text. Table 10-3 lists the applicable constants.

In addition to running an action query, the example populates a record set with the results of a stored procedure. The stored procedure AuthorList is nothing more than a SELECT statement, but take a look at Figure 10-10, a more complicated stored procedure. Stored procedures accept parameters and can perform any task possible with Transact-SQL. Using the Execute method to run stored procedures gives your application the power to run resource intensive operations without causing a drag on the client machine. Learning Transact-SQL may take some work, but it adds a lot of potential to your VB applications.

Using SQL statements to do your inserts on a table will often be the most efficient method. In this exercise you’ll send an INSERT statement to SQL Server to add a record to the authors table of the pubs database.

SQL Server uses permissions to enforce security on database objects. The DBA grants permissions to users and roles: select, update, insert, delete, execute, DRI. Roles are groups of database users. Both users and roles are specific to the database. SQL Server uses logins for authentication. Logins are specific to the server and are mapped to database users. SQL Server can perform its own authentication of a username and password, or it use a trusted connection to authenticate an NT domain username. A login can represent one user or an NT domain group. With ADO, the connection string either supplies a username and password, or instructs the provider to use a trusted connection.

Cursors let the application loop through a set of records, one row at time. The cursor library determines if the cursor will run on the server or workstation. Cursors can be static, forward only, dynamic, or keyset. There are four locking options in creating a cursor: read only, optimistic, batch optimistic, and pessimistic. Choosing cursor options carefully makes a positive impact on overall performance. The Recordset object in Visual Basic represents a cursor.

Enforcing data integrity includes both validation and referential integrity. VB allows client-side validation through the use of controls: TextBox, CheckBox, ComboBox, Masked Edit, OptionButton, ListBox. SQL Server implements server-side validation with constraints, rules, defaults, triggers, and stored procedures. Referential integrity insures that normalized data maintains the proper relationships. SQL Server uses the PRIMARY and FOREIGN KEY constraints to enforce referential integrity. A foreign key is a column that can only include values present in the primary key of another table. The Errors collection of the ADO Connection objects lets the application handle referential integrity errors.

The Execute method of the ADO Connection objects gives the programmer the ability to execute statements and stored procedures on the database server. Any stored procedure can be run from a VB application. A VB record set can hold a result set returned by a stored procedure. The application can also run action queries against data on the server. Using the Execute method takes advantage of server resources and SQL Server’s performance capabilities.

1. Declarative Referential Integrity
2. Default Range Indicator
3. Default Role Integrity
4. Don’t Remember Information
   A. Declarative Referential Integrity. A user with DRI permission on a table may create a referential integrity relationship to that table.

1. You want to let William have access to the information in your database. You must

1. Create an NT account for William and give that account the appropriate permissions.
2. Create an NT account for William and assign that account to a role with the appropriate permissions.
3. Create an NT account for William, map that account to a SQL login, make a database user for the SQL login, and assign permissions to the database user.
4. Create a SQL login for William, make a database user for the login, and assign permissions to the database user.
   C or D. Option C uses NT Authentication mode; D uses SQL Server authentication. An NT account cannot directly be given database permissions or included in a role.

1. Two databases can share

1. Stored Procedures.
2. Tables.
3. Roles.
4. Logins.
   D. Logins. Logins are defined at the server level. Each database draws from the same group of logins. Two databases can have identical stored procedures, tables, and roles, but these are logically and physically independent.

1. Which choice does not describe a possible SQL Server login/NT account relationship?

1. A login corresponds to one NT user account.
2. A login corresponds to multiple NT domain groups.
3. A login corresponds to one NT domain group.
4. A login does not correspond to an NT user or domain.
   B. A login corresponds to multiple NT domain groups. Each login can represent an NT user account or an NT domain group, or it may not relate to an NT security entity at all. A login cannot represent multiple groups.

1. SQL Server trusts NT security because

1. NT security is invulnerable.
2. Microsoft sells it.
3. NT’s security facilities validate the identity of a user when they first connect to a network.
4. SQL Server does not trust NT security.
   C. NT’s security facilities validate the identity of a user when they first connect to a network. SQL Server, a database management system, does not include the level of security sophistication that has been developed for NT.

1. When should you use Mixed mode?

1. When SQL Server is running on Windows 95.
2. When trusted connections are not available.
3. When connecting to a database from a Web site.
4. Mixed mode is not available in SQL Server 7.
   A, B, and C. SQL Server cannot use NT authentication when it runs on Windows 95 or 98. NT authentication requires a trusted connection. Web sites connect with standard security.

1. Which parameter of the connection string for an ADO connection causes the server to use SQL authentication?

1. Provider.
2. Server.
3. Integrated Security.
4. None of the above.
   D. None of the above. Providing a User Id and Password will connect to the server with SQL Authentication.

1. Which parameter of the connection string for an ADO connection causes the server to use NT authentication?

1. Provider.
2. Server.
3. Integrated Security.
4. None of the above.
   C. Integrated Security. Setting Integrated Security = SSPI will cause the server to accept a username from NT, assuming a trusted connection exists.

1. Which ADO object represents a query result set?

1. Recordset.
2. Connection.
3. Error.
4. Resultset.
   A. Recordset. The Recordset object represents the results of a query. Multiple record sets can be created under one connection. ADO does not have a Resultset object.

1. Which cursor type will give the best overall performance for a report?

1. Static.
2. Forward only.
3. Dynamic.
4. Keyset.
   B. Forward only. In most cases a report can be generated with one pass through a record set. Static, dynamic, and keyset cursors are all slower than the forward-only cursor, though sometimes the difference may be very slight.

1. Which cursor type users the most resources?

1. Static.
2. Forward only.
3. Dynamic.
4. Keyset.
   C. Dynamic. The dynamic cursor provides the most flexibility and reflect all updates made to the underlying data. Thus, it should only be used when necessary.

1. Which term is a synonym for locking?

1. Keyset.
2. Pessimistic.
3. Unlocking.
4. Concurrency.
   D. Concurrency. Concurrency really means the condition of two users accessing the same rows at the same time, but it has come to be an interchangeable term with locking.

1. When should you use pessimistic concurrency?

1. When the table is half empty.
2. When the cursor is read only.
3. When the updates made with the cursor must commit.
4. When you need an exclusive lock.
   C. When the updates made with the cursor must commit. Read-only cursors use read-only concurrency. A cursor with optimistic locking will also obtain an exclusive lock when the update commits.

1. Which ADO record set property determines the cursor library?

1. CursorLibrary.
2. CursorDLL.
3. LockType.
4. CursorLocation.
   D. CursorLocation. The locktype constants are used for concurrency. There is not CursorLibrary or CursorDLL property.

1. Which method can you use to move to the next record in a record set?

1. Move.
2. MoveFirst.
3. MovePrevious.
4. MoveNext.
   A. Move and D. MoveNext. The MoveNext record is preferred, but the Move method can move the pointed any number of rows forward or back, including one forward.

1. Which cursor will run fastest?

1. A forward-scrolling, read-only cursor.
2. A dynamic, pessimistic cursor.
3. A static, optimistic cursor.
4. A C++ cursor.
   A. A forward-scrolling, read-only cursor. This cursor will use the minimum resources, followed by the static optimistic cursor. The dynamic, pessimistic cursor will be slowest.

1. Which control can help the user format a phone number?

1. OptionButton
2. ListBox
3. TextBox
4. Masked Edit.
   D. Masked Edit. Masked edit controls use an input mask to provide formatting help and restrictions to the user.

1. The user of your application must assign department codes to new courses. Which control will provide the best validation?

1. CheckBox.
2. OptionButton
3. ListBox.
4. Masked Edit.
   C. ListBox. List boxes let the user scroll through a limited number of choices.

1. Which is the primary method of server-side validation?

1. Constraints.
2. Rules.
3. Defaults.
4. Triggers.
   A. Constraints. Constraints are part of a table definition and should be used whenever possible.

1. How does the DEFAULT constraint differ from a saved default?

1. One DEFAULT constraint can be applied to multiple columns of one table.
2. One DEFAULT constraint can only apply to one column of one table.
3. One DEFAULT constraint can apply to multiple columns in multiple tables.
4. They are the same thing.
   B. One DEFAULT constraint can only apply to one column of one table. A default can be saved and made available to any table in a database. The DEFAULT constraint is the preferred method.

1. Triggers are like

1. Chiggers.
2. The VB Change event.
3. Rules.
4. Primary keys.
   B. The VB Change event. A trigger can be made to fire when a table is modified.

1. Which example does not allow referential integrity?

1. A products table with multiple products for each record in a suppliers table.
2. A students table that has one possible row for each row in a people table.
3. A books table that may or may not have a corresponding record in a publishers table.
4. A transcript table that has multiple rows for each row in a student table.
   C. A books table that may or may not have a corresponding record in a publishers table. Sometimes it’s not possible to establish referential integrity because of design limitations.

1. The suppliers table has a one-to-many relationship with a products table. The SupplierID column is in both tables. Therefore,

1. SupplierID is a primary key in suppliers and a primary key in products.
2. SupplierID is a primary key in suppliers and a foreign key in products.
3. SupplierID is a foreign key in suppliers and a primary key in products.
4. SupplierID is a foreign key in suppliers and a foreign key in products.
   B. SupplierID is a primary key in suppliers and a foreign key in products. The foreign key is a nonunique column in a table that refers to the primary key of another table.

1. When is a FOREIGN KEY constraint created?

1. When the table is created.
2. During performance tuning.
3. Immediately after the table is created.
4. Any time.
   A. When the table is created. Like all other constraints, the FOREIGN KEY constraint is created with the same script that creates the table.

1. Which type of query cannot result in a referential integrity violation?

1. Select.
2. Insert.
3. Update.
4. Delete.
   A. Select. Select queries do not change data. Even an update query can violate referential integrity if you try to change a primary or foreign key.

1. Which collection of the ADO Connection object can be used to handle referential integrity errors?

1. Err.
2. Er.
3. Description.
4. Errors.
   D. Errors. The Errors collection contains an Error object for each error created in a connection.

1. Which method of the ADO Connection object will allow you to run a stored procedure with a VB application?

1. Run.
2. Command.
3. Execute.
4. Stored procedures can only be run from SQL Server.
   C. Execute. The Execute method accepts a command text argument, which may be the name of stored procedure.

1. Why should you use the Execute method to populate a record set with the output from a complex stored procedure?

1. SQL Server is optimized to run stored procedures.
2. The Execute method is the only way to populate a record set.
3. Stored procedures use client resources.
4. To avoid concurrency problems.
   A. SQL Server is optimized to run stored procedures. Complex data operations are best handled by the server. The result set then can be passed to the client, instead of manipulating data on the client.

1. Which constant is used with the Execute method to run a stored procedure?

1. AdCmdText.
2. AdCmdTable.
3. AdCmdUnknown.
4. AdCmdStoredProc.
   D. AdCmdStoredProc. This constant indicates to SQL server that the command text contains the name of a stored procedure.

1. Which event can you use with an asynchronous command?

1. The Asynchronous event.
2. The CommandFinish event.
3. The ExecuteComplete event.
4. The Synchronize event.
   C. The ExecuteComplete event. The ExecuteComplete event occurs when an asynchronous command, query, or procedure has finished.

1. VB front ends can be used with which database?

1. SQL Server.
2. Oracle.
3. Access.
4. Unidata.
   A, B, C, and D. VB can use ADO to connect to any ODBC compliant database. Good luck with Unidata, though.

1. Which database access technology should you use to connect a Web site to SQL Server?

1. RDO.
2. DAO.
3. SQL-DMO.
4. ADO.
   D. ADO. With version 2, ADO has come to be Microsoft’s primary database access technology for almost every application. Previously, RDO had been the method of choice for VB and SQL server applications, but ADO has replaced it.

1. Which are SQL Server 7 Authentication modes?

1. Standard.
2. Mixed.
3. Integrated.
4. Separated.
   B. Mixed, and C. Integrated. Standard mode no longer exits in SQL Server 7.

1. SQL Server

1. Never stores passwords.
2. Stores passwords for some users.
3. Stores passwords for all users.
4. Never stores passwords when running on Windows 98.
   B. Stores passwords for some users. Running in Mixed mode, SQL server can rely on NT to authenticate passwords or use its own password list for authentication.

1. Which method opens an ADO database connection?

1. Connect.
2. Query.
3. Open.
4. Execute.
   C. Open. Use the Open method of the Connection object to initiate a connection.

1. When should you use a cursor?

1. To loop through a result set one record at a time.
2. To add one row to a table.
3. To sum values in a table.
4. For all database operations.
   A. To loop through a result set one record at a time. Cursors are a strain on resources, so use query statements or stored procedures whenever possible.

1. Which type of change is not reflected in a keyset cursor?

1. Update.
2. Insert.
3. Delete.
4. Select.
   B. Insert. New records do not appear in a keyset cursor. Use a dynamic cursor if you need access to new rows.

1. Optimistic locking

1. Never applies shared locks.
2. Never applies exclusive locks.
3. Never applies any locks.
4. Can apply both shared and exclusive locks.
   D. Can apply both shared and exclusive locks. A cursor with optimistic locking will obtain shared locks at creation. Exclusive locks will be requested when an update occurs.

1. Validation can occur

1. On the client.
2. On the server.
3. On both the client and the server.
4. Neither on the client nor on the server.
   C. On both the client and the server. Both VB and SQL Server can help with validation.