3709 error while trying to call Stored Procedure in Classic ASP

Question

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

You need to set `ConnectionStr` to a Connection object not a string. — Brian M Stafford, Nov 29 '17 at 17:59
It throws the same error regardless of if I manage the connection myself or pass the string to the activeConnection. ADODB.Command should be able to manage the connection for me. I've done: `set Conn = Server.CreateObject("ADODB.Connection")` `Conn.open ConnectionStr` And passed that to the Active connection just to check, and same thing. — Wnanjo, Nov 29 '17 at 18:08

score 1 · Answer 1 · answered Nov 29 '17 at 18:08

1

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.

answered Nov 29 '17 at 18:08

Brian M Stafford

8,483
2
16
25

1

As Stated in [ADO-API](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/command-object-ado) You only need to persist the connection object if you are using the connection more than once. ADO can handle being passed a connection string instead of an object. I tried using a ADODB.Connection object instead and I have the same results. – Wnanjo Nov 29 '17 at 18:14
I have never tried that technique. I guess I learned something new! However, I would verify your connection string is actually valid. It sounds like ADO cannot create the connection. – Brian M Stafford Nov 29 '17 at 18:24
I'm almost certain I'm using the right connection string. I'm wondering if there could be a security thing blocking my connection? Have you encountered anything like this before. It's not my system I'm working on so if the code itself looks good, I think I need to ask someone about how permissions and whatnot work. – Wnanjo Nov 29 '17 at 18:27
I suppose that's possible, too. This isn't something I've encountered though. – Brian M Stafford Nov 29 '17 at 18:42
@Wnanjo It is possible but not if you declare the string as an Object Variable, this is wrong `Set StrConnection = "..."` this is right, `StrConnection = "..."`. `Set` is specifically for setting a reference to an object not string variables. – user692942 Nov 29 '17 at 20:25

user692942 · Accepted Answer · 2017-11-29T20:39:18.880

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;

Remove Set from beginning of ConnectionStr.

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

to

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

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

Error trying to call stored procedure with prepared statement (example of how to structure an ADODB.Command call to a database)
ADODB.Parameters error '800a0e7c' Parameter object is improperly defined. Inconsistent or incomplete information was provided

It turned out to be a combination of a few errors. changing the set and including the Metadata fixed the problem completely. — Wnanjo, Nov 29 '17 at 22:06

score 0 · Answer 3 · edited Nov 29 '17 at 20:34

0

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

edited Nov 29 '17 at 20:34

user692942

16,398
7
76
175

answered Nov 29 '17 at 20:13

Wnanjo

15
4

This might be true but not the cause of the original error. The issue there was trying to reference a string variable as an Object Reference using the `Set` statement. – user692942 Nov 29 '17 at 20:36

3709 error while trying to call Stored Procedure in Classic ASP

3 Answers3

Useful Links