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 Exception:General Error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Exception:General Error" Watch "SQL Exception:General Error" New topic
Author

SQL Exception:General Error

Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
I'm looking at the quotes very carefully here but I can't find the reason I am getting a SQL General error:

I thought an extra set of eyes may help


I have checked the Database table empDetail every thing looks fine there also.
Can somebody steer me in the direction where I need to look if it is elsewhere to this code.
I am probably missing something simple here so please forgive me but I cannot see the problem (and I have my glasses on)
Deepak Rao
Ranch Hand

Joined: Jan 24, 2012
Posts: 31

Nick,

On quick look I see that the first set [Emp ID] value txtEmpNo.getText() does not have an opening single quote

String sql = "UPDATE empDetail SET [Emp ID]= "+txtEmpNo.getText()+ should be
String sql = "UPDATE empDetail SET [Emp ID]= '"+txtEmpNo.getText()+

-Deepak
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Deepak you have got to be kidding me I'm going blind, thank you

sorry all I really checked and checked, maybe its time to get my eyes checked again

thanks again !
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

You're right, it's a breeding ground for bugs. Not to mention that if you've got an employee named O'Brien then it's going to break. So clean it up by using a PreparedStatement with parameters, rather than trying to build an SQL string and get all the quotes right. Here's what it would look like:
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Paul,

I am glad you mentioned that, because I have not learned about prepared statements yet but I did see somebody point to them in this forum and asked one of my instructors
who informed me that we would be introduced to them next week and for now to use the SQL String like I did.
Maybe I can impress them and do a search on them now to see how they work (and now you have given me the code maybe I can use it?)

Thank you
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Typical education. They start off by teaching you bad habits. Well, at least they are going to get to it soon and not just leave you with those bad habits.
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Paul Clapham wrote:You're right, it's a breeding ground for bugs. Not to mention that if you've got an employee named O'Brien then it's going to break. So clean it up by using a PreparedStatement with parameters, rather than trying to build an SQL string and get all the quotes right. Here's what it would look like:


Paul,

Would you mind looking at my code to see if I am getting close to how the prepared statements work.
I know this code is not right because I am getting a SQL exception Count field incorrect. ( I am presuming I need to check the fields here)
But I just want to know if I am heading in the right direction.

Carlo Moore
Greenhorn

Joined: Aug 02, 2005
Posts: 27
Looks all good, except you don't need the result set, I'm sure there is an execute method that doesn't return a ResultSet.
Have a look at java api PreparedStatement you'll need to look at the methods inherited from Statement. If you're going to be running your SQL more than once, say in a loop, then use PreparedStatement as you can set your args in the loop. Otherwise Statement may be a better fit if you only need to run it once.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

You get the exception because you have 11 parameters (count the question marks in your statement), but only provide values for 10 of them. You need to add the 11th parameter with the original value of the Emp ID of the record you're updating (the one in the WHERE clause).

You should also use setter function of the proper type. I suppose the Emp ID column is declared as a a number, not as a string in your database. If this is true, you should use setInt (or maybe setLong) instead of setString to set its value. This way, you will convert the text entered by the user into a textbox in Java, and can catch and handle possible errors there. If you don't do that and the user enters a text that is not a number into the textbox, the conversion and subsequent error will happen in the database instead of your application, which is harder to catch and interpret.

There are some really minor issues:

1) It is good idea to separate code regarding UI interface from code dealing with database. Searching for "DAO Design Pattern" can bring up some interesting reading on this. In a course project that is not an issue, but it is good to get used to it soon.

2) You should put the close() statements of individual resources (rs, pst, con) into a finally section, so that they get properly closed even if an error occurs. Java 7 has a simplified support for this with its try with resources statement (google it up to get a better idea).

3) You're updating the Emp ID column to the same value it already has. It does practically no harm, except that it could confuse someone (including yourself after a longer time) who would get to modify that code.

I suppose this will be covered in your course. Other than this, you got it right!
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Martin Vajsar wrote:You get the exception because you have 11 parameters (count the question marks in your statement), but only provide values for 10 of them. You need to add the 11th parameter with the original value of the Emp ID of the record you're updating (the one in the WHERE clause).


Martin thank you for your post I am reading through Dao Design Pattern now.
I will be working on this to improve on what I have if possible.
Also I am taking your advice and changing a few of your minor points, I will post when I achieve this or when I think I have achieved this.

I must admit I am struggling to get rid of the Count Field error, I am not sure if I am understanding this correctly.
I have changed my code to this :

the error is the same , is this what you meant for me to do?
I'm trying to find some examples online but I can't seem to find anything that's similar. I am going through a couple of SQL tutorials now just to see if I can figure out where I am going wrong.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

You had a correct PreparedStatement before, but then you changed it to use string concatenation. Why did you do that?

And also, why did you add in the comma before the WHERE clause which makes it not valid SQL any more?
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Paul Clapham wrote:You had a correct PreparedStatement before, but then you changed it to use string concatenation. Why did you do that?

And also, why did you add in the comma before the WHERE clause which makes it not valid SQL any more?

Sorry Paul I edited this to answer your question with a little more detail.

I changed the statement after reading Martin's post
You get the exception because you have 11 parameters (count the question marks in your statement), but only provide values for 10 of them. You need to add the 11th parameter with the original value of the Emp ID of the record you're updating (the one in the WHERE clause).

I did not know what else to do so I was trying different scenarios obviously not the right one, I wonder why my instructor did not mention this
The comma was a mistake I left when I was trying to find a reason for the count field error.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

