Error in Insertion clause

[JohannIcon]

Dear All, I have an error in the insertion clause which I cannot find. Here is what the browser is displaying:-

INSERT INTO Users (Name,Surname,Email,Password,LastPollVoted,DateRegistered) VALUES ('Johann','Montfort','johann@icon.com.mt','QuizGame',0,'01/01/2000');

What I am also thinking of is that every row has an Autonumber, called UserId, and is the primary key. How can I insert that?

Thanks for your help

 

[Tarwn]

You should never attempt to insert into an autonumber field, databases will not allow it. If you are using MS Access (I am assuming due to AutoNumber field) than your dates should be surrounded by #s instead of single quotes:

INSERT INTO Users (Name,Surname,Email,Password,LastPollVoted,DateRegistered) VALUES ('Johann','Montfort','johann@icon.com.mt','QuizGame',0,#01/01/2000#);

you may also have problems with the field named "Name" as it may be a reserved word, I generally assume everything is a reserved word in Access b/c it is truly a pain
Hope that helps,
-Tarwn "The problem with a kludge is eventually you're going to have to back and do it right." - Programmers Saying (The Wiz Biz - Rick Cook)
"Your a geek!" - My Girlfriends saying

 

[JohannIcon]

I still have the same problem when I inserted the # tags:-

INSERT INTO Users (Name,Surname,Email,Password,LastPollVoted,DateRegistered) VALUES ('johann','montfort','johann@icon','PNQuizGame',0,#9/24/2002#);

I don't think it is the name field that is creating problems since I have another database with the same fields, ie instead of a field named Name, I have it as name in the other database and insertion is working fine!

To be more clear, I am doing this insertion clause in the other database :-

sql1 = "INSERT INTO contestants (name,surname,eMail) VALUES (" & PerName & "," & PerSurname & "," & PerEmail & ""
and it is working fine!!!

 

[EugenePaliev]

Can you post the error message and say what DataBase you are working with? Good Luck!

 

[JohannIcon]

Yeah sure

I am using an Access database and the error is :-

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.

 

[EugenePaliev]

What I usually do in such cases - drop all fileds from the sql and leave only one. Try it. If it works add another one. If it's still OK add third one... It will tell you what field you have troubles with. In my opinion the problem occurs with DateRegistered field... Good Luck!

 

[JohannIcon]

Yeah good idea! I will try that and give you my results. I think the date is the problem too, hope to find a solution to insert it in the database

 

[JohannIcon]

Ok I found something

The name, surname and e-mail address are ok when inserted, but the other fields, as I am hardcoding them, it is not accepting them

What i am doing is in the beginning, when I retreive the name, surname and e-mail address, I hardcode the other vars like this:-

PerName = "'" & request.Form("issem&quot & "'"
PerSurname = "'" & request.Form("kunjom&quot & "'"
PerEmail = "'" & request.Form("email&quot & "'"
PerPassword = "'" & "PNQuizGame" & "'"
PerPoll = 0
PerDateReg = Date()


Then I do the insert
sql1 = "INSERT INTO Users (Name,Surname,Email,Password) VALUES (" & PerName & "," & PerSurname & "," & PerEmail & "," & PerPassword & ""


and it is giving me the problems. How can I hardcode things in the insert clause?

 

[EugenePaliev]

What data type is Password field in the Access database? Good Luck!

 

[JohannIcon]

The data type of the password field is text

 

[EugenePaliev]

OK, what is the size of the field? Why I'm asking 'cause your code looks OK. But there must be something wrong. It could be field type oe its size. If size is OK, try to insert only one field - password. If it still doesn't work - post the entire code... Good Luck!

 

[JohannIcon]

Don't worry if you ask questions, I appreciate a lot!

Well the size is 50, so it is supposed to be ok. I will try to insert one field now then.

 

[EugenePaliev]

I think that 50 should be enough. Try inserting into one field only. Good Luck!

 

[JohannIcon]

Hi Eugene,

Goodmorning, I tried to insert into only one field, ie the password and it gave me problems. So it must be the password field. But I cannot figure out why!

 

[JohannIcon]

Ok I confirmed now, it is the password field that is giving me the trouble and it is quite weird. I can't figure out why!

 

[JohannIcon]

I think i know what the problem is. Since the Password field is a reserved word, it is not allowing me to insert anything. Now I cannot change the Password name of the field since it is being used in another system. Instead of using an "INSERT" SQL statement which inserts a field, how can I do that with rs.addnew? Is it simple? Are there any tutorials?

Thanks for your help

 

[EugenePaliev]

Hi Johann,

It's really possible that "Password" is reserved word. I don't know it for sure but it's very possible. There was once very similar situation with a field called Date - it was created OK, but it was impossible to insert any value into this field...

The only thing is to create another field and call it something different (ie UserPassword) or change existed field name (but you said it's not possible). Try to delete Password and create new one. Anyway, the best solution is to have right field with a right name, forget about other things such as AddNew method and others - you must have right names!!! ))))))))) Good Luck!

 

[jen89]

I have another suggestion that you could try. It is possible to insert a record without specifying the field names if you use every single field. For Example,

INSERT INTO Users VALUES ('johann','montfort','johann@icon','PNQuizGame',0,#9/24/2002#);

You could see if this would work. Just hardcode the word "null" in for any field that you aren't adding.
If you had an extra field at the end called bday that you didn't want to populate, you would write

INSERT INTO Users VALUES ('johann','montfort','johann@icon','PNQuizGame',0,#9/24/2002#,null);

It is a good idea anyways to check all of your request variables for empty strings and assign "null" to the variable if you find one.

if request.Form(&quot;issem&quot;) <> &quot;&quot; then
  PerName = &quot;'&quot; & request.Form(&quot;issem&quot;) & &quot;'&quot;
else
  PerName = &quot;null&quot;
end if

hope this helps

 

[JohannIcon]

Thanks for all your help people. I finished up using the rs.Addnew syntax and it worked fine for me.

Thanks again