File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL Queries: Some errors Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Queries: Some errors" Watch "SQL Queries: Some errors" New topic
Author

SQL Queries: Some errors

Shyam Prasad Murarka
Ranch Hand

Joined: May 02, 2005
Posts: 209
Dear Readers,
I am having problems regarding the execution of SQL Queries. My Table in MS Access is as follows:
stodkID - Number - Integer (Field Size)
Stock Symbol - Text - 50
Shares - Number - Long Integer
Price - Number - Long Integer
Buy Date - Date/Time - Input Mask (99/99/0000;0;_)

I have defined queries for Adding, Updating, Finding and Deleting Records.
My Finding query works perfectly but somehow I am facing problems with the others.

My Add query is as follows:
"INSERT INTO Stocks (stockID, 'Stock Symbol', Shares, Price, 'Buy Date') " +
"VALUES (?,?,?,?,?)";

Error while executing:
[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented


My Update query is as follows:
"UPDATE Stocks " +
"SET stockID = "+ st.getID() + ", " +
"'Stock Symbol' = '" + st.getSymbol()+ "', " +
"Shares = " + st.getShares() + ", " +
"Price = " + st.getPrice() + ", " +
"'Buy Date' = '" + st.getBuyDate() + "' " +
"WHERE stockID = " + id;


Error while executing:
UPDATE Stocks SET stockID = 1, 'Stock Symbol' = 'ASAP', Shares = 14, Price = 50,
'Buy Date' = '2005-09-12 00:00:00' WHERE stockID = 1
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.


My Delete Record is as follows:
"DELETE FROM Stocks " + " WHERE stockID = " + id;

Error while executing:
It informs me that rows were deleted, but when I open the database to check, the record is still there.

Is it possible that there is a conflict in the versions of the database? The file format is MS Access 2000 but I am currently using MS Access 2003.
[ May 30, 2007: Message edited by: Shyam Prasad Murarka ]

With Best Regards,
Shyam Prasad Murarka
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30753
    
156

The update statement has a syntax error
'Buy Date' = '2005-09-12 00:00:00' - You can't set the date to the string 'Buy Date'. You need to remove the quotes to assign it to a column.

The delete statement looks ok. Is autocommit turned off? Are you in a transaction that is rolling back?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Shyam Prasad Murarka
Ranch Hand

Joined: May 02, 2005
Posts: 209
Dear Readers,
Ok, first of all sorry. I had put in the wrong column names.
I changed it. And now my update statement does not produce any error... BUT it still does not update the record in the database! Same thing is still happening with Delete statement.
And my error for Add statement is still the same.
What is autocommit? I don't know anything about it? And as far as I know I am not doing any rollback. Its a very simple application.
Thank you for your reply.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30753
    
156

Can you post the source code?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Queries: Some errors