Professional Web Applications Themes

INSERT MAX(ID) Syntax problem - ASP Database

Trying to get the ID (autonumber) of a record that has just been inserted and insert it into a different table but I either get a syntax error or a data type mismatch. Ive tried switching the Sched_CustID field type to both number and text. I think it has to do with my quotes or double quotes on '" SCHED_CUSTID "' but Ive tried just about every combination I coud think of. This is an Access 2000 db. What am I doing wrong? Thanks! '''''''''''''INSERT NEW CUSTOMER mySQL = "INSERT INTO Customer (Firstname,LastName,Phone,Email)" mySQL = mySQL3 & " VALUES ('" ...

  1. #1

    Default INSERT MAX(ID) Syntax problem

    Trying to get the ID (autonumber) of a record that has just been inserted
    and insert it into a different table but I either get a syntax error or a
    data type mismatch. Ive tried switching the Sched_CustID field type to both
    number and text. I think it has to do with my quotes or double quotes on '"
    SCHED_CUSTID "' but Ive tried just about every combination I coud think of.
    This is an Access 2000 db.
    What am I doing wrong?

    Thanks!
    '''''''''''''INSERT NEW CUSTOMER
    mySQL = "INSERT INTO Customer (Firstname,LastName,Phone,Email)"
    mySQL = mySQL3 & " VALUES ('" & request.form("Sched_FirstName") & "', '" &
    request.form("Sched_LastName") & "' blah, blah, blah)"

    '''''''''''''GET LAST RECORD
    mySQL2 = "select MAX(id) As Sched_CustID from Clients"
    '''''''''''''''mySQL2 = "SELECT Identity from Clients"

    '''''''''''''INSERT SCEDULE RECORD
    mySQL3 = "INSERT INTO Schedule
    (Sched_CustID,Sched_FirstName,Sched_LastName)"
    mySQL3 = mySQL3 & " VALUES ('" SCHED_CUSTID "', '" &
    request.form("Sched_FirstName") & "', '" & blah blah "')"

    Conn.execute(mySQL)
    Conn.execute(mySQL2)
    Conn.execute(mySQL3)




    Targa Guest

  2. #2

    Default Re: INSERT MAX(ID) Syntax problem

    a) Do the INSERT into Customers table.

    b) Then do a SELECT IDENTITY

    c) Then do the next INSERT



    <%
    ' Do first insert

    ' Now get IDENTITY
    strSQL = "SELECT IDENTITY"
    Set objRS = objConn.Execute(strSQL)

    If not objRS.EOF then
    intNewId = objRS(0)

    strSQL = _
    "INSERT INTO Schedule " & _
    "(Sched_CustID,Sched_FirstName,Sched_LastName) " & _
    " VALUES (" & _
    intNewID ", " & _
    "'" & Request.Form("Sched_FirstName") & "', '" & _
    & blah blah "')"

    End If
    %>

    NOW, you can troubleshoot these easier by doing:

    Response.Write(strSQL)
    Response.End

    before you execute the SQL statement. This will echo to the screen what you
    are sending to the database. Much easier to see what you're doing wrong (eg
    mismatching quotes etc)

    Secondly - you *MUST* validate your input - do not take things directly from
    the Request.Form/Querystring collection and put it into an SQL Statement.
    That's just asking to be hacked.

    Some SQL Injection papers:
    =============
    The original doent (from NGS Software) is available from:
    [url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url]
    Chris has an update available from:
    [url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url]

    This was also released recently by a different group, and is aimed at
    showing that attempting to look for SQL Injection "signatures" (filtering
    for known bad input) is not a good idea:
    [url]http://www.imperva.com/adc/papers/sigevasion[/url]
    =============

    Also, read up on XSS (Cross Site Scripting) vulernabilities.


    Validate all input:
    [url]www.adopenstatic.com/resources/code/UIValidation.asp[/url]

    Cheers
    Ken


    "Targa" <targa1SPAMSalltel.net> wrote in message
    news:u1ybKegLEHA.2100TK2MSFTNGP10.phx.gbl...
    : Trying to get the ID (autonumber) of a record that has just been inserted
    : and insert it into a different table but I either get a syntax error or a
    : data type mismatch. Ive tried switching the Sched_CustID field type to
    both
    : number and text. I think it has to do with my quotes or double quotes on
    '"
    : SCHED_CUSTID "' but Ive tried just about every combination I coud think
    of.
    : This is an Access 2000 db.
    : What am I doing wrong?
    :
    : Thanks!
    : '''''''''''''INSERT NEW CUSTOMER
    : mySQL = "INSERT INTO Customer (Firstname,LastName,Phone,Email)"
    : mySQL = mySQL3 & " VALUES ('" & request.form("Sched_FirstName") & "', '" &
    : request.form("Sched_LastName") & "' blah, blah, blah)"
    :
    : '''''''''''''GET LAST RECORD
    : mySQL2 = "select MAX(id) As Sched_CustID from Clients"
    : '''''''''''''''mySQL2 = "SELECT Identity from Clients"
    :
    : '''''''''''''INSERT SCEDULE RECORD
    : mySQL3 = "INSERT INTO Schedule
    : (Sched_CustID,Sched_FirstName,Sched_LastName)"
    : mySQL3 = mySQL3 & " VALUES ('" SCHED_CUSTID "', '" &
    : request.form("Sched_FirstName") & "', '" & blah blah "')"
    :
    : Conn.execute(mySQL)
    : Conn.execute(mySQL2)
    : Conn.execute(mySQL3)
    :
    :
    :
    :


    Ken Schaefer Guest

  3. #3

    Default Re: INSERT MAX(ID) Syntax problem

    Thanks! Good info.


    "Ken Schaefer" <kenREMOVETHISadOpenStatic.com> wrote in message
    news:uOqd$SmLEHA.3944tk2msftngp13.phx.gbl...
    > a) Do the INSERT into Customers table.
    >
    > b) Then do a SELECT IDENTITY
    >
    > c) Then do the next INSERT
    >
    >
    >
    > <%
    > ' Do first insert
    >
    > ' Now get IDENTITY
    > strSQL = "SELECT IDENTITY"
    > Set objRS = objConn.Execute(strSQL)
    >
    > If not objRS.EOF then
    > intNewId = objRS(0)
    >
    > strSQL = _
    > "INSERT INTO Schedule " & _
    > "(Sched_CustID,Sched_FirstName,Sched_LastName) " & _
    > " VALUES (" & _
    > intNewID ", " & _
    > "'" & Request.Form("Sched_FirstName") & "', '" & _
    > & blah blah "')"
    >
    > End If
    > %>
    >
    > NOW, you can troubleshoot these easier by doing:
    >
    > Response.Write(strSQL)
    > Response.End
    >
    > before you execute the SQL statement. This will echo to the screen what
    you
    > are sending to the database. Much easier to see what you're doing wrong
    (eg
    > mismatching quotes etc)
    >
    > Secondly - you *MUST* validate your input - do not take things directly
    from
    > the Request.Form/Querystring collection and put it into an SQL Statement.
    > That's just asking to be hacked.
    >
    > Some SQL Injection papers:
    > =============
    > The original doent (from NGS Software) is available from:
    > [url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url]
    > Chris has an update available from:
    > [url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url]
    >
    > This was also released recently by a different group, and is aimed at
    > showing that attempting to look for SQL Injection "signatures" (filtering
    > for known bad input) is not a good idea:
    > [url]http://www.imperva.com/adc/papers/sigevasion[/url]
    > =============
    >
    > Also, read up on XSS (Cross Site Scripting) vulernabilities.
    >
    >
    > Validate all input:
    > [url]www.adopenstatic.com/resources/code/UIValidation.asp[/url]
    >
    > Cheers
    > Ken
    >
    >
    > "Targa" <targa1SPAMSalltel.net> wrote in message
    > news:u1ybKegLEHA.2100TK2MSFTNGP10.phx.gbl...
    > : Trying to get the ID (autonumber) of a record that has just been
    inserted
    > : and insert it into a different table but I either get a syntax error or
    a
    > : data type mismatch. Ive tried switching the Sched_CustID field type to
    > both
    > : number and text. I think it has to do with my quotes or double quotes on
    > '"
    > : SCHED_CUSTID "' but Ive tried just about every combination I coud think
    > of.
    > : This is an Access 2000 db.
    > : What am I doing wrong?
    > :
    > : Thanks!
    > : '''''''''''''INSERT NEW CUSTOMER
    > : mySQL = "INSERT INTO Customer (Firstname,LastName,Phone,Email)"
    > : mySQL = mySQL3 & " VALUES ('" & request.form("Sched_FirstName") & "', '"
    &
    > : request.form("Sched_LastName") & "' blah, blah, blah)"
    > :
    > : '''''''''''''GET LAST RECORD
    > : mySQL2 = "select MAX(id) As Sched_CustID from Clients"
    > : '''''''''''''''mySQL2 = "SELECT Identity from Clients"
    > :
    > : '''''''''''''INSERT SCEDULE RECORD
    > : mySQL3 = "INSERT INTO Schedule
    > : (Sched_CustID,Sched_FirstName,Sched_LastName)"
    > : mySQL3 = mySQL3 & " VALUES ('" SCHED_CUSTID "', '" &
    > : request.form("Sched_FirstName") & "', '" & blah blah "')"
    > :
    > : Conn.execute(mySQL)
    > : Conn.execute(mySQL2)
    > : Conn.execute(mySQL3)
    > :
    > :
    > :
    > :
    >
    >

    Targa Guest

Similar Threads

  1. Error in Insert into syntax
    By Hydrowizard in forum Coldfusion Flash Integration
    Replies: 0
    Last Post: October 5th, 01:44 PM
  2. Replies: 1
    Last Post: June 23rd, 12:08 AM
  3. Syntax error in INSERT INTO statement
    By Speegs in forum Dreamweaver AppDev
    Replies: 7
    Last Post: May 25th, 12:45 PM
  4. Syntax error in INSERT INTO and UPDATE statements
    By Snowball2050 in forum Coldfusion Database Access
    Replies: 3
    Last Post: September 11th, 07:38 PM
  5. Replies: 1
    Last Post: August 24th, 03:45 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139