Solved

Date Manipulation

Posted on 2006-07-05
22
233 Views
Last Modified: 2010-04-01
Hi there

Question 1)

For some reason when i am inputting data it wont let me leave a date field blank, or null. But i need to be able to do this. e.g. client walks in and we register them on our system, but one field is paid date and obviously i only want to enter that once they have actually paid. If it is default to present date then obviously it will look like they paid, or if i default it to 00-00-0000 when i click on the calendar i have a shite load of clicking to do to get to the current date. How do i allow it to stay blank?

P.S. Its not a primary key and is not set to 'not null' either.

Question 2)

See code below. What I want to do is after each block of dates i want to leave an open line satting the day of the dates to follow, e.g.

The output is Like this at the moment:

01-01-2006 ....
01-01-2006 ...
02-01-2006 ... etc.

I want:

Mon
01-01-2006 ...
01-01-2006 ...
Tue
02-01-2006 ... etc

Any Ideas?


try {
      Driver DriverRecordset = (Driver)Class.forName(_driver).newInstance();
      con = DriverManager.getConnection(_url, _uid,_pwd);
       stmt = con.createStatement();
        rs = stmt.executeQuery("select * from bookings where embassy='French' order by consulatedate asc");
      while (rs.next()){      
      
            c++;
      %>
            <TR>
                  <Td  bgcolor="#ffffff" width=80><%=rs.getString("consulatedate")%></td>
                  <Td  bgcolor="#ffffff"><a href="viewclient.jsp?id=<%=rs.getString("id")%>"> <%=rs.getString("name")%> <%=rs.getString("surname")%> </a> </td>

                  <Td  bgcolor="#ffffff" width=70><%=rs.getString("tel")%></td>
                  <Td  bgcolor="#ffffff" width=70><%=rs.getString("mobile")%></td>
                  <Td  bgcolor="#ffffff"><%=rs.getString("email")%></td>
                  <Td  bgcolor="#ffffff" width=60><%=rs.getString("traveldate")%></td>
                  <Td  bgcolor="#ffffff" width=60><%=rs.getString("documents")%></td  
            </tr>
      <%       
                }
       con.close();
}

catch (SQLException sqle) {
       out.println("SQLException" + sqle.toString());
}
0
Comment
Question by:AndriesKeun
  • 9
  • 8
  • 5
22 Comments
 
LVL 8

Expert Comment

by:colr__
ID: 17043681
1) Have you tried replacing the nulls with "" - if the field is blank. it might appear at the procesing formas null, sho try checking for this and replacing with "" instead.
0
 
LVL 19

Accepted Solution

by:
Kuldeepchaturvedi earned 400 total points
ID: 17057894
for first question.. if you dont enter anything it should remain blank....

for second one...

string tempdate = new String();
while (rs.next()){    
     
          c++;
if (tempdate.equals(rs.getString("consulatedate"))
//leave spaces
end if
     %>
          <TR>
               <Td  bgcolor="#ffffff" width=80><%=rs.getString("consulatedate")%></td>

..
..
..
<%tempdate=rs.getString(consulatedate);%>
       <Td  bgcolor="#ffffff" width=60><%=rs.getString("traveldate")%></td>
               <Td  bgcolor="#ffffff" width=60><%=rs.getString("documents")%></td  
          </tr>
     <%      
              }

0
 

Author Comment

by:AndriesKeun
ID: 17080103
1) This is the error I am getting when i try to save without entering a date:
>>SQLExceptionjava.sql.SQLException: Incorrect date value: '' for column 'traveldate' at row 1

Its a field in my db set as a Date with no default value. Any other ideas?

