Select, Insert, Update, Delete Records in Database - Employee

File 1 : emp.asp
To select the employee ID


<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
dim con, cmd, rs
set con = server.CreateObject("ADODB.Connection")
set cmd = server.CreateObject("ADODB.Command")
set rs = server.CreateObject("ADODB.Command")
con.open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\empdb.mdb;Persist Security Info=False"
cmd.commandtext = "select * from emptable"
cmd.activeconnection = con
set rs = cmd.execute
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Select Employee ID</title>
</head>

<body>
<center>
<h1>Employee Details </h1>
<form name = "frmemp" action="empdb.asp" method="post">
Employee ID : <br />
<select case name = "empid">
<% while not rs.eof %>
<option value = <%=rs.fields(0)%>><%=rs.fields(0)%>
<% rs.movenext 
wend%>
</select>
<%rs.movefirst%>
<input type="submit" name="btnsubmit" value="Display" />
</form>
</body>
</html>

File 2 : empdb.asp
To display the information in html form to view, edit, add, delete


<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% dim con, cmd, rs, empids
set con = server.CreateObject("ADODB.Connection")
set cmd = server.CreateObject("ADODB.Command")
set rs = server.CreateObject("ADODB.Command")
con.open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\empdb.mdb;Persist Security Info=False"
if request.Form("empid") = "" then
response.redirect("emp.asp")
end if
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script language="vbscript">
function newr()
document.empdisp.empid.value = ""
document.empdisp.empname.value = ""
document.empdisp.empdept.value = ""
document.empdisp.empsalary.value = ""
end function
</script>
</head>

<body>
<%


empids = request.Form("empid")
cmd.commandtext = "select * from emptable  where empid=" &empids
cmd.activeconnection = con
set rs = cmd.execute
%>
<form name="empdisp" method="post" action="empdbm.asp" >
Employee Details
<p>Emp ID <input type="text" name="empid" value="<% =rs.fields(0)%>" size="20"  /></p>
<p>Emp Name <input type="text" name="empname"  value="<%=rs.fields(1)%>" size="20" /></p>
<p>Department <input type="text" name="empdept" value="<%=rs.fields(2)%>" size="20"  /></p>
<p>Salary <input type="text" name="empsalary" value="<%=rs.fields(3)%>" size="20"  /></p>
<input type="button" name="btnnew" onclick=newr() value="new" />
<input type="submit" name="btnsave" value="update" />
<input type="submit" name="btnsave" value="save" />
<input type="submit" name="btnsave" value="delete"  />
</form>
<a href="emp.asp"> Back </a>
</body>
</html>

File 3 : empdbm.asp
To process the insert, update, delete operations


<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% dim con, com, rs, cond
set con = server.CreateObject("ADODB.Connection")
set cmd = server.CreateObject("ADODB.Command")
set rs = server.CreateObject("ADODB.Command")
con.open = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\empdb.mdb;Persist Security Info=False"

if request.Form("btnsave") = "" then
response.Redirect("emp.asp")
end if


cond = request.Form("btnsave")
empids = request.Form("empid")
empnames = request.Form("empname")
empdepts = request.Form("empdept")
empsalarys = request.Form("empsalary")
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<%
if cond = "save" then
cmd.commandtext = "select * from emptable where empid="&empids
cmd.activeconnection = con
set rs = cmd.execute
if not rs.eof  then
response.Write("ID Number already exists.. <br/> Please choose different roll number")
else
cmd.commandtext = "INSERT INTO emptable VALUES (" &empids & ",'" &empnames &"','" &empdepts &"'," &empsalarys &")"
cmd.activeconnection = con
cmd.execute
response.Write("Record Saved")
end if
elseif cond = "edit" then
cmd.commandtext = "UPDATE emptable SET empname = '"&empnames &"', dept='" &empdepts &"', salary=" &empsalarys &" where empid = " &empids
cmd.activeconnection = con
cmd.execute
response.Write("Record Updated")
elseif cond = "delete" then
cmd.commandtext = "delete * from emptable where empid = " & empids
cmd.activeconnection = con
cmd.execute
response.Write("Record Deleted")
end if
%>
<a href="emp.asp"> Back </a>





</body>
</html>

File 4 : empdb.mdb
Create MS Access database file with the following information and place it in D:\empdb.mdb
Columns

Empid - Long Integer(4) - Primary Key
Empname - Tex(255)
Dept - Text(255)
Salary - Long Integer(4)

Add new comment