Page 309 - DCAP408_WEB_PROGRAMMING

Unit 14: Database Connectivity

 Reusable query definitions-pass from field variable to query Notes
 Concatenate multiple records single text field
 Change names/address from upper case to proper case Access VB example.

Self Assessment

Fill in the blanks:
8. The …………………… can leverage server cursors and do what it is supposed to do being
connected to the data source all the time.
9. The …………………… is a container that can be programmatically filled with static
snapshot of data. In this sense, it can be seen as a repository of disconnected recordsets.
10. The Connection object contains information about the desired …………………… and
location.
11. The base .NET class is ……………………

14.3 Inserting, Updating and Deleting Database Records

14.3.1 Inserting Records

Data driven sites must be capable of editing as well as addition and deletion of data. When we
make a database accessible over the WEB, there are many people accessing it at once. Accessing
is OK till the people are just reading, when they try to manipulate it, it may cause problems.
To prevent this the first person who tries to change the record puts a “LOCK” on it (lock
basically means that another person cannot edit). While the lock is on no one can change the
records. As soon as the user is finished the lock is removed.
In the current scenario we need not only reading access but also the writing access. The
adLockReadOnly is not sufficient. Currently the lock that is put is “adLockOptimistic” (Optimistic
locking means that records are locked by the provider when update is called).

AddNew and UpDate

There are two methods of Recordset object that you will need to make changes to the database
: AddNew and Update. AddNew creates a new record in the recordset. The new record is not
added to the database till the Update method is called. After AddNew is called, the new record
becomes the current record, and it remains the current record even after Update is called.

Example:
ObjRS.AddNew

ObjRS(“Name”)= “Nikhil”
ObjRS(“Email”)= xyz@asd.com
ObjRS.Update
In this example AddNew creates the new record and sets it as the current record which helps in
assigning the values. Update command add the record to the database.
While we are editing record and call the AddNew function. So the following has the effect of
creating one new record, adding it to the table, creating a second new record, and it to the table:

LOVELY PROFESSIONAL UNIVERSITY 303