Help!! Simple question about date formating.

How you pass the date to sql statement?

 What database are you using? And in what format does the database expects the date?

Through a insert into <tablename> (columnname) value (variable)

MSSQL database, I suspect it expects to get it as a date.

 Can you use java.sql.Date instead of java.util.Date and tell us what happens? And pelase do not format it. Just create the java.sql.Date object and send it to the database.

I send the Date dato variable in my statement, but I think the problem is that the parsing changes my SimpleDateFormat so that the db doesn't recognize it.

Same error as before.

What is the difference between the Date in java.sql an util?

 java.sql.Date is more appropriate date object for database interaction. Lets try something else.

  Can you just ignore the following code:

Date currentDate = new Date();
         SimpleDateFormat fmt = new SimpleDateFormat("dd.MM.yyyy");
         String formatert = fmt.format(currentDate);
         fmt.setLenient(false);
         System.out.println(formatert);
         Date dato = fmt.parse((formatert),new java.text.ParsePosition(0));
         System.out.println(dato);

  and just do:

 java.sql.Date date = new java.sql.Date()

  and then try to send this "date" object to the database?

You can use
PrepareStatment and set values as java objects.The JDBC  
driver will convert the object into rigth form.
Example.

SQL Statment: insert into <tablename> (columnname) ? (variable)
String sqlStat = "insert into <tablename> (columnname) ? (variable)";
Date value = ....;
Connection connection = ....;
PrepareStatment ps = connection.getPrepareStatment(sqlStat);

ps.setDate(1, value);

ps.exequte();

girionis
>and just do:

>java.sql.Date date = new java.sql.Date()

I get a can not resolve symbol in the Date() constructor.

 Yes you are right. The constructor is different. Try this:

java.sql.Date = new java.sql.Date(System.currentTimeMillis());

  Not that the currentTimeMillis represents the date from the midnight of 1st of January 1970.

Copied it and got cannot resolve symbol in class sql.

 Can you please post the exact error message?

java.sql.Date date = new java.sql.Date(System.currentTimeMillis());

  Sorry for before.

java.sql.Date date = new java.sql.Date(System.currentTimeMillis());

  Sorry for before.

Ok so far so good. But there is still some problems. I'll post a code snippet were I try to send the date variable to my db. I get a cannot resolve symbol on the date variable. Date date is a global variable.
public void DbClient()
     {
          username = new String("****");
          password = new String("****");
          databaseURL = new String("jdbc:microsoft:sqlserver://server-2:1433");
          databaseName = new String("NO");

          //Establish connection to db via driver
          try
          {
               Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); //Load driver
          }
          catch (ClassNotFoundException ex)
          {
               System.out.println(ex);
          }
          System.out.println("JDBC driver loaded");

          try
          {
               con = DriverManager.getConnection(databaseURL,username,password); //Oppretter forbindelse
               System.out.println("Connection created");
          }
          catch (java.sql.SQLException ex)
          {
               System.out.println("Error " + ex.getErrorCode() +ex.getMessage());
               System.exit(0);
          }
          //Initialize preparedStatement
          try
          {
               String sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                      + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
               PreparedStatement ps = con.prepareStatement(sqlStat);
          }
          catch(SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }
          ps.setInt(1,160379);
          ps.setInt(2,0);
          ps.setString(3,"headline");
          ps.setString(4,"bilde");
          ps.setString(5,"intro");
          ps.setString(6,"brodtekst2");
          ps.setString(7,"brodtekst3");
          ps.setString(8,"brodtekst4");
          ps.setString(9,"brodtekst5");
          ps.setString(10,"brodtekst6");
          ps.setString(11,"brodtekst7");
          ps.setString(12,"brodtekst8");
          ps.setString(13,"brodtekst9");
          ps.setString(14,"brodtekst10");
          ps.setString(15,"brodtekst");
          ps.setString(16,"kilde");
          ps.setDate(17,date);
          ps.setString(18,"link");
          ps.setString(19,"fnyheter");
          ps.setInt(20,1);
          ps.execute();
     }

 Can you post the exact error message?

