Forum:

Unable to insert date into Excel

Ranch Hand

Posts: 153

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Sheriff

Posts: 67756

173

I like...

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Why on earth is the code in a JSP?

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

HTML 5 (verified skill)
Skill verified by Jeanne Boyarsky

CSS 3 (verified skill)
Skill verified by Jeanne Boyarsky

JavaScript (verified skill)
Skill verified by Jeanne Boyarsky

Rakesh Keerthi

Ranch Hand

Posts: 153

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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: 28436

104

I like...

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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.

Java 8 (verified skill)
Skill verified by Paul Clapham

yeah, but ... what would PIE do? Especially concerning this tiny ad:

Clean our rivers and oceans from home

https://www.kickstarter.com/projects/paulwheaton/willow-feeders