sql error

[dethfire]

"INSERT INTO clients (username, password, yahoo_im, icq_im, aol_im, msn_im, sex, zipcode, country, birthdate) VALUES ('" + username + "','" + password + "','" + yahoo + "','" + icq + "','" + aol + "','" + msn + "','" + sex + "','" + zipcode + "','" + country + "','" + birthdate + "')";

error saying the intsert syntax is wrong

[DaveMaxwell]

Username and password are reserved words in SQL. Surround the fields in brackets([ and ]) and the SQL should run. I would suggest renaming those two fields, however since they are reserved words.

[r937]

dave, username and password are okay column names in some databases -- but your point is valid, they could be renamed

brackets are not okay in some databases, doublequotes are necessary

the problem may also be the date string in singlequotes -- for example, microsoft access requires date strings in octothorps (see http://pages.zoom.co.uk/leveridge/dictionary.html#O)

rudy

[DaveMaxwell]

dave, username and password are okay column names in some databases -- but your point is valid, they could be renamed

brackets are not okay in some databases, doublequotes are necessary

the problem may also be the date string in singlequotes -- for example, microsoft access requires date strings in octothorps (see http://pages.zoom.co.uk/leveridge/dictionary.html#O)

rudy

Yeah, I made the mistake of assuming access/SQL Server where username/password are reserved words and the brackets are what you use to override.

I did forget about the date bit with access. I usually keep my dates as strings just so I don't need to remember to change them when I convert to the different database types. Thanks for the reminder.

[dethfire]

yeah my dates are in a string too. I still get something wrong with the INSERT syntax

[DaveMaxwell]

Try moving the INSERT statement into a variable and display that variable instead of executing it. You may see something in one of the values that you're not expecting.

[dethfire]

Here is an example of an output

INSERT INTO clients (user, pass, yahoo_im, icq_im, aol_im, msn_im, sex, zipcode, country, birthdate) VALUES ('greg','hi','yo','','','','Male','12','12','12')

I scaled it down and this even returns a syntax error:

INSERT INTO clients (user) VALUES ('" + usern + "')

[DaveMaxwell]

OK, do you possibly have any fields that are required that no value is getting assigned to? Or have a field that has only specific values allowed when you insert? And make sure the datatypes are correct.

[r937]

dethfire, what database is this? you never said, and as you may know, it does make a difference

your first query used username, which is okay, but user is a reserved word in many databases

rudy

[dethfire]

hi, it's access and I have an autonumber field. thanks!!

[r937]

okay, i created an access table using this --

create table clients ( apk counter, user text(20) )

then i inserted a bunch of rows with this --

insert into clients (user) values ('curly')
insert into clients (user) values ('larry')
insert into clients (user) values ('moe')

then i quickly displayed the table like this [see note] --

select * from clients

and got results like this --

apk user
1 curly
2 larry
3 moe

so i really don't understand what kind of trouble you're having

rudy

[note] one of the few situations i would ever use "select star"

[dethfire]

me either, whatever I do I get a syntax error. maybe my database is messed up?

I think so, because whenever I run my asp.net program and then get the error I can't then open the database even with the asp.net app closed and there is no lbd file. It always says someone else is using it or something. So I have to restart and then I can open until I run the app again.

[dethfire]

so I built a new database, now when I try to run the app I get this error "Operation must use an updateable query."

[dethfire]

ok this is getting really weird. I try this query INSERT INTO clients (username) VALUES ('gregg')";

and it works! BUT

If I try to add another thing it breaks, example:

INSERT INTO clients (username, password) VALUES ('gregg','hi')";

[//andrew-p//]

Don't know really what is going on--can't say anything other than to check the datatypes on all of your fields and make sure they agree with your query.

A list of Access 2000 reserved words if you should ever need it:
http://support.microsoft.com/default...NoWebContent=1