Retrieve New Record ID

Based on some web research, this is what I have:

********************************

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open MM_xxxxx_STRING
SQLStmt = "INSERT INTO tblCustomers (first_name, last_name,
email_address,) "
SQLStmt = SQLStmt & "VALUES ('" & first_name & "','" & last_name &
"','" & email_address & "') "
SQLStmt = SQLStmt & "SET customer_id = SCOPE_IDENTITY()"
Set RS = Conn.Execute(SQLStmt)
....
....
....

********************************

I get the following error:


********************************

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
'='.

/dev/account/register-3.asp, line 50


********************************

What is wrong with the code?

Thanks.

Brett

Re: Retrieve New Record ID

Brett_A wrote:
> Based on some web research, this is what I have:
>
> ********************************
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open MM_xxxxx_STRING
> SQLStmt = "INSERT INTO tblCustomers (first_name, last_name,
> email_address,) "
> SQLStmt = SQLStmt & "VALUES ('" & first_name & "','" & last_name &
> "','" & email_address & "') "
> SQLStmt = SQLStmt & "SET customer_id = SCOPE_IDENTITY()"
> Set RS = Conn.Execute(SQLStmt)

Remember, when using dynamic sql, the goal is to generate a string that
will be executed by the query engine. To solve syntax errors, your first
step needs to be to look at the generated string (you should generate
the string BEFORE opening the connection):

Response.Write SQLStmt
Response.End

Run the page and look at the generated string. Usually the problem will
stick out like a sore thumb. If you've generated the string correctly,
you should be able to copy it to the clipboard and paste it into query
analyzer where you should be able to run it without modification. If you
still cannot see the problem, show US the generated string.

Without seeing the string, it's hard to be sure what the problem is, but
I suspect your goal is to retrieve the new identity value, correct? I
can address that. SET does not generate a resultset. You need to use
SELECT to generate a resultset that will be returned to the client. Like
this:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open MM_xxxxx_STRING
SQLStmt = "INSERT INTO tblCustomers (first_name, last_name,
email_address,) "
SQLStmt = SQLStmt & "VALUES ('" & first_name & "','" & last_name &
"','" & email_address & "'); " '<<-- use a semicolon to delimit the
statement
SQLStmt = SQLStmt & "SELECT customer_id = SCOPE_IDENTITY()"
Set RS = Conn.Execute(SQLStmt)


Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, but that is a topic for a
new thread.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Retrieve New Record ID

On Nov 19, 1:32 pm, "Bob Barrows [MVP]"
wrote:
> Brett_A wrote:
> > Based on some web research, this is what I have:
>
> > ********************************
>
> > Set Conn = Server.CreateObject("ADODB.Connection")
> > Conn.Open MM_xxxxx_STRING
> > SQLStmt = "INSERT INTO tblCustomers (first_name, last_name,
> > email_address,) "
> > SQLStmt = SQLStmt & "VALUES ('" & first_name & "','" & last_name &
> > "','" & email_address & "') "
> > SQLStmt = SQLStmt & "SET customer_id = SCOPE_IDENTITY()"
> > Set RS = Conn.Execute(SQLStmt)
>
> Remember, when using dynamic sql, the goal is to generate a string that
> will be executed by the query engine. To solve syntax errors, your first
> step needs to be to look at the generated string (you should generate
> the string BEFORE opening the connection):
>
> Response.Write SQLStmt
> Response.End
>
> Run the page and look at the generated string. Usually the problem will
> stick out like a sore thumb. If you've generated the string correctly,
> you should be able to copy it to the clipboard and paste it into query
> analyzer where you should be able to run it without modification. If you
> still cannot see the problem, show US the generated string.
>
> Without seeing the string, it's hard to be sure what the problem is, but
> I suspect your goal is to retrieve the new identity value, correct? I
> can address that. SET does not generate a resultset. You need to use
> SELECT to generate a resultset that will be returned to the client. Like
> this:
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open MM_xxxxx_STRING
> SQLStmt = "INSERT INTO tblCustomers (first_name, last_name,
> email_address,) "
> SQLStmt = SQLStmt & "VALUES ('" & first_name & "','" & last_name &
> "','" & email_address & "'); " '<<-- use a semicolon to delimit the
> statement
> SQLStmt = SQLStmt & "SELECT customer_id = SCOPE_IDENTITY()"
> Set RS = Conn.Execute(SQLStmt)
>
> Further points to consider:
> Your use of dynamic sql is leaving you vulnerable to hackers using sql
> injection:http://mvp.unixwiz.net/techtips/sql-injection.html http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> See here for a better, more secure way to execute your queries by using
> parameter markers:http://groups-beta.google.com/group/microsoft.public .inetserver.asp.d...
>
> Personally, I prefer using stored procedures, but that is a topic for a
> new thread.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Thank you for the great response - yeah I left out the detail about
wanting to get the new ID value. I will work on your suggestions
tomorrow morning. Thanks again.