C:\Documents and Settings\raymond.ZOONONWEB\My Documents\Kode\ArticleApplication\ArticleApplicationBack.java:295: cannot resolve symbol
symbol  : variable date  
location: class ArticleApplicationBack
          ps.setDate(17,date);
                              ^
1 error

 Can you post a sniplet of code where you declare "date"?

Man I think I have changed to much of my code now, the program compiles but it's hard to check if the date works because I get a nullpointerexeption when running it. If you give me a hand with this I would appreceate it and certanly up the points.

It doesn't like my prepared statement.

 OK first of all where do you get this nullpointer exception? Can you post a snippet of code?

         //Initialize preparedStatement
          try
          {
               String sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                      + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
               PreparedStatement ps = con.prepareStatement(sqlStat);
          }
          catch(SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }
          try
          {
          ps.setInt(1,160379);
          ps.setInt(2,0);
          ps.setString(3,"headline");
          ps.setString(4,"bilde");
          ps.setString(5,"intro");
          ps.setString(6,"brodtekst2");
          ps.setString(7,"brodtekst3");
          ps.setString(8,"brodtekst4");
          ps.setString(9,"brodtekst5");
          ps.setString(10,"brodtekst6");
          ps.setString(11,"brodtekst7");
          ps.setString(12,"brodtekst8");
          ps.setString(13,"brodtekst9");
          ps.setString(14,"brodtekst10");
          ps.setString(15,"brodtekst");
          ps.setString(16,"kilde");
          ps.setDate(17,date);
          ps.setString(18,"link");
          ps.setString(19,"fnyheter");
          ps.setInt(20,1);
          ps.execute();
          }
          catch (SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }

     }

It doesn't like the way I implement the prepared statement I think. I use this sniplet to initialize the db variables with temp values. And then I call functions that read user input and configure the variables.
These functions also generate nullpointerexceptions.
Example:
/**
     *Function that alteres the kilde/source in db
     */
     public void setKilde()
     {
          source = tfSource.getText();
          btSource.setBackground(Color.green);
          try
          {
               ps.setString(16,source);
               ps.execute();

          }
          catch(SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }
     }

 Sorry I am lost here. Which one of the specific lines throw the NullPointerException?

  I also assume this line: ps.setDate(17,date); compiles fine now.

Yes everything compiles fine. Sorry, the nullpointer exception gets thrown in this line:
ps.setInt(1,160379);
This is the primary key of the db and is supposed to set itself as a random number in the end produkt. I assignet the value just to test if the prepared statement worked.

 Can you do a System.out.println("ps: " + ps); just before the ps.setInt(1, 160379); and tell me the output?

ps: null Why, I am connected to the db. I must have the statement all wrong.

 Well you might not be connected after all. Do you see the "Connection created" message?

Yes, and I have tested the connection before I started using the prepared statement.

 I can only say that the error is in this block of code:

//Initialize preparedStatement
try
{
  String sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                     + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  PreparedStatement ps = con.prepareStatement(sqlStat);
}
catch(SQLException sqle)
{
  System.out.println("SQL problem:" + sqle);
}

  please add another one catch snippet at the end. It should look like:

//Initialize preparedStatement
try
{
  String sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                     + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  PreparedStatement ps = con.prepareStatement(sqlStat);
}
catch(SQLException sqle)
{
  System.out.println("SQL problem:" + sqle);
}
catch(Exception e)
{
  System.out.println("Exception occured: " + e);
  e.printStackTrace();
}

  and let me know for the error message.

I get the same error: Nullpointerexception on the same line.

 Do you have any more "ps" variables defined in your programme? The "ps" variable inside your try statement is only accessible to the try block..

No, it's declared globally but not defined anywhere else, don,t you have to use a try statement when defining it?

 So you have a global declaration like:

  PreparedStatement ps = null;

  or

  PreparedStatement ps;

  and inside your try statement you have another one. You see, all the variables inside the try statement are only *local* to the try statement, they cannot be used outside of it. Now you are having two identical "ps" variables, but as long as JVM is concerned they are different (despite the same name). So the "ps" outside the try is different to the "ps" inside your try. You initialize the "ps" inside the try but you use your global "ps" outside the try, which of course, your global "ps" is not initialized.

  The problem can be solved if you change the following line:

  PreparedStatement ps = con.prepareStatement(sqlStat);

  to

  ps = con.prepareStatement(sqlStat);

  Try it and tell me the output.

