Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post

    Syntax error with INSERT INTO

    Hello

    I am getting a syntax error among this little bunch:

    Code:
    sSQL="INSERT INTO tblWolf " _
         & "(fullName, wolfID, telNo, address, email, description, received, action, dispatched)" _
         & "  VALUES ('" & my_fullName & "', " & my_wolfID & ", " _
         & my_telNo & ", '" & my_address & "', '" & my_email & "', '" & my_description & "'," _
         & " #" & my_received & "', '" & my_action & "#, #" & my_dispatched & "#)"
    I can't see it, but maybe I have missed an apostrophe?

    Thanks!

    Steve

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,653 Times in 4,615 Posts
    & " #" & my_received & "', '" & my_action & "#,

    Delimiters for constant values must match: #...# or '...'

    Not crazy mixed up as you have them.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello Old Pedant

    Thank you for your reply.

    I am still getting this error:
    Microsoft JET Database Engine error '80040e14'

    Syntax error in INSERT INTO statement.
    Apparently, "This commonly occurs when your field name is a reserved word" (http://www.adopenstatic.com/faq/80040e14.asp).

    However, I have visited here: http://support.microsoft.com/?id=248738, and I haven't used any of those reserved words.

    The fields I have are:

    fullName (text field)
    wolfID (number)
    telNo (number)
    address (text)
    email (text)
    description (text)
    received (Date/Time)
    action (text)
    dispatched (text)

    So, I decided that the syntax is wrong because I haven't used the '#' correctly. It seems that you use it instead of the inverted comma. I am not sure if it is related, but the dispatched/my_dispatched field is a text field in my MDB, not a date/time, or numeric field.

    I have tried different combinations and this is what I now have:

    Code:
    sSQL="INSERT INTO tblWolf " _
         & "(fullName, wolfID, telNo, address, email, description, received, action, dispatched)" _
         & "  VALUES ('" & my_fullName & "', " & my_wolfID & ", " & my_telNo & ", '" & address & "', '" & my_email & "', '" & my_description & "', #" & my_received & "', '" & my_action & "', '" & my_dispatched & "')"
    As I say, this generates the error message above.

    If you could throw any light on this labyrinth of confusion, I would be grateful.

    Steve

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,653 Times in 4,615 Posts
    You *ONLY* use #...# for date/time values. Only. And even then only with Access/JET databases. (Other databases just use apostrophoes.)

    But you are *STILL* mismatched!!

    ... & my_description & "', #" & my_received & "', '" & my_action ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello OP

    Thanks for getting back to me.

    You mean like this:
    Code:
    , #" & my_received & "#,
    Steve

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,653 Times in 4,615 Posts
    Yes, assuming the field received in the DB is a DATETIME field.

    Regarding the issue of possible keyword conflict: I don't think any of those field names you showed are keywords. *POSSIBLY* action is, but I don't think so.

    But in any case the answer to that is easy: Just enclose the field names in [...].

    Code:
    sSQL="INSERT INTO tblWolf " _
         & "(fullName,wolfID,telNo,address,email,description,received,[action],dispatched)" _
    ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    SteveH (09-16-2013)

  • #7
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Thanks again.

    I was getting an error in the INSERT INTO and thought it was related to the #. I corrected that, thanks to you, but still got the error.

    Then I enclosed action as [action] and the error has disappeared! But action as a reserved word is not listed here: http://support.microsoft.com/?id=248738

    However, I am still getting an error when I try to add a new record:
    Microsoft VBScript runtime error '800a01c2'

    Wrong number of arguments or invalid property assignment: 'Replace'

    /display_recordsTEST1.asp, line 163
    The server doesn't like this:
    Code:
    my_dispatched = Replace( Request.Form("dispatched") )
    This field originally was:
    Code:
    my_dispatched = CDATE( Request.Form("dispatched") )
    but the field in the MDB is now a text field so I changed the CDATE to Replace - but it doesn't seem to like it.

    Steve

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,653 Times in 4,615 Posts
    Why in the world would you ever do that????

    The REPLACE function requires *THREE ARGUMENTS*.

    Code:
    REPLACE( stringOfContent, stringToBeReplaced, replacementString )
    Did you mean to do
    Code:
    my_dispatched = Replace( Request.Form("dispatched"), "'", "''" )
    to handle apostrophes and to avoid SQL injection???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,653 Times in 4,615 Posts
    A minor comment: If you want to protect against getting errors from NULL values (the REPLACE function will barf on your feet if you give it a null content string), just use TRIM, as well. TRIM() guarantees to produce a string, even if its argument is NULL.

    Example:
    Code:
    my_dispatched = Replace( Trim(Request.Form("dispatched")), "'", "''" )
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,653 Times in 4,615 Posts
    Have you ever downloaded the MS Script Documentation???

    If not, it is way past time to do so:
    http://www.microsoft.com/en-us/downl...s.aspx?id=2764

    That is a HELP file that is *WONDERFUL*. All the script docs in one place. Drop it on your desktop and the reference is always a mouse click away.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    SteveH (09-16-2013)

  • #11
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    No, I did it because the date/time attributes of that field in the database have changed from date/time to text, so now the 'Add Records' script looks like this:

    Code:
    <% 
    Dim conn, rs, my_fullName, my_wolfID, my_telNo, my_address, my_email, my_description, my_received, my_action, my_dispatched
    
    my_fullName = Replace( Request.Form("fullName"), "'", "''" )
    my_wolfID = CDBL( Request.Form("wolfID") )
    my_telNo = CDBL( Request.Form("telNo") )
    my_address = Replace( Request.Form("address"), "'", "''" )
    my_email = Replace( Request.Form("email"), "'", "''" )
    my_description = Replace( Request.Form("description"), "'", "''" )
    my_received = CDATE( Request.Form("received") )
    my_action = Replace( Request.Form("action"), "'", "''" )
    my_dispatched = Replace( Request.Form("dispatched") )
    
    
    sSQL="INSERT INTO tblWolf " &_
     "(fullName, wolfID, telNo, address, email, description, received, [action], dispatched)" &_
     " VALUES ('" & my_fullName & "', " & my_wolfID & ", " & my_telNo & ", '" & address & "', '" & my_email & "', '" & my_description & "', #" & my_received & "#, '" & my_action & "', '" & my_dispatched & "')"
    
    connection.Execute sSQL
    
    Response.write "<div align='center'><br>The record has been added.</div>"
    
     connection.Close
     
     Set connection = Nothing
    %>
    This script is placed under the script that displays the records on a Web page. It lies between the connect/open the database statement on the one hand, and the close database statement on the other.

    Steve

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,653 Times in 4,615 Posts
    *PLEASE* read my replies again.

    You can *NOT* use the REPLACE function as you are trying to do there. PERIOD.

    If you use REPLACE, you *MUST* give it THREE (3) (two more than one) arguments.

    You are only giving it ONE argument.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello OP

    So something along these lines (according to a Web Wiz tutorial I have just been reading) to pull the data from the 'Add Records' form:

    Code:
    my_fullName = Request.Form("fullName")
    my_wolfID = Request.Form("wolfID")
    my_telNo = Request.Form("telNo")
    my_address = Request.Form("address")
    my_email = Request.Form("email")
    my_description = Request.Form("description")
    my_received = Request.Form("received")
    my_action = Request.Form("action")
    my_dispatched = Request.Form("dispatched")
    The W3 schools site seems to prefer something like:

    Code:
    my_fullName = Request.Form("fullName"), "'", "''" 
    my_wolfID = Request.Form("wolfID")
    my_telNo = Request.Form("telNo")
    my_address = Request.Form("address"), "'", "''"
    my_email = Request.Form("email"), "'", "''"
    my_description = Request.Form("description"), "'", "''"
    my_received = Request.Form("received") 
    my_action = Request.Form("action"), "'", "''"
    my_dispatched = Request.Form("dispatched")
    I am not too sure what the difference is, but I have removed 'Replace'.

    Cheers, OP.

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,653 Times in 4,615 Posts
    Omigawd.

    What terrible terrible terrible advice from BOTH of those.

    NO NO NO NO NO NO NO NO.

    ALL YOU NEED to do is change that ONE LINE in your code from post #11 to match the SAME PATTERN you had in ALL the other lines ALREADY!!!!
    Code:
    my_fullName = Replace( Request.Form("fullName"), "'", "''" )
    my_wolfID = CDBL( Request.Form("wolfID") )
    my_telNo = CDBL( Request.Form("telNo") )
    my_address = Replace( Request.Form("address"), "'", "''" )
    my_email = Replace( Request.Form("email"), "'", "''" )
    my_description = Replace( Request.Form("description"), "'", "''" )
    my_received = CDATE( Request.Form("received") )
    my_action = Replace( Request.Form("action"), "'", "''" )
    my_dispatched = Replace( Request.Form("dispatched"), "'", "''" )
    Are you truly not seeing that you used that SAME PATTERN for *ALL* the PRIOR lines where you used Replace????

    I am at a complete loss of words for what else to tell you. I don't understand how you have can get it right on all the lines in blue and then suddenly decide to do something different on that last line.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #15
    Regular Coder
    Join Date
    Nov 2005
    Posts
    750
    Thanks
    138
    Thanked 1 Time in 1 Post
    Hello OP

    Yes, I can see a pattern - it's repeated apart from those fields which have some kind of numeric attribute such as telephone number or date. Previous to:
    Code:
    my_dispatched = Replace( Request.Form("dispatched"), "'", "''" )
    I had
    Code:
    my_received = CDATE( Request.Form("received") )
    because it was a date/time field.

    I am not sure why different sites say different things - they may be outdated or I may have misinterpreted.

    I can only put it down to an oversight....

    Many thanks for your help - I am grateful.

    Steve


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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