Forum:

Unable to insert date into Excel

Rakesh Keerthi

Ranch Hand

Posts: 152

posted 3 years ago

Hi,

I've the below HTML Page.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title></title>
    <style type="text/css">
        .auto-style1 {
            width: 100%;
        }
		div.ui-datepicker{
 font-size:10px;
}
    </style>
        <link rel="stylesheet" href="jquery-ui-1.10.4.custom/development-bundle/themes/base/jquery.ui.all.css">
	<script src="jquery-ui-1.10.4.custom/development-bundle/jquery-1.10.2.js"></script>
	<script src="jquery-ui-1.10.4.custom/development-bundle/ui/jquery.ui.core.js"></script>
	<script src="jquery-ui-1.10.4.custom/development-bundle/ui/jquery.ui.widget.js"></script>
	<script src="jquery-ui-1.10.4.custom/development-bundle/ui/jquery.ui.datepicker.js"></script>
	<link rel="stylesheet" href="../jquery-ui-1.10.4.custom/development-bundle/demos.css">

</head>
<body>
<form method="post" action="ToExcel.jsp">
    <div>
        <table class="auto-style1">
            <tr>
                <td>Comments</td>
                <td><input type="text" name="comments" id="comments"></td>
                <td colspan="2">No. of lines updated in PSIS</td>
                <td colspan="2"><input type="text" name="updatedl" id="updatedl"></td>
            </tr>
            <tr>
                <td>Shipment ID</td>
                <td><input type="text" name="sid" id="sid"></td>
                <td>SAP TXT Description</td>
                <td><input type="text" name="desc" id="desc"></td>
                <td>Phoenix Pub Code</td>
                <td><input type="text" name="pubcode" id="pubcode"></td>
            </tr>
            <tr>
                <td>Product Type</td>
                <td><select name="Type" id="Type">
                  <option value="" disabled selected>Select your option</option>
                  <option value="Looseleaf - Update">Looseleaf - Update</option>
                  <option value="Pamphlet">Pamphlet</option>
                </select></td>
                <td colspan="2">Status of the job</td>
                <td colspan="2"><select name="status" id="status">
                <option value="" disabled selected>Select your option</option>
<option value="Assigned">Assigned</option>
<option value="Completed">Completed</option>
                  <option value="From AE">From AE</option>
                  <option value="Sent to AE">Sent to AE</option>
                </select></td>
            </tr>
            <tr>
                <td>Date Request received</td>
                <td><input type="text" id="DateRec" name="DateRec"></td>
                <td colspan="2">Start Date</td>
                <td colspan="2"><input type="text" id="startD" name="startD"></td>
            </tr>
            <tr>
                <td>Date assigned</td>
                <td><input type="text" id="AssignedD" name="AssignedD"></td>
                <td colspan="2">Date file sent to AE</td>
                <td colspan="2"><input type="text" id="sentToAE" name="sentToAE"></td>
            </tr>
            <tr>
                <td>Date response from AE</td>
                <td><input type="text" id="RespFrmAE" name="RespFrmAE"></td>
                <td colspan="2">Date completed --VWR</td>
                <td colspan="2"><input type="text" id="VWRCmp" name="VWRCmp"></td>
            </tr>
            <tr>
                <td>Assigned to PS</td>
                <td><select name="PS" id="PS">
                <option value="" disabled selected>Select your option</option>
                <option value="Amreen, Fatima (GGO)">Amreen, Fatima (GGO)</option>
                <option value="Attarwala, Nisrin (GGO)">Attarwala, Nisrin (GGO)</option>
                <option value="Balam, Santhoshini (GGO)">Balam, Santhoshini (GGO)</option>
                <option value="Bongu, Shalini (GGO)">Bongu, Shalini (GGO)</option>
                <option value="Gaddam, Sandesh R. (GGO)">Gaddam, Sandesh R. (GGO)</option>
                <option value="Grace, Shalom (GGO)">Grace, Shalom (GGO)</option>
                <option value="Johney, Liya M. (GGO)">Johney, Liya M. (GGO)</option>
                <option value="N, Steven (GGO)">N, Steven (GGO)</option>
                <option value="Varghese, Shebin D. (GGO)">Varghese, Shebin D. (GGO)</option>
              </select></td>
                <td colspan="2">Time Estimate</td>
                <td colspan="2"><input type="text" name="TEst" id="TEst"></td>
            </tr>
            <tr>
                <td>Units</td>
                <td><input type="text" name="Units" id="Units"></td>
                <td colspan="2">Date file needs to be sent to Vendor (VWR)</td>
                <td colspan="2"><input type="text" name="VWR" id="VWR"></td>
            </tr>
            <tr>
                <td>Internal Errors</td>
                <td><input type="text" name="IE" id="IE"></td>
                <td>Review Date</td>
                <td><input type="text" id="RevDate" name="RevDate"></td>
                <td>Review Sent (Yes/No)</td>
                <td><select name="ReviewS" id="ReviewS">
                  <option value="" disabled selected>Select your option</option>
                  <option value="Yes">Yes</option>
                  <option value="No">No</option>
                </select></td>
            </tr>
            <tr>
            <td><input type="submit" value="Submit"></td>
            </tr>
        </table>
    </div>
