DT221/3 Internet Application Development

1
DT221/3 Internet Application Development
Active Server Pages Database Connection
2
Example of ASP Database Connection

• A simple application example
• Use ASP for HTML form
• Login Check
• Database Connection
• Database Insertion, Update and Deletion

3
Example of ASP Database Connection

4
Example of ASP Database Connection-login.asp

lt_at_ LanguageVBScript gt ltHTMLgt ltHEADgtlttitlegtLogin
pagelt/titlegt ltMETA NAME"GENERATOR"
Content"Microsoft Visual Studio
6.0"gt lt/HEADgt ltBODYgt ltform nameform1
method"post" action"check.asp"gt User Name
ltinput type"text" id"user" name"user"gt ltpgt Pass
word ltINPUT type"password" id"password"
name"password"gt ltpgt ltINPUT type"reset"
value"Reset" idreset nameresetgtnbspnbspnbs
pnbsp ltINPUT type"submit" value"Login"
idsubmit namesubmitgt lt/formgt lt/BODYgt lt/HTMLgt
5
Example of ASP Database Connection-check.asp(1)

lt_at_ LanguageVBScript gt lt Dim user,
password user Request.Form("user").Item password
Request.Form("password").Item dim Conn, Rs,
mySQL set Conn Server.CreateObject("ADODB.Co
nnection") ' conn objects set Rs
Server.CreateObject("ADODB.RecordSet")' Record
Set Conn.Open ("ProvidersqloledbServerCIAND
atabasecliuUidtestuserPassword") Rs.Active
Connection Conn mySQL "Select SecurityLevel
From cliu.users Where UserName '" user "'
and Password '" password "'" Rs.Open(mySQ
L)
6
Example of ASP Database Connection-check.asp(2)
if not Rs.eof then if Rs("securityLevel")12
then Session("level")12 Conn.Close Set
Conn NOTHING Response.Redirect
("menu.asp") end if if Rs("securityLevel")8
then Session("level")8 Conn.Close Set Conn
NOTHING Response.Redirect("showuser.asp") en
d if if Rs("securityLevel")1
then Session("level")1 Conn.Close Set Conn
NOTHING Response.Redirect("welcome.asp") end
if

7
Example of ASP Database Connection-check.asp(3)
else Session("level")-1 Conn.Close Set Conn
NOTHING Response.Redirect("login.asp") end
if gt

8
Example of ASP Database Connection-check.asp(3)
else Session("level")-1 Conn.Close Set Conn
NOTHING Response.Redirect("login.asp") end
if gt

9
Example of ASP Database Connection-adduser.asp
lt_at_ LanguageVBScript gt ltpgtltform nameform1
method"post" action"addusertodb.asp"gt Please
enter the new user name ltinput type"text"
id"user" name"user" size"20"gt ltpgtPlease enter
the new password ltINPUT type"password"
id"password" name"password" size"20"gt ltpgtPlease
enter the first name ltinput type"text"
id"first" name"first" size"20"gt ltpgtPlease
enter the second name ltinput type"text"
id"second" name"second" size"20"gt ltpgtPlease
enter the security level ltinput type"text"
id"level" name"level" size"20"gt ltpgtltINPUT
type"reset" value"Reset" idreset
nameresetgtnbspnbspnbspnbsp ltINPUT
type"submit" value"Add" idsubmit
namesubmitgt lt/formgt lt/BODYgt lt/HTMLgt

10
Example of ASP Database Connection-addusertodb.asp
lt_at_ LanguageVBScript gt ltDim user, password,
first, second, level user Trim(Request.Form("use
r").Item) password Trim(Request.Form("password")
.Item) first Trim(Request.Form("first").Item) se
cond Trim(Request.Form("second").Item) level
Request.Form("level").Item dim Conn, Rs,
mySQL set Conn Server.CreateObject("ADODB.Conne
ction") ' conn objects set Rs
Server.CreateObject("ADODB.RecordSet")' Record
Set Conn.Open ("ProvidersqloledbServerCIANData
basecliuUidtestuserPassword") mySQL
"Insert into cliu.users values ( '" user "' ,
'" password "', '" first "', '" second
"' , " level ") Conn.Execute(mySQL) Conn.C
lose Set Conn NOTHING Response.Redirect("showus
er.asp") gt

11
Example of ASP Database Connection-edit.asp
lt_at_ LanguageVBScript gt lt Response.Write("Ple
ase select the user to editltpgt") dim Conn, Rs,
user_id, i set Conn Server.CreateObject("ADODB.
Connection") ' conn objects set Rs
Server.CreateObject("ADODB.RecordSet")' Record
Set Conn.Open ("ProvidersqloledbServerCIANDat
abasecliuUidtestuserPassword") Rs.ActiveCon
nection Conn Rs.Open("Select From
cliu.users") While not Rs.eof Response.Write("us
er name " Rs("UserName").Value "
") Response.Write("lta hrefedituser.asp?id"
Rs("UserID").Value "gteditlt/agt") Response.Write(
"ltbrgt") Rs.MoveNext Wend gt lt/bodygt lt/htmlgt

12
Example of ASP Database Connection-edituser.asp(1)
lt_at_ LanguageVBScript gt ltDim userid userid
Int(Request.QueryString("id").Item) dim Conn, Rs,
mySQL set Conn Server.CreateObject("ADODB.Con
nection") ' conn objects set Rs
Server.CreateObject("ADODB.RecordSet")' Record
Set Conn.Open ("ProvidersqloledbServerCIANDat
abasecliuUidtestuserPassword") Rs
Conn.Execute("cliu.selectuser "
userid) gt ltHTMLgt ltHEADgtlttitlegtSelect user to
editlt/titlegt ltMETA NAME"GENERATOR"
Content"Microsoft FrontPage 6.0"gt lt/HEADgt ltBODYgt

13
Example of ASP Database Connection-edituser(2).asp
ltSCRIPT languageJavascriptgt //Client side
javascript function check() testtrue if
(document.form1.user.value"") alert("Please
enter the user name") document.form1.user.focus
() testfalse if (document.form1.password.va
lue"") alert("Please enter the
password") document.form1.password.focus() t
estfalse if (document.form1.first.value"")
alert("Please enter the user' first
name") document.form1.first.focus() testfal
se if (document.form1.second.value"") aler
t("Please enter the user's second
name") document.form1.second.focus() testfa
lse

14
Example of ASP Database Connection-edituser(3).asp
if (document.form1.level.value""(document.form
1.level.value!12document.form1.level.value!8
document.form1.level.value!1)) alert("Please
enter the correct level") document.form1.user.f
ocus() testfalse return
test lt/SCRIPTgt ltform nameform1 method"post"
actioneditusertodb.asp onSubmit"return
check()"gt ltinput type"hidden" nameuserid
iduserid valueltuseridgtgt

15
Example of ASP Database Connection-edituser.asp(4)
Please enter the new user name ltinput
type"text" id"user" name"user"
value"ltRs("UserName").Valuegt"
size"20"gt ltpgtPlease enter the new password
ltINPUT type"password" id"password"
name"password" value"ltRs("Password").Valuegt"
size"20"gt ltpgtPlease enter the first name
ltinput type"text" id"first" name"first"
value"ltRs("FirstName").Valuegt"
size"20"gt ltpgtPlease enter the second name
ltinput type"text" id"second" name"second"
value"ltRs("SecondName").Valuegt"
size"20"gt ltpgtPlease enter the security level
ltinput type"text" id"level" name"level"
value"ltRs("SecurityLevel").Valuegt"
size"20"gt ltpgtltINPUT type"reset" value"Reset"
idreset nameresetgtnbspnbspnbspnbsp
ltINPUT type"submit" value"Edit" idsubmit
namesubmitgt lt/formgtlt/BODYgt lt/HTMLgt

16
Example of ASP Database Connection-editusertodb.as
p
lt_at_ LanguageVBScript gt ltDim user, password,
first, second, level userid Int(Request.Form("us
erid").Item) user Trim(Request.Form("user").Item
) password Trim(Request.Form("password").Item) f
irst Trim(Request.Form("first").Item) second
Trim(Request.Form("second").Item) level
Int(Request.Form("level").Item) dim Conn, Rs,
mySQL set Conn Server.CreateObject("ADODB.Conne
ction") ' conn objects set Rs
Server.CreateObject("ADODB.RecordSet")' Record
Set Conn.Open ("ProvidersqloledbServerCIAND
atabasecliuUidtestuserPassword") Conn.Execu
te("cliu.edituser " userid ",'" user "' , '"
password "', '" first "', '" second "'
, " level) Conn.Close Set Conn
NOTHING Response.Redirect("menu.asp") gt

17
Example of ASP Database Connection-deluser.asp(1)
lt_at_ LanguageVBScript gt lthtmlgt ltheadgtlttitlegtDatab
ase Connectionlt/titlegt ltscriptgt function
ConfirmDelete(s_User, s_ID) var s_Dialog
"Are you sure you want to delete user '" s_User
"'?" if(confirm(s_Dialog)) document.locati
on.href "deluserdb.asp?id" s_ID // end
function lt/scriptgt lt/headgt ltbodygt

18
Example of ASP Database Connection-deluser(2).asp
lt Response.Write("Please select the user to
deleteltpgt") dim Conn, Rs, user_id, i set Conn
Server.CreateObject("ADODB.Connection") ' conn
objects set Rs Server.CreateObject("ADODB.Recor
dSet")' Record Set Conn.Open
("ProvidersqloledbServerCIANDatabasecliuUid
testuserPassword") Rs.ActiveConnection
Conn Rs.Open("Select From cliu.users") While
not Rs.eof Response.Write("user name "
Rs("UserName").Value " ") Response.Write("lta
href""javascriptConfirmDelete('"
Rs("UserName").Value "','" Rs("UserID").Value
"')""gtdellt/agt") Response.Write("ltbrgt") Rs.Move
Next Wend gt lt/bodygt lt/htmlgt

19
Example of ASP Database Connection-deluserdb.asp
lt_at_ LanguageVBScript gt lt Dim userid userid
Int(Request.QueryString("id").Item) dim Conn,
Rs, mySQL set Conn Server.CreateObject("AD
ODB.Connection") ' conn objects set Rs
Server.CreateObject("ADODB.RecordSet")' Record
Set Conn.Open ("ProvidersqloledbServerCIAND
atabasecliuUidtestuserPassword") Conn.Execu
te("cliu.deluser " userid ) Conn.Close Set
Conn NOTHING Response.Redirect("menu.asp") gt