Solved

Excel 2010/2013 VBA accessing SQL 2012 Stored Procedure Error 3265

Posted on 2014-03-13
5
1,038 Views
Last Modified: 2016-02-11
I have the following code in a spreadsheet:

Dim strConn As String
Dim conn As New Connection
Dim cmd As New ADODB.Command
Dim rs As New Recordset
strConn = "DRIVER=SQL Server;SERVER=AV-W12-ROMS-1;DATABASE=RESUMES"

conn.ConnectionString = strConn
conn.Open
cmd.ActiveConnection = conn
cmd.CommandTimeout = 0
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_list_updated_documents_within_x_days"
cmd.Parameters("@number_of_days") = PromptNoDays.TextBoxDays.Text

Set rs = cmd.Execute
        
rs.MoveFirst
...

Open in new window

and here is the beginning of the Stored Procedure:
USE [RESUMES]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_list_updated_documents_within_x_days]  
	-- Add the parameters for the stored procedure here
	@number_of_days nvarchar(1024) 
AS
BEGIN
...

Open in new window

I can run this without a problem on my computer but it we get the "Item cannot be found in the collection corresponding to the requested name or ordinal" error when it runs on another user's computer when it hits this line in the VBA Code:
cmd.Parameters("@number_of_days") = PromptNoDays.TextBoxDays.Text

Open in new window

I'm using Excel 2013, the other user 2010.  Any ideas on why it works for me but not on another computer?
0
Comment
Question by:wchestnut
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39927657
Can you try:

        cmd.Parameters.Append prm cmd.CreateParameter("@number_of_days", adVarChar, adParamInput,1024, PromptNoDays.TextBoxDays.Text)

Thomas
0
 

Author Comment

by:wchestnut
ID: 39927670
cmd.Parameters.Append prm cmd.CreateParameter("@number_of_days", adVarChar, adParamInput,1024, PromptNoDays.TextBoxDays.Text)

Open in new window

With that I get a Syntax Error?
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 39927687
Damn cut-and-paste-and-tweak, it should be

        cmd.Parameters.Append cmd.CreateParameter("@number_of_days", adVarChar, adParamInput,1024, PromptNoDays.TextBoxDays.Text)

My bad,

Thomas
0
 

Author Closing Comment

by:wchestnut
ID: 39927779
No, you good! : )  It worked!

Strange that the original line worked on mine but not the user's.  I searched all over the place for the error and proper command line and thought I had the correct one.  I do have Visual Studio Express 2012 for Windows Desktops, the SQL Server Management Studio plus some web development tools installed on mine so maybe they included something that allowed it to work.

Thanks, Thomas!
0
 

Expert Comment

by:DataSense
ID: 40759520
I have a similar problem with a call to a sql server (2014) stored procedure which works when called from Access 2013 but not from Access 2007.

This is the stored proc:

CREATE PROCEDURE uspGetStockOnOrder
@OnOrder int output,
@StockID int
AS
      set nocount on;
      SELECT @OnOrder = dbo.vStock_on_order.OnOrder
      FROM dbo.vStock_on_order
      WHERE dbo.vStock_on_order.Ord_SppSizeID = @StockID;
      RETURN
Go
---------
In VBA the code is:
Public Function GetStockOnOrder(Param1 As Long, Param2 As Long) As Long
On Error GoTo ConnectError
Dim objConnection As New ADODB.Connection, objCom As ADODB.Command, strConnect As String, DataSrce As String, RetVal As Integer
    DataSrce = DLookup("ServerName", "Master_Central", "User_Loc = '" & Pub_UserLoc & "'")
'      different users can have different paths to the server
    Set objCom = New ADODB.Command
    objConnection.Provider = "sqloledb"
    strConnect = "Integrated Security=SSPI; Persistent Security info = false; Data Source = " & DataSrce & "; Initial Catalog=CM_BE"
    objConnection.Open strConnect
    With objCom
        .ActiveConnection = objConnection
        .CommandType = adCmdStoredProc
        .CommandText = "uspGetStockOnOrder"
        .CreateParameter ("@OnOrder")
        .CreateParameter ("@StockID")
        .Parameters("@StockID").Value = Param1
        .Parameters("@OnOrder").Value = Param2
        .Execute
        GetStockOnOrder = Nz(CLng(.Parameters("@OnOrder").Value))    ' return value
    End With

Exit_ConnectError:
    Exit Function
ConnectError:
GetStockOnOrder = -1    ' flag to convey error to calling routine
    errornum = Err.Number
    If errornum = -2147467259 Then
        MsgBox "The back-end database name or path to the server is not valid." & vbCrLf & "      DO NOT CONTINUE!", vbCritical, "DataSense Help"
        DoCmd.Close acForm, Pub_FormName
        Resume Exit_ConnectError
    End If
End Function
--------------------
As mentioned above, this works OK from computers running the latest version of Access, but not on those running Access 2007.
As suggested in the above answer, I have tried using a more complete definition of the parameters in the calling routine:
   .Parameters.Append   .CreateParameter("@StockID", adInteger, adParamInput, 4)
   .Parameters("@StockID").Value = Param1
 etc
But this still bombs out when the .Execute statement is reached.

I would be grateful if someone could point out what I'm doing wrong (or not doing right).
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question