Solved

Ado cmd.execute not working

Posted on 2001-08-29
3
614 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:Paullkha
[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
  • 2
3 Comments
 

Accepted Solution

by:
abhijitmathure earned 75 total points
ID: 6437474
As u have used the
ocmd.Parameters.Refresh to clear the cmd collection, u would have to do the same within the loop , so that it does not contain information of the first run , add the following line immediately after the

   For Each oID In oIDs
'/******
ocmd.Parameters.Refresh
'/*******
       ocmd.Parameters("@CID") = oID
       ocmd.Execute
       myfax = ocmd.Parameters("@FAX")
   Next
0
 
LVL 2

Author Comment

by:Paullkha
ID: 6437530
I need some help on .Refresh I think.
The below additional code does not give me the same problem as .Refresh does:
   Set opar = ocmd.CreateParameter("@cid", adChar, adParamInput, 2)
    ocmd.Parameters.Append opar
    Set opar = ocmd.CreateParameter("@FAX", adVarChar, adParamOutput, 20)
    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.

0
 

Expert Comment

by:abhijitmathure
ID: 6437711
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)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

734 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