brett

Re: Retrieve New Record ID

On Nov 19, 1:32 pm, "Bob Barrows [MVP]"
wrote:
> Brett_A wrote:
> > Based on some web research, this is what I have:
>
> > ********************************
>
> > Set Conn = Server.CreateObject("ADODB.Connection")
> > Conn.Open MM_xxxxx_STRING
> > SQLStmt = "INSERT INTO tblCustomers (first_name, last_name,
> > email_address,) "
> > SQLStmt = SQLStmt & "VALUES ('" & first_name & "','" & last_name &
> > "','" & email_address & "') "
> > SQLStmt = SQLStmt & "SET customer_id = SCOPE_IDENTITY()"
> > Set RS = Conn.Execute(SQLStmt)
>
> Remember, when using dynamic sql, the goal is to generate a string that
> will be executed by the query engine. To solve syntax errors, your first
> step needs to be to look at the generated string (you should generate
> the string BEFORE opening the connection):
>
> Response.Write SQLStmt
> Response.End
>
> Run the page and look at the generated string. Usually the problem will
> stick out like a sore thumb. If you've generated the string correctly,
> you should be able to copy it to the clipboard and paste it into query
> analyzer where you should be able to run it without modification. If you
> still cannot see the problem, show US the generated string.
>
> Without seeing the string, it's hard to be sure what the problem is, but
> I suspect your goal is to retrieve the new identity value, correct? I
> can address that. SET does not generate a resultset. You need to use
> SELECT to generate a resultset that will be returned to the client. Like
> this:
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open MM_xxxxx_STRING
> SQLStmt = "INSERT INTO tblCustomers (first_name, last_name,
> email_address,) "
> SQLStmt = SQLStmt & "VALUES ('" & first_name & "','" & last_name &
> "','" & email_address & "'); " '<<-- use a semicolon to delimit the
> statement
> SQLStmt = SQLStmt & "SELECT customer_id = SCOPE_IDENTITY()"
> Set RS = Conn.Execute(SQLStmt)
>
> Further points to consider:
> Your use of dynamic sql is leaving you vulnerable to hackers using sql
> injection:http://mvp.unixwiz.net/techtips/sql-injection.html http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> See here for a better, more secure way to execute your queries by using
> parameter markers:http://groups-beta.google.com/group/microsoft.public .inetserver.asp.d...
>
> Personally, I prefer using stored procedures, but that is a topic for a
> new thread.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.

Bob,

Here is the String output:

INSERT INTO tblCustomers (first_name, last_name, email_address,
reg_email_address, pass_word, phone_number, mail_address, mail_city,
mail_state, mail_postal, mail_country, opt_in) VALUES
('Brett','Atkin','brett@xxxxxx.com','brett@xxxx.com','passwo rd','','','','','','Select
Country','Yes');SELECT customer_id = SCOPE_IDENTITY()

It works correctly when I paste it into the Query Analyzer and returns
the correct value.

How do I retrieve that value and use it on the page?

Thanks

Brett

Re: Retrieve New Record ID

Brett_A wrote:
> Bob,
>
> Here is the String output:
>
> INSERT INTO tblCustomers (first_name, last_name, email_address,
> reg_email_address, pass_word, phone_number, mail_address, mail_city,
> mail_state, mail_postal, mail_country, opt_in) VALUES
>
('Brett','Atkin','brett@xxxxxx.com','brett@xxxx.com','passwo rd','','',''
,'','','Select
> Country','Yes');SELECT customer_id = SCOPE_IDENTITY()
>
> It works correctly when I paste it into the Query Analyzer and returns
> the correct value.
>
That generates an error in your ASP code? A syntax error? Or are you
getting a different error now? As a guess, I would suspect that I have
failed to prevent you from making the mistake of excluding the "SET
NOCOUNT ON" line in your batch. DML statements generate extra resultsets
containing those "x rows affected " messages you see in query analyzer.
ADO receives them as closed recordsets whcih cannot be read. What you
need to do is use NOCOUNT to prevent those messages from being
generated:

SQLStmt = "SET NOCOUNT ON;" & _
"INSERT INTO tblCustomers " & _
"(first_name, last_name,email_address,) "
SQLStmt = SQLStmt & ...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.