<script type="text/javascript">

$(function() {
	$( "#DateRec" ).datepicker({ dateFormat: "d-M" }).val(); 
});
$(function() {
	$( "#AssignedD" ).datepicker({ dateFormat: "d-M" }).val(); 
});
$(function() {
	$( "#RespFrmAE" ).datepicker({ dateFormat: "d-M" }).val(); 
});
$(function() {
	$( "#RevDate" ).datepicker({ dateFormat: "d-M" }).val(); 
});
$(function() {
	$( "#sentToAE" ).datepicker({ dateFormat: "d-M" }).val(); 
});
$(function() {
	$( "#VWRCmp" ).datepicker({ dateFormat: "d-M" }).val(); 
});
$(function() {
	$( "#startD" ).datepicker({ dateFormat: "d-M" }).val(); 
});
$(function() {
	$( "#VWR" ).datepicker({ dateFormat: "d-M" }).val(); 
});
</script>
</form>
</body>
</html>

And i'm trying to insert data into an excel sheet with the below jsp.

<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.sql.*"%>
<%
Connection con = null;
Statement stmnt = null;
try
{
    
	SimpleDateFormat sdf= new SimpleDateFormat("d-mmm");  
       
	String a=request.getParameter("comments");
	//int a=Integer.parseInt(as);
	String b=request.getParameter("updatedl");
	//int b=Integer.parseInt(bs);
	String c=request.getParameter("sid");
	//int c=Integer.parseInt(cs);
	String d=request.getParameter("desc");
	String e=request.getParameter("pubcode");
	String f=request.getParameter("Type");
	String g=request.getParameter("status");
        String hs=request.getParameter("DateRec");
        java.util.Date h=sdf.parse(hs);  
	String is=request.getParameter("startD");
        java.util.Date i= sdf.parse(is);  
	String js=request.getParameter("AssignedD");
        java.util.Date j= sdf.parse(js);  
	String ks=request.getParameter("sentToAE");
        java.util.Date k= sdf.parse(ks);  
	String ls=request.getParameter("RespFrmAE");
        java.util.Date l= sdf.parse(ls);  
	String ms=request.getParameter("VWRCmp");
        java.util.Date m= sdf.parse(ms);  
	String n=request.getParameter("PS");
	String o=request.getParameter("TEst");
	String ps1=request.getParameter("Units");
	//int p=Integer.parseInt(ps1);
	String qs=request.getParameter("VWR");
        java.util.Date q= sdf.parse(qs); 
	String r=request.getParameter("IE");
	//int r=Integer.parseInt(rs);
	String ss=request.getParameter("RevDate");
        java.util.Date s= sdf.parse(ss);  
	String t=request.getParameter("ReviewS");

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
con = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/jus/Book1.xls; ReadOnly=False;");

stmnt = con.createStatement(); 
//String query="select column_name from [Feb$[";
String query = "insert into [Feb$] ([comments],[No of lines updated in PSIS],[Shipment ID],"+
"[SAP TXT Description],[Phoenix Pub Code], [Product Type], [Status of the job],[Date Request received],"+
"[Start Date], [Date assigned],[Date file sent to AE],[Date response from AE],"+
"[Date completed --VWR], [Assigned to PS], [Time Estimate],[Units], [Date file needs to be sent to Vendor (VWR)],"+
"[Internal Errors],[Review Date],[Review Sent (Yes/No)]) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
/*String query = "insert into [Feb$] ([comments],[No of lines updated in PSIS],[Shipment ID],"+
"[SAP TXT Description],[Phoenix Pub Code], [Product Type], [Status of the job],[Date Request received],"+
"[Start Date], [Date assigned],[Date file sent to AE],[Date response from AE],"+
"[Date completed --VWR], [Assigned to PS], [Time Estimate],[Units], [Date file needs to be sent to Vendor (VWR)],"+
"[Internal Errors],[Review Date],[Review Sent (Yes/No)]) values ('"+a+"','"+b+"','"+c+"','"+d+"','"+e+"','"+f+"','"+g+"','"+h+"','"+i+"','"+j+"','"+k+"','"+l+"','"+m+"','"+n+"','"+o+"','"+ps1+"','"+q+"','"+r+"','"+s+"','"+t+"')";*/
//String query = "insert into [Feb$] ([comments]) values (?)";
PreparedStatement ps=con.prepareStatement(query);
ps.setString(1,a);
ps.setString(2,b);
ps.setString(3,c);
ps.setString(4,d);
ps.setString(5,e);
ps.setString(6,f);
ps.setString(7,g);
ps.setDate(8,new java.sql.Date(h.getTime()));
ps.setDate(9,new java.sql.Date(i.getTime()));
ps.setDate(10,new java.sql.Date(j.getTime()));
ps.setDate(11,new java.sql.Date(k.getTime()));
ps.setDate(12,new java.sql.Date(l.getTime()));
ps.setDate(13,new java.sql.Date(m.getTime()));
ps.setString(14,n);
ps.setString(15,o);
ps.setString(16,ps1);
ps.setDate(17,new java.sql.Date(q.getTime()));
ps.setString(18,r);
ps.setDate(19,new java.sql.Date(s.getTime()));
ps.setString(20,t);
//out.println(query);
int new1=ps.executeUpdate();

}
catch( Exception e )
{
out.println( e );
}
finally
{
try
{
stmnt.close();
con.close();
}
catch(Exception e)
{
 e.printStackTrace();
}
}

But it is giving me the below Exception

please let me know where am i going wrong.

Thanks

Bear Bibeault

Author and ninkuma
Marshal

Posts: 66207

151

I like...

posted 3 years ago

Why on earth is the code in a JSP?

[Asking smart questions] [About Bear] [Books by Bear]

Rakesh Keerthi

Ranch Hand

Posts: 152

posted 3 years ago

Sorry Mr.Bear, this was just a small reference project, so i was making it in JSP, even my friend tried it in servlets, but the exception is save, both of us were pulling our hair to solve this, and posting here was our last option after trying in our level. please help us to solve this.

Thanks

Paul Clapham

Sheriff

Posts: 22509

I like...

posted 3 years ago

At least in a servlet you'll get a stack trace which tells you what line of code threw the exception. You don't get that with JSPs. A JSP is a really poor environment for testing, for that and other reasons.

But you do need to know which line of code is the problem, so start by finding that out.

Unable to insert date into Excel

Similar Threads