Crap!!, I redeclare the PreparedStatement inside the try statement. I get a bit closer to getting it to work, but there is still a sql error. As I mentioned the ID is automaticly set if you punch the data directly into the db. So the IDENTITY_INPUT is set to off. I can't just leave it out of my ps can I?

Actually I get the IDENTITY_INSERT is set to off on all the variables.

 No you can't. If it needs it it should be there. What is the error message you are getting this time? Can you post the exact message?

> As I mentioned the ID is automaticly set if you punch the data directly into the db

  So the ID field is set to Auto? Then you could leave it out yes.

Cannot insert explisit vakue for identity column in table 'avisklipp' when IDENTITY_INSERT is set to OFF.

 Yes you could leave it out... Please ignore my comment on: 03/06/2003 05:07AM PST I didn't read your post carefuly so I didn't realize it was set to auto. You cannot insert anythign into columns that used as IDENTITY, i.e. to identify data.

The ultimate anticlimax, no error but nothing works. I'm pretty new to JDBC but by running the sniplets I posted above you are supposed to add a new line in the db right??

 Yes that's what is supposed to happen. Are you sure you are not getting any error messages? Can you post again the new code thsi time that inserts records into the database?

//To connect and initialise the prepared statement I use this.
public void DbClient()
     {
          username = new String("****");
          password = new String("****");
          databaseURL = new String("jdbc:microsoft:sqlserver:server-2");
          databaseName = new String("NO");

          //Establish connection to db via driver
          try
          {
               Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); //Load driver
          }
          catch (ClassNotFoundException ex)
          {
               System.out.println(ex);
          }
          System.out.println("JDBC driver loaded");

          try
          {
               con = DriverManager.getConnection(databaseURL,username,password); //Oppretter forbindelse
               System.out.println("Connection created");
          }
          catch (java.sql.SQLException ex)
          {
               System.out.println("Error " + ex.getErrorCode() +ex.getMessage());
               System.exit(0);
          }
          //Initialize preparedStatement
          try
          {
           sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                 + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
           ps = con.prepareStatement(sqlStat);
           System.out.println(ps);
          }
          catch(SQLException sqle)
          {
           System.out.println("SQL problem:" + sqle);
          }
          catch(Exception e)
          {
           System.out.println("Exception occured: " + e);
           e.printStackTrace();
          }

          try
          {
          ps.setInt(1,0);
          ps.setString(2,"headline");
          ps.setString(3,"bilde");
          ps.setString(4,"intro");
          ps.setString(5,"brodtekst2");
          ps.setString(6,"brodtekst3");
          ps.setString(7,"brodtekst4");
          ps.setString(8,"brodtekst5");
          ps.setString(9,"brodtekst6");
          ps.setString(10,"brodtekst7");
          ps.setString(11,"brodtekst8");
          ps.setString(12,"brodtekst9");
          ps.setString(13,"brodtekst10");
          ps.setString(14,"brodtekst");
          ps.setString(15,"kilde");
          ps.setDate(16,date);
          ps.setString(17,"link");
          ps.setString(18,"fnyheter");
          ps.setInt(19,1);
          System.out.println(sqlStat);
          ps.execute();
          }
          catch (SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }

     }
And to change the individual entries in the db I use several functions like this:
     *Function that alteres intro/bildetekst in db
     */
     public void setIntro()
     {
          pictureText = taPictureText.getText();
          pictureTextLength = pictureText.length();
          btPictureText.setBackground(Color.green);

          try
          {
               ps.setString(4,pictureText);
               ps.execute();
          }
          catch(SQLException sqle)
          {
               System.out.println("SQL problem:" + sqle);
          }
     }

 It looks fine. What do you get if you do a:

  System.out.println(ps.execute());

I assume you meen System.out.println(ps);
If so I get:
com.microsoft.jdbc.BasePreparedStatement@ce2dd4

