Error getting recordset

[developer155]

Hi I am using the following code to execute stored proc and get a recordset, but getting an error that recordset object is closed. Can someone please take a look if there is issue in code:
Thanks!!

<%
var CS_updateConnection = MM_mediakitDB_STRING;
Application("ConnectionProvider") = "MSDASQL";
Application("ConnectionString") = CS_updateConnection;
var Conn = Server.CreateObject("ADODB.Connection");


Conn.Provider = Application("ConnectionProvider");
Conn.Open(Application("ConnectionString"));


////////
var oCmd = Server.CreateObject("ADODB.Command");
oCmd.ActiveConnection = Conn;
oCmd.CommandText = "processAdLink";
oCmd.CommandType = 4;

var p = oCmd.Parameters;
p.Append (oCmd.CreateParameter("@adID",17))
oCmd("@adID") = adID;



var adInfo = Server.CreateObject("ADODB.Recordset")
adInfpen (oCmd)

if (!adInfo.EOF) { //Getting error here!!!
var goURL = String(adInfo("goURL"));
var winID = String(adInfo("winID"));
} else {
var adID="failed"
}
adInfo.Close
Conn.Close

%>

 

[gmmastros]

In the processAdLink, do you have Set Nocount ON.

Ex:

[tt][blue]
Alter Procedure processAdLink
@Param1 ....,
@Param2 ....
As

[!]SET NOCOUNT ON[/!]

Everything else in the stored procedure....
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[developer155]

nope

 

[gmmastros]

Well. I didn't say it, but... you should put Set Nocount On in to the stored procedure and then try again. If this works, I will explain WHY it works so that you will understand when it's needed.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[developer155]

before I make the change... the code used to work when it did not have parameters and was executing stored proc like this:
Conn.exec("processAdLink " + AdID)

Then I added all that code to out adID in a parameter (to avoid SQL injection)
Does that make any difference?

 

[gmmastros]

Here's what I know. For fact.

Adding Set Nocount On will not, cannot cause any problems in your system. Not having it can cause problems under certain circumstances. In my opinion, every stored procedure should have that line at the top of it. Here's why...

When you have a stored procedure that 'does' a couple things, you will get multiple recordsets returned. The first recordset will have...

1 record affected

The next recordset will have your data.

By including Set NoCount On, the first recordset will not appear so that the only data returned will be the recordset you were expecting.

Does this make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom