JDBC problem with prepared statements

[Sonny101]

Hi all,

I am trying to use a prepared statement for the first time and I am getting confused with parameters. The idea is that a GUI opens with 10 fields filled in, now somebody can update one or more fields and this will be updated to an Access Database table called empDetail.

I keep receiving a count field error,
I have posted this on another forum also but I am not sure if I am allowed to post the link to it here. I am looking at various examples online but cannot find a similar example.
there are examples with setting the parameters of one or two but not with getting them from user input.
My code may have a few problems but I am trying to understand the proper way to use them.
I have been told by my instructors to just not use it but I am sure this will come up in the exam.

my code is as follows(I will be changing the minor issues when I can get the statement to work.:

private void btnUpdateActionPerformed(java.awt.event.ActionEvent evt) {                                          
      
       // ResultSet rs = null;
        openDatabase();
        //int EmpNo = Integer.parseInt(txtEmpNo.getText());

        try{
            con.setAutoCommit(false);
             PreparedStatement pst = con.prepareStatement("UPDATE" +
            " empDetail SET [Emp ID]=?" +
            " [Emp Name]=?, 'Address'=?, 'Suburb'=?,'PostCode'=?," +
            "'DOB'=?, [Home Phone]=?, 'Extension'=?, 'Mobile'=?," +
            " 'Email'=? WHERE [Emp ID]=?");

            pst.executeUpdate();
            //pst.setInt(1,Integer.parseInt(txtEmpNo.getText()));
            pst.setString(1,txtEmpNo.getText());
            pst.setString(2,txtName.getText());
            pst.setString(3,txtAddress.getText());
            pst.setString(4,txtSuburb.getText());
            pst.setString(5,txtPostCode.getText());
            pst.setString(6,txtDOB.getText());
            pst.setString(7,txtPhone.getText());
            pst.setString(8,txtWorkExt.getText());
            pst.setString(9,txtMobile.getText());
            pst.setString(10,txtEmail.getText());
            pst.setString(11, txtEmpNo.getText());
            //pst.setInt(11,Integer.parseInt(txtEmpNo.getText()));
            
            



            //rs = pst.executeQuery();
           // while(rs.next()){
            con.commit();
            con.setAutoCommit(true);

            
            msgMessage.showMessageDialog(this,"Record updated successfully\n");
          //  rs.close();
            pst.close();
            con.close();

        }
        catch(SQLException e){
            msgMessage.showMessageDialog(this, e.toString());
        }
        closeDatabase();

Any help will be appreciated. The reason why I am re-posting is because I have to finish this today so If I cannot find a solution I will just use a SQL statement which I can get to work.
Thanks all

[StephNicolaou]

Line 27 isn't needed. Has this been solved already now that this is 2 days old?

[Sonny101]

Line 27 isn't needed. Has this been solved already now that this is 2 days old?

I am still receiving a count field error with or without line 27.

[jbennet]

How many fields does the table empDetail have, and what are thier datatypes?

[Sonny101]

How many fields does the table empDetail have, and what are thier datatypes?

Hi,

10 fields in the table all text except empNo which is number

[jbennet]

Well, then "pst.setString(11, txtEmpNo.getText());" is wrong.. if its an int, you need to do setInt() and cast the string from .getText() to an int.

[Sonny101]

Well, then "pst.setString(11, txtEmpNo.getText());" is wrong.. if its an int, you need to do setInt() and cast the string from .getText() to an int.

yes thanks I know (see my commented out code) but its not the reason I am getting the count field error, have tried already.

[StephNicolaou]

Can you please put line 15, pst.executeUpdate(); on line 29, after your setString's?
Try that and let us know the exact error message.

[Sonny101]

Can you please put line 15, pst.executeUpdate(); on line 29, after your setString's?
Try that and let us know the exact error message.

hi,

have done as you said now I am getting a different error message

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Pa_RaM000 [Emp Name]=Pa_RaM001'.

[javaAddict]

As the error message says, you have a syntax error at your query. You must first write your queries in an sql editor, run it with some values and then take it and put it into your code. In your case it seems that you forgot a comma:

"UPDATE empDetail SET [Emp ID]=? [Emp Name]=?,"

At the above part of your query you are missing a comma after the first '?'

It should be:

"UPDATE empDetail SET [Emp ID]=? , [Emp Name]=? , ......." and the rest of your query

[Sonny101]

As the error message says, you have a syntax error at your query. You must first write your queries in an sql editor, run it with some values and then take it and put it into your code. In your case it seems that you forgot a comma:

"UPDATE empDetail SET [Emp ID]=? [Emp Name]=?,"

At the above part of your query you are missing a comma after the first '?'

It should be:

"UPDATE empDetail SET [Emp ID]=? , [Emp Name]=? , ......." and the rest of your query

Thanks

have added the comma as you say now recieve this error:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 18.

I think I will give this up now and not use the prepared statement it seems to me to be more trouble then it is worth, if I just use an sql statement all works fine so I will just do that, since I will be receiving new and different error messages every time I do change something , my original error was a count field error and I still have not understood why I receive that but thanks to you all

[javaAddict]

Thanks

have added the comma as you say now recieve this error:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 18.

I think I will give this up now and not use the prepared statement it seems to me to be more trouble then it is worth, if I just use an sql statement all works fine so I will just do that, since I will be receiving new and different error messages every time I do change something , my original error was a count field error and I still have not understood why I receive that but thanks to you all

No you should use PreparedStatements. The the error that you get is very simple. Have you tried reading it? Or doing some search on what it means. When you use '?' at the statement you must have as many paramaters at your PreparedStatement. Count how many '?' you have and how many parameters do you set. And then if it still doesn't work repost your latest code.

[anuj6928]

whats is the actual error u r receiving ....

[Sonny101]

If you know of anywhere that has a good explenation of the prepared statement as I want to use it please send me a link, as I have
tried searching, I have asked my IT instructors who tell me to use the sql statement because they cannot find the problem, I do not know
how to set more parameters, in my post I have all the code that I have tried. I have tried setting 11 parameters because there are 11 question marks but
this is also wrong. In my original post I have said what needs to happen but for example the empNo field if it is 100 it needs to stay as 100 and not change to a
different value because for example I need to update their address etc I am a beginner but it seems to me if my instructors cannot explain what I need to do and I
cannot find a similar explenation online than I may need to wait until I find some book that will explain things better.

[aloncito]

UPDATE empDetail SET [Emp Name] = ?, Address = ?, Suburb = ?, PostCode =?, DOB = ?, [Home Phone] = ?, Extension = ?, Mobile =?, Email=? WHERE [Emp ID]=?");

       try{            
        pst.setString(1,txtName.getText());
        pst.setString(2,txtAddress.getText());
        pst.setString(3,txtSuburb.getText());
        pst.setString(4,txtPostCode.getText());
        pst.setString(5,txtDOB.getText());
        pst.setString(6,txtPhone.getText());
        pst.setString(7,txtWorkExt.getText());
        pst.setString(8,txtMobile.getText());
        pst.setString(9,txtEmail.getText());
        pst.setString(10, txtEmpNo.getText());  //or pst.setInt(10,Integer.parseInt(txtEmpNo.getText()));
        pst.executeUpdate();
         JOptionPane.showMessageDialog(null, "Update");
          }
         catch (SQLException | HeadlessException e){
        JOptionPane.showMessageDialog(null, e);}