INSERT MAX(ID) Syntax problem

[Targa]

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]

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 document (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" <targa1SPAMSUCKS@alltel.net> wrote in message
news:u1ybKegLEHA.2100@TK2MSFTNGP10.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]

Thanks! Good info.


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:uOqd$SmLEHA.3944@tk2msftngp13.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 document (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" <targa1SPAMSUCKS@alltel.net> wrote in message
> news:u1ybKegLEHA.2100@TK2MSFTNGP10.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)
> :
> :
> :
> :
>
>