?
Solved

Urgent: MySQL & JDBC Error in JSP

Posted on 2003-03-17
16
Medium Priority
?
295 Views
Last Modified: 2010-04-01
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
Comment
Question by:Juggy1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
16 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 8154048
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
 
LVL 14

Expert Comment

by:kennethxu
ID: 8154139
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
 
LVL 14

Expert Comment

by:kennethxu
ID: 8154176
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Expert Comment

by:kennethxu
ID: 8154232
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
 
LVL 18

Expert Comment

by:bobbit31
ID: 8154263
kenneth:

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

Expert Comment

by:kennethxu
ID: 8154586
if you have int data, use:
ps.setInt(6, bnSecurityId );
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8154589
thanks bobbit :)
0
 

Author Comment

by:Juggy1
ID: 8157880
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
 
LVL 18

Expert Comment

by:bobbit31
ID: 8159522
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
 
LVL 14

Accepted Solution

by:
kennethxu earned 600 total points
ID: 8159530
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
 

Author Comment

by:Juggy1
ID: 8160110
Hi Bobbit31 and kennethxu

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

Juggy
0
 

Author Comment

by:Juggy1
ID: 8160118
Thanks alot for your help.

Juggy
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 8160210
Thanks Juggy.
would you consider to split points to bobbit? he gaves the correct insert syntax in the first place.
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 8160393
no, don't worry about it kenneth, you deserve the points... glad you got it working juggy
0
 

Author Comment

by:Juggy1
ID: 8161234
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
 
LVL 18

Expert Comment

by:bobbit31
ID: 8161285
sure
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Check out the latest tech news, community articles, and expert highlights in August's newsletter.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question