hertzgordman
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the component checker came back clean?
ASKER
Yes it came back clean for version 2.82
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes there is Access 2007 and Access 2003
same setup on server 1 and server 2?
I'm not sure how well those 2 play together...
I'm not sure how well those 2 play together...
ASKER
I would bet the mult versions has a lot to do with this problem...
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).
Please post the complete code you are using.
ASKER
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("Use rName", 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 RunStoredProcedureUserName AndOnePara m(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
Public Function RunStoredProcedureUserName
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("Use
MyParameter_UserName.Value
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 RunStoredProcedureUserName
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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)
.Parameters.Append .CreateParameter("@Param1"
Should be:
.Parameters.Append .CreateParameter("@Param1"
ASKER