Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Puzzling - @@Identity with DSN-less connection

Reply
Thread Tools

Puzzling - @@Identity with DSN-less connection

 
 
Rob Meade
Guest
Posts: n/a
 
      12-31-2004
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


 
Reply With Quote
 
 
 
 
Roji. P. Thomas
Guest
Posts: n/a
 
      12-31-2004
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
>



 
Reply With Quote
 
 
 
 
Rob Meade
Guest
Posts: n/a
 
      12-31-2004
"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


 
Reply With Quote
 
Lance Wynn
Guest
Posts: n/a
 
      12-31-2004
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



 
Reply With Quote
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      12-31-2004
> 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.)


 
Reply With Quote
 
Rob Meade
Guest
Posts: n/a
 
      12-31-2004
"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


 
Reply With Quote
 
Rob Meade
Guest
Posts: n/a
 
      12-31-2004
"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


 
Reply With Quote
 
Lance Wynn
Guest
Posts: n/a
 
      01-02-2005
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



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Puzzling VPN problem with Windows 2003 John Rennie Cisco 0 03-14-2006 07:19 PM
Puzzling polymorphism question James Java 5 08-31-2004 10:34 PM
Puzzling Browser Refresh and session attribute behavior GIMME Java 1 04-14-2004 11:27 PM
puzzling routing problem Geert Cisco 0 04-07-2004 10:28 PM
Puzzling issue in casting a class GaryM Java 0 12-21-2003 01:24 PM



Advertisments
 



1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57