Solved

Date Manipulation

Posted on 2006-07-05
22
229 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__
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 8

Expert Comment

by:colr__
Comment Utility
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
Comment Utility
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__
Comment Utility
Instead of trying to insert an empty string, try not mentioning it in your sql statement
0
 
LVL 19

Expert Comment

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

Author Comment

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

Expert Comment

by:colr__
Comment Utility
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
Comment Utility
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__
Comment Utility
Use setDate instead of setString!!!
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:AndriesKeun
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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__
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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__
Comment Utility
Glad you got it sorted ;-)
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
Comment Utility
Glad to be of some help...:-)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Apache server configuration 7 78
method inner class 6 80
How to Post an If Statement in JSP 3 45
thymeleaf natural templating vs JSP 2 21
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now