CODEWITHSUNDEEP
sql-serverstored-proceduresasp-classic
Edelcom
Edelcom

Reputation: 5058

Call to stored procedure fails in classic asp when the stored proc INSERTs or UPDATEs tables

I am developing/maintaining classic asp pages and asp code on a Windows 7 PC with data taking from an Sqlserver/Express 2008/R2.

In this code I use a stored procedure to log-on to the web site.

A result set is returned with some fields on both cases (with a field set to -1 or the internal user nr depending on the correctness of login-id and password).

I have been using this same routine for over 5 years now , running on all sorts of IIS servers and with all sorts of Sqlservers.

The code, which works on other IIS/Sqlservers combinations does not work on my development PC.

Any idea why?

Since it is a development environment I use the sqlserver SA account with the appropriate password. So as far as I can tell, this SA user should have all appropriate rights.

To be complete I will now give the routine ( but you will see it's very straight forward ):

  function DoLogon( byval psNickName, byval psPassword)

     dim sSql

     DoLogon = False

     psNickName = Trim(psNickName)
     psPassword = Trim(psPassword)

     if (psNickName = "") then exit function
     if (psPassword = "") then exit function

   '  on error Resume Next

     call DbOpen

     sSql = "exec sp_EcomLogon " & SqlString( psNickName ) & "," & _
                              SqlString(psPassWord) & "," & _
                              SqlString(Request.ServerVariables("REMOTE_ADDR")) & "," & _
                              Trim(cStr(Session.SessionId))

     objRec.Open sSql ,objConn  ' ,adOpenStatic,adLockReadOnly ,adCmdText

     Klant_NetId = FldDft("K_NetId",-1)

     Call SetVarValue(cKlant_NetId,Klant_NetId)
     objRec.Close
     call DbClose

     if Klant_NetId <> -1 then
        DoLogon = True
        Klant_IsLoggedOn = 1
     else
        DoLogon = False
        Klant_IsLoggedOn = 0
     end if
     call  SetVarValue(cKlant_IsLoggedOn, Klant_IsLoggedOn)

  end function

The following error is displayed when the objRec.Close line is present:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

UPDATE AND SOLUTION

I have asked the person providing me with this solution to create an answer so I can give him proper upvotes, but in the mean time I will present the solution here.

As the first statement if the stored procedure I have added

set nocount on

And this seems to be the trick to get it working.

I have not found why this is necessary (from a technically view point), just that including set nocount on will speed up the stored procedures, but at last I have running stored procedures in combination with classic ASP again !

Upvotes: 1

Views: 2102

Answers (3)

Andomar
Andomar

Reputation: 238086

Try to add set nocount on to the top of the stored procedure?

Upvotes: 3

Nicholas Murray
Nicholas Murray

Reputation: 13533

It could be that the stored procedure that you are accessing is executing with an error. I would suggest that you write out the string sSql to screen copy it and execute it in query analyser to troubleshoot it.

Upvotes: 2

Wade73
Wade73

Reputation: 4509

I ran into a similar problem and in my case the issue was permissions on the table I was selecting from. I didn't have select permissions and it would close the connection. Now you mentioned that you are using the sa account so this should have permissions to all the functionality, but I would double check to make sure that this is true. HTH

Wade

Upvotes: 0

Related Questions