The error was that you didn't set the 11th parameter, right? Then why didn't you just add a line of code which set the 11th parameter?
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Because my sql is atrocious

There are only supposed to be 10 parameters (10 fields) not 11

Is this not saying set the fields [Emp ID]=?, [Emp Name]=?, Address=?, Suburb=?,PostCode=?, DOB=?, [Home Phone]=?, Extension=?, Mobile=?, Email=? where for example Emp ID = 100 to the new/changed values ?





Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8



You had that SQL, which looked right to me. There are 11 parameters, not 10 -- count the question marks for yourself. But you followed this up with code which only set the first 10. Another line of code to set the last parameter would be extremely simple, except I think you are over-thinking things. Here's the part of the SQL which contains the 11th parameter:

You should be able to write a line of code which sets that parameter very easily. Do that.
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Paul,

Firstly thank you for your patience, secondly I hope I don't frustrate you to much with this post.

I am unsure as to what I need to add, I think I know what you are saying but I am somewhat confused please bear with me here:

my code :

the numbers in the

statements refer to the column in the database table correct?

I have 10 columns or fields in that table.
But I do have 11 parameters as you say,
if I were to add :


I would still receive the same error because I only have 10 fields .
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Nick, the parameters do not correspond to fields in the table, but to individual question marks in your query. Therefore you have 11 parameters in your last query and you need need to set them all.
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
I am not sure what to set the 11th parameter to because it will be different each time would I just set it as the first?

like so?


Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Nick de Waal wrote:I am not sure what to set the 11th parameter to because it will be different each time would I just set it as the first?

Since you already wrote the original, non-parametrized statement, you simply replace literals (which came from your text boxes) with question marks and use the setXXX methods to provide values for individual parameters (using the same values from your text boxes). This is a purely mechanical exercise, you only have to be careful and use the correct setXXX method (the correct type) - if you, for example, use setString() to define a value for numeric field, it will cause an implicit conversion in your database to happen, which is error prone and in some databases might expose you to some of the more exotic forms of SQL injection attack.

However, there is one peculiarity in your original statement, which is probably the source of confusion here. Look again at this code of yours, especially at the [Emp ID] field:

You're setting Emp ID to the value of the txtEmpNo textbox in all records that happen to have the Emp ID field equal to the txtEmpNo textbox. Therefore, nothing happens to the value of Emp ID, though, depending on the database, this could have some effects (eg. some databases might fire a trigger for this kind of update, badly coded triggers might cause further problems with this). It is therefore best to avoid these "empty updates", as it also can cause confusion about the intents of the original author of the code, since there are at least two possible interpretations of this situation:

1) The user should be able to change the value of the Emp ID field. In this case, you need to save the original value of the Emp ID field in a variable and use it in the WHERE clause of your statement, the value from the textbox will be used only to set the new value for the field.

2) The user is not supposed to change the Emp ID field. If this case, the textbox should be disabled, to indicate to the user he cannot change its value, and the Emp ID field should be dropped from the list of updated columns in the SET clause (it will remain in the WHERE clause, of course). It would be a good idea to store the original Emp ID value in a variable as in the previous case, though.

Hope this clears it up a bit.
Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42
Martin Vajsar wrote:
Nick de Waal wrote:I am not sure what to set the 11th parameter to because it will be different each time would I just set it as the first?

Since you already wrote the original, non-parametrized statement, you simply replace literals (which came from your text boxes) with question marks and use the setXXX methods to provide values for individual parameters (using the same values from your text boxes). This is a purely mechanical exercise, you only have to be careful and use the correct setXXX method (the correct type) - if you, for example, use setString() to define a value for numeric field, it will cause an implicit conversion in your database to happen, which is error prone and in some databases might expose you to some of the more exotic forms of SQL injection attack.

However, there is one peculiarity in your original statement, which is probably the source of confusion here. Look again at this code of yours, especially at the [Emp ID] field:

You're setting Emp ID to the value of the txtEmpNo textbox in all records that happen to have the Emp ID field equal to the txtEmpNo textbox. Therefore, nothing happens to the value of Emp ID, though, depending on the database, this could have some effects (eg. some databases might fire a trigger for this kind of update, badly coded triggers might cause further problems with this). It is therefore best to avoid these "empty updates", as it also can cause confusion about the intents of the original author of the code, since there are at least two possible interpretations of this situation:

1) The user should be able to change the value of the Emp ID field. In this case, you need to save the original value of the Emp ID field in a variable and use it in the WHERE clause of your statement, the value from the textbox will be used only to set the new value for the field.

2) The user is not supposed to change the Emp ID field. If this case, the textbox should be disabled, to indicate to the user he cannot change its value, and the Emp ID field should be dropped from the list of updated columns in the SET clause (it will remain in the WHERE clause, of course). It would be a good idea to store the original Emp ID value in a variable as in the previous case, though.

Hope this clears it up a bit.


Thanks Martin,

Its been a while but i have been away, I have decided to try this again, after reading the above I have changed the code (see below)
but I am still receiving the count field incorrect error
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19651
    
  18

The order of question marks matters. You are trying to set the employee ID as the first parameter, but it should be the last (10th).


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
jQuery in Action, 2nd edition
 
subject: SQL Exception:General Error
 
Similar Threads
How to retrieve the first record in a database table
Iterate Hashmap through struts
I have a ClassCastException as well
how to use scope in target version of c:set