Retrieve New Record ID

Discussion in 'ASP General' started by Brett_A, Nov 19, 2007.

  1. Brett_A

    Brett_A Guest

    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
    Brett_A, Nov 19, 2007
    #1
    1. Advertising

  2. 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.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.
    Bob Barrows [MVP], Nov 19, 2007
    #2
    1. Advertising

  3. Brett_A

    Brett_A Guest

    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....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
    Brett_A, Nov 20, 2007
    #3
  4. Brett_A

    Brett_A Guest

    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....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','','','password','','','','','','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
    Brett_A, Nov 20, 2007
    #4
  5. 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','','','password','','',''
    ,'','','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.
    Bob Barrows [MVP], Nov 20, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. DaveF
    Replies:
    1
    Views:
    1,547
    Alvin Bruney [MVP]
    Jul 31, 2004
  2. loreille
    Replies:
    1
    Views:
    4,466
    Ben Strackany
    Oct 28, 2004
  3. loreille
    Replies:
    0
    Views:
    451
    loreille
    Oct 28, 2004
  4. Frits van Soldt

    How to insert a new record in detailsview when there are no record

    Frits van Soldt, Nov 16, 2005, in forum: ASP .Net Web Controls
    Replies:
    2
    Views:
    733
    Frits van Soldt
    Nov 17, 2005
  5. Maximus
    Replies:
    2
    Views:
    192
    Bob Barrows [MVP]
    Apr 12, 2007
Loading...

Share This Page