in this single asp file. The screen shot will look like this.
How to use this asp file?
step 1: copy the following code and save it as "updation.asp" under c:\inetpub\wwwroot\test folder
step2. create an mdb with name "itemmanager.mdb" under c:\inetpub\wwwroot\test folder
step 3: In this mdb, create table "itemMaster" with fields ItemId(number),ItemName(text),Itemtype(text)
step 4: In this mdb, create another table"itemTypemaster with fields ItemName
step 5: Enter few records in ItemTypeMaster table (eg, NUTS,BOLTS)
step 6. open the browser and type : http://localhost/test/updation.asp
The screen shot will look like this:
CODE STARTS HERE
<%
THIS_FILE_NAME="updation.asp"
DEFAULT_ORDER_BY_FIELD_NAME="ItemName"
PRIMARY_KEY="ItemId"
PRIMARY_KEY_VALUE=Request(PRIMARY_KEY)
ItemName=trim(Request("ItemName"))
ItemType=trim(Request("ItemType"))
order_by=Request("order_by")
If order_by="" then
ORDER_BY_CONDITION=" order by " & DEFAULT_ORDER_BY_FIELD_NAME
Else
ORDER_BY_CONDITION=" order by " & order_by
End If
POSTBACK_PRIMARY_KEY="PostBackItemId"
SQL_FOR_LIST="select * from ItemMaster" & ORDER_BY_CONDITION
response.write(sql_for_list)
Set con=Server.CreateObject("ADODB.Connection")
con.open "provider=Microsoft.jet.oledb.4.0;data source=c:\inetpub\wwwroot\bs\ItemManager.mdb;"
if PRIMARY_KEY_VALUE <> "" and request("job")="deleteConfirmed" then
con.Execute("delete from ItemMaster where ItemID="& PRIMARY_KEY_VALUE)
response.redirect(THIS_FILE_NAME & "?result=Successfully Deleted.")
end if
if not PRIMARY_KEY_VALUE="" Then
SQL_FOR_EDIT_DELETE_VIEW="select * from ItemMaster where ItemID=" & PRIMARY_KEY_VALUE
Set RsEDV=con.Execute(SQL_FOR_EDIT_DELETE_VIEW)
If Not RsEDV.Eof Then
RsItemID=RsEDV("ItemID")
RsItemName=RsEDV("ItemName")
RsItemType=RsEDV("ItemType")
IsRecordExists="yes"
End If
End if
%>
<%
function SelectBoxItemType
'SELECT BOX INCUDE FILE FOR EDIT FORM OR DELETE CONFIRMATION FORM
'When more than one select box is there in page, then you have to change the Recordset Name,etc
SQL_FOR_SELECT="select * from ItemTypeMaster"
%>
<select size="1" name="ItemType" style="font-family: Verdana; font-size: 8pt; font-weight: bold" accesskey=""
onblur="if (this.value !='')this.style.backgroundColor='white'" >
<%
set ObjRsSelect=con.Execute(SQL_FOR_SELECT)
if ObjRsSelect.eof then %>
<option value>Error : List is Empty</option>
<%
else%>
<option value>--select--</option>
<%
while not ObjRsSelect.eof
RsValue=ObjRsSelect("ItemType")
if RsItemType=RsValue and (Request("job")="edit" or Request("job")="delete")then
%>
<option value="<%=RsValue%>" selected><%=RsValue%></option>
<%
else
%>
<option value="<%=RsValue%>"><%=RsValue%></option>
<%
end if
%>
<%
ObjRsSelect.movenext
wend
end if
%>
</select><font color=red>*</font>
<%
End Function 'SelectBoxForEditForm(SelectBoxName)
%>
<%
sub alertResultMsg()
%>
<script language="javascript">
window.onload=function(){
if (FormResultMsg.resultMsg.value !="")
{
alert(FormResultMsg.resultMsg.value);
}
}
</script>
<form name="FormResultMsg">
<input type="hidden" name="resultMsg" value="<%=request("result")%>">
</form>
<%
end sub
%>
<%
'#####################updation1.asp
job=Request("job")
if job="insert_success" then
response.redirect(THIS_FILE_NAME & "?result=success")
end if
if PRIMARY_KEY_VALUE="" and (job="editConfirmed" or job="deleteConfirmed") then
response.redirect(THIS_FILE_NAME &"?result=FAILED UPDATION...Error code:16:record id not passed")
end if
if PRIMARY_KEY_VALUE <>"" and job="editConfirmed" then
set objRs=Server.CreateObject("ADODB.Recordset")
objRs.CursorLocation=3
response.write(SQL_FOR_EDIT_DELETE_VIEW)
objRs.open SQL_FOR_EDIT_DELETE_VIEW,con,2,3
if not objRs.eof then
objRs("ItemName")=ItemName
objRs("ItemType")=ItemType
objRs.update
response.redirect(THIS_FILE_NAME & "?postback_ItemId=" & ItemId & "&result=Successfully Updated")
end if
end if
if job="insert" then
if ItemName="" then
response.redirect(THIS_FILE_NAME & "?result=Sorry! You havt not typed ITEM NAME.")
end if
'set objRsDup=con.Execute("select * from ItemMaster where "&INSTITUTE_CONDITION&" and ItemName='"&request.Form("ItemName")&"'")
'if not objRsDup.eof then
' response.redirect("ItemMaster.asp?postback_ItemId=" & objRsDUP("ItemId") & "&result=DONE")
'end if
set objRsMaxID=con.Execute("select * from ItemMaster")
if objRsMaxID.eof then
NewItemID=1
else
set objRsMaxId=con.Execute("select max(ItemId) as LastItemId from ItemMaster")
NewItemId=cint(objRsMaxId("LastItemId")) +1
end if
'''''''''''''''''''''''''''''''''''''''''''''''
set objRs=Server.CreateObject("ADODB.Recordset")
sql="select * from ItemMaster where itemId=1"
'the follownig is for oracle
'objRs.CursorLocation=3
'objRs.open sql,con,1,3
'the follownig is access
objRs.open sql,con,2,3
objRs.AddNew
objRs("ItemId")=NewItemId
objRs("ItemName")=ItemName
objRs("ItemType")=ItemType
objRs.update
response.redirect(THIS_FILE_NAME & "?job=insert_success&postbackPrimaryKey=" & NewItemId & "&result=Successfully Added")
end if
'#############################updation1.asp ends here
%>
<html>
<head>
<title><%=THIS_PAGE_TITLE%></title>
</head>
<body >
<%alertResultMsg()%>
<%
order_by=request("order_by")
If order_by="" then
order_by=DEFAULT_ORDER_BY_FIELD_NAME
end If
session("order_by")=order_by
job=Request("job")
%>
<%
If Request(PRIMARY_KEY)<>"" and Request("job")="view" then
set objRsView=con.Execute(SQL_FOR_EDIT_DELETE_VIEW)
If Not objRsView.EOF Then%>
<table align=center bgcolor=#f0f0f0 border=1 cellspacing=0 bordercolor=#d0d0d0 >
<tr>
<td >Item Name </td>
<td ><%=objRsView("ItemName")%></td>
</tr>
<tr>
<td >Item Type </td>
<td > <%SelectBoxItemType%></td>
</tr>
<tr>
<td colspan=2 align=center >
<a href="#">Close</a>
</td>
</tr>
</table>
<%
end If
end If %>
<%
If IsRecordExists="yes" Then
%>
<table align=center bgcolor=#f0f0f0 border=1 cellspacing=0 bordercolor=#d0d0d0 >
<tr>
<td colspan=2>
Please Confirm deletion </td>
</tr>
<tr>
<td >Item ID</td>
<td ><%=RsItemID%></td>
</tr>
<tr>
<td >Item Name</td>
<td ><%=RsItemName%></td>
</tr>
<tr>
<td >Item Type</td>
<td ><%=RsItemType%></td>
</tr>
<tr>
<td colspan=2 align=center >
<font color=red>
Are you sure you want delete this Record?
</font>
<br>
<form action="<%=THIS_FILE_NAME%>">
<input type="hidden" name="ItemId" value="<%=RsItemId%>" />
<input type="hidden" name="job" value="deleteConfirmed" />
<input type="submit" value="Yes ! Delete" />
</form>
</td>
</tr>
</table>
<%
end If
%>
<%
If Request(PRIMARY_KEY)<>"" and Request("job")="edit" then
set objRsEDIT=con.Execute(SQL_FOR_EDIT_DELETE_VIEW)
If not objRsEDIT.eof then
ItemType=objRsEdit(PRIMARY_KEY)%>
<form name="FormEdit" method="Get" action="<%=ACTION_FILE_NAME%>">
<input type="hidden" name="job" value="editConfirmed">
<input type="hidden" name="<%=PRIMARY_KEY%>" value="<%=request(PRIMARY_KEY)%>">
<table align=center bgcolor="#f0f0f0" border=1 cellspacing=0>
<tr>
<td colspan=2>
Edit Form </td>
</tr>
<tr>
<td > Item name </td>
<td >
<input type=text name="ItemName" value="<%=objRsEDIT("ItemName")%>" size=35 >
</td>
</tr>
<tr>
<td >Item Type </td>
<td ><%SelectBoxItemType%></td>
</td>
</tr>
<tr>
<td colspan=2 align=center >
<a href="<%=THIS_FILE_NAME%>?result=IGNORED at your Request&">Ignore & Go Back</a>
<Input style="font:bold 16px arial;width:100px;" type="submit" value ="UPDATE">
</td>
</tr>
</table>
</form><%
end If
end If %>
<%
If Request(PRIMARY_KEY)="" and Request("job")="addNew" then%>
<form name="formAddNew" method="gET" action="<%=THIS_FILE_NAME%>">
<input type="hidden" name="job" value="insert" />
<table align=center bgcolor="#f0f0f0" border=1 cellspacing=0>
<tr>
<td class="ctms_red" colspan=2>
Form : Add a New Item </td>
</tr>
<tr>
<td >Item Name </td>
<td >
<input type=text name="ItemName" value="" size="35" >
</td>
</tr>
<tr>
<td >Item Type </td>
<td ><%SelectBoxItemType%></td>
</tr>
<tr>
<td colspan=2 align=center >
<a href="<%=THIS_FILE_NAME%>">Ignore & Go Back</a>
<input style="width:200px;font:bold 16px arial" type="submit" value="Save">
</td>
</tr>
</table>
</form>
<%
end If %>
<BR>
<%
If request("job")="" then
%>
<%
set objRs=con.Execute(SQL_FOR_LIST)
%>
<table align=center border=2 bordercolor=#d0d0d0 cellpadding=1 width="403">
<tr>
<td height="37" colspan=6 >
<a href="<%=THIS_FILE_NAME%>?job=addNew" >Add a NEW Item </a>
</td>
</tr>
<th >
</th>
<th width="30" nowrap >
SNo
</th>
<th width="176" >
Item Name
<a style="font:normal 9px arial" href="<%=THIS_FILE_NAME%>?job=<%=job%>&<%=PRIMARY_KEY%>=<%=PRIMARY_KEY_VALUE%>&order_by=ItemName"><br />(sort) </a>
</th>
<th width="176" >
Item Type
<a style="font:normal 9px arial" href="<%=THIS_FILE_NAME%>?job=<%=job%>&<%=PRIMARY_KEY%>=<%=PRIMARY_KEY_VALUE%>&order_by=ItemType"><br />(sort) </a>
</th>
</tr>
</thead>
<tbody>
<%
If objRs.eof then
%>
<tr>
<td colspan="10" align="center" >
<font size=3 color=RED>
<B>
NIL
</B>
</font>
The List is empty
</td>
</tr>
<%
else
i=1
while not objRs.eof
tr_bgcolor="white"
If i mod 2=0 then tr_bgcolor="#f0f0f0"
If cstr(objRs(PRIMARY_KEY))=Request(POSTBACK_PRIMARY_KEY) then tr_bgcolor="yellow"%>
<tr bgcolor="<%=tr_bgcolor%>">
<td nowrap> <a href="<%=THIS_FILE_NAME%>?job=edit&<%=PRIMARY_KEY%>=<%=ObjRs("ItemId")%>">Edit</a>
<a href="<%=THIS_FILE_NAME%>?job=delete&<%=PRIMARY_KEY%>=<%=ObjRs("ItemId")%>">Delete</a>
</td>
<td align=right><%=i%> </td>
<td > <%=objRs("ItemName")%></td>
<td > <%=objRs("ItemType")%></td>
</tr><%
i=i+1
objRs.movenext
wend
end If
%>
</tbody>
</table>
<%
end If
%>
CODE ENDS HERE