2) kuldeep, yours works but was not perfect, had to change the syntax a bit and if (tempdate.equals(rs.getString("consulatedate")) is actually !if (tempdate.equals(rs.getString("consulatedate"))
Thanks anyway though
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 8

Expert Comment

by:colr__
ID: 17080171
if you arent providing a default value in your table definition, then you'll have to explicitly provide it in your SQL staement.

The way you have it just now, your trying to write "" into the table, which isnt a valid date (and so your getting the error). Your options are to either to alter the table to use a default value, specify a default value in your sql, or allow the date field to accept null's.
0
 

Author Comment

by:AndriesKeun
ID: 17080319
thats whats so bloody confusing. my db is set to allow null on all the fields except the primary key (id) so i just cant understand why it wont allow me to use null for the dates. all the other fields are happy with being null, except the bloody dates. IT really annoys me sometimes, sometimes things just dont work when they should.
0
 
LVL 8

Expert Comment

by:colr__
ID: 17080443
Instead of trying to insert an empty string, try not mentioning it in your sql statement
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 17081153
It may be database specific.. what database you are using? I am not sure but this information might help
0
 

Author Comment

by:AndriesKeun
ID: 17081243
A SQL database, and I access it using SQLyog and NAVICAT
0
 
LVL 8

Expert Comment

by:colr__
ID: 17081254
Have yuou tried removing the empty strng? This might help, as an empty string != null, so when you are inputting an empty string you are attempting to input an invalid date (as opposed to null)
0
 

Author Comment

by:AndriesKeun
ID: 17081286
Basically i have a form on my application with all the client details, including a few dates. The user inputs all the details, Name, Number, Travel date etc. If they dont input a date then the input box is left blank.

I use the following code to INSERT the new client:

       PreparedStatement stmt2 = con.prepareStatement("INSERT INTO bookings (name, tel, mobile, email, traveldate, consulatedate, paiddate, notes, documents, surname, embassy, status) values(?,?,?,?,?,?,?,?,?,?,?,?)" );  
      stmt2.setString(1, request.getParameter("cfname") );
      stmt2.setString(2, request.getParameter("tel") );
      stmt2.setString(3, request.getParameter("mobile") );
      stmt2.setString(4, request.getParameter("email") );
      stmt2.setString(5, request.getParameter("traveldate") );
      stmt2.setString(6, request.getParameter("consulatedate") );
      stmt2.setString(7, request.getParameter("paiddate") );
      stmt2.setString(8, request.getParameter("notes") );
      stmt2.setString(9, request.getParameter("documents") );
      stmt2.setString(10, request.getParameter("clname") );
      stmt2.setString(11, request.getParameter("embassy") );
      stmt2.setString(12, request.getParameter("status") );
       stmt2.executeUpdate();

So if one of the dates are left blank in the form, then surely it should go to the db as NULL?
0
 
LVL 8

Expert Comment

by:colr__
ID: 17081307
Use setDate instead of setString!!!
0
 

Author Comment

by:AndriesKeun
ID: 17081374
get this error with setDate:

An error occurred at line: 22 in the jsp file: /updateclient.jsp
Generated servlet error:
C:\Program Files\Apache Software Foundation\Tomcat 5.0\work\Catalina\localhost\travelvisas\org\apache\jsp\updateclient_jsp.java:105: cannot find symbol
symbol  : method setDate(int,java.lang.String)
location: interface java.sql.PreparedStatement
      stmt2.setDate(5, request.getParameter("traveldate") );
0
 
LVL 8

Assisted Solution

by:colr__
colr__ earned 100 total points
ID: 17081394
Thats because your trying to put a strng into a date value. Check the API:

setDate(int, Date);
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 17081549
try
if(request.getParameter("traveldate").trin()=="")
{
java.sql.Date d=null;
stmt2.setDate(5, d );
}
else
{
java.sql.Date d = new date(java.util.Date.parse(request.getParameter("traveldate"));
stmt2.setDate(5, d );
}
0
 

Author Comment

by:AndriesKeun
ID: 17081670
now getting this error:  SQLExceptionjava.lang.IllegalArgumentException

new code:

stmt2.setString(4, request.getParameter("email") );


      if(request.getParameter("traveldate").trim()=="") {
            java.sql.Date d = null;
            stmt2.setDate(5, d );
      }
      else
      {
      java.sql.Date d = new Date(java.util.Date.parse(request.getParameter("traveldate")));
      stmt2.setDate(5, d );
      }


      //stmt2.setString(5, request.getParameter("traveldate") );
      stmt2.setString(6, request.getParameter("consulatedate") )
0
 

Author Comment

by:AndriesKeun
ID: 17081834
Ok, Step by step, let me know if you see any errors:

This is my input form:

<tr><td  class='menutable' >Travel Date:</td> <td><input name=traveldate value='' style='width:100' onfocus='popUpCalendar(this, this, "yyyy-mm-dd");'></td></tr>

I have tried without the value=''. This is just a normal input box with no string.

I insert it with:
   stmt2.setString(5, request.getParameter("traveldate") );

Now this works when there is a date in the input box, but not when it isnt, which is confusing me.

The database is set up with traveldate as a date field, that allows null and has a default as null.
0
 
LVL 8

Expert Comment

by:colr__
ID: 17081921
Before you try and put it in the DB, try and output the value to see exactly what you are getting. You mihgt be assuming that it is an empty string, but the javascript you're using might be using something else (whioch would break your code as you descrttiobe):

request.println("value:" + request.getParameter("traveldate") );
0
 
LVL 19

Assisted Solution

by:Kuldeepchaturvedi
Kuldeepchaturvedi earned 400 total points
ID: 17082149
alright this have to work..!!!

stmt2.setString(4, request.getParameter("email") );


     if(request.getParameter("traveldate").trim()=="") {
          stmt2.setNull(5, java.sql.Types.DATE );
     }
     else
     {
     java.sql.Date d = new Date(java.util.Date.parse(request.getParameter("traveldate")));
     stmt2.setDate(5, d );
     }


     //stmt2.setString(5, request.getParameter("traveldate") );
     stmt2.setString(6, request.getParameter("consulatedate") )
0
 

Author Comment

by:AndriesKeun
ID: 17082348
thx kuldeep, eventually sorted it by adapting your code a bit:

      String temp = "";

      if (temp.equals(request.getParameter("traveldate"))) {
            stmt2.setNull(5, java.sql.Types.DATE );
      }
      else {
            stmt2.setString(5, request.getParameter("traveldate") );
      }

Suppose i shouldnt be using setString in the second part, but it works perfectly. In other parts of the program i use getDate to display the date and it works perfectly, so I am happy.
0
 

Author Comment

by:AndriesKeun
ID: 17082403
gave most points to kuldeep cause he came up with both solutions.

gave colr some points too as he was on the right track most of the time too.

Thx guys
0
 
LVL 8

Expert Comment

by:colr__
ID: 17082407
Glad you got it sorted ;-)
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 17082455
Glad to be of some help...:-)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
method inner class 6 82
some methods private some are not 8 212
project group option in netbeans equivalent term in eclipse 1 50
caughtSpeeding java challenge 16 137
In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
This article outlines why you need to choose a backup solution that protects your entire environment – including your VMware ESXi and Microsoft Hyper-V virtualization hosts – not just your virtual machines.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question