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
Solved

ADO Parameter issue

Posted on 2010-09-10
14
519 Views
Last Modified: 2012-05-10
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
Comment
Question by:hertzgordman
  • 5
  • 5
  • 4
14 Comments
 
LVL 3

Assisted Solution

by:packratt_jk
packratt_jk earned 200 total points
ID: 33648187
0
 

Author Comment

by:hertzgordman
ID: 33648888
Thanks updated the MDAC on the non-functioning machine but unfortunately the problem persists.
0
 
LVL 3

Expert Comment

by:packratt_jk
ID: 33649010
the component checker came back clean?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:hertzgordman
ID: 33649092
Yes it came back clean for version 2.82
0
 
LVL 3

Assisted Solution

by:packratt_jk
packratt_jk earned 200 total points
ID: 33650896
Perhaps there are multiple versions of access installed?
0
 

Author Comment

by:hertzgordman
ID: 33650951
Yes there is Access 2007 and Access 2003
0
 
LVL 3

Expert Comment

by:packratt_jk
ID: 33650986
same setup on server 1 and server 2?

I'm not sure how well those 2 play together...
0
 

Author Comment

by:hertzgordman
ID: 33651007
I would bet the mult versions has a lot to do with this problem...
0
 
LVL 3

Expert Comment

by:packratt_jk
ID: 33651143
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33651606
Please post the complete code you are using.
0
 

Author Comment

by:hertzgordman
ID: 33654485
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 33654576
That is because you have not created the parameter yet.  In other words, the collection MyCmd.Parameters has not been initialized anywhere.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33654600
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33654612
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

838 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