Database Access using ASP

1
Database Access using ASP

• COMP 2073
• Week 4
• Winter 2003
• Professor Kona Sirohi
• Email ksirohi_at_gbrownc.on.ca

2
Server ObjectActiveX Data Object (ADO)

• PWS or ISS provides database connectivity via an
  ActiveX Data Object.
• You can refer to the ODBC DSN when connecting to
  the Database.
• You can write code that doesnt care whether the
  database is Access, SQL Server, etc.

3
Connecting to a Database

• Create an ADODB.Connection object
• Declare a connection type variable
• Dim dbconn as Connection
• Create a new Instance of the Connection object
• Set dbconnServer.CreateObject(ADODB.Connection)

Need the set keyword here since we
are assigning an object to a VBScript variable
4
Connecting to a DSN

• Use the Open method
• Dim dbconn as Connection
• Set dbconnServer.CreateObject("ADODB.Connection")
  
• Open the connection using the DSN
• dbconn.open("eiw")

There are other optional arguments (can specify
id,password)
You specify the DSN here
5
SQL and RecordSet Objects

• The ADODB.Connection object has an method named
  Execute
• give it an SQL command (as a parameter)
• It creates a RecordSet object if the SQL command
  returns some records
• The RecordSet object contains the result of the
  SQL query.

6
Execute Example

• Dim dbconn
• Set dbconnServer.CreateObject("ADODB.Connection")
  
• dbconn.open("eiw")
• Set result dbconn.Execute("SELECT FROM
  Password")

This is the SQL Query
Resulting RecordSet object
7
Create New Account Form

• ltFORM ACTIONindex.aspgt
• Full Name ltINPUT TYPETEXT NamefullnamegtltBRgt
• IDltINPUT TYPETEXT NAMEuidgtltBRgt
• Password ltINPUT TYPEpassword NAMEpwgtltBRgt
• Credit Card NumberltINPUT TYPEtext NameccgtltBRgt
• ltINPUT TYPEsubmit Nameoperation value"Create
• Account"gt
• lt/FORMgt

8
INSERT Statement

• SQL "INSERT" command
• INSERT INTO table(field1,field2, .)
• VALUES (val1, val2, )
• For string type fields, the values must be in
  single quotes! For e.g.
• INSERT INTO Members(Fname,UID,pw) VALUES
  (KONA, ksirohi, monkey)

Field Names
Table Name
Field Values
9
Creating a new account code
Fullname request.querystring(fullname) Uid
request.querystring(uid) Pwrequest.querystring(
pw) Set DBConnServer.CreateObject("ADODB.Connec
tion") DBConn.Open("eiw") DBConn.Execute("INSERT
INTO Members(Fname, UID,pw) VALUES('" fullname
"', uid "', pw "')" ) End Sub
This is all one line
INSERT INTO Members(Fname,UID,pw) VALUES (KONA,
ksirohi, monkey)
10
Login Form
ltFORM ACTIONindex.asp method POSTgt Name
ltINPUT TYPETEXT NAMEUIDgtltBRgt PasswordltINPUT
TYPEpassword NAMEpwgtltBRgt ltINPUT TYPEsubmit
Nameoperation value"Login"gt lt/FORMgt
11
RecordSet Objects

• A RecordSet object holds a collection of fields
  and has some methods
• EOF()and BOF() boolean that indicates there are
  no more records.
• MoveNext() moves to the next record
• MovePrevious() move to previous. record
• MoveFirst() move to the first record
• MoveLast() move to the last record
• Lots more (check an ASP reference for the entire
  list)
• Syntax for referring to the value of a field in
  the recordset
• RecordsetName(FieldName)
• For e.g.
• Result(UID) refers to the value of the userID
  field in the recordset named Result.

12
Validate Login code

• uid request.form(uid)
• pw Request.Form(pw)
• Dim DBConn as Connection
• Set DBConnServer.CreateObject("ADODB.Connection
  ")
• DBConn.Open("eiw")
• Set res DBConn.Execute("SELECT FROM
  Members")
• Do Until res.EOF
• If res(UID") uid And res("PW") pw Then
• Successful Login. Stop processing any more
• records and Proceed ahead
• Exit Sub
• Else
• res.MoveNext
• End If
• Loop

Note Leave this line out and you will hang your
web server (and you will need to reboot).
13
A Better code
uid request.form(uid) pw Request.Form(pw)
Dim DBConn as connection Set DBConnServer.Creat
eObject("ADODB.Connection") DBConn.Open("eiw")
Set res DBConn.Execute _ ("SELECT FROM
Members WHERE UID uid "'") If
res.EOFFalse Then if res(pw) pw then
Successful Login. Match Found. Code to
proceed End if Else Unsuccessful Login. No
match found. Recordset is empty. Code here to
ask the user to re-login End If
VBScript line continuation
Select from Members where UID ksirohi
14
Record Updates

• We may want to allow people to change their
  password.
• we need to update a single record.
• General Strategies
• Use SQL
• or
• Find the record (with a RecordSet Object)
• Use the RecordSet Update Method

15
Change Password Form
ltFORM ACTIONindex.asp method POSTgt IDltINPUT
TYPETEXT NAMEuidgtltBRgt Old Password ltINPUT
TYPEpassword NAMEopwgtltBRgt New Password ltINPUT
TYPEpassword NAMEnpwgtltBRgt ltINPUT TYPEsubmit
Nameoperation value"Change"gt lt/FORMgt
16
SQL Updates

• UPDATE table
• SET fieldname1newval1
• fieldname2newval2
• WHERE
• UPDATE members
• SET PW'blah'
• WHERE UID KONA'

17
Change Password code
Set DBConnServer.CreateObject("ADODB.Connection
") DBConn.Open("eiw") DBConn.Execute
(UPDATE Members SET PW '" npw "' WHERE UID
'" uid "'" )
This is all in one line
UPDATE Members SET PW'blah' WHERE UIDKONA'
18
Wildcard Characters

• We need to use the SQL "LIKE" keyword.
• means "any string of zero or more characters"
• In some SQL version you use ""
• ? means "any single character"
• is a list of characters
• ! is a negated list of characters

19
Code Overview

• If operation is Create
• make sure we got a uid and pw from the user
• make sure uid is not already used (Code not
  included in the example)
• create new record in database
• If operation is Login
• Get the uid and the pw from the user
• validate uid and pw using a recordset
• If Operation is Change Password
• Get the new password and the UID from the user
• Update the old password with the new password
  only for the given UID.