CODEWITHSUNDEEP
stored-proceduresvbscriptasp-classic
Wnanjo
Wnanjo

Reputation: 15

3709 error while trying to call Stored Procedure in Classic ASP

I'm trying to call a Stored Procedure from a classic ASP application and I'm getting a 3709 error being thrown. This is a connection could not be... error. 

On Error Resume Next
set ConnectionStr = "driver=SQL Server;server=***;uid=***;pwd=***;database=***"

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    Set .ActiveConnection = ConnectionStr
    .CommandType = adCmdStoredProc
    .CommandText = "storedprocedure"  

    'Input Parameters
    .Parameters.Append .CreateParameter("@email", adVarchar, adParamInput, 50, Request.Form("email")) 
    .Parameters.Append .CreateParameter("@pass", adVarchar, adParamInput, 50, Request.Form("password")) 

End With 

set rs = Server.CreateObject("ADODB.RecordSet") 

'rs.Open cmd.Execute
set rs = cmd.Execute 'Here is 3709 Error

If not rs.eof Then 'I get a 3704 Error here because of the 3709 error above
    'Do Things with the RS
End If
Set cmd = Nothing

My Stored Procedure is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[nameofprocedure]
    @email nvarchar(50),
    @pass nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON
    SELECT is, fstname, lstname, [password]
    FROM users
    WHERE email = @email AND [password] = @pass;
    RETURN
END

Upvotes: 1

Views: 601

Answers (3)

user692942
user692942

Reputation: 16672

The reason for the error is ConnectionStr is a string variable not an object variable so Set is not expected. You have two choices, either;

  1. Remove Set from beginning of ConnectionStr.

    set ConnectionStr = "driver=SQL Server;server=***;uid=***;pwd=***;database=***"

    to

    ConnectionStr = "driver=SQL Server;server=***;uid=***;pwd=***;database=***"

  2. Instantiate an object variable in this case it will be an ADODB.Connection that you will have to call the Open() method on passing the connection string (unless you are re-using the connection object this is overkill). Brian covers this in their answer.

Useful Links

Upvotes: 1

Wnanjo
Wnanjo

Reputation: 15

ASP file was missing the Library Reference to ADO Data Objects. 

<!--METADATA
TYPE="TypeLib"
NAME="Microsoft ActiveX Data Objects 2.6 Library"
UUID="{00000206-0000-0010-8000-00AA006D2EA4}"
VERSION="2.6"
-->

Upvotes: 0

Brian M Stafford
Brian M Stafford

Reputation: 8868

You need to create and open a connection. Try this code:

Set ConnectionStr = Server.CreateObject("ADODB.Connection")  
ConnectionStr.Open "driver=SQL Server;server=***;uid=***;pwd=***;database=***"

You might want to rename your variable to something else since it's not really a connection string.

Upvotes: 1

Related Questions