?
Solved

ADO Parameter issue

Posted on 2010-09-10
14
Medium Priority
?
553 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 800 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 800 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 1200 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

719 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