Help with sql insert single quotes
String insert = "INSERT INTO users(firstName, lastName, emailAdd, password) VALUES("+ firstNameForm + "," + lastNameForm + "," + emailForm + "," + passwordForm + ")";
Statement stmt = conn.createStatement();
int ResultSet = stmt.executeUpdate(insert);
I have that sql insert statment in my servlet the servlet compiles fine but does not insert into the users table, i have been told that it is something to do with single quotes in sql statement, can anybody help me out?
[498 byte] By [
ajrobsona] at [2007-11-26 13:11:58]
# 1
You could add the single quotes like ( '"+ firstNameForm + "' ,Or you could use a preparedstatement http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html
# 2
Thanks that worked here have some duke dollars (never even looked up what they are what they do to be honest) Now i have another problem though, the users table as a coloum called user_id which is an int the default is 0.
my problem is now when i try to insert data it won't let me as there is already data in user_id 0. I tested the servlet by setting user_id to 3 and it worked, so my question is how would i make this servlet automatically increase user_id each time it is called?
Or can i change my sql table is there a autonumber which would increase everytime this servlet runs?
# 3
What database are you using?
# 5
i'm using a mysql database
# 6
Use
the following query,
SELECT MAX(USER_ID) + 1 from USER_TABLE
and insert the value to the table using servlet.
INSERT INTO users(firstName, lastName, emailAdd, password) VALUES('"+ firstNameForm + "','" + lastNameForm + "','" + emailForm + "','" + passwordForm + '"
If ware to use this I would have written a trigger to do this for me.
# 7
DO NOT concat data in to sql string. This can lead to several problems including security issues.When you want to include data in a query always use PreparedStatement
LRMKa at 2007-7-7 17:28:16 >
# 8
>Or can i change my sql table is there a autonumber which would increase everytime this servlet runs?
make your field autoincrement :-)
example
ALTER TABLE `users` CHANGE `user_id` `user_id` INT( 10 ) UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT
To insert record in the table.
example:
you have a table test and got two fields,
id = (INT) autoincrement
name = VARCHAR / TEXT etc.
to insert data to the table test try something like this:
String SQLStatement = "INSERT INTO test";
SQLStatement += "(name)";
SQLStatement += " VALUES (?)";
statement = Conn.prepareStatement(SQLStatement);
statement.setString(1, "Duke");
statement.executeUpdate();
statement.close();
Conn.close();
Note we dont provide the field for id on our sql statement since it is set as auto-increment ;-)
regards,
Message was edited by:
jie2ee
# 9
Thank i used the alter table method but when i tried to registar a user the tomcat screen says "com.mysql.jdbc.MysqlDataTruncation: Data truncation: out of range value adjusted for column 'user_id' at row 1"
Any ideas what that means lol is it to do with the fact that there was data already in the table before i chnaged the user id to a auto increment?
# 11
hi :-)
sorry, for the delayed reply, i've just read your post.
note that i used the phpmyadmin in altering the table.
you can use that also but if want to do it manually kindly
check the sql manual on mysql. thank you :-)
sorry again for the delayed reply :-(
regards,
# 12
hi :-) http://dev.mysql.com/doc/refman/5.0/en/alter-table.htmlregards,
# 13
hi, when i tried using alter table it kept saying there was an error in the sql so i ended up dropping that coloumn and making a new one by doing,
ALTER TABLE users ADD user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (user_id);
i then made that the primary key but i still get the error i put above. any ideas?
# 14
Judging by your error message it looks like MySQL dont like to reduce the column length once the column is created.The default length of INT is 11.
LRMKa at 2007-7-7 17:28:16 >
# 15
yeah the column length is 11 that row looks like this:
fieldtypenullkeydefaultextra
user_idint(11)noprinullauto_increment
so how can i sett the default to 0? i tried
alter table users
alter user_id set default 0;
it gave no errors but did not change anything.
# 16
to create the user_id coloumn is did
alter table users
add user_id
int not null
auto_increment,
add index (user_id);
This gave me a int of 11 if i used unsigned it came 10, has any body got any ideas why i get the data truncation error when i try to enter data?
# 17
any one got any ideas? also why can't i chnage the defautl i tried using set default on the user_id and it just said no rows affected?
# 18
hi :-)have you already got it?if not, can you post your sql script for creating your table (not yet auto-increment)so i can try to alter the column :-)thank you.regards,
# 19
Hi I got it working, i was making a simple mistake i looked at the code for ages and didn't see anything up with it, a day later i literally just sat at my desk and knew what was wrong I hate when that happens lol.
If you or anybody reading this wants to help me I have another topic going http://forum.java.sun.com/thread.jspa?threadID=5118841&tstart=0 i'm trying to make a catalog servlet but am very stuck!!
# 20
bumpsorry i bumped the wrong one, firefox tabs are really good but can be really bad lol Message was edited by: ajrobson
