Go4Expert

Go4Expert (http://www.go4expert.com/)
-   JSP (http://www.go4expert.com/forumdisplay.php?f=28)
-   -   selection of records from table n applying a query on them (http://www.go4expert.com/showthread.php?t=21032)

silversurface 18Feb2010 12:51
selection of records from table n applying a query on them
 
Hi everyone I have a jsp code for attendance with access database.

Basically the code is supposed to work like this:

1. The teacher first enters information about the date ,semester , subject and time and submits.
2. Then depending on semester..the SELECT query is used to obtain the list of all students from a STUDENT table for that semester. So now a form is filled with the relevant information and the teacher has to only enter the attendance for each student. Once she s done she submits all this.
3. And the INSERT query is executed for table ATTENDANCE with the above info and the work is done

Problem:

Since there is a selective display of records from the STUDENT table using WHILE loop..the records are getting displayed. But only the first record is getting inserted after adding the attendance..The rest are simply ignored. Maybe its because the form fields have the same names but have diff records for each loop which is not being accounted for. Maybe there is a need of arrays or counter somewhere..I don't know..Please suggest something..i have a project report coming up next week..i ll mail the code now..

silversurface 18Feb2010 12:53
Re: selection of records from table n applying a query on them
 
THIS IS MY ORIGINAL CODE

T-ATTENDANCE.JSP [This is where the teacher inputs the date, subject, her name, and the time of lecture, semester]

PHP Code:
<body>
<h2>Enter the <strong>Attendance </strong>of a class </h2><br /><br /><br />
<fieldset>
<legend> Please enter following details </legend>
<form name="timeform" action="uploadattend.jsp" method="post" Onsubmit="return validate_form(this)">

<table border=0>
<tr><th>Enter Subject: </th><td><input type="text" name="sub1" /></td></tr> <br />
<tr><th>Enter Semester: </th><td><input type="text" name="sem" /></td></tr> <br />
<tr><th>Enter Name: </th><td><input type="text" name="tfname" /></td></tr> <br />
<tr><th>Enter Date: </th><td><input type="text" name="txtDate" />(mm/dd/yyyy)</td></tr> <br />
<tr><th>Enter Time: </th><td><input type="text" name="time" /> </td></tr> <br />
<th >Lecture Type: </th> <td> <select Name="ltype"> <option value=" " selected="selected">- Select Type -</option>
<option value="theory">Theory</option>
<option value="practical">Practical</option>
</select> </td>

<tr><td><input type="submit" value="Upload assignment" ></td>
<td><input type="reset" value="Clear Details" ></td></tr>
</table></form>
</fieldset>
</body>







UPLOADATTEND.JSP [THIS IS WHERE THE SEMESTER ENTERED IS MATCHED WITH SEM IN STUDENTS TABLE TO GET THE LIST OF STUDENTS FROM THAT SEM AND TEACHER ENTERS THE STATUS FOR EACH STUDENT IN THE FORM]

<body>
<%

int j=0;




ResultSet rs1=null;

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

Connection conn1=DriverManager.getConnection("jdbcdbc:new2");
Statement stmt1 = conn1.createStatement();
out.println ("Database Connected.");
String query1= new String();


String database1="C:\\student1.mdb";

String sem=request.getParameter("sem");
String txtDate=request.getParameter("txtDate");
String sub1=request.getParameter("sub1");
String ltype=request.getParameter("ltype");
String time=request.getParameter("time");
String tfname=request.getParameter("tfname");


rs1=stmt1.executeQuery("SELECT * FROM Student where Sem='"+sem+"' ");%>



<form name="input" method="get" action= "insertattnd.jsp" Onsubmit="return validate_form(this)" >
<table border=1 cellpadding=" 5" width="100" margin="25">
<tr><th> Roll_No </th> <th>Sem </th> <th> Class </th> <th> Date </th> <th> Time </th> <th> Subject </th> <th> Lecture</th> <th> Attendance </th> <th> Teacher </th></tr>
<%while (rs1.next())
{

out.println("sem is" +sem);

j=j+1;


out.println("value of "+j);

%>


<tr><td ><input type="text" name="rollno" value=<%=rs1.getString("Roll_no")%>></td>
<td ><input type="text" name="sem" value=<%=rs1.getString("Sem")%> ></td>
<td > <input type="text" name="class" value=<%=rs1.getString("Class")%>> </td>
<td><input type="text" name="txtDate" value=<%out.println(txtDate);%>/></td>
<td ><input type="text" name="time" value=<%out.println(time);%> /></td>
<td> <input type="text" name="sub1" value=<%out.println(sub1);%> /></td>
<td> <input type="text" name="ltype" value=<%out.println(ltype);%> /></td>
<td> <input type="text" name="stat" value="" /> </td>
<td> <input type="text" name="tfname" value=<%out.println(tfname);%> /> </td></tr>
<% }%>
<tr> <td> <input type="Submit" name="submit" value="Submit"/></td></tr></table></form>

<%
rs1.close();
conn1.close();
}
catch( SQLException e)
{
out.println("Exception Occured "+e);
}
%>
</body>



INSERTATTEND.JSP [THE PREV FORM INFO IS COPIED HERE AND THE WHOLE INFO IS INSERTED IN THE ATTENDANCE TABLE]


<body>

<%

ResultSet rs=null;


try
{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

Connection conn=DriverManager.getConnection("jdbcdbc:new2");

Statement stmt = conn.createStatement();


String query= new String();

String database="C:\\Database1.mdb";




String rollno=request.getParameter("rollno");
String sub1 =request.getParameter( "sub1" );
String txtDate=request.getParameter( "txtDate" );
String time=request.getParameter( "time" );
String class1=request.getParameter( "class" );

String ltype=request.getParameter( "ltype" );
String stat=request.getParameter( "stat" );
String sem=request.getParameter( "sem" );

String tfname=request.getParameter("tfname");


rs=stmt.executeQuery("INSERT INTO Attendance VALUES('"+rollno+"','"+sub1+"','"+txtDate+"','"+sem+"','"+tfname+"','"+class1+"','"+time+"','"+ltype +"','"+stat+"')");



rs.close();

conn.close();

}
catch (SQLException e) {
out.println("Error occurred " + e);
}


int j=0;

ResultSet rs1=null;

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

Connection conn1=DriverManager.getConnection("jdbcdbc:new2");
Statement stmt = conn1.createStatement();
out.println ("Database Connected.");
String query= new String();


String database="C:\\student1.mdb";

String sem=request.getParameter("sem");
String txtDate=request.getParameter("txtDate");
String sub1=request.getParameter("sub1");
String ltype=request.getParameter("ltype");
String time=request.getParameter("time");
String class1=request.getParameter("class");
String rollno=request.getParameter("rollno");

String stat=request.getParameter("stat");
String tfname=request.getParameter("tfname");
rs1=stmt.executeQuery("SELECT * FROM Student where Sem='"+sem+"' ");



%>

<form name="input" method="get" action= "insertattnd.jsp" Onsubmit="return validate_form(this)" >
<table border=1 cellpadding=" 5" width="100" margin="25">
<tr><th> Roll_No </th> <th>Sem </th> <th> Class </th> <th> Date </th> <th> Time </th> <th> Subject </th> <th> Lecture</th> <th> Attendance </th> <th> Teacher </th></tr>

<%while (rs1.next())
{

out.println("sem is" +sem);

j=j+1;
out.println("value of "+j);

%>
<tr><td ><input type="text" name="rollno" value=<%out.println(rollno);%>></td>
<td ><input type="text" name="sem" value=<%out.println(sem);%> ></td>
<td > <input type="text" name="class" value=<%out.println(class1);%>> </td>
<td><input type="text" name="txtDate" value=<%out.println(txtDate);%>/></td>
<td ><input type="text" name="time" value=<%out.println(time);%> /> </td>
<td> <input type="text" name="sub1" value=<%out.println(sub1);%> /></td>
<td> <input type="text" name="ltype" value=<%out.println(ltype);%> /></td>
<td> <input type="text" name="stat" value=<%out.println(stat);%> /> </td>
<td> <input type="text" name="tfname" value=<%out.println(tfname);%> /></td></tr>
<% }%>
<tr> <td> <input type="Submit" name="submit" value="Submit"/></td></tr></table></form>

%>

<%
rs1.close();
conn1.close();
}
catch( SQLException e)
{
out.println("Exception Occured "+e);
}
%>

</body> 

silversurface 18Feb2010 21:53
This is the reduced version [i hope its clear now]
 
Quote:
INSERTATTEND.JSP [THE PREV FORM INFO IS COPIED HERE AND THE WHOLE INFO IS INSERTED IN THE ATTENDANCE TABLE]
PHP Code:
[CODE]<body>

<%
ResultSet rs=null;
try
{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

Connection conn=DriverManager.getConnection("jdbcdbc:new2");

Statement stmt = conn.createStatement();
String query= new String();
String database="C:\\Database1.mdb";
String rollno=request.getParameter("rollno");
String time=request.getParameter( "time" );
 String stat=request.getParameter( "stat" );
String sem=request.getParameter( "sem" );

rs=stmt.executeQuery("INSERT INTO Attendance VALUES('"+rollno+"','"+sub1+"' ,'"+sem+"',  '"+time+"', '"+stat+"')");
rs.close();
conn.close();
}
catch (SQLException e) {
out.println("Error occurred " + e);
}

int j=0;

ResultSet rs1=null;

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
Connection conn1=DriverManager.getConnection("jdbcdbc:new2");
Statement stmt = conn1.createStatement();
out.println ("Database Connected.");
String query= new String();
String database="C:\\student1.mdb";
String sem=request.getParameter("sem");
String time=request.getParameter("time");
 String rollno=request.getParameter("rollno");
String stat=request.getParameter("stat");
 rs1=stmt.executeQuery("SELECT * FROM Student where Sem='"+sem+"' ");
%>

<form name="input" method="get" action= "insertattnd.jsp" Onsubmit="return validate_form(this)" >
<table border=1 cellpadding=" 5" width="100" margin="25">
<tr><th> Roll_No </th> <th>Sem </th> <th> Class </th> <th> Date </th> <th> Time </th> <th> Subject </th> <th> Lecture</th> <th> Attendance </th> <th> Teacher </th></tr>

<%while (rs1.next())
{
j=j+1;
%>
<tr><td ><input type="text" name="rollno" value=<%out.println(rollno);%>></td>
<td ><input type="text" name="sem" value=<%out.println(sem);%> ></td>
 
<td ><input type="text" name="time" value=<%out.println(time);%> /> </td>
 <td> <input type="text" name="stat" value=<%out.println(stat);%> /> </td>
<% }%>
<tr> <td> <input type="Submit" name="submit" value="Submit"/></td></tr></table></form>
%>
<%
rs1.close();
conn1.close();
}
catch( SQLException e)
{
out.println("Exception Occured "+e);
}
%>
</body>[/CODE] 


All times are GMT +5.5. The time now is 04:20.