«

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

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

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

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

"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

"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 )

Thanks

Rob

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

Thanks

Rob