Connection provider not working in Vbscript

Asked

Viewed 543 times

4

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?

  • (comentário de um outro utilizador que não tem rep para postar): Are you sure you have SQLCNI installed on your server? "I have already checked and the command is executed in the database." what you say above, was verified by the log or you doing the query directly in the database? It is common for you to have the error that Recordset is empty when "Provider" was not actually found. "Provider not found"

  • Hello. Checked with SQL Server Profiler.

1 answer

2

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.

  • Should "Output" be different depending on whether you use Provider or Driver on the connection? As I said, using Driver {SQL Native Client} works smoothly.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.