Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • Last Modified:

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?
0
hertzgordman
Asked:
hertzgordman
  • 5
  • 5
  • 4
3 Solutions
 
packratt_jkCommented:
0
 
hertzgordmanAuthor Commented:
Thanks updated the MDAC on the non-functioning machine but unfortunately the problem persists.
0
 
packratt_jkCommented:
the component checker came back clean?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hertzgordmanAuthor Commented:
Yes it came back clean for version 2.82
0
 
packratt_jkCommented:
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:
That is because you have not created the parameter yet.  In other words, the collection MyCmd.Parameters has not been initialized anywhere.
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now