Classic ASP - How get Data from Excel and insert into excel
<%
excel_file_name="../EXCEL_MODULE/TRAINEES.XLS"
excel_sheet_name="INPLANT_LIST"
Set fs=Server.CreateObject("scripting.FileSystemObject")
excel_file_name=Server.Mappath(excel_file_name)
response.write(excel_file_name)
Set MyconnXL=Server.CreateObject("adodb.connection")
MyconnXL.open "Provider=Microsoft.jet.oledb.4.0;data source=" & excel_file_name & ";Extended Properties=Excel 8.0;"
Set ObjRsXL=MyconnXL.Execute("select * from ["& excel_sheet_name & "$]")
If ObjRsXL.eof then
response.write("no records present in the excel worksheet")
Else
While Not ObjRsXL.Eof
Err.Clear
trainee_name=ObjRsXL("TRAINEE_NAME")
Response.write("
" & trainee_name)
ObjRsXL.Movenext
Wend
End If
MyconnXL.close
Set MyconnXL=nothing
%>
<%
Set MyconnXL=Server.CreateObject("adodb.connection")
MyconnXL.open "Provider=Microsoft.jet.oledb.4.0;data source=" & excel_file_name & ";Extended Properties=Excel 8.0;"
sqlINS="select * from ["& excel_sheet_name & "$]"
Set ObjRsXL=MyconnXL.Execute("select * from ["& excel_sheet_name & "$] where trainee_name='RAMESH'")
set rsINS=server.createobject("adodb.recordset")
rsINS.cursorLocation=3
rsINS.open sqlINS,myconnXL,1,3
rsINS("AGE")=5
RsIns.Update
Set MyconnXL=Nothing
%>
Select Box Template
code for classic asp select box:
<%
If objrscen.eof then
%>
<%else
while not objrsCen.Eof
if cint(objRsEdit("centre_id"))=cint(objrscen("centre_id")) then%>
<%=objrscen("centre_name")%>
<%else%>
<%
end if%>
<%
objrscen.movenext
wend
End If
%>
Common Functions
In my classic asp programs, i keep certain customized functions which are frequently used in different ASP files, I am keeping them in a separte include file such as common_functions_inc.asp
Date conversion funtion 1: We get the date in dd/mm/yyyy through a form.
But different database accept different date formats.
One thing is common among various databases. they will always accept if the date is in the format: 31-
December-2013
So whenever we are going to insert a date, you can use this function.
<%
function ddmmyy_to_database(this_date)
'input format is 31/12/2013
'output format is 31-December-2013
dim dd_this_date,mm_this_date,yy_this_date
if this_date="" or this_date="//" then
ddmmyy_to_database=""
else
date_array=split(this_date,"/")
dd_this_date=date_array(0)
if dd_this_date <10 p="" then=""> dd_this_date="0" & cint(dd_this_date)
end if
mm_this_date=date_array(1)
mm_this_date=monthname(mm_this_date)
yy_this_date=date_array(2)
ddmmyy_to_database=dd_this_date & "-" & mm_this_date & "-" & yy_this_date
end if
end function
%>
=========================================================
In the same way, when we take out the date from the database and displaying, we can use this function:
<%
function ddmmyy_from_database(this_date)
'input format we need not know
'output format is 31/12/2013
dim dd_this_date,mm_this_date,yy_this_date
if not isdate(this_date) then
ddmmyy_from_database=""
else
dd_this_date=day(this_date)
if dd_this_date <10 p="" then=""> dd_this_date="0" & dd_this_date
end if
mm_this_date=month(this_date)
if mm_this_date <10 p="" then=""> mm_this_date="0" & mm_this_date
end if
yy_this_date=right(year(this_date),2)
ddmmyy_from_database=dd_this_date & "/" & mm_this_date & "/" & yy_this_date
end if
end function
%>
=====================================================
Date conversion funtion 1: We get the date in dd/mm/yyyy through a form.
But different database accept different date formats.
One thing is common among various databases. they will always accept if the date is in the format: 31-
December-2013
So whenever we are going to insert a date, you can use this function.
<%
function ddmmyy_to_database(this_date)
'input format is 31/12/2013
'output format is 31-December-2013
dim dd_this_date,mm_this_date,yy_this_date
if this_date="" or this_date="//" then
ddmmyy_to_database=""
else
date_array=split(this_date,"/")
dd_this_date=date_array(0)
if dd_this_date <10 p="" then=""> dd_this_date="0" & cint(dd_this_date)
end if
mm_this_date=date_array(1)
mm_this_date=monthname(mm_this_date)
yy_this_date=date_array(2)
ddmmyy_to_database=dd_this_date & "-" & mm_this_date & "-" & yy_this_date
end if
end function
%>
=========================================================
In the same way, when we take out the date from the database and displaying, we can use this function:
<%
function ddmmyy_from_database(this_date)
'input format we need not know
'output format is 31/12/2013
dim dd_this_date,mm_this_date,yy_this_date
if not isdate(this_date) then
ddmmyy_from_database=""
else
dd_this_date=day(this_date)
if dd_this_date <10 p="" then=""> dd_this_date="0" & dd_this_date
end if
mm_this_date=month(this_date)
if mm_this_date <10 p="" then=""> mm_this_date="0" & mm_this_date
end if
yy_this_date=right(year(this_date),2)
ddmmyy_from_database=dd_this_date & "/" & mm_this_date & "/" & yy_this_date
end if
end function
%>
=====================================================
Create LAST ID
In any database table, record id is very much important.
In MS access, there is a facility of AUTO NUMBER.
There is a simple method. Get the maximum value of record id and then add one.
This method has some drawbacks. If the last record has recently been deleted and if this value is sitting in other tables, inconsistency of database will occur.
so, the best way is maintain a separate table for keeping the last record id of required tables.
the code is like this:
If Request("job")="addNew" Then
'******CREATE NEW RECORD ID**********
Set ObjRsId=Myconn.Execute("select LAST_ID from eetp_last_id_master where field_name='BATCH_ID'")
If ObjRsId.EOf Then
Myconn.Execute("insert into eetp_last_id_master(last_id,field_name)values(1,'BATCH_ID')")
new_batch_id=1
Else
last_id=cint(ObjRsId("last_id"))
new_batch_id=last_id +1
End If
Myconn.Execute("update eetp_last_id_master set last_id=" & new_batch_id & " where field_name='BATCH_ID'")
'******CREATE NEW RECORD ID**********
So the new record id is ready and you can use that id as given below:
set objRsAddNew=Server.CreateObject("ADODB.Recordset")
objRsAddNew.CursorLocation=3
If session("database_type")="ms access" then
sql="select top 1 * from batch_master "
Else
sql="select * from batch_master where rownum=1"
End If
objRsAddNew.open sql,myconn,1,3
ObjRsAddNew.addNew
ObjRsAddNew("batch_id")=new_batch_id
........
add-cum-edit-delete-3level
This classic asp program has add,edit and delete in a single asp file.
At the same time, a 3 level selection of course.> schedule > topic is also there.
because of this it is somewhat complicated.
</p>
<p>
Timetable Master</p>
<p>
<%
THIS_FILE_NAME="WRITE THIS FILE NAME"
%>
<%
PRIMARY_KEY_NAME="timetable_record_id"
PRIMARY_KEY_VALUE=REQUEST("timetable_record_id")
DEFAULT_ORDER_BY_FIELDS="subject_code"
THIS_FILE_NAME="My_timetable_master_aed.asp"
BACK_FILE_NAME="admin_welcome.asp"
%>
<%
If Request("job")="edit" Then
timetable_record_id_value=Request("timetable_record_id")
set objRsEdit=Server.CreateObject("ADODB.Recordset")
objRsEdit.CursorLocation=3
sql="select * from My_timetable_master where timetable_record_id=" & timetable_record_id_value
objRsEdit.open sql,myconn,1,3
If ObjRsEdit.EOf then
response.write("Error...")
Else
ObjRsEdit("schedule_code")=Request.form("schedule_code")
ObjRsEdit("class_date")=ddmmyy_to_database(Request.form("class_date"))
ObjRsEdit("subject_code")=Request.form("subject_code")
ObjRsEdit("start_time_hour")=Request.form("start_time_hour")
ObjRsEdit("start_time_minute")=Request.form("start_time_minute")
ObjRsEdit("end_time_hour")=Request.form("end_time_hour")
ObjRsEdit("end_time_minute")=Request.form("end_time_minute")
ObjRsEdit("i_group_no")=Request.form("i_group_no")
objRsEdit.update
response.redirect(THIS_FILE_NAME & "?timetable_record_id=" & timetable_record_id_value & "&postback_timetable_record_id=" & timetable_record_id_value & "&msg=Successfully Updated")
end if
end if
%>
<%
function select_module_code
%>
<%
if Request("selected_subject_code") <> "" then
sql="select module_code,module_name from My_module_master where subject_code='"&Request("selected_subject_code") & "' order by module_name"
Set ObjRsMod=Myconn.Execute(sql)
If ObjRsMod.EOF then
%>
<%
else
%>
<%
While Not ObjRsMod.EOF
If asp_module_code=ObjRsMod("module_code") or session("module_code")=ObjRsMod("module_code") Then
%>
<%
Else
%>
<%
End If
ObjRsMod.MoveNext
Wend
End If
End If
%>
<%
end function
%>
<%
sub select_subject_code
%>
<%
if Request("selected_schedule_code") <> "" and Request("selected_subject_code") <> "" then
sql="select sub.subject_code from My_subject_master sub,My_course_master co,My_course_category_master cat,My_schedule_master sch where cat.course_category_code=sub.course_category_code and cat.course_category_code=co.course_category_code and co.course_code=sch.course_code and sub.subject_code='"&Request("selected_subject_code") & "' and sch.schedule_code='"&Request("selected_schedule_code") & "' order by sub.subject_code"
select_subject_code1
elseif Request("selected_schedule_code") <> "" then
sql="select sub.subject_code from My_subject_master sub,My_course_master co,My_course_category_master cat,My_schedule_master sch where cat.course_category_code=sub.course_category_code and cat.course_category_code=co.course_category_code and co.course_code=sch.course_code and sch.schedule_code='"&Request("selected_schedule_code") & "' order by sub.subject_code"
select_subject_code1
else
%>
<%
End If
end sub
%>
<%
sub select_subject_code1
Set ObjRsSub=Myconn.Execute(sql)
If ObjRsSub.EOF then
%>
<%
else
%>
<%
While Not ObjRsSub.EOF
If Request("selected_subject_code")=ObjRsSub("subject_code") Then
%>
<%
Else
%>
<%
End If
ObjRsSub.MoveNext
Wend
End If
end sub
%>
<%
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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
%>
<%
If Request("job")="deleteconfirmed" and Request("timetable_record_id")<> "" Then
timetable_record_id=Request("timetable_record_id")
If timetable_record_id="" Then
Response.redirect("../My_faculty_module/error.asp?line_number=Error.RecId Missing")
End If
set rs1=myconn.execute("select * from My_timetable_master where timetable_record_id=" & timetable_record_id)
If rs1.eof then
response.redirect("../My_faculty_module/error.asp?line_number=Error.Record is empty")
Else
myarray=rs1.getrows()
End if
field_list=""
insert_stt=""
for i=0 to (rs1.fields.count - 1)
field_list=field_list & rs1.fields(i).name
If i < rs1.fields.count -1 then
field_list=field_list & ","
End if
next
%>
<%for i =0 to ubound(myarray,2)%>
<%
insert_stt="insert into My_timetable_master " & "("&field_list&") values ("
j=0
for j =0 to (rs1.fields.count - 1)
dt=""
field_type=cint(rs1.fields(j).type)
if isNULL(myarray(j,i)) then
insert_stt=insert_stt & "NULL"
Elseif field_type=200 Then
insert_stt=insert_stt & "'"
insert_stt=insert_stt & myarray(j,i)
insert_stt=insert_stt & "'"
Elseif field_type=135 Then
dt=myarray(j,i)
if isdate(dt) then
dd=day(myarray(j,i))
mm=monthname(month(myarray(j,i)))
yy=year(myarray(j,i))
dtf=dd & "-" & mm & "-" & yy
insert_stt=insert_stt & "'" & dtf & "'"
else
insert_stt=insert_stt & "'"
insert_stt=insert_stt & myarray(j,i)
insert_stt=insert_stt & "'"
end if
Elseif field_type=5 or field_type=131then
insert_stt=insert_stt & myarray(j,i)
End If
If j < rs1.fields.count -1 then
insert_stt=insert_stt & ","
End if
Next
insert_stt=insert_stt & ");"
%>
<%next %>
<%
set objRsAddNew=Server.CreateObject("ADODB.Recordset")
objRsAddNew.CursorLocation=3
If session("database_type")="ms access" Then
sql="select top 1 * from My_deleted_tt_records "
Else
sql="select * from My_deleted_tt_records where rownum=1"
End If
objRsAddNew.open sql,myconn,1,3
ObjRsAddNew.addNew
objRsAddNew("insert_statement")=insert_stt
ObjRsAddNew.Update
ObjRsAddNew.Close
Set ObjRsAddNew=Nothing
Myconn.Execute("delete from My_timetable_master where timetable_record_id=" & timetable_record_id)
response.redirect(THIS_FILE_NAME & "?msg=Record Deleted SUCCESSFULLY.To restore back the deleted record, you may contact the Admininstrator")
End If
%>
<%
If Request("job")="addNew" Then
Set ObjRsMaxId=Myconn.Execute("select max(timetable_record_id) AS last_timetable_record_id from My_timetable_master")
last_timetable_record_id=ObjRsMaxID("last_timetable_record_id")
if isnull(last_timetable_record_id) then
new_timetable_record_id=1
Else
new_timetable_record_id=cint(last_timetable_record_id) +1
End If
chkDup="Select * from My_timetable_master where "
chkDup=chkDup & " schedule_code='"&Request("schedule_code") &"' "
chkDup=chkDup & " and subject_code='"&Request("subject_code") &"' "
chkDup=chkDup & " and start_time_hour="&Request("start_time_hour")
chkDup=chkDup & " and i_group_no="&Request("i_group_no")
if session("database_type")="ms access" then
chkDup=chkDup & " and class_date=#"& ddmmyy_to_database(Request("class_date") & "#")
Else
chkDup=chkDup & " and class_date='"& ddmmyy_to_database(Request("class_date") & "'")
End If
' response.redirect("../My_main_module/error.asp?line_number=" & chkDup)
Set ObjRsChk=Myconn.execute(chkDup)
If Not ObjRsChk.EOF then
postback_timeable_record_id=ObjRsChk("timetable_record_id")
response.redirect(THIS_FILE_NAME & "?timetable_record_id=" & timetable_record_id_value & "&postback_timetable_record_id=" & postback_timetable_record_id_value & "&msg=Sorry!Record ALREADY EXISTS")
End If
set objRsAddNew=Server.CreateObject("ADODB.Recordset")
objRsAddNew.CursorLocation=3
If Session("database_type")="ms access" then
sql="select top 1 * from My_timetable_master"
Else
sql="select * from My_timetable_master where rownum=1"
End If
objRsAddNew.open sql,myconn,1,3
ObjRsAddNew.addNew
ObjRsAddNew("timetable_record_id")=new_timetable_record_id
ObjRsAddNew("schedule_code")=Request.form("schedule_code")
ObjRsAddNew("i_group_no")=Request.form("i_group_no")
ObjRsAddNew("subject_code")=Request.form("subject_code")
ObjRsAddNew("module_code")=Request.form("module_code")
ObjRsAddNew("room_no")=Request.form("room_no")
ObjRsAddNew("start_time_hour")=Request.form("start_time_hour")
ObjRsAddNew("start_time_minute")=Request.form("start_time_minute")
ObjRsAddNew("end_time_hour")=Request.form("end_time_hour")
ObjRsAddNew("end_time_minute")=Request.form("end_time_minute")
ObjRsAddNew("start_time_minute")=Request.form("start_time_minute")
ObjRsAddNew("class_date")=ddmmyy_to_database(Request.form("class_date"))
ObjRsAddNew.update
session("tt_subject_code")=Request.form("subject_code")
session("tt_schedule_code")=Request.form("schedule_code")
session("tt_start_time_hour")=Request.form("start_time_hour")
session("tt_start_time_minute")=Request.form("start_time_minute")
session("tt_end_time_hour")=Request.form("end_time_hour")
session("tt_end_time_minute")=Request.form("end_time_minute")
session("tt_class_date")=Request.form("class_date")
session("tt_module_code")=Request.form("module_code")
session("tt_room_no")=Request.form("room_no")
session("tt_i_group_no")=Request.form("i_group_no")
response.redirect(THIS_FILE_NAME & "?postback_timetable_record_id=" & new_timetable_record_id & "&msg=Successfully Added")
End If
%>
<%If Request("job") <> "AddNewForm" Then %>
Add a New Time Table Record
<%
End If %>
<%
if Request("job")="AddNewForm" Then
Set ObjRsCentre=Myconn.execute("select * from My_study_centre_master where study_centre_id=" & session("study_centre_id"))
%>
Enter The New TimeTable Record
" />
<%
sqlSch="select * from My_schedule_master sch,My_course_master c where c.course_code=sch.course_code and sch.study_centre_id=" & session("study_centre_id") & " order by schedule_code"
set ObjRsSch=Myconn.Execute(sqlSch)
If ObjRsSch.EOF then
%>
<%
else
%>
<%
While Not ObjRsSch.EOF
If asp_schedule_code=ObjRsSch("schedule_code") or Request("selected_schedule_code")=ObjRsSch("schedule_code") Then
%>
<%
Else
%>
<%
End If
ObjRsSch.MoveNext
Wend
End If
%>
<%
'if Request("selected_schedule_code") <> "" and Request("selected_subject_code") <> "" then
'if asp_subject_code <> "" or Request("selected_subject_code") <> "" then
'sql="select sub.subject_code from My_subject_master sub,My_course_master co,My_course_category_master cat,My_schedule_master sch where cat.course_category_code=sub.course_category_code and cat.course_category_code=co.course_category_code and co.course_code=sch.course_code and sub.subject_code='"&Request("selected_subject_code") & "' and sch.schedule_code='"&Request("selected_schedule_code") & "' order by sub.subject_code"
'select_subject_code1
'elseif Request("selected_schedule_code") <> "" then
If Request("selected_schedule_code") = "" then
%>
<%
Else
%>
<%
sql="select * from My_subject_master"
Set ObjRsSub=Myconn.Execute(sql)
If ObjRsSub.EOF then
%>
<%
else
%>
<%
While Not ObjRsSub.EOF
If Request("selected_subject_code")=ObjRsSub("subject_code") Then
%>
<%
Else
%>
<%
End If
ObjRsSub.MoveNext
Wend
End If
End If
%>
<%select_module_code%>
" />*
" />* dd/mm/yyyy
" />
: " />
*
" />
: " /> *
" maxlength="2" />*
Cancel and Go Back
Faculty Home
<%
End if ' job %>
<%
if Request("timetable_record_id") <>"" and Request("job")="editForm" then
set objRsEDIT=Myconn.execute("select * from My_timetable_master where timetable_record_id ="& Request("timetable_record_id"))
If ObjRsEdit.EOF Then
response.write("error.")
Else%>
EDIT TIME TABLE DETAILS
">
<%
asp_schedule_code=ObjRSEdit("schedule_code")
%>
<%
sqlSch="select * from My_schedule_master sch,My_course_master c where c.course_code=sch.course_code and sch.study_centre_id=" & session("study_centre_id") & " order by schedule_code"
set ObjRsSch=Myconn.Execute(sqlSch)
If ObjRsSch.EOF then
%>
<%
else
%>
<%
While Not ObjRsSch.EOF
If asp_schedule_code=ObjRsSch("schedule_code") or Request("selected_schedule_code")=ObjRsSch("schedule_code") Then
%>
<%
Else
%>
<%
End If
ObjRsSch.MoveNext
Wend
End If
%>
<%
asp_subject_code=ObjRSEdit("subject_code")
%>
<%
if Request("selected_schedule_code") <> "" and Request("selected_subject_code") <> "" then
'if asp_subject_code <> "" or Request("selected_subject_code") <> "" then
sql="select sub.subject_code from My_subject_master sub,My_course_master co,My_course_category_master cat,My_schedule_master sch where cat.course_category_code=sub.course_category_code and cat.course_category_code=co.course_category_code and co.course_code=sch.course_code and sub.subject_code='"&Request("selected_subject_code") & "' and sch.schedule_code='"&Request("selected_schedule_code") & "' order by sub.subject_code"
'select_subject_code1
'elseif Request("selected_schedule_code") <> "" then
If Request("selected_schedule_code") = "" then
%>
<%
Else
%>
<%
sql="select * from My_subject_master"
End If
Set ObjRsSub=Myconn.Execute(sql)
If ObjRsSub.EOF then
%>
<%
else
%>
<%
While Not ObjRsSub.EOF
If Request("selected_subject_code")=ObjRsSub("subject_code") Then
%>
<%
Else
%>
<%
End If
ObjRsSub.MoveNext
Wend
End If
End If
%>
<%
if Request("selected_subject_code") <> "" then
sql="select module_code,module_name from My_module_master where subject_code='"&Request("selected_subject_code") & "' order by module_name"
Set ObjRsMod=Myconn.Execute(sql)
If ObjRsMod.EOF then
%>
<%
else
%>
<%
While Not ObjRsMod.EOF
If ObjRsEdit("module_code")=ObjRsMod("module_code") Then
%>
<%
Else
%>
<%
End If
ObjRsMod.MoveNext
Wend
End If
End If
%>
" />*
" size="12" />*
<%
formatted_start_time_minute=""
if cint(ObjRsEdit("start_time_minute"))< 10 then
formatted_start_time_minute="0" & ObjRsEdit("start_time_minute")
else
formatted_start_time_minute=ObjRsEdit("start_time_minute")
end if
%>
" />
:
*
<%
formatted_end_time_minute=""
if cint(ObjRsEdit("end_time_minute"))< 10 then
formatted_end_time_minute="0" & ObjRsEdit("end_time_minute")
else
formatted_end_time_minute=ObjRsEdit("end_time_minute")
end if
%>
" />
:
*
" />*
Cancel and Go Back
Faculty Home
<%
end if
end if
%>
<%
if Request("timetable_record_id") <>"" and (Request("job")="deleteview" or Request("job")="view") then
set objRsView=Myconn.execute("select * from My_timetable_master where timetable_record_id="& Request("timetable_record_id"))
If ObjRsView.EOF Then
response.write("error.")
Else%>
<%
if Request("job")="view" Then
%>
Details of the Record
<%
Else
%>
Please Confirm Deletion of the this Record
<%
End If
%>
<%=ObjRsView("schedule_code")%>
<%=ObjRsView("subject_code")%>
<%=ObjRsView("module_code")%>
<%=ObjRsView("room_no")%>
<%=ObjRsView("i_group_no")%>
<%=ddmmyy_from_database(ObjRsView("class_date"))%> :::
<%=ObjRsView("start_time_hour")%>:<%=ObjRsView("start_time_minute")%> To <%=ObjRsView("end_time_hour")%>:<%=ObjRsView("end_time_minute")%>
<%
if request("job")="deleteview" then
%>
Are You Sure You want to Delete this Record?
">Yes! Delete
No!
<%end if
%>
<%
end if
end if%>
<%
sqlRs="select tt.room_no,tt.module_code,tt.timetable_record_id,tt.schedule_code,tt.class_date,tt.start_time_hour,tt.start_time_minute,tt.end_time_hour,tt.end_time_minute,tt.i_group_no,tt.subject_code from My_timetable_master tt,My_study_centre_master sc,My_schedule_master sch where sc.study_centre_id=sch.study_centre_id and tt.schedule_code=sch.schedule_code and sc.study_centre_id=" & session("study_centre_id") & " order by tt.class_date,tt.start_time_hour,tt.i_group_no,tt.subject_code"
'response.redirect("../My_main_module/error.asp?line_number=" & sqlRs)
Set ObjRs=Myconn.Execute(sqlRs)
%>
Sl.No
Date
Schedule
Code
Subject
Topic
Room No/Location
Time
Group No
Edit/Delete
<%
if objRs.eof then
%>
NIL
The List is empty
<%
else
i=1
old_class_date="1-January-2000"
while not objRs.eof
start_time_hour=cint(ObjRs("start_time_hour"))
if start_time_hour < 10 then
start_time_hour="0" & start_time_hour
end if
start_time_minute=cint(ObjRs("start_time_minute"))
if start_time_minute < 10 then
start_time_minute="0" & start_time_minute
end if
end_time_hour=cint(ObjRs("end_time_hour"))
if end_time_hour < 10 then
end_time_hour="0" & end_time_hour
end if
end_time_minute=cint(ObjRs("end_time_minute"))
if end_time_minute < 10 then
end_time_minute="0" & end_time_minute
end if
new_class_date=objRs("class_date")
if old_class_date<>new_class_date then
%>
<%End If
%>
<%=i%>
<%=ddmmyy_from_database(objRs("class_date"))%>
<%=objRs("schedule_code")%>
<%=objRs("subject_code")%>
<%=objRs("module_code")%>
<%=objRs("room_no")%>
<%=start_time_hour%>:<%=start_time_minute%> To <%=objRs("end_time_hour")%>:<%=end_time_minute%>
"><%=objRs("i_group_no")%>
">Delete
&selected_subject_code=<%=ObjRs("subject_code")%>&timetable_record_id=<%=objRs("timetable_record_id")%>">Edit
<%
i=i+1
objRs.movenext
old_class_date=new_class_date
Wend
end if
%>
At the same time, a 3 level selection of course.> schedule > topic is also there.
because of this it is somewhat complicated.
<%
THIS_FILE_NAME="WRITE THIS FILE NAME"
%>
<%
PRIMARY_KEY_NAME="timetable_record_id"
PRIMARY_KEY_VALUE=REQUEST("timetable_record_id")
DEFAULT_ORDER_BY_FIELDS="subject_code"
THIS_FILE_NAME="My_timetable_master_aed.asp"
BACK_FILE_NAME="admin_welcome.asp"
%>
<%
If Request("job")="edit" Then
timetable_record_id_value=Request("timetable_record_id")
set objRsEdit=Server.CreateObject("ADODB.Recordset")
objRsEdit.CursorLocation=3
sql="select * from My_timetable_master where timetable_record_id=" & timetable_record_id_value
objRsEdit.open sql,myconn,1,3
If ObjRsEdit.EOf then
response.write("Error...")
Else
ObjRsEdit("schedule_code")=Request.form("schedule_code")
ObjRsEdit("class_date")=ddmmyy_to_database(Request.form("class_date"))
ObjRsEdit("subject_code")=Request.form("subject_code")
ObjRsEdit("start_time_hour")=Request.form("start_time_hour")
ObjRsEdit("start_time_minute")=Request.form("start_time_minute")
ObjRsEdit("end_time_hour")=Request.form("end_time_hour")
ObjRsEdit("end_time_minute")=Request.form("end_time_minute")
ObjRsEdit("i_group_no")=Request.form("i_group_no")
objRsEdit.update
response.redirect(THIS_FILE_NAME & "?timetable_record_id=" & timetable_record_id_value & "&postback_timetable_record_id=" & timetable_record_id_value & "&msg=Successfully Updated")
end if
end if
%>
<%
function select_module_code
%>
<%
if Request("selected_subject_code") <> "" then
sql="select module_code,module_name from My_module_master where subject_code='"&Request("selected_subject_code") & "' order by module_name"
Set ObjRsMod=Myconn.Execute(sql)
If ObjRsMod.EOF then
%>
<%
else
%>
<%
While Not ObjRsMod.EOF
If asp_module_code=ObjRsMod("module_code") or session("module_code")=ObjRsMod("module_code") Then
%>
<%
Else
%>
<%
End If
ObjRsMod.MoveNext
Wend
End If
End If
%>
<%
end function
%>
<%
sub select_subject_code
%>
<%
if Request("selected_schedule_code") <> "" and Request("selected_subject_code") <> "" then
sql="select sub.subject_code from My_subject_master sub,My_course_master co,My_course_category_master cat,My_schedule_master sch where cat.course_category_code=sub.course_category_code and cat.course_category_code=co.course_category_code and co.course_code=sch.course_code and sub.subject_code='"&Request("selected_subject_code") & "' and sch.schedule_code='"&Request("selected_schedule_code") & "' order by sub.subject_code"
select_subject_code1
elseif Request("selected_schedule_code") <> "" then
sql="select sub.subject_code from My_subject_master sub,My_course_master co,My_course_category_master cat,My_schedule_master sch where cat.course_category_code=sub.course_category_code and cat.course_category_code=co.course_category_code and co.course_code=sch.course_code and sch.schedule_code='"&Request("selected_schedule_code") & "' order by sub.subject_code"
select_subject_code1
else
%>
<%
End If
end sub
%>
<%
sub select_subject_code1
Set ObjRsSub=Myconn.Execute(sql)
If ObjRsSub.EOF then
%>
<%
else
%>
<%
While Not ObjRsSub.EOF
If Request("selected_subject_code")=ObjRsSub("subject_code") Then
%>
<%
Else
%>
<%
End If
ObjRsSub.MoveNext
Wend
End If
end sub
%>
<%
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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
%>
<%
If Request("job")="deleteconfirmed" and Request("timetable_record_id")<> "" Then
timetable_record_id=Request("timetable_record_id")
If timetable_record_id="" Then
Response.redirect("../My_faculty_module/error.asp?line_number=Error.RecId Missing")
End If
set rs1=myconn.execute("select * from My_timetable_master where timetable_record_id=" & timetable_record_id)
If rs1.eof then
response.redirect("../My_faculty_module/error.asp?line_number=Error.Record is empty")
Else
myarray=rs1.getrows()
End if
field_list=""
insert_stt=""
for i=0 to (rs1.fields.count - 1)
field_list=field_list & rs1.fields(i).name
If i < rs1.fields.count -1 then
field_list=field_list & ","
End if
next
%>
<%for i =0 to ubound(myarray,2)%>
<%
insert_stt="insert into My_timetable_master " & "("&field_list&") values ("
j=0
for j =0 to (rs1.fields.count - 1)
dt=""
field_type=cint(rs1.fields(j).type)
if isNULL(myarray(j,i)) then
insert_stt=insert_stt & "NULL"
Elseif field_type=200 Then
insert_stt=insert_stt & "'"
insert_stt=insert_stt & myarray(j,i)
insert_stt=insert_stt & "'"
Elseif field_type=135 Then
dt=myarray(j,i)
if isdate(dt) then
dd=day(myarray(j,i))
mm=monthname(month(myarray(j,i)))
yy=year(myarray(j,i))
dtf=dd & "-" & mm & "-" & yy
insert_stt=insert_stt & "'" & dtf & "'"
else
insert_stt=insert_stt & "'"
insert_stt=insert_stt & myarray(j,i)
insert_stt=insert_stt & "'"
end if
Elseif field_type=5 or field_type=131then
insert_stt=insert_stt & myarray(j,i)
End If
If j < rs1.fields.count -1 then
insert_stt=insert_stt & ","
End if
Next
insert_stt=insert_stt & ");"
%>
<%next %>
<%
set objRsAddNew=Server.CreateObject("ADODB.Recordset")
objRsAddNew.CursorLocation=3
If session("database_type")="ms access" Then
sql="select top 1 * from My_deleted_tt_records "
Else
sql="select * from My_deleted_tt_records where rownum=1"
End If
objRsAddNew.open sql,myconn,1,3
ObjRsAddNew.addNew
objRsAddNew("insert_statement")=insert_stt
ObjRsAddNew.Update
ObjRsAddNew.Close
Set ObjRsAddNew=Nothing
Myconn.Execute("delete from My_timetable_master where timetable_record_id=" & timetable_record_id)
response.redirect(THIS_FILE_NAME & "?msg=Record Deleted SUCCESSFULLY.To restore back the deleted record, you may contact the Admininstrator")
End If
%>
<%
If Request("job")="addNew" Then
Set ObjRsMaxId=Myconn.Execute("select max(timetable_record_id) AS last_timetable_record_id from My_timetable_master")
last_timetable_record_id=ObjRsMaxID("last_timetable_record_id")
if isnull(last_timetable_record_id) then
new_timetable_record_id=1
Else
new_timetable_record_id=cint(last_timetable_record_id) +1
End If
chkDup="Select * from My_timetable_master where "
chkDup=chkDup & " schedule_code='"&Request("schedule_code") &"' "
chkDup=chkDup & " and subject_code='"&Request("subject_code") &"' "
chkDup=chkDup & " and start_time_hour="&Request("start_time_hour")
chkDup=chkDup & " and i_group_no="&Request("i_group_no")
if session("database_type")="ms access" then
chkDup=chkDup & " and class_date=#"& ddmmyy_to_database(Request("class_date") & "#")
Else
chkDup=chkDup & " and class_date='"& ddmmyy_to_database(Request("class_date") & "'")
End If
' response.redirect("../My_main_module/error.asp?line_number=" & chkDup)
Set ObjRsChk=Myconn.execute(chkDup)
If Not ObjRsChk.EOF then
postback_timeable_record_id=ObjRsChk("timetable_record_id")
response.redirect(THIS_FILE_NAME & "?timetable_record_id=" & timetable_record_id_value & "&postback_timetable_record_id=" & postback_timetable_record_id_value & "&msg=Sorry!Record ALREADY EXISTS")
End If
set objRsAddNew=Server.CreateObject("ADODB.Recordset")
objRsAddNew.CursorLocation=3
If Session("database_type")="ms access" then
sql="select top 1 * from My_timetable_master"
Else
sql="select * from My_timetable_master where rownum=1"
End If
objRsAddNew.open sql,myconn,1,3
ObjRsAddNew.addNew
ObjRsAddNew("timetable_record_id")=new_timetable_record_id
ObjRsAddNew("schedule_code")=Request.form("schedule_code")
ObjRsAddNew("i_group_no")=Request.form("i_group_no")
ObjRsAddNew("subject_code")=Request.form("subject_code")
ObjRsAddNew("module_code")=Request.form("module_code")
ObjRsAddNew("room_no")=Request.form("room_no")
ObjRsAddNew("start_time_hour")=Request.form("start_time_hour")
ObjRsAddNew("start_time_minute")=Request.form("start_time_minute")
ObjRsAddNew("end_time_hour")=Request.form("end_time_hour")
ObjRsAddNew("end_time_minute")=Request.form("end_time_minute")
ObjRsAddNew("start_time_minute")=Request.form("start_time_minute")
ObjRsAddNew("class_date")=ddmmyy_to_database(Request.form("class_date"))
ObjRsAddNew.update
session("tt_subject_code")=Request.form("subject_code")
session("tt_schedule_code")=Request.form("schedule_code")
session("tt_start_time_hour")=Request.form("start_time_hour")
session("tt_start_time_minute")=Request.form("start_time_minute")
session("tt_end_time_hour")=Request.form("end_time_hour")
session("tt_end_time_minute")=Request.form("end_time_minute")
session("tt_class_date")=Request.form("class_date")
session("tt_module_code")=Request.form("module_code")
session("tt_room_no")=Request.form("room_no")
session("tt_i_group_no")=Request.form("i_group_no")
response.redirect(THIS_FILE_NAME & "?postback_timetable_record_id=" & new_timetable_record_id & "&msg=Successfully Added")
End If
%>
<%If Request("job") <> "AddNewForm" Then %>
Add a New Time Table Record
<%
End If %>
<%
if Request("job")="AddNewForm" Then
Set ObjRsCentre=Myconn.execute("select * from My_study_centre_master where study_centre_id=" & session("study_centre_id"))
%>
Enter The New TimeTable Record
" />
<%
sqlSch="select * from My_schedule_master sch,My_course_master c where c.course_code=sch.course_code and sch.study_centre_id=" & session("study_centre_id") & " order by schedule_code"
set ObjRsSch=Myconn.Execute(sqlSch)
If ObjRsSch.EOF then
%>
<%
else
%>
<%
While Not ObjRsSch.EOF
If asp_schedule_code=ObjRsSch("schedule_code") or Request("selected_schedule_code")=ObjRsSch("schedule_code") Then
%>
<%
Else
%>
<%
End If
ObjRsSch.MoveNext
Wend
End If
%>
<%
'if Request("selected_schedule_code") <> "" and Request("selected_subject_code") <> "" then
'if asp_subject_code <> "" or Request("selected_subject_code") <> "" then
'sql="select sub.subject_code from My_subject_master sub,My_course_master co,My_course_category_master cat,My_schedule_master sch where cat.course_category_code=sub.course_category_code and cat.course_category_code=co.course_category_code and co.course_code=sch.course_code and sub.subject_code='"&Request("selected_subject_code") & "' and sch.schedule_code='"&Request("selected_schedule_code") & "' order by sub.subject_code"
'select_subject_code1
'elseif Request("selected_schedule_code") <> "" then
If Request("selected_schedule_code") = "" then
%>
<%
Else
%>
<%
sql="select * from My_subject_master"
Set ObjRsSub=Myconn.Execute(sql)
If ObjRsSub.EOF then
%>
<%
else
%>
<%
While Not ObjRsSub.EOF
If Request("selected_subject_code")=ObjRsSub("subject_code") Then
%>
<%
Else
%>
<%
End If
ObjRsSub.MoveNext
Wend
End If
End If
%>
<%select_module_code%>
" />*
" />* dd/mm/yyyy
" />
: " />
*
" />
: " /> *
" maxlength="2" />*
Cancel and Go Back
Faculty Home
<%
End if ' job %>
<%
if Request("timetable_record_id") <>"" and Request("job")="editForm" then
set objRsEDIT=Myconn.execute("select * from My_timetable_master where timetable_record_id ="& Request("timetable_record_id"))
If ObjRsEdit.EOF Then
response.write("error.")
Else%>
EDIT TIME TABLE DETAILS
">
<%
asp_schedule_code=ObjRSEdit("schedule_code")
%>
<%
sqlSch="select * from My_schedule_master sch,My_course_master c where c.course_code=sch.course_code and sch.study_centre_id=" & session("study_centre_id") & " order by schedule_code"
set ObjRsSch=Myconn.Execute(sqlSch)
If ObjRsSch.EOF then
%>
<%
else
%>
<%
While Not ObjRsSch.EOF
If asp_schedule_code=ObjRsSch("schedule_code") or Request("selected_schedule_code")=ObjRsSch("schedule_code") Then
%>
<%
Else
%>
<%
End If
ObjRsSch.MoveNext
Wend
End If
%>
<%
asp_subject_code=ObjRSEdit("subject_code")
%>
<%
if Request("selected_schedule_code") <> "" and Request("selected_subject_code") <> "" then
'if asp_subject_code <> "" or Request("selected_subject_code") <> "" then
sql="select sub.subject_code from My_subject_master sub,My_course_master co,My_course_category_master cat,My_schedule_master sch where cat.course_category_code=sub.course_category_code and cat.course_category_code=co.course_category_code and co.course_code=sch.course_code and sub.subject_code='"&Request("selected_subject_code") & "' and sch.schedule_code='"&Request("selected_schedule_code") & "' order by sub.subject_code"
'select_subject_code1
'elseif Request("selected_schedule_code") <> "" then
If Request("selected_schedule_code") = "" then
%>
<%
Else
%>
<%
sql="select * from My_subject_master"
End If
Set ObjRsSub=Myconn.Execute(sql)
If ObjRsSub.EOF then
%>
<%
else
%>
<%
While Not ObjRsSub.EOF
If Request("selected_subject_code")=ObjRsSub("subject_code") Then
%>
<%
Else
%>
<%
End If
ObjRsSub.MoveNext
Wend
End If
End If
%>
<%
if Request("selected_subject_code") <> "" then
sql="select module_code,module_name from My_module_master where subject_code='"&Request("selected_subject_code") & "' order by module_name"
Set ObjRsMod=Myconn.Execute(sql)
If ObjRsMod.EOF then
%>
<%
else
%>
<%
While Not ObjRsMod.EOF
If ObjRsEdit("module_code")=ObjRsMod("module_code") Then
%>
<%
Else
%>
<%
End If
ObjRsMod.MoveNext
Wend
End If
End If
%>
" />*
" size="12" />*
<%
formatted_start_time_minute=""
if cint(ObjRsEdit("start_time_minute"))< 10 then
formatted_start_time_minute="0" & ObjRsEdit("start_time_minute")
else
formatted_start_time_minute=ObjRsEdit("start_time_minute")
end if
%>
" />
:
*
<%
formatted_end_time_minute=""
if cint(ObjRsEdit("end_time_minute"))< 10 then
formatted_end_time_minute="0" & ObjRsEdit("end_time_minute")
else
formatted_end_time_minute=ObjRsEdit("end_time_minute")
end if
%>
" />
:
*
" />*
Cancel and Go Back
Faculty Home
<%
end if
end if
%>
<%
if Request("timetable_record_id") <>"" and (Request("job")="deleteview" or Request("job")="view") then
set objRsView=Myconn.execute("select * from My_timetable_master where timetable_record_id="& Request("timetable_record_id"))
If ObjRsView.EOF Then
response.write("error.")
Else%>
<%
if Request("job")="view" Then
%>
Details of the Record
<%
Else
%>
Please Confirm Deletion of the this Record
<%
End If
%>
<%=ObjRsView("schedule_code")%>
<%=ObjRsView("subject_code")%>
<%=ObjRsView("module_code")%>
<%=ObjRsView("room_no")%>
<%=ObjRsView("i_group_no")%>
<%=ddmmyy_from_database(ObjRsView("class_date"))%> :::
<%=ObjRsView("start_time_hour")%>:<%=ObjRsView("start_time_minute")%> To <%=ObjRsView("end_time_hour")%>:<%=ObjRsView("end_time_minute")%>
<%
if request("job")="deleteview" then
%>
Are You Sure You want to Delete this Record?
">Yes! Delete
No!
<%end if
%>
<%
end if
end if%>
<%
sqlRs="select tt.room_no,tt.module_code,tt.timetable_record_id,tt.schedule_code,tt.class_date,tt.start_time_hour,tt.start_time_minute,tt.end_time_hour,tt.end_time_minute,tt.i_group_no,tt.subject_code from My_timetable_master tt,My_study_centre_master sc,My_schedule_master sch where sc.study_centre_id=sch.study_centre_id and tt.schedule_code=sch.schedule_code and sc.study_centre_id=" & session("study_centre_id") & " order by tt.class_date,tt.start_time_hour,tt.i_group_no,tt.subject_code"
'response.redirect("../My_main_module/error.asp?line_number=" & sqlRs)
Set ObjRs=Myconn.Execute(sqlRs)
%>
Sl.No
Date
Schedule
Code
Subject
Topic
Room No/Location
Time
Group No
Edit/Delete
<%
if objRs.eof then
%>
NIL
The List is empty
<%
else
i=1
old_class_date="1-January-2000"
while not objRs.eof
start_time_hour=cint(ObjRs("start_time_hour"))
if start_time_hour < 10 then
start_time_hour="0" & start_time_hour
end if
start_time_minute=cint(ObjRs("start_time_minute"))
if start_time_minute < 10 then
start_time_minute="0" & start_time_minute
end if
end_time_hour=cint(ObjRs("end_time_hour"))
if end_time_hour < 10 then
end_time_hour="0" & end_time_hour
end if
end_time_minute=cint(ObjRs("end_time_minute"))
if end_time_minute < 10 then
end_time_minute="0" & end_time_minute
end if
new_class_date=objRs("class_date")
if old_class_date<>new_class_date then
%>
<%End If
%>
<%=i%>
<%=ddmmyy_from_database(objRs("class_date"))%>
<%=objRs("schedule_code")%>
<%=objRs("subject_code")%>
<%=objRs("module_code")%>
<%=objRs("room_no")%>
<%=start_time_hour%>:<%=start_time_minute%> To <%=objRs("end_time_hour")%>:<%=end_time_minute%>
"><%=objRs("i_group_no")%>
">Delete
&selected_subject_code=<%=ObjRs("subject_code")%>&timetable_record_id=<%=objRs("timetable_record_id")%>">Edit
<%
i=i+1
objRs.movenext
old_class_date=new_class_date
Wend
end if
%>
Rearrange Records in Timetable Design
This program will display the records in the timetable format as shown here:
class asp code:
</p>
<p>
Timetable</p>
<p>
<%
Function min_class_date(timetable)
min_class_date=cdate("1-january-2015")
for i=0 to ubound(timetable,2)-1
'response.Write("
curr date inside array=" & cdate(timetable(1,i)))
curr_class_date=cdate(timetable(1,i))
if cdate(curr_class_date) < cdate(min_class_date) then
min_class_date=cdate(curr_class_date)
end if
next
End Function
%>
<%
Function max_class_date(timetable)
max_class_date=cdate("2000-January-22")
for i=0 to ubound(timetable,2)-1
curr_class_date=timetable(1,i)
if cdate(curr_class_date) > cdate(max_class_date) then
max_class_date=curr_class_date
end if
next
End Function
%>
<%
Function is_class_date(timetable,curdate)
is_class_date="no"
for k=0 to ubound(timetable,2)-1
'response.Write("
cdate input:" & cdate(timetable(1,k)))
'response.Write("
cur:" & cdate(curdate))
if cdate(timetable(1,k)) = cdate(curdate) then
is_class_date="yes"
response.Write(timetable(3,k)& "
")
response.Write("@" & timetable(2,k) & "
")
end if
next
End Function
%>
<%
If Request("course_code")="" Then
Response.Redirect("../YOUR FOLDER NAME HERE/YOUR ASP FILE NAME HERE.asp?msg=Records Not available
or Not authorised for your View")
ElseIf Request("study_centre_id") ="" Then
Response.Redirect("../YOUR FOLDER NAME HERE/YOUR ASP FILE NAME HERE.asp?msg=Study Centre NOT selected or
Records Not authorised for your View")
End If
sql="YOUR SQL STATEMENT FOR THE SCHEDULE HERE"
Set ObjRsSch=Myconn.Execute(sqlSch)
If ObjRsSch.EOF then
Response.Redirect("../YOUR FOLDER NAME HERE/YOUR ASP FILE NAME HERE.asp?msg=Error. Schedule is missing.
Inform administrator")
Else
schedule_code=ObjRsSch("schedule_code")
study_centre_display_name=ObjRsSch("city")
course_display_name=ObjRsSch("course_display_name")
course_session=ObjRsSch("course_session")
student_group_no=Request("i_group_no")
sqlTT="YOUR TIMETABLE RECORDS SQL STATEMENT HERE"
Set ObjRsTT=Myconn.Execute(sqlTT)
If ObjRsTT.EOf then
Response.Redirect(BACK_FILE_NAME &"?msg=Error. Timetable is not ready. Inform administrator")
End If
End If
'start_date=objRsSch("default_start_date")
'end_date=objRsSch("default_end_date")
schedule_code=objRsSch("schedule_code")
%>
<%
y=0
dim timetable()
redim timetable(3,y)
while Not ObjRsTT.EOF
timetable(0,y)=ObjRsTT("schedule_code")
' old line timetable(1,y)=yyyymmmdd_from_database(ObjRsTT("class_date"))
timetable(1,y)=ddmmyy_from_database(ObjRsTT("class_date"))
timetable(3,y)=ObjRsTT("module_code")
start_time=cint(ObjRsTT("start_time_hour"))* 100 + cint(ObjRsTT("start_time_minute"))
if start_time < 1000 then
start_time="0" & cstr(start_time)
end if
end_time=cint(ObjRsTT("end_time_hour"))* 100 + cint(ObjRsTT("end_time_minute"))
if end_time < 1000 then
end_time="0" & cstr(end_time)
end if
timetable(2,y)=start_time & "-" & end_time
redim preserve timetable(3,y+1)
'response.write("
...." & timetable(0,y) & ".." & timetable(1,y))
ObjRsTT.Movenext
y=y+1
Wend
%>
<%
start_date=cdate(min_class_date(timetable))
end_date=cdate(max_class_date(timetable))
%>
Timetable for
Batch No. <%=student_group_no%>
Of <%=course_display_name%> (<%=course_session %> Semester)
Learning Centre <%=study_centre_display_name%>
<%
days=cdate(end_date) - cdate(start_date)+1
wdstart_date=weekday(start_date)
empty_boxes=wdstart_date -1
wdend_date=weekday(end_date)
suffix_empty_boxes=7-wdend_date
total_boxes=empty_boxes + days
first_date=start_date - empty_boxes
'response.Redirect("../mY_main_module/error.asp?line_number=" & first_date)
response.write(total_boxes)
%>
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
<%
for i=1 to total_boxes
record_exists_color="#ff0000"
wday=weekday(first_date)
if wday=7 and day(first_date)>=8 and day(first_date)<=14 then
second_saturday="yes"
else
second_saturday="no"
end if
if wday=1 then
disabled="disabled"
else
disabled=""
end if
%>
<%=ddmmyy_from_database(first_date) %>
<%
record_exists_color="#ffcccc"
%>
<%
if i <= empty_boxes or i >total_boxes then
Response.write(" ")
first_date=first_date +1
Elseif second_saturday="yes" then
%>
<%
is_class_date timetable,first_date
first_date=first_date +1
'========================
Response.write("
2nd Sat")
'=====================
%>
<%
Else
%>
<%
is_class_date timetable,first_date
first_date=first_date +1
%>
<%
End if
%>
<%
if i mod 7=0 then
%>
<%
end if
%>
<%
next
%>
<%
Function min_class_date(timetable)
min_class_date=cdate("1-january-2015")
for i=0 to ubound(timetable,2)-1
'response.Write("
curr date inside array=" & cdate(timetable(1,i)))
curr_class_date=cdate(timetable(1,i))
if cdate(curr_class_date) < cdate(min_class_date) then
min_class_date=cdate(curr_class_date)
end if
next
End Function
%>
<%
Function max_class_date(timetable)
max_class_date=cdate("2000-January-22")
for i=0 to ubound(timetable,2)-1
curr_class_date=timetable(1,i)
if cdate(curr_class_date) > cdate(max_class_date) then
max_class_date=curr_class_date
end if
next
End Function
%>
<%
Function is_class_date(timetable,curdate)
is_class_date="no"
for k=0 to ubound(timetable,2)-1
'response.Write("
cdate input:" & cdate(timetable(1,k)))
'response.Write("
cur:" & cdate(curdate))
if cdate(timetable(1,k)) = cdate(curdate) then
is_class_date="yes"
response.Write(timetable(3,k)& "
")
response.Write("@" & timetable(2,k) & "
")
end if
next
End Function
%>
<%
If Request("course_code")="" Then
Response.Redirect("../YOUR FOLDER NAME HERE/YOUR ASP FILE NAME HERE.asp?msg=Records Not available
or Not authorised for your View")
ElseIf Request("study_centre_id") ="" Then
Response.Redirect("../YOUR FOLDER NAME HERE/YOUR ASP FILE NAME HERE.asp?msg=Study Centre NOT selected or
Records Not authorised for your View")
End If
sql="YOUR SQL STATEMENT FOR THE SCHEDULE HERE"
Set ObjRsSch=Myconn.Execute(sqlSch)
If ObjRsSch.EOF then
Response.Redirect("../YOUR FOLDER NAME HERE/YOUR ASP FILE NAME HERE.asp?msg=Error. Schedule is missing.
Inform administrator")
Else
schedule_code=ObjRsSch("schedule_code")
study_centre_display_name=ObjRsSch("city")
course_display_name=ObjRsSch("course_display_name")
course_session=ObjRsSch("course_session")
student_group_no=Request("i_group_no")
sqlTT="YOUR TIMETABLE RECORDS SQL STATEMENT HERE"
Set ObjRsTT=Myconn.Execute(sqlTT)
If ObjRsTT.EOf then
Response.Redirect(BACK_FILE_NAME &"?msg=Error. Timetable is not ready. Inform administrator")
End If
End If
'start_date=objRsSch("default_start_date")
'end_date=objRsSch("default_end_date")
schedule_code=objRsSch("schedule_code")
%>
<%
y=0
dim timetable()
redim timetable(3,y)
while Not ObjRsTT.EOF
timetable(0,y)=ObjRsTT("schedule_code")
' old line timetable(1,y)=yyyymmmdd_from_database(ObjRsTT("class_date"))
timetable(1,y)=ddmmyy_from_database(ObjRsTT("class_date"))
timetable(3,y)=ObjRsTT("module_code")
start_time=cint(ObjRsTT("start_time_hour"))* 100 + cint(ObjRsTT("start_time_minute"))
if start_time < 1000 then
start_time="0" & cstr(start_time)
end if
end_time=cint(ObjRsTT("end_time_hour"))* 100 + cint(ObjRsTT("end_time_minute"))
if end_time < 1000 then
end_time="0" & cstr(end_time)
end if
timetable(2,y)=start_time & "-" & end_time
redim preserve timetable(3,y+1)
'response.write("
...." & timetable(0,y) & ".." & timetable(1,y))
ObjRsTT.Movenext
y=y+1
Wend
%>
<%
start_date=cdate(min_class_date(timetable))
end_date=cdate(max_class_date(timetable))
%>
Timetable for
Batch No. <%=student_group_no%>
Of <%=course_display_name%> (<%=course_session %> Semester)
Learning Centre <%=study_centre_display_name%>
<%
days=cdate(end_date) - cdate(start_date)+1
wdstart_date=weekday(start_date)
empty_boxes=wdstart_date -1
wdend_date=weekday(end_date)
suffix_empty_boxes=7-wdend_date
total_boxes=empty_boxes + days
first_date=start_date - empty_boxes
'response.Redirect("../mY_main_module/error.asp?line_number=" & first_date)
response.write(total_boxes)
%>
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
<%
for i=1 to total_boxes
record_exists_color="#ff0000"
wday=weekday(first_date)
if wday=7 and day(first_date)>=8 and day(first_date)<=14 then
second_saturday="yes"
else
second_saturday="no"
end if
if wday=1 then
disabled="disabled"
else
disabled=""
end if
%>
<%=ddmmyy_from_database(first_date) %>
<%
record_exists_color="#ffcccc"
%>
<%
if i <= empty_boxes or i >total_boxes then
Response.write(" ")
first_date=first_date +1
Elseif second_saturday="yes" then
%>
<%
is_class_date timetable,first_date
first_date=first_date +1
'========================
Response.write("
2nd Sat")
'=====================
%>
<%
Else
%>
<%
is_class_date timetable,first_date
first_date=first_date +1
%>
<%
End if
%>
<%
if i mod 7=0 then
%>
<%
end if
%>
<%
next
%>
Create Insert Statements from Oracle Table
THIS PROGRAM WILL TAKE ALL THE RECORDS
FROM AN ORACLE TABLE AND CONSTRUCT
INSERT STATEMENTS.
USE: IF THE TABLE IS LOST, YOU CAN RECONSTRUCT
HOW? JUST COPY THE OUTPUT THIS PROGRAM AND PASTE IT IN SQLPLUS PROMT
<%
result=request("result")
sql1=request("sql1")
if sql1 <> "" then
sqlarray=split(sql1,"$")
table_name=sqlarray(0)
if ubound(sqlarray)=1 then
extention=sqlarray(1)
end if
end if
password=request("password")
password_data="PUT YOUR PASSWORD HERE"
if password=password_data then
permit="yes"
end if
%>
insert statement
<%
response.write(table_name)
%>
<%
if result="DONE" then
%>
SUCCESSFULLY COMPLETED
<%
end if
%>
<%
if result="CHECKPASSWORD" then
%>
CHECK THE PASSWORD
<%
end if
%>
<% if table_name <> "" and permit="yes" then
if extention="" then
sql="select * from " & table_name
else
sql="select * from " & table_name & " " & extention
end if
' response.write(sql)
set rs1=myconn.execute(sql)
if rs1.eof then
response.redirect("sql.asp?result=Sorry.No records found")
end if
myarray=rs1.getrows()
end if
%>
<% if table_name <> "" and permit="yes" then%>
<%
field_list=""
for i=0 to (rs1.fields.count - 1)
field_list=field_list & rs1.fields(i).name
If i < rs1.fields.count -1 then
field_list=field_list & ","
End if
next
%>
<%for i =0 to ubound(myarray,2)%>
<%
Response.write("insert into " & table_name & "("&field_list&") values (")
j=0
for j =0 to (rs1.fields.count - 1)
dt=""
field_type=cint(rs1.fields(j).type)
' Response.write(rs1.fields(j).type & "******")
if isNULL(myarray(j,i)) then
Response.write("NULL")
Elseif field_type=200 Then
Response.write("'")
Response.write(myarray(j,i))
Response.write("'")
Elseif field_type=135 Then
dt=myarray(j,i)
if isdate(dt) then
dd=day(myarray(j,i))
mm=monthname(month(myarray(j,i)))
yy=year(myarray(j,i))
dtf=dd & "-" & mm & "-" & yy
Response.write("'" & dtf & "'")
else
Response.write("'")
Response.write(myarray(j,i))
Response.write("'")
end if
Elseif field_type=5 or field_type=131then
'Response.write("'")
Response.write(myarray(j,i))
'Response.write("'")
End If
If j < rs1.fields.count -1 then
Response.write(",")
End if
Next
Response.write(");" )
' Response.write(")" & """" & ")")
%>
<%next %>
<% end if %>
<%
if Request("is_report") ="" then
%>
Password :
Enter the Select Statement
Separate tablename and the condition by $ sign
<%end if%>
FROM AN ORACLE TABLE AND CONSTRUCT
INSERT STATEMENTS.
USE: IF THE TABLE IS LOST, YOU CAN RECONSTRUCT
HOW? JUST COPY THE OUTPUT THIS PROGRAM AND PASTE IT IN SQLPLUS PROMT
<%
result=request("result")
sql1=request("sql1")
if sql1 <> "" then
sqlarray=split(sql1,"$")
table_name=sqlarray(0)
if ubound(sqlarray)=1 then
extention=sqlarray(1)
end if
end if
password=request("password")
password_data="PUT YOUR PASSWORD HERE"
if password=password_data then
permit="yes"
end if
%>
<%
response.write(table_name)
%>
<%
if result="DONE" then
%>
SUCCESSFULLY COMPLETED
<%
end if
%>
<%
if result="CHECKPASSWORD" then
%>
CHECK THE PASSWORD
<%
end if
%>
<% if table_name <> "" and permit="yes" then
if extention="" then
sql="select * from " & table_name
else
sql="select * from " & table_name & " " & extention
end if
' response.write(sql)
set rs1=myconn.execute(sql)
if rs1.eof then
response.redirect("sql.asp?result=Sorry.No records found")
end if
myarray=rs1.getrows()
end if
%>
<% if table_name <> "" and permit="yes" then%>
<%
field_list=""
for i=0 to (rs1.fields.count - 1)
field_list=field_list & rs1.fields(i).name
If i < rs1.fields.count -1 then
field_list=field_list & ","
End if
next
%>
<%for i =0 to ubound(myarray,2)%>
<%
Response.write("insert into " & table_name & "("&field_list&") values (")
j=0
for j =0 to (rs1.fields.count - 1)
dt=""
field_type=cint(rs1.fields(j).type)
' Response.write(rs1.fields(j).type & "******")
if isNULL(myarray(j,i)) then
Response.write("NULL")
Elseif field_type=200 Then
Response.write("'")
Response.write(myarray(j,i))
Response.write("'")
Elseif field_type=135 Then
dt=myarray(j,i)
if isdate(dt) then
dd=day(myarray(j,i))
mm=monthname(month(myarray(j,i)))
yy=year(myarray(j,i))
dtf=dd & "-" & mm & "-" & yy
Response.write("'" & dtf & "'")
else
Response.write("'")
Response.write(myarray(j,i))
Response.write("'")
end if
Elseif field_type=5 or field_type=131then
'Response.write("'")
Response.write(myarray(j,i))
'Response.write("'")
End If
If j < rs1.fields.count -1 then
Response.write(",")
End if
Next
Response.write(");" )
' Response.write(")" & """" & ")")
%>
<%next %>
<% end if %>
<%
if Request("is_report") ="" then
%>
Enter the Select Statement
Separate tablename and the condition by $ sign
<%end if%>
Subscribe to:
Posts (Atom)