Puzzling - @@Identity with DSN-less connection

[Rob Meade]

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]

Try adding SET NOCOUNT ON to the Stored Proc.

 

[Rob Meade]

...

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]

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


...

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

 

[Aaron [SQL Server MVP]]

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.

 

[Rob Meade]

...

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]

...

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 )

Thanks

Rob

 

[Lance Wynn]

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
...

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 )

Thanks

Rob