[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

Urgent: MySQL & JDBC Error in JSP

Hello All

I was wondering if anyone can help me I have this table:

create table User
(
dbUserId integer not null primary key auto_increment,
dbFirstNames varchar(50) not null,
dbSurName varchar(50) not null,
dbPassword varchar(12) not null,
dbEmail varchar(50) not null,
dbFaculty varchar(50) not null,
dbSecurityId integer not null references Security(dbSecurityId),
dbAuthorId integer not null references Author(dbAuthorId) (I don't want to use this column, Its for checking only)
)
;

which as you can see have 3 keys, 2 of them are refernced.
However, when I want to insert values into my database, I get this error:

"failed.java.sql.SQLException: General error: Column count doesn't match value count at row 1"

I understand the error its due to my table has 8 columns, but I only want to use 7 columns.
Here is the code for my JDBC connection:

String sqlQuery = "INSERT INTO User VALUES ('?' '" +
                        bnFirstname + "', '" + bnSurname + "', '" +
                        bnPassword + "', '" + bnEmail + "', '" +
                        bnFaculty + "', '" + bnSecurityId + "')";
                   myStatement.executeUpdate(sqlQuery);

Technologies I'm using are:

1. MySQL 3.23.5
2. Tomcat 4.0.4
3. JSP 1.2

Please can anyone help to me fix this problem or give some tips.

Thanks

Regards
Juggy
0
Juggy1
Asked:
Juggy1
  • 7
  • 5
  • 4
1 Solution
 
bobbit31Commented:
you need to include what cols you want to update in your insert clause:

insert into User(field1, field2, field3, field4, field5, field7) VALUES (...)
0
 
kennethxuCommented:
you have 2 problems here contribute to the error you get,

problem 1.
>> dbAuthorId integer not null references Author(dbAuthorId) (I don't want to use this column, Its for checking only)
this column definition means you must give a value to this column becuase it is "not null", and the value you supplied must exists in Author table. change it to

dbAuthorId integer references Author(dbAuthorId)

problem 2.
the sytax you used to insert need finetune:

INSERT INTO User ( dbFirstNames, dbSurName, dbPassword, dbEmail, dbFaculty, dbSecurityId ) VALUES ("'" +
                       bnFirstname + "', '" + bnSurname + "', '" +
                       bnPassword + "', '" + bnEmail + "', '" +
                       bnFaculty + "', '" + bnSecurityId + "')";

the above will still fail if any of bnXXX string contains single quote character ('). So you'd better to use PreparedStatement for insert task.

String sqlQuery = "INSERT INTO User ( dbFirstNames, dbSurName, dbPassword, dbEmail, dbFaculty, dbSecurityId ) VALUES ( ?, ?, ?, ?, ?, ? )"

PreparedStatement ps = conn.prepareStatement( sqlQuery );
ps.setString(1, bnFirstname );
ps.setString(2, bnSurname );
ps.setString(3, bnPassword );
ps.setString(4, bnEmail );
ps.setString(5, bnFaculty );
ps.setString(6, bnSecurityId );
ps.executeUpdate();
0
 
kennethxuCommented:
Just a reminder, you have some open questions that you didn't followup and two B grades in your profile. all those could affect your ability to get quick and quality responses from top experts in further :-)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
kennethxuCommented:
sorry bobbit31, didn't see your post. I cannot believe that I spent 12 minutes to type those, must be EE delay or cache problem again.
0
 
bobbit31Commented:
kenneth:

no problem, your post is much more descriptive and i agree that a prepared statement would be the best route to take...
0
 
kennethxuCommented:
if you have int data, use:
ps.setInt(6, bnSecurityId );
0
 
kennethxuCommented:
thanks bobbit :)
0
 
Juggy1Author Commented:
Thanks for the answer it can successfully add to my db. I am really grateful.

Just one more question, I have the security table:

create table Security
(
dbSecurityId integer not null primary key,
dbSecurityStatus varchar(20) not null
)
;

Also I have already inserted the values in:
INSERT INTO Security Values (0,'Adminstrator');
INSERT INTO Security Values (1,'Guest');

When I insert the values of user into my database, I use the  SecurityId as reference so to give a user a status.
I want to auotmatically give them a guest status, how could I do this?

Here is my code:
public void setBnSecurityId(int argSecurityId)
     {
          argSecurityId = 1;
          bnSecurityId = argSecurityId;
     }

This what I tried but I cannot grab the guest value out of Security table and associate it with the user.
Of course, the administrator is me.

Please could you help, I would be grateful for your tips and guidance.

Thanks again.
Juggy
0
 
bobbit31Commented:
run two queries... first one to get the id of the 'Guest' level and second one to do insert (pseudo-code):

select dbSecurityID from Security where dbSecurityStatus = 'Guest'

int secID = rs.getInt("dbSecurityID");

...

ps.setInt(6, secID);
ps.executeUpdate();
0
 
kennethxuCommented:
If I understand you correctly, I think you need this in your user bean:

private bnSecurityId = 1;
public void setBnSecurityId(int argSecurityId)
    {
         bnSecurityId = argSecurityId;
    }

this way, the security id in user bean will default to 1. and you can change it later by calling the setter method.
0
 
Juggy1Author Commented:
Hi Bobbit31 and kennethxu

Thanks a lot you have been a great help to my work on JDBC & MySQL in JSP.

Juggy
0
 
Juggy1Author Commented:
Thanks alot for your help.

Juggy
0
 
kennethxuCommented:
Thanks Juggy.
would you consider to split points to bobbit? he gaves the correct insert syntax in the first place.
0
 
bobbit31Commented:
no, don't worry about it kenneth, you deserve the points... glad you got it working juggy
0
 
Juggy1Author Commented:
Hi Bobbit31

Maybe you can help me on another question, I will post it very soon. I'm sorry I should have split the points.
Is that cool?

Juggy
0
 
bobbit31Commented:
sure
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 7
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now