• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Date Manipulation

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
AndriesKeun
Asked:
AndriesKeun
  • 9
  • 8
  • 5
3 Solutions
 
colr__Commented:
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
 
KuldeepchaturvediCommented:
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
 
AndriesKeunAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
colr__Commented:
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
 
AndriesKeunAuthor Commented:
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
 
colr__Commented:
Instead of trying to insert an empty string, try not mentioning it in your sql statement
0
 
KuldeepchaturvediCommented:
It may be database specific.. what database you are using? I am not sure but this information might help
0
 
AndriesKeunAuthor Commented:
A SQL database, and I access it using SQLyog and NAVICAT
0
 
colr__Commented:
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
 
AndriesKeunAuthor Commented:
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
 
colr__Commented:
Use setDate instead of setString!!!
0
 
AndriesKeunAuthor Commented:
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
 
colr__Commented:
Thats because your trying to put a strng into a date value. Check the API:

setDate(int, Date);
0
 
KuldeepchaturvediCommented:
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
 
AndriesKeunAuthor Commented:
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
 
AndriesKeunAuthor Commented:
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
 
colr__Commented:
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
 
KuldeepchaturvediCommented:
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
 
AndriesKeunAuthor Commented:
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
 
AndriesKeunAuthor Commented:
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
 
colr__Commented:
Glad you got it sorted ;-)
0
 
KuldeepchaturvediCommented:
Glad to be of some help...:-)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now