Puzzling - @@Identity with DSN-less connection

Discussion in 'ASP General' started by Rob Meade, Dec 31, 2004.

  1. Rob Meade

    Rob Meade Guest

    Hi all,

    Just spent an hour and a half trying to get something to work when we
    realised we had the same problem over a year ago...

    I have an ASP page which calls a stored procedure, all the stored procedure
    does is write 4 values to a table, and then returns the identity of the just
    inserted row, syntax as follows:

    CREATE PROCEDURE spAddLink

    @CommunityID int,
    @LinkName varchar(50),
    @LinkURL varchar(255),
    @LinkOrder int

    AS

    BEGIN

    INSERT INTO tblLinks(CommunityID, LinkName, LinkURL, LinkOrder)
    VALUES(@CommunityID, @LinkName, @LinkURL, @LinkOrder)

    SELECT @@IDENTITY AS ROBSID

    END

    GO


    If I use a DNSLess connection to get this value, I get an error on the page
    telling me that it cant perform the operation because the connection is
    closed. If I use an ODBC connection the above does exactly as expected...

    The code used to call the stored procedure and pickup the value is as
    follows:

    <%
    SQL2 = "EXEC spAddLink '" & strCommunityID & "', '" & strLinkName & "',
    '" & _
    strLinkURL & "', '" & strLinkOrder & "'"
    %>
    <!--#Include File="_IncludeScripts/ReadOnly2.asp"-->
    <%
    If Not RS2.BOF And Not RS2.EOF Then
    strLinkID = RS2("ROBSID")
    Else
    Response.Write "No recordset returned"
    Response.End
    End If
    %>
    <!--#Include File="_IncludeScripts/ReadOnlyClose2.asp"-->

    For referenec, the two include scripts code is as follows:

    ReadOnly2.asp
    <%
    Set objConnection2 = Server.CreateObject("ADODB.Connection")

    objConnection2.Open "Provider=sqloledb;Data Source=<source>;Initial
    Catalog=<cat>,User Id=<ui>;Password=<pw>"

    Set objCommand2 = Server.CreateObject("ADODB.Command")
    Set RS2 = Server.CreateObject("ADODB.Recordset")
    objCommand2.CommandText = SQL2
    objCommand2.CommandType = adCmdText
    Set objCommand2.ActiveConnection = objConnection2
    RS2.Open objCommand2,,adOpenForwardOnly, adLockReadOnly
    %>

    ReadOnlyClose2.asp
    <%
    RS2.Close
    objConnection2.Close
    Set RS2 = Nothing
    Set objConnection2 = Nothing
    %>


    Any info on this would be appreciated - its probably something quite silly
    that I've missed, or some kind of mystery...

    Thanks in advance for any help.

    Regards

    Rob
     
    Rob Meade, Dec 31, 2004
    #1
    1. Advertising

  2. Try adding SET NOCOUNT ON to the Stored Proc.

    --
    Roji. P. Thomas
    Net Asset Management
    https://www.netassetmanagement.com


    "Rob Meade" <> wrote in message
    news:...
    > Hi all,
    >
    > Just spent an hour and a half trying to get something to work when we
    > realised we had the same problem over a year ago...
    >
    > I have an ASP page which calls a stored procedure, all the stored
    > procedure does is write 4 values to a table, and then returns the identity
    > of the just inserted row, syntax as follows:
    >
    > CREATE PROCEDURE spAddLink
    >
    > @CommunityID int,
    > @LinkName varchar(50),
    > @LinkURL varchar(255),
    > @LinkOrder int
    >
    > AS
    >
    > BEGIN
    >
    > INSERT INTO tblLinks(CommunityID, LinkName, LinkURL, LinkOrder)
    > VALUES(@CommunityID, @LinkName, @LinkURL, @LinkOrder)
    >
    > SELECT @@IDENTITY AS ROBSID
    >
    > END
    >
    > GO
    >
    >
    > If I use a DNSLess connection to get this value, I get an error on the
    > page telling me that it cant perform the operation because the connection
    > is closed. If I use an ODBC connection the above does exactly as
    > expected...
    >
    > The code used to call the stored procedure and pickup the value is as
    > follows:
    >
    > <%
    > SQL2 = "EXEC spAddLink '" & strCommunityID & "', '" & strLinkName & "',
    > '" & _
    > strLinkURL & "', '" & strLinkOrder & "'"
    > %>
    > <!--#Include File="_IncludeScripts/ReadOnly2.asp"-->
    > <%
    > If Not RS2.BOF And Not RS2.EOF Then
    > strLinkID = RS2("ROBSID")
    > Else
    > Response.Write "No recordset returned"
    > Response.End
    > End If
    > %>
    > <!--#Include File="_IncludeScripts/ReadOnlyClose2.asp"-->
    >
    > For referenec, the two include scripts code is as follows:
    >
    > ReadOnly2.asp
    > <%
    > Set objConnection2 = Server.CreateObject("ADODB.Connection")
    >
    > objConnection2.Open "Provider=sqloledb;Data Source=<source>;Initial
    > Catalog=<cat>,User Id=<ui>;Password=<pw>"
    >
    > Set objCommand2 = Server.CreateObject("ADODB.Command")
    > Set RS2 = Server.CreateObject("ADODB.Recordset")
    > objCommand2.CommandText = SQL2
    > objCommand2.CommandType = adCmdText
    > Set objCommand2.ActiveConnection = objConnection2
    > RS2.Open objCommand2,,adOpenForwardOnly, adLockReadOnly
    > %>
    >
    > ReadOnlyClose2.asp
    > <%
    > RS2.Close
    > objConnection2.Close
    > Set RS2 = Nothing
    > Set objConnection2 = Nothing
    > %>
    >
    >
    > Any info on this would be appreciated - its probably something quite silly
    > that I've missed, or some kind of mystery...
    >
    > Thanks in advance for any help.
    >
    > Regards
    >
    > Rob
    >
     
    Roji. P. Thomas, Dec 31, 2004
    #2
    1. Advertising

  3. Rob Meade

    Rob Meade Guest

    "Roji. P. Thomas" wrote ...

    > Try adding SET NOCOUNT ON to the Stored Proc.


    Hi Roji,

    I did try that - I spotted some article on the web - I did something like
    this:

    SET NOCOUNT ON; SELECT @@IDENTITY AS ROBSID; SET NOCOUNT OFF


    But it still didn't work?

    Regards

    Rob
     
    Rob Meade, Dec 31, 2004
    #3
  4. Rob Meade

    Lance Wynn Guest

    SET NOCOUNT ON at the beginning of the procedure, before the first insert
    statement.


    "Rob Meade" <> wrote in message
    news:...
    "Roji. P. Thomas" wrote ...

    > Try adding SET NOCOUNT ON to the Stored Proc.


    Hi Roji,

    I did try that - I spotted some article on the web - I did something like
    this:

    SET NOCOUNT ON; SELECT @@IDENTITY AS ROBSID; SET NOCOUNT OFF


    But it still didn't work?

    Regards

    Rob
     
    Lance Wynn, Dec 31, 2004
    #4
  5. > Thanks for the reply - I'll give that a go when I'm back in the office,
    I've
    > obviously put it in the wrong place then....


    Yes, it goes at the beginning, and don't bother with the SET NOCOUNT OFF.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
     
    Aaron [SQL Server MVP], Dec 31, 2004
    #5
  6. Rob Meade

    Rob Meade Guest

    "Lance Wynn" wrote ...

    > SET NOCOUNT ON at the beginning of the procedure, before the first insert
    > statement.


    Hi Lance,

    Thanks for the reply - I'll give that a go when I'm back in the office, I've
    obviously put it in the wrong place then....

    Regards

    Rob
     
    Rob Meade, Dec 31, 2004
    #6
  7. Rob Meade

    Rob Meade Guest

    "Aaron [SQL Server MVP]" wrote ...

    > Yes, it goes at the beginning, and don't bother with the SET NOCOUNT OFF.


    Cheers for the confirmation Aaron - bit of luck I'll get that working on
    Tuesday :eek:)

    Thanks

    Rob
     
    Rob Meade, Dec 31, 2004
    #7
  8. Rob Meade

    Lance Wynn Guest

    Yeah, I forget this everytime I write a Stored Procedure, it's good to know
    others do the same thing I do ;-)

    Happy New Year

    Lance
    "Rob Meade" <> wrote in message
    news:eek:MdBd.85$...
    "Aaron [SQL Server MVP]" wrote ...

    > Yes, it goes at the beginning, and don't bother with the SET NOCOUNT OFF.


    Cheers for the confirmation Aaron - bit of luck I'll get that working on
    Tuesday :eek:)

    Thanks

    Rob
     
    Lance Wynn, Jan 2, 2005
    #8
    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. Michael Appelmans

    oleDbConnection with DSN less connection string?

    Michael Appelmans, Jun 4, 2004, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    200
    Michael Appelmans
    Jun 4, 2004
  2. Gameface

    ASP Dsn-less connection error

    Gameface, May 3, 2004, in forum: ASP General
    Replies:
    2
    Views:
    96
    Ray at
    May 3, 2004
  3. shank

    SQL and DSN-Less connection

    shank, Jul 21, 2004, in forum: ASP General
    Replies:
    1
    Views:
    98
    Aaron [SQL Server MVP]
    Jul 21, 2004
  4. A P
    Replies:
    1
    Views:
    176
    Joker
    Sep 29, 2004
  5. SIMON GARE

    DSN-Less Connection Help

    SIMON GARE, Mar 10, 2006, in forum: ASP General
    Replies:
    1
    Views:
    107
    Bob Barrows [MVP]
    Mar 10, 2006
Loading...

Share This Page