How to Handle Null Values in Oracle

My classic asp program was like this:

Set ObjRs=Myconn.Execute("select max(record_id) as last_record_id from student_master").

I am supposed to add one to this value for creating a new record_id.
last_record_id=cint(objrs("last_record_id"))


But when there was no records in that table, it created an error like this:

Error Type:
Microsoft VBScript runtime (0x800A005E)
Invalid use of Null: 'cint'

So, What is the solution?

Whenever there is null value in a field,  oracle should not rerurn NULL, Instead it should return zero.

For this, modify above program like this:



Set ObjRs=Myconn.Execute("select nvl(max(record_id),0) as last_record_id from student_master").

I am supposed to add one to this value for creating a new record_id.
last_record_id=cint(objrs("last_record_id"))

The nvl function returns ZERO whenever it gets a NULL value.

It is a very usefull function. Remember it!

NVL(FIELDNAME,0)

Commonly used SqlPlus Commands

SOME IMPORTANT FREQUENTLY USED ORACLE SQLPLUS COMMANDS:

CREATE USER username IDENTIFIED BY password;
ALTER USER username IDENTIFIED BY newpassword;
DROP USER username [CASCADE]
To list all the usernames:
SELECT NAME FROM SYS.USER$
GRANT CONNECT, RESOURCE TO myuser;

REVOKE UNLIMITED TABLESPACE FROM myuser;

GRANT CREATE SESSION, CREATE TABLE TO myuser;


.
CREATE TABLESPACE myspace
DATAFILE 'C:\oracle\tables\tb.dbf' SIZE 30M;

CREATE USER myuser IDENTIFIED BY mypassword
DEFAULT TABLESPACE myspace
QUOTA UNLIMITED ON myspace;

Create table mytable(name varchar2(50),staff_no number primary key)
Alter table mytable add(age number);
Alter table mytable drop column age;

Alter table mytable enable constraint constr_maxval;

alter table mytable add constraint constraint  uk_staff_no UNIQUE(STAFF_NO);






to_date,('1-3-2012','mm/dd/yyyy')
round(x,2)
tunc(x,2)
ceil,floor,avg,count,max

ADD EDIT DELETE IN SINGLE PROGRAM

Using a single Classic ASP program, we can ADD,EDIT and DELETE the records from a table.
Following is the code:



 <%
'CONNECTING TO THE DATABASE 
Set MyConn =Server.CreateObject("ADODB.Connection")
MyConn.open="provider=MSDAORA;data source=mydatasource; user id=myuserid;password=mypassword"
'
'SESSION SECURITY
'if session("role")<>"ADMINISTRATOR" or  session("user_name")="" then
  'response.redirect("../myhomepage.ASP?result=Session Timed out. Please login again")
'end if

PRIMARY_KEY_NAME="student_id"
PRIMARY_KEY_VALUE=REQUEST("student_id")
DEFAULT_ORDER_BY_FIELDS="start_date"
THIS_FILE_NAME="student_master_aed.asp"
BACK_FILE_NAME="admin_welcome.asp"
%>

<%
'EDIT   EDIT   EDIT   EDIT   EDIT   EDIT   EDIT   EDIT   
If Request("job")="edit" Then
     student_id_value=Request("student_id")
     set objRsEdit=Server.CreateObject("ADODB.Recordset")
objRsEdit.CursorLocation=3
sql="select * from  student_master where student_id=" & request("student_id")
 
objRsEdit.open sql,myconn,1,3
If ObjRsEdit.EOf then
response.write("<font size=5>Error...</font>")
Else

ddmmyy_start_date=trim(request("start_date"))
mmddyy_start_date=mmddyy(ddmmyy_start_date)
ddmmyy_end_date=trim(request("end_date"))
mmddyy_end_date=mmddyy(ddmmyy_end_date)
            objRsEdit("student_id")=trim(request("student_id"))
objRsEdit("start_date")=mmddyy_start_date
objRsEdit("end_date")=mmddyy_end_date
objRsEdit("course_code")=trim(request("course_code"))
 
objRsEdit.update
response.redirect(THIS_FILE_NAME & "?postback_student_id=" & student_id_value & 

"&result=Successfully Updated")
 
end if
end if
%>




<html>                                 
<head>
<link href="../style_sheet_module/style_sheet.css" rel="stylesheet">



</head>
<body topmargin=0 leftmargin=0>
<a href="<%=BACK_FILE_NAME%>">back</a>
<!--BANNER COMES HERE-->


<%
'DISPLAYING MESSAGES
if Request("msg")<> "" then
 %>  
<div style="background-color:#ffff00;color:#ff0000;font:bold 18px arial;padding:5px">
    <%=Request("msg") %> 
