asked on

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

bobbit31

you need to include what cols you want to update in your insert clause:

insert into User(field1, field2, field3, field4, field5, field7) VALUES (...)

kennethxu

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();

kennethxu

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 :-)

kennethxu

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.

bobbit31

kenneth:

no problem, your post is much more descriptive and i agree that a prepared statement would be the best route to take...

kennethxu

if you have int data, use:
ps.setInt(6, bnSecurityId );

kennethxu

thanks bobbit :)

Juggy1

ASKER

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

bobbit31

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();

ASKER CERTIFIED SOLUTION

kennethxu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Juggy1

ASKER

Hi Bobbit31 and kennethxu

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

Juggy

Juggy1

ASKER

Thanks alot for your help.

Juggy

kennethxu

Thanks Juggy.
would you consider to split points to bobbit? he gaves the correct insert syntax in the first place.

bobbit31

no, don't worry about it kenneth, you deserve the points... glad you got it working juggy

Juggy1

ASKER

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

bobbit31

sure

Urgent: MySQL &amp; JDBC Error in JSP

Urgent: MySQL & JDBC Error in JSP