I suppose it is the way it is suppose to be but the ps doesn't change from the time it is initiated to after the values in it is set.

 No I meant System.out.println(ps.execute()); the execute() method returns either true or false.

  Just replace the ps.execute() with the System.out...

 Or you could better also do a System.out.println(ps.executeUpdate()); and tell me the result? It should return a numebr, probably 1 if everything is fine.

Ran both:
System.out.println(ps.execute()); returned false
System.out.println(ps.executeUpdate()); returned 1
I wouldn't say everything is fine though.;)

 Hmmm. weird. Can you please do a System.out.println(con.getAutoCommit()); and tell me the result?

Sorry, girionis I've had to leave my post for today. I'll try it first thing tomorrow and let u know. Thank you for your patience and help so far. Youre a lifesaver.

>System.out.println(con.getAutoCommit()); returns true

 Can you set it to false after creating the connection:

  con.setAutoCommit(false);

  and after you do the ps.execute(); add the following line:

  con.commit();

  Can you also try and close the database connections after you've done and tell me if this helps?

Nope, still ain't adding anything.

 Are you sure you are not getting any error messages? Have you checked the log files?

I'm not getting any errors in my console. I'm not sure how to access db logs, have enterprise installed tough.

Don't know if it is supposed to be like that but the current->SQL server logs doesn't show any activity from me at all, but I've been printing tables all day so that doesn't say much.

 Ok lets try something else. Can you try sending a JDBC statement using the Statement object and see what happens?

  Just change this:

   sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

  into this:

 sqlStat = "INSERT INTO avisklipp (pri,overskrift,bilde,intro,brodtekst,brodtekst2,brodtekst3,brodtekst4,brodtekst5,brodtekst6,brodtekst7,brodtekst8,brodtekst9,brodtekst10,kilde,dato,link,fnyheter,hit)"
                                                                + "VALUES (0,'headline' ....)";

  initialize Statement and then do: stmt.executeUpdate(sqlStat);

Allreaddy tryed that, I think I'll have to go back to the drawing board. But I have a concern about the way I have built the app. If it works,SOMETIME IN THE FUTURE, what will happen when I run the functions for changing the individual values. Like the setIntro wich I posted above.
Will I create whole new entry in the db. Thats not what I'm aiming for.

No, it did not work I'm afraid. I think I'll have to take a talk to the system guys, I think we have covered every aspect of my code don't you agree?

 That's what I think as well. Your code compiles fine and it looks fine. Of course it is difficult to say without looking at all of your files and with no error messages generated but if you could get a second opinion from the database guys this would be more helpful.

I will do that, you have been a big help girionis, your persistence and knowledge are impressive. I will post the answer as soon as I find it. But as I mentioned there are a couple of issues that bothers me a bit. With the prepared statement. Won't I get multiple entries in the db, when I run the functions for configuring the individual variables in the ps. As posted above?
And another concern I have is the date issue wich we discussed in the beginning. In the db the date is set by a datetime with the format dd.MM.yyyy. Sure this compatible with the solution you gave me before:
java.sql.Date date = new java.sql.Date(System.currentTimeMillis()); or do I need to format it?
Some thoughts from the top of your head will be appreciated imensly. Thnx a lot.

Defenitive answer will be posted.

 Thank you :-)

>  With the prepared statement. Won't I get multiple entries in the db, when I run the functions for configuring the individual variables in the ps. As posted above?

  It depends. Entries in the database will go only after the ps.execute() has been reached and the database commits the changes. This commit is by default set to auto so the database commits the changes automatically after executing each statement (in our case after we do a ps.execute() the database will commit automatically). If you set it to manual (by doing a connection.setAutoCommit(false) then you need to do a connection.commit() explicitly. If changes are not commited in the database then there is nothing there, regardless if you have executed a statement.

>  In the db the date is set by a datetime with the format dd.MM.yyyy. Sure this compatible with the solution you gave me before:
java.sql.Date date = new java.sql.Date(System.currentTimeMillis()); or do I need to format it?

  No you shouldn't need to format it. java.sql.Date is written in a way that it complies with database standards. I never needed to format any date in order to insert it into SQL server 2000. Besides if this were the problem for the database you should really be getting error messages about the date format.