Solved

ADO Parameter issue

Posted on 2010-09-10
14
528 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
[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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

762 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