</div>
<%
End If
 %>





<%
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

order_by_fields=request("order_by_fields")
if order_by_fields="" then
order_by_fields=DEFAULT_ORDER_BY_FIELDS
end if
session("order_by_fields")=order_by_fields
ORDER_BY_CONDITION=" order by " & order_by_fields
%>



<%
'DELETECONFIRMED DELETECONFIRMED DELETECONFIRMED
If Request("job")="deleteconfirmed" and Request("student_id")<> "" Then
Myconn.Execute="Delete from student_master where student_id="&request("student_id")
Myconn.Execute="Delete from student_vs_centre where student_id="&request("student_id")
response.redirect(THIS_FILE_NAME & "?result=Deleted SUCCESSFULLY")
End If
%>

<%
'ADDNEW  ADDNEW  ADDNEW  ADDNEW  ADDNEW  ADDNEW  ADDNEW  ADDNEW  ADDNEW  
If Request("job")="addNew" Then
Set ObjRsNew=Myconn.Execute("select max(student_id)as last_student_id from student_master")
If not objRsNew.Eof then
last_student_id=ObjRsNew("last_student_id")
if isnull(last_student_id) then
new_student_id=1
else
new_student_id=cint(last_student_id) +1
end if
end if


     set objRsAddNew=Server.CreateObject("ADODB.Recordset")
objRsAddNew.CursorLocation=3
sql="select * from  student_master where rownum=1"
 
objRsAddNew.open sql,myconn,1,3
ObjRsAddNew.addNew
 

ddmmyy_start_date=trim(request("start_date"))
mmddyy_start_date=mmddyy(ddmmyy_start_date)
ddmmyy_end_date=trim(request("end_date"))
mmddyy_end_date=mmddyy(ddmmyy_end_date)
 objRsAddNew("student_id")=new_student_id
      objRsAddNew("student_code")=trim(request("student_code"))
objRsAddNew("start_date")=mmddyy_start_date
objRsAddNew("end_date")=mmddyy_end_date
objRsAddNew("course_code")=trim(request("course_code"))
objRsAddNew.update
Set ObjRsCen=Myconn.Execute("select study_centre_id from study_centre_master where 

is_active=1")
If not ObjRsCen.EOF then
 
While Not OBjRsCen.EOF
tab_study_centre_id="c" & ObjRsCen("study_centre_id")
if Request(tab_study_centre_id)="y" then
sch_cen_batch_code=new_student_id &"-" & ObjRsCen("study_centre_id")
sql_sch_cen="insert into student_vs_centre(batch_code,study_centre_id,student_id) values('"& 

sch_cen_batch_code &"',"& ObjRsCen("study_centre_id")&","& new_student_id &")"
Myconn.Execute(sql_sch_cen)
End if
ObjRsCen.Movenext
Wend
end if
 
response.redirect(THIS_FILE_NAME & "?postback_student_id =" & new_student_id & "&result=Successfully Added")
end if
%>






<%
'ADDNEWFORM  ADDNEWFORM ADDNEWFORM ADDNEWFORM ADDNEWFORM ADDNEWFORM 
if Request("job")="addNewForm" then
%>
        <form name="formAdd"  method="POST" action="<%=THIS_FILE_NAME%>">
        <input type="hidden" name="job" value="addNew">


<table align="center" class="form_table" >
<tr>
<td  class="medium_title" colspan="2">
Add New </td>
</tr>
<tr class="form_tr">
<td  class="form_td_left">Course</td>
<td class="form_td_right">

<%
     set ObjRsCo=Myconn.Execute("select * from course_master where is_deleted=0 order by course_name")

        %>
<select name="course_code">
<option value="">Select</option>
<%
If objrsco.eof then
%>
<option value="">Error.Course List is empty</option>
<%else
while not objrsCo.Eof
%>
<option  

value="<%=objrsCo("course_code")%>"><%=objRsCo("course_code")%>:<%=objrsCo("course_name")%></option>
<%
objrsco.movenext
wend
End If
%>
</select>
</td>
</tr>
<tr class="form_tr">
<td  class="form_td_left">student Code</td>
<td class="form_td_right">
<input type=text name="student_code"  size="25"  class="form_td_right">
<span class="eg">eg:[course code]-[year]-[sl.no]</span>
</td>
</tr>

<tr class="form_tr">
<td  class="form_td_left">Commencing Date</td>
<td class="form_td_right">
<input type=text name="start_date"  size="15" maxlength="15"  class="form_td_right">
</td>
</tr>

    <tr class="form_tr">
<td  class="form_td_left">Ending Date</td>
<td class="form_td_right">
<input type=text name="end_date"  size="15" maxlength="15"  class="form_td_right">
</td>
</tr>

<tr class="form_tr">
<td colspan=2 align=center class="form_submit">
<input type="submit" value="Insert">
</td>
</tr>
</table>
</form><%
 
 end if%>


<%
'EDITFORM   EDITFORM   EDITFORM   EDITFORM   EDITFORM   EDITFORM   EDITFORM   EDITFORM   
if Request("student_id") <>"" and Request("job")="editForm" then
set objRsEDIT=Myconn.execute("select * from student_master  where  student_id ='"& Request("student_id")&"'")
If ObjRsEdit.EOF Then
 response.write("error.")
Else%>
<form name="formEdit"  method="POST" action="<%=THIS_FILE_NAME%>">
        <input type="hidden" name="job" value="edit">
        <input type="hidden" name="student_id" value="<%=objRsEdit("student_id")%>">

<table align=center class="form_table" border=1 cellspacing=0>
<tr>
<td  class="form_title" colspan=2>
Edit the student Details </td>
</tr>
<tr>
<td  cclass="form_td_left">Course Name</td>
<td  class="form_td_right">

<%
set ObjRsCo=Myconn.Execute("select * from course_master where is_deleted=0 order by 

course_name")

       %>
<select name="course_code">
<option value="">Select</option>
<%
If objrsco.eof then

%>
<option value="">Error.Course List is empty</option>
<%else
while not objrsCo.Eof
If ObjRsEdit("course_code")=ObjrsCo("course_code") then
%>
<option selected="selected"  

value="<%=ObjrsCo("course_code")%>"><%=objrsCo("course_name")%></option>
<%
else
%>
<option  value="<%=ObjrsCo("course_code")%>"><%=ObjrsCo("course_name")%></option>
<%
end if
objrsco.movenext
wend
End If
%>
</select>
</td>
</tr>
<tr>
<td  class="form_td_left">student Code</td>
<td class="form_td_right">
<span style="form_td_right"><%=ObjRsEdit("student_code")%></span>
</td>
             

</tr>

<tr>
<td  class="form_td_left">Commencing Date</td>
<td class="form_td_right">
<input type=text name="start_date" value="<%=ddmmyyslash(objRsEDIT("start_date"))%>" 

size="15" maxlength="15" class="shiv_mand"></td>
</td>
</tr>

<tr>
<td  class="form_td_left">Ending Date</td>
<td class="form_td_right">
<input type=text name="end_date" value="<%=ddmmyyslash(objRsEDIT("end_date"))%>" size="15" 

maxlength="15" class="shiv_mand"></td>
</td>
</tr>

<tr>
<td colspan=2 align=center class="form_submit">
<input type="submit" class="form_submit"  value="Update">
</td>
</tr>
</table>
</form>

<%
 end if
 end if%>
 
 
 
 

<%
'VIEW/DELETEVIEW  VIEW/DELETEVIEW VIEW/DELETEVIEW VIEW/DELETEVIEW VIEW/DELETEVIEW VIEW/DELETEVIEW 

if Request("student_id") <>"" and (Request("job")="deleteview" or Request("job")="view") then
set objRsView=Myconn.execute("select * from student_master  where  student_id ='"& Request("student_id")&"'")
If ObjRsView.EOF Then
 response.write("error.")
Else%>

      <table align=center class="form_table" border=1 cellspacing=0>
<tr>
<td  class="form_title" colspan=2>
</td>
</tr>

<tr>
<td  class="form_td_left">student Code</td>
<td class="form_td_right">
 <%=ObjRsview("student_code")%>
</td>            
</tr>

<tr>
<td  class="form_td_left">course_code</td>
<td class="form_td_right">
 <%=ObjRsview("course_code")%>
</td>            
</tr>

<tr>
<td  class="form_td_left">Commencing Date</td>
<td class="form_td_right">
 <%=ddmmyy(ObjRsview("start_date"))%>
</td>            
</tr>

<tr>
<td  class="form_td_left">Ending Date</td>
<td class="form_td_right">
 <%=ddmmyy(ObjRsview("end_date"))%>
</td>            
</tr>


<tr>
<td  class="form_td_left">Course</td>
<td class="form_td_right">
<%
Set ObjRsViewCo=Myconn.Execute("select * from course_master where course_code='" & 

objrsView("course_code") & "'")
If ObjRsViewCo.EOF then
ViewCo="Error"
Else
ViewCo=ObjRsviewCo("course_name")
End If
 
 %>
 <%=ViewCo%>
</td>            
</tr>

<tr>
<td colspan="2" style="font:bold 15px arial;background-color:yellow">
<%
if request("job")="deleteview" then
%>
<a href="<%=THIS_FILE_NAME%>?job=deleteconfirmed&student_id=<%=request("student_id")%>">Are 

you sure you want to delete this record?</a>
<%end if
%>
</td>
</tr>
</table>
</form>

<%
 end if
 end if%>

  

<%
sql="select * from student_master sm, course_master cm where cm.course_code=sm.course_code and cm.is_deleted=0 order by 

sm.start_date"
set objRs=Myconn.execute(sql)
%>
<table cellspacing="0" align="center" border=0 style="border-collapse: collapse" width="70%" >
<%
 if Request("job")<>"addNewForm" then
  %>
<tr>
<td style="font:bold 14px arial;">  
<a href="<%=THIS_FILE_NAME%>?job=addNewForm"> Add New  student</a> &nbsp;&nbsp;&nbsp;&nbsp; 
<a href="<%=THIS_FILE_NAME%>?job=viewlist"> student List</a> &nbsp;&nbsp;&nbsp;&nbsp; 
</td>
</tr>

<%
End if
' end of view
%>


<tr>
<td width="100%" align="center" <td class="form_title">
student LIST
</td>
</tr>
</table>


<table border="1" cellspacing=0 align=center width=70% class="shiv">
<tr>
<td colspan="6" style="font:bold 18px arial">
<% if Request("job")="viewlist" Then %>
<a href="<%=THIS_FILE_NAME%>?job="> Back to Add/Edit/Delete</a>
<%

End If%>
</td>
</tr>
<tr>
<th width="10%" nowrap class="form_header">
Edit/Delete
</th>

<th nowrap width="5" class="form_header">
SNo
</th>
<th width="40%" class="form_header">
student Code
</th>
<th width="30%" class="form_header">
Start Date
</th>





</tr>
<%
if  objRs.eof then
%>
<tr>
<td colspan=14 align=center >
<font size=4 color=RED>
<B>
NIL
</B>
</font>
The List is empty
</td>
</tr>
<%
else
i=1
old_student_id=""
while not objRs.eof
tr_bgcolor="white"
if i mod 2=0 then tr_bgcolor="#f0f0f0"
if cstr(objRs("student_id"))=Request(POSTBACK_student_id) then 
  tr_bgcolor="yellow"
end if
student_id=ObjRs("student_id")
%>


<tr bgcolor="<%=tr_bgcolor%>">
<% 'if Request("job")<>"view" Then %>
<td nowrap class="shiv_very_small">
<a href="<%=THIS_FILE_NAME%>?job=deleteview&student_id=<%=objRs("student_id")%>">Delete</a>
<a href="<%=THIS_FILE_NAME%>?job=editForm&student_id=<%=objRs("student_id")%>">Edit</a>
<a href="<%=THIS_FILE_NAME%>?job=view&student_id=<%=objRs("student_id")%>">View</a>
</td>
     <%'End If%>
<td class="form_td_right"><%=i%></td>
<td class="form_td_right"><%=objRs("student_code")%></td>
<td class="form_td_right"> <%=ddmmyy(objRs("start_date"))%></td>


</td>
</tr>

<%

i=i+1
objRs.movenext
wend
end if
%>

</table>

The output screen of this asp program will be looking like this:

CSS Transition Property Sample Code

What is TRANSITION?
It is animation without using swf or other animation files.
For example, the color of the font, size of the font, background color,etc can be changed GRADUALLY when the user hover over a link.
Move the mouse over the links given below to understand what is transition.

This CSS deals with Transition

Transition is animation using css

Enjoy this ASP Store

In the above demo, the background color changes SLOWLY
Font size increases slowly
Height of the div increases slowly


Copy the following code in NotePad and save it as  abc.htm and run that file
This transition will not work in Internet Explorer

<html> <head> <style> div {     font:normal 15px arial; width:300px; transition-property:width,background-color; transition-duration:1s; -webkit-transition-property:width,background-color,font-size; -webkit-transition-duration:1s; -moz-transition-property:width,background-color; -moz-transition-duration:1s; -o-transition-property:width,background-color; -o-transition-duration:1s; background-color:#f0f0f0; color:White; margin-bottom:2px; padding:10px 30px; } div:hover {   font-size:20px;     width:500px;     background-color:Red;     color:#808080; } div a {    color:#336699;    text-decoration:none; } </style> </head> <body> <div> <a href="abc.htm">   This CSS deals with Transition </a> </div> <div> <a href="abc.htm">   Transition is animation using css </a> </div> <div> <a href="schedule_master_aed.asp">   Enjoy this ASP Store </div> </body> </html>