April 19, 2010 at 9:25 am
Eric Mamet (4/18/2010)
I tried Parameters.Refresh but it fails all the time.Apparently, when I do this, it seems to try and fire the stored procedure which does not work because some parameters are required...
Is there anything more to do for this to work?
I do seem to remember that the .Refresh fails in some circumstances depending on the connection object - could you post your code that is generating the error?
April 19, 2010 at 9:31 am
It's a little bit longish but here we go.
My ADO reference is ADO 2.5 (I don't know whether I can use 2.6 yet)
Function TrackApplicationUsage()
Dim cmd As ADODB.Command
On Error GoTo ErrorHandler
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = Application.CurrentProject.Connection 'or your connection object
.CommandText = "basilea.p_TrackUsage" 'Proc Name
.CommandType = adCmdStoredProc
End With
' WARNING: the parameters must be the same and in the same order!!!
Dim sqlParam As ADODB.Parameter
Set sqlParam = New ADODB.Parameter
With sqlParam
.Name = "@Debug"
.Type = adBoolean
.Value = False
End With
cmd.Parameters.Append sqlParam
Set sqlParam = New ADODB.Parameter
With sqlParam
.Name = "@WarningMsg"
.Type = adVarChar
.Value = "..."
.Size = 8000
.Direction = adParamInputOutput
End With
cmd.Parameters.Append sqlParam
Set sqlParam = New ADODB.Parameter
With sqlParam
.Name = "@Return"
.Type = adInteger
.Value = 0
.Direction = adParamInputOutput
End With
cmd.Parameters.Append sqlParam
Set sqlParam = New ADODB.Parameter
With sqlParam
.Name = "@Version"
.Type = adDouble
.Value = modCommon.Version
End With
cmd.Parameters.Append sqlParam
Set sqlParam = New ADODB.Parameter
With sqlParam
.Name = "@CompatibleVersion"
.Type = adDouble
.Value = modCommon.CompatibleVersion
End With
cmd.Parameters.Append sqlParam
' blows up here...
'cmd.Parameters.Refresh
cmd.Execute
April 19, 2010 at 9:42 am
try this:
Function TrackApplicationUsage()
Dim cmd As ADODB.Command
On Error GoTo ErrorHandler
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = Application.CurrentProject.Connection 'or your connection object
.CommandText = "basilea.p_TrackUsage" 'Proc Name
.CommandType = adCmdStoredProc
.Parameters.Refresh
'Set the value of any parameters here by name e.g.
.Parameters("@Debug") = False
.Parameters("@WarningMsg") = "..."
.Parameters("@Return") = 0
.Parameters("@Version") = modCommon.Version
.Parameters("@CompatibleVersion") = modCommon.Compatib
.Execute
End With
You don't need to add your parameters - the .Refresh should return the collection to the
connection object, and you should then be able to set them in any order using their names
April 19, 2010 at 9:57 am
This worked for me:
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "sp_AdoTest"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters("varName").Value = 10
Set Rs1 = Cmd1.Execute()
From the following link:
April 19, 2010 at 10:02 am
I just tried that.
In that case, it first blows up when doing the refresh because ADO tries to investigate the stored proc by firing it with no parameter and the usual discovery tool SET FMTONLY ON
Because the proc does not have NULLs for this, it fails
I tried to supply default values, then it gets through the cmd.Parameters.Refresh but it still does not know about any parameter(s) afterwards so it blows up when I try to assign a value.
It looks like I should try to use ADO 2.6 if I can.
April 19, 2010 at 11:24 am
FYI - I'm using ADO 2.5 and it works
April 20, 2010 at 2:05 am
Damn!
Ok, I'll try to come up with a simple piece of code based on one of the sql server sample databases...
Can't do it right now but I'll be back!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply