Help with select code?
[nobr]I have a calendar program in jsp. The users can enter description for the dates. These dates are stored in the database. I need to provide colors to those dates,which have a value 'N' in the weekender field. Am able to write the query, but am not able to provide the color. how can i do it? please help..
<%@ page import="java.util.List"%>
<%@page import="java.sql.*, java.util.*, java.text.*" %>
<%@page import="java.sql.Connection, java.sql.DriverManager,java.sql.ResultSet,java.sql.SQLException" %>
<html>
<head>
<title>Print a month page.</title>
<meta name="version">
<script>
var lastId ="";
var curId ="";
function selectDate(f)
{
if(document.forms[0].lastDateId !=null)
{
lastId = document.forms[0].lastDateId.value;
}
if(lastId != document.forms[0].todayId.value)
{
if(document.getElementById(lastId).title =="")
{
document.getElementById(lastId).style.backgroundColor='white';
}
}
if(f.id != document.forms[0].todayId.value)
{
document.getElementById(f.id).style.backgroundColor='blue';
}
document.forms[0].lastDateId.value=f.id;
curId = f.id;
document.getElementById("descr").style.visibility='visible';
document.forms[0].descText.value = f.title;
}
function setTitle()
{
document.getElementById(curId).title=document.forms[0].descText.value;
if(document.forms[0].descText.value =="" && curId != document.forms[0].todayId.value)
{
document.getElementById(curId).style.backgroundColor='white';
}
else
{
if(document.forms[0].list.value =="" )
{
document.forms[0].list.value = curId+":"+document.getElementById(curId).title;
}
else
{
list = replaceList(document.forms[0].list.value,curId);
list = list+","+curId+":"+document.getElementById(curId).title;
document.forms[0].list.value = list;
}
}
document.getElementById("descr").style.visibility='hidden';
}
function replaceList(list,id)
{
arr = list.split(",");
newlist ="";
for(i in arr)
{
if(arr[i].match(id) ==null)
{
if(newlist =="")
{
newlist = arr[i];
}
else
{
newlist +=","+arr[i]+",";
}
}
}
return newlist
}
function showCalendar()
{
location.href="CalendarPage.jsp?year="+document.forms[0].year.value;
}
</script>
</head>
<body bgcolor="#c6d9e4">
<%
Connection con =null;
response.setContentType("text/html");
try
{
String driverName ="com.mysql.jdbc.Driver";
Class.forName(driverName);
String serverName ="192.168.10.5";
String mydatabase ="Trainees";
String url ="jdbc:mysql://" + serverName +"/" + mydatabase;
String username ="venkat";
String password ="venkat";
con = DriverManager.getConnection(url, username, password);
Statement stmt =null;
ResultSet rset =null;
PreparedStatement PREPstmt1;
stmt = con.createStatement();
String year = request.getParameter("year");
out.println("year value is : "+year);
String date="",descr="";
String list = request.getParameter("list");
if(list !=null)
{
String dateDescrList[] = list.split(",");
for(int i=0;i<dateDescrList.length;i++)
{
String listObj = dateDescrList[i];
if(!listObj.equals("") || !listObj.trim().equals(""))
{
date= year+"-"+listObj.substring(0,listObj.indexOf(":"));
descr = listObj.substring(listObj.indexOf(":")+1);
out.println("date is : "+date);
out.println("description is : "+descr);
}
String query ="insert into Holiday(HolidayDate, Description, Weekender) VALUES (?, ?, 'N')";
PREPstmt1=con.prepareStatement(query);
PREPstmt1.setString(1,date);
PREPstmt1.setString(2,descr);
PREPstmt1.executeUpdate();
}
}
}
catch(Exception e)
{
System.err.println("Exception: " + e.getMessage());
}
finally
{
try
{
if(con !=null)
con.close();
}
catch(SQLException e)
{
}
}
%>
<%
Calendar c = Calendar.getInstance( );
boolean yyok =false;
int yy = 0, mm = 0;
String yyString = String.valueOf(c.get(Calendar.YEAR));//setting calendar with current year
String STyear = request.getParameter("year");//to get selected year
if(STyear !=null)//If an year is selected, then set that year. Else Current Year
{
yyString=STyear;
}
if (yyString !=null && yyString.length() > 0)
{
try
{
yy = Integer.parseInt(yyString);
yyok =true;
}
catch (NumberFormatException e)
{
out.println("Year " + yyString +" invalid");
}
}
if (!yyok)yy = c.get(Calendar.YEAR);
mm = c.get(Calendar.MONTH);
String todayId ="";
%>
<form method=get action="CalendarPage.jsp">
Enter Year : <select name="year">
<%
for(int i=2000;i<=2015;i++)
{
if(i==Integer.parseInt(yyString))
{
%>
<OPTION SELECTED= <%=i%> > <%=i%> </option>
<%
}
else
{
%>
<OPTION VALUE= <%=i%> > <%=i%> </option>
%>
<%
}
}
%>
</select>
<input type="button" value="Display" onClick="showCalendar()">
<br><br>
<div id="descr" style="visibility:hidden">Enter Description : <input type="text" name="descText" value=""> </div>
<br>
<%
String driverName ="com.mysql.jdbc.Driver";
Class.forName(driverName);
String serverName ="192.168.10.5";
String mydatabase ="Trainees";
String url ="jdbc:mysql://" + serverName +"/" + mydatabase;
String username ="venkat";
String password ="venkat";
con = DriverManager.getConnection(url, username, password);
Statement stmt =null;
ResultSet rset =null;
PreparedStatement PREPstmt1;
stmt = con.createStatement();
rset=stmt.executeQuery("select count(*) from Holiday where YEAR(HolidayDate)='"+yyString+"'");
int cnt=0;
boolean ALflag=false;
while(rset.next())
{
cnt= rset.getInt(1);
out.println("count is : "+cnt);
if(cnt>1)
ALflag=true;
}
%>
<%
rset=stmt.executeQuery("select count(*) from Holiday where Weekender='N'");
int cnte=0;
boolean SELflag=false;
while(rset.next())
{
cnte = rset.getInt(1);
out.println("count is : "+cnte);
if(cnte>1)
SELflag=true;
}
%>
<%!
String[] months ={
"January","February","March",
"April","May","June",
"July","August","September",
"October","November","December"
};
int dom[] ={
31, 28, 31, 30,
31, 30, 31, 31,
30, 31, 30, 31
};
%>
<%
int leadGap = 0;
%>
<table border="0" width="100%" align="Left">
<%
GregorianCalendar calendar =null;
for(int j=0;j<12;j++)
{
calendar =new GregorianCalendar(yy, j, 1);
if(j==0 || j%3==0)
{
%>
<tr>
<%
}
%>
<td halign="top" >
<table border="1" width="33%" align="Left"><tr align="right">
<th colspan=7>
<%= months[j] %>
<%= yy %>
</th>
</tr>
<tr>
<td>Sun<td>Mon<td>Tue<td>Wed<td>Thu<td>Fri<td>Sat
</tr>
<%
leadGap = calendar.get(Calendar.DAY_OF_WEEK)-1;
int daysInMonth = dom[j];
if (calendar.isLeapYear(calendar.get(Calendar.YEAR)) && j == 1)
++daysInMonth;
out.println("<tr>");
out.println(" ");
for (int i = 0; i < leadGap; i++)
{
out.print("<td> </td>");
}
for (int iday = 1; iday <= daysInMonth; iday++)
{
out.println("<td>");
int dayOfWeek = (leadGap + iday) % 7;
GregorianCalendar today =new GregorianCalendar();
if(today.get(Calendar.DATE) == iday && today.get(Calendar.MONTH) == j)
{
todayId = iday+"-"+(j+1);
%>
<div id="<%=((j+1)+"-"+iday)%>" onClick="selectDate(this)" style="background-color:#cccc00" title="" ><a href="#" style="text-decoration:none;color:808080"><%=iday %> </a></div>
<%
}
else
{
if(dayOfWeek <= 1)
{
String str = yyString+"-"+(j+1)+"-"+ iday;
if((dayOfWeek == 0) && (ALflag==false))
{
String query ="insert into Holiday VALUES ('" + str +"','Saturday','Y')";
stmt=con.createStatement();
stmt.executeUpdate(query);
}
elseif ((dayOfWeek == 1) && (ALflag==false))
{
String query ="insert into Holiday VALUES ('" + str +"','Sunday','Y')";
stmt=con.createStatement();
stmt.executeUpdate(query);
}
%>
<div id="<%=((j+1)+"-"+iday)%>" onClick="selectDate(this)" style="background-color:#cccccc" title=""> <a href="#" style="text-decoration:none;color:808080"><%=iday %> </a></div>
<%
}
else
{
%>
<div id="<%=((j+1)+"-"+iday)%>" onClick="selectDate(this)" style="background-color:white" title="" ><a href="#" style="text-decoration:none;color:808080"><%=iday %> </a></div>
<%
}
}
out.println("</td>");
if(calendar.getActualMaximum(Calendar.DAY_OF_MONTH) == iday)
{
out.println("</td></tr></table>");
}
elseif ((leadGap + iday) % 7 == 0)
{
out.println("</tr>");
out.println("<tr>");
}
}
%>
</td>
<%
if((j+1)%3==0)
{
%>
</tr>
<%
}
}
%>
<input type="hidden" name="lastDateId" value="<%=todayId%>">
<input type="hidden" name="todayId" value="<%=todayId%>">
<input type="hidden" name="list" value="">
<input type="submit" value="Submit" onClick="setTitle()">
</form>
</body>
</table>
</html>
This is the extract of the above code. This is how i have provided the query. In which part of my above code should i provide the coloring aspect. If the (SELflag==false), then i want to provide the color. where should i give? please help
<%
rset=stmt.executeQuery("select count(*) from Holiday where Weekender='N'");
int cnte=0;
boolean SELflag=false;
while(rset.next())
{
cnte = rset.getInt(1);
out.println("count is : "+cnte);
if(cnte>1)
SELflag=true;
}
%>
[/nobr]
