Connection provider not working in Vbscript

Question

I have the following connection string:

conn = "Provider=SQLNCLI; Server=" & conn_server &"; Database="& conn_database &"; UID="& conn_uid &"; PWD="& conn_pwd

And the code to open and run SP:

set conexao = server.CreateObject("ADODB.connection")
conexao.ConnectionString = conn
conexao.Open

set cmd = Server.CreateObject("ADODB.Command")        
cmd.ActiveConnection = conexao
cmd.CommandText = "sp_login"
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append cmd.CreateParameter("@EMA_PES", adVarChar, adParamInput, 100, login)
cmd.Parameters.Append cmd.CreateParameter("@PSW_PES", adVarChar, adParamInput, 35, psw)

set Rs1 = Server.CreateObject("ADODB.RecordSet")
Rs1.CursorLocation = adUseClient
Rs1.CursorType = adOpenStatic

Rs1.Open cmd

session("id_pes") = Rs1("id")

Rs1.Close()
Set Rs1 = nothing
conexao.Close()
Set conexao = nothing

The problem occurs in the command session("id_pes") = Rs1("id"). Says the item could not be found in the corresponding collection.

I already checked and the command runs in the bank. When I change the connection string to use the Driver = {SQL Native Client} works smoothly.

Could someone help me?

Answer 1

From what I see in your code, you’re running the Stored Procedure sp_login, and after executing it trying to capture an id Rs1("id"). What happens is that "apparently" (by your information) your Stored Procedure is not doing the "Output" of this id.

Your driver is correct, and the error you receive has no connection with the connection, but with the attempt to read a data that does not exist in the object.

I use SQL Server 2008R2 with SQLNCLI10 driver.

Following this link:

At the bottom of the page, you have the versions of SQL Server and when choosing the database, their respective drivers.