• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Looks right to me

 
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I continue to get a syntax error but can't figure out why.
I have tried a number of things to get this to work.
It looks like an extra single quote is being inserted (based on
what I see in the printStackTrace). I have tried removing the
quotes to no avail as well.

The value in the first query (facility) comes from a combo box
which passes the value to this class. Just the code that I am
having problems with follows. I have verified that the values
I am trying to query on match the values in the DB. I am sure
that something simple is going wrong but can't see what it is...



Here is the print stack trace:

____________
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'Select FacilityID FROM MD_Facility
WHERE FacilityName = 'LifeCare of Greely''.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)
at BackUpAdd.<init>(BackUpAdd.java:46)
at PatientAdd$AddBackRoomListener.actionPerformed(PatientAdd.java:167)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Sour
ce)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
______________________

I know that there should be nothing hard about this. Is there something
being added to the value passed from the combo box that I need
to account for? Other than that, I can't figure out what is causing
this to not work.

Thanks for your help.
 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I can't see anything wrong in the code you posted. Kind of a mystery. Out of curiosity, if you print out the value of facility before the first try/catch block that you posted, what is the value? Do you see any extra characters with the value by itself?
 
Al Wells
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I did not print it out. However, I knew that the value was correct
because the print stack trace reports it and it is the value
that I selected in the combo box.

I am scratching my head.
 
Al Wells
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just did a test print and the consol shows no other characters.
 
Al Wells
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I added some things to this class and now I am not getting the earlier
error. But, I am getting a new one. First I got a missing operator
error. So, I concanterated my string further (I don't know why...
just trying anything now). That fixed that error. Now I get an
"invalid cursor state" error but it does not tell me in which
block of code I am getting this. That could be the insert statement.
I have not yet done any inserting in Java so I could be doing that
wrong. Here is the updated code.



This creates a printStackTrace that tells me that I have an
"Invalid Cursor State". If I had any clue what that was,
I would try and fix it and see if this works.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Al,
There are many "wrong" things in the code you posted in your last reply. What you are trying to do is fairly simple and standard for JDBC. So I get the impression you have no idea what you are doing or how to accomplish your current task.

I'm assuming you would like to learn how to do this properly -- rather than have someone write the code for you. If my assumption is correct, then I suggest (if you haven't already done so) that you peruse the JDBC Trail in the Java Tutorial.

Good Luck,
Avi.
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My guess is that the problem lies in your Database class where you actually perform all the JDBC operations. AS far as your program structure, it is a little sloppy joe, but I know how you code so I am not worried that will all be cleaned up.

Can you show us your Database class?
 
Al Wells
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I do agree that I am still learning. But, know that I first worry
about getting the thing to compile and run and then I modify,
fix and clean up the code for what I am trying to do. I will
read the turtorial though. Maybe that will at least help with
the invalid cursor state problem. Still, you are probably right
when you indicate that I am a little lost. That is why I posted!

The DB class is posted below. However, it works for all other instances
where I am doing SELECT queries. Where I think it is going wrong is
in the INSERT. The book that I am learning from does not give me
enough to work with. Nonetheless, I'll figure this out one way or
another today.

Thanks for the help so far!


[ May 31, 2005: Message edited by: Al Wells ]
 
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think, you should be using executeUpdate(sqlstring) method rather than executeQuery(sqlstring).

Hope I'm not spoiling your fun,

Cnu
 
Al Wells
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It is past the point of fun! Spoil away.

I tried executeUpdate(query) as well. I also seperated my trys
into two blocks. That helped me see the error better. What I am getting is
an invalid cursor error in the first result set. Since that is the case
no facilityID is being returned and I believe that the error in the
next block is due to that. The INSERT error is "Error in row".

The data table that I am accessing in the select query is MD_Facility.
The column that I am selecting is "FacilityID". The filter is
"FacilityName". FacilityName comes from a combo box object which
returns all of the facility names to that box and the user then
selects the faciity which the patient is added to (in this case a
patient is a 'back up room'). That passes to this object as String
facility. I am printing out that string in my console and it is
right. Now I am trying to figure out how to move the cursor through the
result set. I changed the way that the insert code works to be
commisserate with 2.0. Now I am going to try the same with the select
query and see what happens.
 
Al Wells
Ranch Hand
Posts: 87
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Incidentally, the combo box referenced above gets its values from a
vector which adds all of the facilities in the DB to itself before
being added to the combo box. So, I know that the facility names are
correct because it is only facility names that exist in the table that
I am trying to get an ID on that can be returned.
 
Pavan Kumar
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"WHERE FacilityName =" + " '" + facility+"'";
dataRequest = Database.createStatement();facIDResults = dataRequest.executeQuery(facID);String finalFacID = new String();finalFacID = facIDResults.getString(1);

I may be totally wrong, but I think if you want to iterate through the Resultset you have to say facIDResults.next() in order to move the cursor to the next row in the result set.

from javadoc of Resultset
Moves the cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

[Edit] removed code tags, because it's all appearing in one long line.
Best Regards,
Cnu
[ May 31, 2005: Message edited by: cnu sri ]
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, cnu is correct. The following should resolve the cursor issue:

String finalFacID = new String();
facIDResults.next(); //moves the cursor to the first record
finalFacID = facIDResults.getString(1);

If you don't call next() then the cursor actually is sitting before the first record. There are method calls to get back to that point as well as after the last record.

And just some advice, and again realizing you will clean up your code later, there is no need to nest try/catch blocks in the code like you are. One try/catch should be sufficient.
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Oh, and one more tid bit, the execute(String query) method should be enough as well for inserts.
  • executeQuery() - When you need a ResultSet. Usually used for SELECT statements
  • executeUpdate() - When you issue an UPDATE statement
  • execute() - When you issue an INSERT statement.

  •  
    Al Wells
    Ranch Hand
    Posts: 87
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Wish I would have refreshed the page...

    I finally figured that out and just got it all working.

    The great thing about learning the hard way is that once it is learned,
    it is learned. I finally got the row to insert into the DB!

    I can not figure out why you have a virtual cursor in Java. It would make
    sense to me that a Select with a WHERE which returns only one record
    would mean that there is only one record and therefore that is the
    record that is desired. But, now at least I know.

    Thanks for your help.
     
    With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    reply
      Bookmark Topic Watch Topic
    • New Topic