#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Location
    Sydney
    Posts
    33
    Rep Power
    12

    "Syntax error with insert into statement"


    I am interacting with my database by way of asp and ado...unfortunately, it seems i have a problem with the following query - my browser reports an error "syntax error with insert into statement"....

    any ideas on why this might be????? the database is Access


    insertstr = "insert into Project(ProjectName, Department, Priority, Start_date, Est_end_date, IT_proj_mgr, Bus_mgr, Bus_owner) Values('" & proj_name & "', " & department & ", '" & priority & "', #" & begdate & "#, #" & findate & "#, " & IT_pm_id & ", " & bus_pm_id & ", " & bus_owner_id & ")"
  2. #2
  3. Second highest poster :p
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2001
    Posts
    7,322
    Rep Power
    33
    " & department & "
    Where are the quotes around this bit of data, is it text data or an int?
    - Andreas Koepke

    Koepke Photography

  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Location
    Sydney
    Posts
    33
    Rep Power
    12
    department is the id of a department, so it is an integer - no quotes required
  6. #4
  7. Second highest poster :p
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2001
    Posts
    7,322
    Rep Power
    33
    I really can't see the problem.

    Try using debug.print insertstr before the execute statement and then post the sql statement. That way we can see what is getting passed with sample data in there. Makes it much easier to see any faults.
    - Andreas Koepke

    Koepke Photography

  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Canada - Egypt
    Posts
    60
    Rep Power
    12

    few things might be worth checking


    bella,

    I completely agree with a.koepke.
    I haven't used the debug.print, just print the insertstr variable and don't really execute the query, so you would be able to see exactly what is being passed.

    In sql staments a few things you might want to check on:

    1. The table doesn't have any columns that can not be empty
    and were not included in the insert statment.
    I hope this is of any help to anyone.

    Yassoor
    http://www.WebsitesCreation.ca
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Canada - Egypt
    Posts
    60
    Rep Power
    12

    few things might be worth checking


    bella,

    I completely agree with a.koepke.
    I haven't used the debug.print, normally I just print the insertstr variable and don't really execute the query, so you would be able to see exactly what is being passed.

    In sql staments a few things you might want to check on:

    1. The table is not open for read only. create a dumy table and try inserting somthing into it just to make sure insertion in to the db from asp is possible.

    2. The table doesn't have any columns that can not be empty "NOT Null" property in MySQL or "zero length allowed" in ACCESS
    a. and were not included in the insert statment.
    b. or the value passed was blank string.

    3. The integer values retrieve from the FORM is not blank
    eg.
    Dim x = Request.(xFromForm)
    insert into table( col1str. col2int. col3str ) values ( "v1", x, "v2)
    This will generate:
    insert into table( col1str. col2int. col3str ) values ( "v1", , "v2)
    causing errors.

    4. The date format is valid.

    5. Stings passed in variables do not include '
    if strx = "I'll get this done"
    insert into (strvalue) Values (' " & x & " ')
    will result in
    insert into (strvalue) Values ('I'll get this done') which will cause error

    Note: I think, and I have done it before, the date is passed as '2000/02/02' not like the way you have done it #2000/02/02#

    Hope this was of any help
    Last edited by yassoor; February 19th, 2003 at 02:59 AM.
    I hope this is of any help to anyone.

    Yassoor
    http://www.WebsitesCreation.ca
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Location
    Sydney
    Posts
    33
    Rep Power
    12
    turned out i had some problems with my client side scripts that weren't changing field values correctly...consequently some of the values being passed were blank which caused the dramas

    thankyou for all your help anyway though!!!!
  14. #8
  15. Second highest poster :p
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2001
    Posts
    7,322
    Rep Power
    33
    Awesome... remember when you have problems with SQL its always a good idea to print out the SQL so you can see what is going on. Easiest way to debug SQL.
    - Andreas Koepke

    Koepke Photography


IMN logo majestic logo threadwatch logo seochat tools logo