ADO Parameter issue

Using Access application with SQL Server 2005.

Application works on Machine 1 with Server 1 but not on Machine 2 with Server 2

One Server 1 I am able to set the value of Parameter by going:

MyCmd.Parameters(1) = 100

On Marchine 2 this triggers and error 3265':ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application.

On Machine 2 the code will work if I use the CreateParameter method.

QUESTION: any way to set things on Machine 2 to allow for the setting parameters simply by using the numeric ordinal?
hertzgordmanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
That is because you have not created the parameter yet.  In other words, the collection MyCmd.Parameters has not been initialized anywhere.
0
 
packratt_jkConnect With a Mentor Commented:
0
 
hertzgordmanAuthor Commented:
Thanks updated the MDAC on the non-functioning machine but unfortunately the problem persists.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
packratt_jkCommented:
the component checker came back clean?
0
 
hertzgordmanAuthor Commented:
Yes it came back clean for version 2.82
0
 
packratt_jkConnect With a Mentor Commented:
Perhaps there are multiple versions of access installed?
0
 
hertzgordmanAuthor Commented:
Yes there is Access 2007 and Access 2003
0
 
packratt_jkCommented:
same setup on server 1 and server 2?

I'm not sure how well those 2 play together...
0
 
hertzgordmanAuthor Commented:
I would bet the mult versions has a lot to do with this problem...
0
 
packratt_jkCommented:
Yeah.  I would try to do a repair on one version and try again.  If it doesn't work still, do a repair on the other.  If you're lucky, it will work one of the two ways (and hopefully not break anything else in the process).
0
 
Anthony PerkinsCommented:
Please post the complete code you are using.
0
 
hertzgordmanAuthor Commented:
Here is the code that works:


Public Function RunStoredProcedureUserName(MySP As String, UserName As String)

   
       Dim MyCmd As New ADODB.Command
       Dim MyConnectionString As String
       Dim MyParameter_UserName As New ADODB.Parameter
        Dim MyReturn As Variant
       
       MyConnectionString = GetConnectionString()
   
       MyCmd.ActiveConnection = MyConnectionString
       MyCmd.CommandType = adCmdStoredProc
       MyCmd.CommandText = MySP


       ' UserName Param
       Set MyParameter_UserName = MyCmd.CreateParameter("UserName", adVarChar, adParamInput, 50)
       MyParameter_UserName.Value = Trim(UserName)
       MyCmd.Parameters.Append MyParameter_UserName
       
       'Set timeout
       MyCmd.COMMANDTIMEOUT = COMMANDTIMEOUT
       
       ' Execute
       MyCmd.Execute
       
       ' Close out
       Set MyCmd = Nothing
       

   
End Function


Here is some similar code that does not work:

Public Function RunStoredProcedureUserNameAndOneParam(MySP As String, UserName As String, Param1 As Double)

   
       Dim MyCmd As New ADODB.Command
       
       MyCmd.ActiveConnection = GetConnectionString()
       MyCmd.CommandType = adCmdStoredProc
       
       ' Command Text
       MyCmd.CommandText = MySP

        ' UserName Parameter
       MyCmd.Parameters(1) = UserName
       
       ' Parameter 1
       MyCmd.Parameters(2) = Param1
       
       'Set timeout
       MyCmd.CommandTimeout = 500
       
       ' Execute
       MyCmd.Execute
       
       ' Close out
       Set MyCmd = Nothing
       

   
End Function


0
 
Anthony PerkinsCommented:
Do it this way, it is a lot simpler and easier:
' Use a Sub instead of a Function as it does not appear to return any values
Public Sub RunStoredProcedureUserNameAndOneParam(MySP As String, UserName As String, Param1 As Double)
Dim MyCmd As ADODB.Command      ' Do not use New here.

Set MyCmd = New ADODB.Command   ' Initialize it here
With MyCmd
    .ActiveConnection = GetConnectionString()
    .CommandType = adCmdStoredProc

    ' Command Text
    .CommandText = MySP

    ' UserName Parameter
    .Parameters.Append .CreateParameter("@UserName", adVarChar, adParamInput, 50, UserName)
    
    ' Parameter 1
    .Parameters.Append .CreateParameter("@Param1", adVarChar, adParamInput, 50, Param1)

    'Set timeout
    .CommandTimeout = 500      ' Do you really want to wait 8 minutes?  This seems excessive

    ' Execute
    .Execute

End With

' You are not closing here, just disposing of the memory used by MyCmd
Set MyCmd = Nothing

End Sub

Open in new window

0
 
Anthony PerkinsCommented:
Oops, I jsut noticed that Param1 is double, so I am going to assume that you are using float (for better or worse) in your Stored Procedure and the size is 8 bytes.  So the following line:
    .Parameters.Append .CreateParameter("@Param1", adVarChar, adParamInput, 50, Param1)
Should be:
    .Parameters.Append .CreateParameter("@Param1", adDouble, adParamInput, 8, Param1)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.