SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: SQL woes

  1. #1
    SitePoint Zealot willmoss's Avatar
    Join Date
    Apr 2002
    Location
    London, England
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy SQL woes

    Hey guys,

    Could someone help me? When I run the following INSERT query on an access 2002 db, I get a syntax error

    Code:
    INSERT INTO meetings SET uid=3, pid=2, summary='asd', body='asdasd', date=#9/12/2004#, time=#8:45 AM#, imgfn='img.jpg', wpfn=''
    the columns "date" and "time" are both set to hold date/time - the column "date" is Short Date format and "time" is Medium Time format.

    This is so frustrating. aaaaargh!

    Cheers,

    - Will

  2. #2
    <? james('rules'); ?>
    Join Date
    Jun 2004
    Location
    Wales, UK
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What does the syntax error say? Quote it.

  3. #3
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think your problem is combining your INSERT and UPDATE syntax. Also, I think 'date' and 'time' are reserved names, which could be throwing an error. Try this instead:

    Code:
    INSERT INTO meetings
        (uid, pid, summary, body, [date], [time], imgfn, wpfn)
        VALUES (3,2,'asd','asdasd',#9/12/2004#,#8:45 AM#,'img.jpg','')

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that INSERT INTO SET syntax is from mysql,
    and it is an abomination

    while you are renaming your date and time columns
    (the advice from wes was, dare i say it, right on de money ),
    you might also consider using just one column, since every
    datetime column in access carries a time component anyway
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot willmoss's Avatar
    Join Date
    Apr 2002
    Location
    London, England
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah thanks - I thought SQL was universal independent of databases used? obviously not

    what's wrong with INSERT INTO... SET? I use it with mySQL because it means that if you change the position of a column (ie. if you add or remove a column which occurs before the one you want) it doesn't affect your SQL queries.

    I know this isn't really SQL, but it follows on from my initial question ..

    How do you change a medium time to a short date in ASP? Could you give me the relevant functions?

    Really grateful for your help; thanks everyone!

    - Will

  6. #6
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by willmoss
    ah thanks - I thought SQL was universal independent of databases used? obviously not

    - Will
    SQL is an IBM invention, and lots of companies thought "we can improve it with a few extra commands over here, and we don't need those other commands over there (or can't get them to work properly at present)"

  7. #7
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by willmoss
    what's wrong with INSERT INTO... SET? I use it with mySQL because it means that if you change the position of a column (ie. if you add or remove a column which occurs before the one you want) it doesn't affect your SQL queries.
    No, because you provide the attribute list in the statement, right? e.g.
    INSERT INTO foo( col1, coll2 ) VALUES( 12345, 'Happy day' )


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
  •