Solved

ADO Parameter issue

Posted on 2010-09-10
14
489 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now