Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-03-13
5
Medium Priority
?
1,098 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

596 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