Paullkha
asked on
Ado cmd.execute not working
I have a stored procedure as follows:
CREATE PROCEDURE Test @cid as char(2) ,@fax as varchar(20) output as
select @fax = fax
from t1
where cid = @cid
The table T1 contains the following rows:
cid fax
aa 100
cc 200
dd <NULL>
bb <NULL>
And here is the ado code:
Public Sub test()
Dim oconn As ADODB.Connection
Dim ors As ADODB.Recordset
Dim ocmd As ADODB.Command
Dim opar As ADODB.Parameter
Dim oIDs As New Collection
Dim oID As Variant
Dim myfax as variant
Set oconn = New ADODB.Connection
Set ocmd = New ADODB.Command
oconn.Open (testconnstr)
ocmd.ActiveConnection = oconn
ocmd.CommandText = "Test"
ocmd.CommandType = adCmdStoredProc
ocmd.Parameters.Refresh
oIDs.Add ("aa")
oIDs.Add ("qq")
oIDs.Add ("bb")
For Each oID In oIDs
ocmd.Parameters("@CID") = oID
ocmd.Execute
myfax = ocmd.Parameters("@FAX")
Next
Set ocmd = Nothing
Set oconn = Nothing
End Sub
When oid = "qq", I want myfax to = null. It appears @FAX is not update and instead myfax = 100 when oid = qq. I can make some work arounds, but what is going on, how is this normally handled?
CREATE PROCEDURE Test @cid as char(2) ,@fax as varchar(20) output as
select @fax = fax
from t1
where cid = @cid
The table T1 contains the following rows:
cid fax
aa 100
cc 200
dd <NULL>
bb <NULL>
And here is the ado code:
Public Sub test()
Dim oconn As ADODB.Connection
Dim ors As ADODB.Recordset
Dim ocmd As ADODB.Command
Dim opar As ADODB.Parameter
Dim oIDs As New Collection
Dim oID As Variant
Dim myfax as variant
Set oconn = New ADODB.Connection
Set ocmd = New ADODB.Command
oconn.Open (testconnstr)
ocmd.ActiveConnection = oconn
ocmd.CommandText = "Test"
ocmd.CommandType = adCmdStoredProc
ocmd.Parameters.Refresh
oIDs.Add ("aa")
oIDs.Add ("qq")
oIDs.Add ("bb")
For Each oID In oIDs
ocmd.Parameters("@CID") = oID
ocmd.Execute
myfax = ocmd.Parameters("@FAX")
Next
Set ocmd = Nothing
Set oconn = Nothing
End Sub
When oid = "qq", I want myfax to = null. It appears @FAX is not update and instead myfax = 100 when oid = qq. I can make some work arounds, but what is going on, how is this normally handled?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you profile the processing of the stored proc on the database, would will notice the following, during the 3 loops that take place thru the VB code
a) declare @P1 varchar(20)
set @P1='100'
exec Test 'aa', @P1 output
select @P1
b) declare @P1 varchar(20)
set @P1='100'
exec Test 'qq', @P1 output
select @P1
c) declare @P1 varchar(20)
set @P1='200'
exec Test 'bb', @P1 output
select @P1
Remember that the oCMD object is persistent in your VB code, when u first run the stored proc for 'aa' the VALUES are set in the oCMD object for all the 3 types defined in the stored procedures (3rd being the returntype)
The first run update the fax variable, which is used for the SET in the second run, within the database context. This SET is done, since the oCMD is now NOT REFRESHED to reflect the context of the database, which would be essential for updating the object reference in VB , which does not still have the info. from the database execution.
If you want to check the behaviour, keep the table in MS SQL 7 + , run profiler and keep the events for only stored procedures, you will notice the about 3 steps taking place, with the SET before the exec for the stored procedure.
Do note the as per the second part, where you do
Set opar = ocmd.CreateParameter("@cid ", adChar, adParamInput, 2)
you are resetting the stored-procedure variables (the 3 as mentioned above)
a) declare @P1 varchar(20)
set @P1='100'
exec Test 'aa', @P1 output
select @P1
b) declare @P1 varchar(20)
set @P1='100'
exec Test 'qq', @P1 output
select @P1
c) declare @P1 varchar(20)
set @P1='200'
exec Test 'bb', @P1 output
select @P1
Remember that the oCMD object is persistent in your VB code, when u first run the stored proc for 'aa' the VALUES are set in the oCMD object for all the 3 types defined in the stored procedures (3rd being the returntype)
The first run update the fax variable, which is used for the SET in the second run, within the database context. This SET is done, since the oCMD is now NOT REFRESHED to reflect the context of the database, which would be essential for updating the object reference in VB , which does not still have the info. from the database execution.
If you want to check the behaviour, keep the table in MS SQL 7 + , run profiler and keep the events for only stored procedures, you will notice the about 3 steps taking place, with the SET before the exec for the stored procedure.
Do note the as per the second part, where you do
Set opar = ocmd.CreateParameter("@cid
you are resetting the stored-procedure variables (the 3 as mentioned above)
ASKER
The below additional code does not give me the same problem as .Refresh does:
Set opar = ocmd.CreateParameter("@cid
ocmd.Parameters.Append opar
Set opar = ocmd.CreateParameter("@FAX
ocmd.Parameters.Append opar
I though .Refresh does exactly like above; go to server, get parameters, append parameters, return.
Why do I have to .Refresh before each .Execute? Why does this problem occur only when "empty" set returned.