3

I am writing a VBScript which executes a stored procedure with parameters which should return a recordset from the SQL Server.

I am able to execute simple select statements and using recordset object. The stored procedure returns a single column recordset which I want to process with the VBScript. However, I get an error.

ADODB.Recordset: Item cannot be found in the collection corresponding to the requested name or ordinal.

I am pretty sure that I haven't made any mistake with the column name.

Here's my code for reference:

conStr = "Provider=SQLOLEDB;Server=servername,port;Database=db_name;Trusted_Connection=yes;"
Set connection = CreateObject("ADODB.Connection")
connection.Open conStr
Set cmdObj = CreateoOject("ADODB.Command")

With cmdObj
  .ActiveConnection = conStr
  .CommandType = 4
  .CommandText = "my_sp_name" 'This is my SP name
  .Parameters.Refresh
  .Parameters(1).Value = "para1"  
  .Parameters(2).Value = "para2"                
  .Parameters(3).Value = "para3" 
  .Parameters(4).Value = "para4"
  .Parameters(5).Value = "para5"
End With
'The SP will be executed after this statement
Set recObj = cmdObj.Execute

strResult = recObj("time_id")
writeFile.WriteLine strResult
recObj.MoveNext
strResult = recObj("time_id")
writeFile.WriteLine strResult
recObj.MoveNext

recObj.Close
Set recObj = Nothing
Set cmdObj = nothing

As Lankymart pointed out, I could rectify that issue. Also, I had a look at the other post that you mentioned in the comments. Now I've changed my code to this:

conStr = "Provider=SQLOLEDB;Server=servername,port;Database=db_name;Trusted_Connection=yes;"
Set connection = CreateObject("ADODB.Connection")
connection.Open conStr
Set cmdObj = CreateoOject("ADODB.Command")

With cmdObj
  .ActiveConnection = conStr
  .CommandType = 4
  .CommandText = "my_sp_name" 'This is my SP name
  .Parameters.Append(.CreateParameter("@fact_table", 202,1,100))
  .Parameters.Append(.CreateParameter("@time_table", 202, 1,100))
  .Parameters.Append(.CreateParameter("@sales_column", 202, 1, 100 ))
  .Parameters.Append(.CreateParameter("@database", 202, 1, 100 ))
  .Parameters.Append(.CreateParameter("@server", 202, 1, 100))

  .Parameters("@fact_table").Value = "dbo.em_fact_sls_daily_Costco"
  .Parameters("@time_table").Value = "dbo.em_dim_time_iso"
  .Parameters("@sales_column").Value = "sales_value"
  .Parameters("@database").Value = "em_csd_mx"
  .Parameters("@server").Value = "self"
End With
'The SP will be executed after this statement
Set recObj = cmdObj.Execute

strResult = recObj("time_id")
writeFile.WriteLine strResult
recObj.MoveNext
strResult = recObj("time_id")
writeFile.WriteLine strResult
recObj.MoveNext

recObj.Close
Set recObj = Nothing
Set cmdObj = nothing

and now I get an error

ADODB.Recordset : Item cannot be found in the collection corresponding to the requesetd name or ordinal.

in the line Set recObj = cmdObj.Execute.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Shubho Banerjee
  • 63
  • 3
  • 3
    The `Parameters` collection like most collections in VBScript begins from ordinal position 0. If you have 5 parameters the first will be 0 and the last will be 4. The error is due to `parameters(5)` not existing as that would refer to the sixth parameter. – user692942 Nov 25 '16 at 08:07
  • Thanks for the quick reply, I'll check and post here – Shubho Banerjee Nov 25 '16 at 08:10
  • 3
    Also using `Parameters.Refresh` isn't ideal as if requires another trip to the SQL Server to workout the parameters. If you know what is expected, add them to the `Parameters` collection manually using the `.CreateParameter()` and `.Parameters.Append()` methods. Example of how to define them [here](http://stackoverflow.com/a/21698468/692942). – user692942 Nov 25 '16 at 08:15

0 Answers0