SitePoint Sponsor

User Tag List

Results 1 to 15 of 15

Thread: sql error

  1. #1
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql error

    Code:
    "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
    Free Science Homework Help
    http://www.physicsforums.com

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by 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
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  5. #5
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah my dates are in a string too. I still get something wrong with the INSERT syntax
    Free Science Homework Help
    http://www.physicsforums.com

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  7. #7
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 + "')
    Last edited by dethfire; May 8, 2003 at 14:24.
    Free Science Homework Help
    http://www.physicsforums.com

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    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.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

  10. #10
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi, it's access and I have an autonumber field. thanks!!
    Free Science Homework Help
    http://www.physicsforums.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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"

  12. #12
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by dethfire; May 8, 2003 at 20:26.
    Free Science Homework Help
    http://www.physicsforums.com

  13. #13
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so I built a new database, now when I try to run the app I get this error "Operation must use an updateable query."
    Free Science Homework Help
    http://www.physicsforums.com

  14. #14
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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')";
    Free Science Homework Help
    http://www.physicsforums.com

  15. #15
    SitePoint Zealot //andrew-p//'s Avatar
    Join Date
    Dec 2002
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    "When the ship misses the harbor, it is seldom the fault of the ship, and it is never the fault of the harbor..."
    //think of that when you get an error


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •