• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

Cannot get value returned from a called stored procedure in VB before the rest of my program completes

I am calling a stored procedure in VB 6.0 that is suppose to return a value needed to run andother stored procedure and complete the rest of my Sub. The first stored procedure can take anywhere from 3 minutes to only 20 seconds depending on how many people are accessing the server so I cannot set a standard wait time for the stored procedure to complete and right now I am not getting a returned value at all before my Sub moves on to a part of my code that needs information from the stored procedure.  Can anyone help me with this?
Private Sub Act_On_StdDev_Result(LastID As Long)
 
On Error Resume Next
 
Dim QueryID As String
Dim TypeID As Long
 
'Load frmStdDev_Wait
 
frmStdDev_Wait.Show
 
frmStdDev_Wait.ZOrder 0
 
 
If OpenDataBaseConnection Then 'This is a function that opens my database connection
 
 
 
    'Execute Stored procedure to Update TypeID
 
        Dim dbobjcmd_T As New ADODB.Command
        
        dbobjcmd_T.ActiveConnection = dbobjConnSD
        dbobjcmd_T.CommandText = "dbo.sp_UpdateTypeID"
        dbobjcmd_T.CommandType = adCmdStoredProc
        dbobjcmd_T.Parameters.Append dbobjcmd_T.CreateParameter("Data_ID", adInteger, adParamInput, LastID)
        dbobjcmd_T.Parameters.Append dbobjcmd_T.CreateParameter("Type_ID", adInteger, adParamOutput)
        dbobjcmd_T.Execute
                    
                    
        TypeID = dbobjcmd_T("Type_ID")
        
 
        'Delete parameters of dbobjCmd for more use
        dbobjcmd_T.Parameters.Delete ("Data_ID")
        dbobjcmd_T.Parameters.Delete ("Type_ID")
 
        
    'Execute Stored procedure to Calculate
 
        Dim dbobjcmd_C As New ADODB.Command
 
        dbobjcmd_C.ActiveConnection = dbobjConnSD
        dbobjcmd_C.CommandText = "dbo.sp_Calculate"
        dbobjcmd_C.CommandType = adCmdStoredProc
        dbobjcmd_C.Parameters.Append dbobjcmd_C.CreateParameter("Type_I_D", adInteger, adParamInput, TypeID)
        dbobjcmd_C.Execute
                    
        'Delete parameters of dbobjCmd for more use
        dbobjcmd_C.Parameters.Delete ("Type_I_D")
    
 
        Unload frmStdDev_Wait
 
End If '1
 
    Set dbobjcmd_T = Nothing
    Set dbobjcmd_C = Nothing
    Call CloseDataBaseConnection 'This is a function that closes my database connection
 
 
End Sub

Open in new window

0
Elisheva_Binyamin
Asked:
Elisheva_Binyamin
  • 9
  • 5
  • 3
1 Solution
 
karstiemanCommented:
Here's what you should do.
declare a public bool, --> dim sub1finished as boolean = false

In the first sub, the one that has different times to complete, add an boolean that changes to true at the end of the sub.  --> sub1finished = true

Then, in the main code, just before calling the second sub add the code from the code snippet below:



do until sub1finished = true
application doevents()
loop
' set the variable back to false
sub1finished = false

Open in new window

0
 
karstiemanCommented:
i messed up the previos post. This is the VB6 way:
Declare the boolean as

public sub1finished as boolean
sub1finished = false

The code is just a bit different:
do until sub1finished = true
application.doevents()
loop
 
' set the variable back to false
sub1finished = false

Open in new window

0
 
Elisheva_BinyaminAuthor Commented:
Thank you for your reply, but I am not sure which part of my application should be contained within the do until loop.  I did try before submitting my question the following loop (see below), but the loop never terminated despite the fact that when I just ran the stored procedure from SQL it only took 20 seconds that time.  Where in the code should I put my do until?  I don't want to repeatedly call the stored procedure, because I want it to only run once, but I do need to know when the stored procedure has completed and returned a result.
Private Sub Act_On_StdDev_Result(LastID As Long)
 
On Error Resume Next
 
Dim QueryID As String
Dim TypeID As Long
 
'Load frmStdDev_Wait
 
frmStdDev_Wait.Show
 
frmStdDev_Wait.ZOrder 0
 
 
If OpenDataBaseConnection Then 'This is a function that opens my database connection
 
 
 
    'Execute Stored procedure to Update TypeID
 
        Dim dbobjcmd_T As New ADODB.Command
        
        dbobjcmd_T.ActiveConnection = dbobjConnSD
        dbobjcmd_T.CommandText = "dbo.sp_UpdateTypeID"
        dbobjcmd_T.CommandType = adCmdStoredProc
        dbobjcmd_T.Parameters.Append dbobjcmd_T.CreateParameter("Data_ID", adInteger, adParamInput, LastID)
        dbobjcmd_T.Parameters.Append dbobjcmd_T.CreateParameter("Type_ID", adInteger, adParamOutput)
        dbobjcmd_T.Execute
                    
        
	Do
            
        TypeID = dbobjcmd_T("Type_ID")
 
	Until TypeID <> 0 'This is the default TypeID if the stored procedure has not returned a value
        
 
        'Delete parameters of dbobjCmd for more use
        dbobjcmd_T.Parameters.Delete ("Data_ID")
        dbobjcmd_T.Parameters.Delete ("Type_ID")
 
        
    'Execute Stored procedure to Calculate
 
        Dim dbobjcmd_C As New ADODB.Command
 
        dbobjcmd_C.ActiveConnection = dbobjConnSD
        dbobjcmd_C.CommandText = "dbo.sp_Calculate"
        dbobjcmd_C.CommandType = adCmdStoredProc
        dbobjcmd_C.Parameters.Append dbobjcmd_C.CreateParameter("Type_I_D", adInteger, adParamInput, TypeID)
        dbobjcmd_C.Execute
                    
        'Delete parameters of dbobjCmd for more use
        dbobjcmd_C.Parameters.Delete ("Type_I_D")
    
 
        Unload frmStdDev_Wait
 
End If '1
 
    Set dbobjcmd_T = Nothing
    Set dbobjcmd_C = Nothing
    Call CloseDataBaseConnection 'This is a function that closes my database connection
 
 
End Sub

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Elisheva_BinyaminAuthor Commented:
I also tried simply using a query to get a value, before closing out my stored procedure, but this loop also never terminated.  You can look at the code below to see how I did it.  I also tried this before I submitted my question.
Private Sub Act_On_StdDev_Result(LastID As Long)
 
On Error Resume Next
 
Dim QueryID As String
Dim TypeID As Long
 
'Load frmStdDev_Wait
 
frmStdDev_Wait.Show
 
frmStdDev_Wait.ZOrder 0
 
 
If OpenDataBaseConnection Then 'This is a function that opens my database connection
 
 
 
    'Execute Stored procedure to Update TypeID
 
        Dim dbobjcmd_T As New ADODB.Command
        
        dbobjcmd_T.ActiveConnection = dbobjConnSD
        dbobjcmd_T.CommandText = "dbo.sp_UpdateTypeID"
        dbobjcmd_T.CommandType = adCmdStoredProc
        dbobjcmd_T.Parameters.Append dbobjcmd_T.CreateParameter("Data_ID", adInteger, adParamInput, LastID)
        dbobjcmd_T.Parameters.Append dbobjcmd_T.CreateParameter("Type_ID", adInteger, adParamOutput)
        dbobjcmd_T.Execute
                 
         
	'Execute Query to get TypeID   
        TypeID = dbobjcmd_T("Type_ID")
 
        
	Dim dbobjcmd_P As New ADODB.Command
 
   	Dim dbobjrs_P As New ADODB.Recordset
    
    	QueryID = "SELECT ScanType_ID FROM ScanData_ScanType WHERE ScanData_ID = " & LastScanID
    
    	Do 'Loop until a value is assigned to TypeID from stored procedure
 
        dbobjrs_P.CursorLocation = adUseClient
        dbobjrs_P.Open QueryID, dbobjConnSD, adOpenStatic, adLockReadOnly
        Set dbobjrs_P.ActiveConnection = Nothing
        TypeID = dbobjrs_P(0).value
        dbobjrsSD_Type.Close	
 
	Until TypeID <> 0 'This is the default TypeID if the stored procedure has not returned a value
 
 
 
 
        'Delete parameters of dbobjCmd for more use
        dbobjcmd_T.Parameters.Delete ("Data_ID")
        dbobjcmd_T.Parameters.Delete ("Type_ID")
 
        
    'Execute Stored procedure to Calculate
 
        Dim dbobjcmd_C As New ADODB.Command
 
        dbobjcmd_C.ActiveConnection = dbobjConnSD
        dbobjcmd_C.CommandText = "dbo.sp_Calculate"
        dbobjcmd_C.CommandType = adCmdStoredProc
        dbobjcmd_C.Parameters.Append dbobjcmd_C.CreateParameter("Type_I_D", adInteger, adParamInput, TypeID)
        dbobjcmd_C.Execute
                    
        'Delete parameters of dbobjCmd for more use
        dbobjcmd_C.Parameters.Delete ("Type_I_D")
    
 
        Unload frmStdDev_Wait
 
End If 
 
    Set dbobjcmd_T = Nothing
    Set dbobjcmd_C = Nothing
    Set dbobjcmd_P = Nothing
    Call CloseDataBaseConnection 'This is a function that closes my database connection
 
 
End Sub

Open in new window

0
 
Elisheva_BinyaminAuthor Commented:
Sorry! I typed the wrong code.  Ignore my last comment.  Thanks!
Private Sub Act_On_StdDev_Result(LastID As Long)
 
On Error Resume Next
 
Dim QueryID As String
Dim TypeID As Long
 
'Load frmStdDev_Wait
 
frmStdDev_Wait.Show
 
frmStdDev_Wait.ZOrder 0
 
 
If OpenDataBaseConnection Then 'This is a function that opens my database connection
 
 
 
    'Execute Stored procedure to Update TypeID
 
        Dim dbobjcmd_T As New ADODB.Command
        
        dbobjcmd_T.ActiveConnection = dbobjConnSD
        dbobjcmd_T.CommandText = "dbo.sp_UpdateTypeID"
        dbobjcmd_T.CommandType = adCmdStoredProc
        dbobjcmd_T.Parameters.Append dbobjcmd_T.CreateParameter("Data_ID", adInteger, adParamInput, LastID)
        dbobjcmd_T.Parameters.Append dbobjcmd_T.CreateParameter("Type_ID", adInteger, adParamOutput)
        dbobjcmd_T.Execute
                 
         
	'Execute Query to get TypeID   
        TypeID = dbobjcmd_T("Type_ID")
 
        
	Dim dbobjcmd_P As New ADODB.Command
 
   	Dim dbobjrs_P As New ADODB.Recordset
    
    	QueryID = "SELECT Type_ID FROM Data_Type WHERE Data_ID = " & LastID
    
    	Do 'Loop until a value is assigned to TypeID from stored procedure
 
        dbobjrs_P.CursorLocation = adUseClient
        dbobjrs_P.Open QueryID, dbobjConnSD, adOpenStatic, adLockReadOnly
        Set dbobjrs_P.ActiveConnection = Nothing
        TypeID = dbobjrs_P(0).value
        dbobjrsSD_Type.Close	
 
	Until TypeID <> 0 'This is the default TypeID if the stored procedure has not returned a value
 
 
 
 
        'Delete parameters of dbobjCmd for more use
        dbobjcmd_T.Parameters.Delete ("Data_ID")
        dbobjcmd_T.Parameters.Delete ("Type_ID")
 
        
    'Execute Stored procedure to Calculate
 
        Dim dbobjcmd_C As New ADODB.Command
 
        dbobjcmd_C.ActiveConnection = dbobjConnSD
        dbobjcmd_C.CommandText = "dbo.sp_Calculate"
        dbobjcmd_C.CommandType = adCmdStoredProc
        dbobjcmd_C.Parameters.Append dbobjcmd_C.CreateParameter("Type_I_D", adInteger, adParamInput, TypeID)
        dbobjcmd_C.Execute
                    
        'Delete parameters of dbobjCmd for more use
        dbobjcmd_C.Parameters.Delete ("Type_I_D")
    
 
        Unload frmStdDev_Wait
 
End If 
 
    Set dbobjcmd_T = Nothing
    Set dbobjcmd_C = Nothing
    Set dbobjcmd_P = Nothing
    Call CloseDataBaseConnection 'This is a function that closes my database connection
 
 
End Sub

Open in new window

0
 
karstiemanCommented:
you got it almost correct.

here's the code as I would use it
'### NEW CODE ###
'Add the Boolean
public sub1finished as boolean
sub1finished = false
 
private sub connecttodb()
 dbobjrs_P.CursorLocation = adUseClient
        dbobjrs_P.Open QueryID, dbobjConnSD, adOpenStatic, adLockReadOnly
        Set dbobjrs_P.ActiveConnection = Nothing
        TypeID = dbobjrs_P(0).value
        dbobjrsSD_Type.Close	
        sub1finished = true
end sub
 
 
Private Sub Act_On_StdDev_Result(LastID As Long)
 
On Error Resume Next
 
Dim QueryID As String
Dim TypeID As Long
 
'Load frmStdDev_Wait
 
frmStdDev_Wait.Show
 
frmStdDev_Wait.ZOrder 0
 
 
If OpenDataBaseConnection Then 'This is a function that opens my database connection
 
'### EXTRA CODE
connecttodb()
 
'Here's the wait-loop
do until sub1finished  = true
application.doevents()
loop
sub1finished = false
 
    'Execute Stored procedure to Update TypeID
 
        Dim dbobjcmd_T As New ADODB.Command
        
        dbobjcmd_T.ActiveConnection = dbobjConnSD
        dbobjcmd_T.CommandText = "dbo.sp_UpdateTypeID"
        dbobjcmd_T.CommandType = adCmdStoredProc
        dbobjcmd_T.Parameters.Append dbobjcmd_T.CreateParameter("Data_ID", adInteger, adParamInput, LastID)
        dbobjcmd_T.Parameters.Append dbobjcmd_T.CreateParameter("Type_ID", adInteger, adParamOutput)
        dbobjcmd_T.Execute
                 
         
	'Execute Query to get TypeID   
        TypeID = dbobjcmd_T("Type_ID")
 
        
	Dim dbobjcmd_P As New ADODB.Command
 
   	Dim dbobjrs_P As New ADODB.Recordset
    
    	QueryID = "SELECT Type_ID FROM Data_Type WHERE Data_ID = " & LastID
    
    	Do 'Loop until a value is assigned to TypeID from stored procedure
 
       ###
 
 
 
 
        'Delete parameters of dbobjCmd for more use
        dbobjcmd_T.Parameters.Delete ("Data_ID")
        dbobjcmd_T.Parameters.Delete ("Type_ID")
 
        
    'Execute Stored procedure to Calculate
 
        Dim dbobjcmd_C As New ADODB.Command
 
        dbobjcmd_C.ActiveConnection = dbobjConnSD
        dbobjcmd_C.CommandText = "dbo.sp_Calculate"
        dbobjcmd_C.CommandType = adCmdStoredProc
        dbobjcmd_C.Parameters.Append dbobjcmd_C.CreateParameter("Type_I_D", adInteger, adParamInput, TypeID)
        dbobjcmd_C.Execute
                    
        'Delete parameters of dbobjCmd for more use
        dbobjcmd_C.Parameters.Delete ("Type_I_D")
    
 
        Unload frmStdDev_Wait
 
End If 
 
    Set dbobjcmd_T = Nothing
    Set dbobjcmd_C = Nothing
    Set dbobjcmd_P = Nothing
    Call CloseDataBaseConnection 'This is a function that closes my database connection
 
 
End Sub

Open in new window

0
 
Elisheva_BinyaminAuthor Commented:
Thank you so much for spelling it out for me, literally. :-)  I love it! That is what I need  and it was of enormous help! However, it does not work simply because I think maybe you misunderstood the order of my logic. I cannot get the value I need from dbobjrs_P unless  dbo.sp_UpdateTypeID has successfully completed running. The program is suppose to do this:
1) Execute dbo.sp_UpdateTypeID on SQL Server
2) dbo.sp_UpdateTypeID is suppose to return a Type_ID when it completes
3) This Type_ID is then assigned to the variable TypeID to be used in the next stored procedure called.  TypeID can also be selected from the query "SELECT Type_ID FROM Data_Type WHERE Data_ID = " & LastID as long as dbo.sp_UpdateTypeID has executed successfully and completed its updates.  This was given in the part of my code using dbobjrs_P.
4) Stored procedure dbo.sp_Calculate is executed on SQL Server using the value assigned TypeID
 
I hope this clarifies my problem.  Thanks!
0
 
Anthony PerkinsCommented:
And chance you can post sp_UpdateTypeID and sp_Calculate?
0
 
Elisheva_BinyaminAuthor Commented:
I might, but I want to warn you they are both very long stored procedures.
0
 
Elisheva_BinyaminAuthor Commented:
Here are the create statements for the two stored procedures I am using.  I hope this helps.

Expert-Exchange---Update-Query.txt
Experts-Exchange-Calculate.txt
0
 
Anthony PerkinsCommented:
First, to restate what you are trying to achieve, let me say that you are executing dbo.sp_Update and when it is done it executes dbo.sp_Calculate.

Here are the problems that I see (in no particular order or relevance):
1.  You are prefixing your Stored Procedures with "sp_".  That is a bad idea.  Change the name to something else such as "usp_"

2.  Your Stored Procedure sp_Update contains these lines:

SET @Type_ID = (SELECT Type_ID from dbo.Dataype where Data_ID = @Data_ID)

SELECT @Type_ID                   -- <-- This line is unecessary, it is not needed.

3. In your Stored Procedure sp_Update has this:
FROM dbo.Data s
INNER JOIN CR c
ON s.CR_ID = c.CR_ID
WHERE s.Data_ID NOT IN (Select Data_ID from Type_1)

Try something like this instead:
FROM      dbo.Data s
            INNER JOIN CR c ON s.CR_ID = c.CR_ID
WHERE      NOT EXISTS (
                  Select      1
                  From      Type_1
                  Where      Data_ID = s.Data_ID)


4. Make sure that your tables are appropriately indexed.

5. In your Stored Procedure sp_Update has this:
INSERT
...
ORDER BY Type_ID
It is very rarely necessary to do an ORDER BY on an Insert and in general should be avoided.  The exception is when using a table with an IDENTITY column and the order of that column is important.

6. I believe the real cause of your problems lie with the following line:
On Error Resume Next
Here is why:  The Stored Procedure sp_Update should not complete until it has finished successfully or with an error.  In your case because you are using On Error Resume Next you do not know the difference.  In other words you are assuming that the first stored procedure has not completed before the next one starts.  This is not true.  Unless setup differently they execute synchronously.  So the first one finished executing with an error, but you have chosen to ignore it.

7. You should add SET NOCOUNT ON to the top of your Stored Procedure sp_Update as you did with sp_Calculate.

8. Your Stored Procedure sp_Calculate has this:
SELECT @Type_ID = Type_ID
FROM dbo.Data_Type
WHERE Data_ID = @LastID

But it is unecessary.  @LastID = @TypeID.  (Look at the end of the previous Stored Procedure)

9.  In your Stored Procedure sp_Calculate you have 31 inserts into the same dbo.Averages table.  Many of those, if not all, could be combined into a single INSERT statement.

10.  Don't use code like this:
Dim dbobjcmd_T As New ADODB.Command

Instead use this:
Dim dbobjcmd_T As ADODB.Command
Set dbobjcmd_T = New ADODB.Command

11.  The following is no longer necessary:
      Dim dbobjcmd_P As New ADODB.Command
 
         Dim dbobjrs_P As New ADODB.Recordset
   
          QueryID = "SELECT Type_ID FROM Data_Type WHERE Data_ID = " & LastID
   

        dbobjcmd_T.Parameters.Delete ("Data_ID")
        dbobjcmd_T.Parameters.Delete ("Type_ID")
...
        dbobjcmd_C.Parameters.Delete ("Type_I_D")

12.  The following should have produced an error:
          Do 'Loop until a value is assigned to TypeID from stored procedure

As there is no Loop

13.  This is unnecessary:
do until sub1finished  = true
application.doevents()
loop

The function sub1finished executes synchronously.

14. The QueryID variable in the following line is not defined in this context (it is in the other Sub):
dbobjrs_P.Open QueryID, dbobjConnSD, adOpenStatic, adLockReadOnly
So it should produce an error.

15.  Why not combine the two Stored Procedures into one.  Or at the very least one Stored Procedure that calls both.
0
 
Elisheva_BinyaminAuthor Commented:

Thank you for your comments.  I really appreciate you taking so much time and explaining each item for me. Let me address each issue one by one:

1)  I do not mind changing the name of the stored procedure, but they were named that way because it is the naming convention used by my company, not one I created.

2) If you read over the previous posts on this thread you will see that actually this was a question regarding a problem I was having of not being able to pull a value a from an executed stored procedure in a VB6 to use in a second stored procedure being executed in the same sub.  The SELECT @Type_ID was intended to return the Type_ID from the final query of the stored procedure to be passed into the VB program so that it s value can be assigned to another variable that is used to execute the next stored procedure.  This of course can also be done by a separate select query in the VB sub.  I've tried both methods without success.

3) Unfortunately I cannot use (
                  Select      1
                  From      Type_1
                  Where      Data_ID = s.Data_ID)
since my query is really looking to see if there are any records in Data that are not in Type_1.  If I use select 1 the query will only give me a list of 1 value rather than all the values in the Type_1 table.

4) I think I do, but it is always worth a second look.

5)  This is exactly the reason I have the order by as it is inserting a records into a table that has an identity column and it is important that the types inserted are done in the order they are actually stored chronologically in the database.

6) Excellent point.  I hadn't thought of that.  I will try it and see if it works better without it. .

7) Another good point.  I should have done that to begin with.

8) You may be right about this one.  I had one logic in mind, pass the Type_ID from the previous stored proc and use to calculate averages and standard deviations, but my code has me actually passing the Data_ID, not the Type_ID.  I would just need to change the parameter that I am passing in from Type_ID to Data_ID.  However, I still will not get the right answer with either method if the Update proc doesn't complete first.

9) To do this I would need to either create a cursor or loop since each of these insert statements come from separate columns, separate views, and in many cases separate logic.  I'm not sure how that code would look, but I could try it.

10)  I'll try that.

11) Agreed.  I only need that if I use a select statement to get the value of Type_ID to pass on to the next stored procedure.

12)  It would.  This was a typo, not actually in the stored procedure.

13) This was an effort to take one of the suggestions made by karstieman as a way to ensure the first stored procedure completes before the second one runs.  Unfortunately this turned into an unending loop.

14) it didn't, but you would think it would.  Again this was an effort to follow someone else's suggestion.

15)  Now that may be a great idea!  If they were in the same stored procedure I could make sure the first set of statements had completed before the second.  I will try that first thing tomorrow morning when I actually at my work computer that at home on my laptop.  I will let you know if works.
 Thank you so much for your detailed comments.  I will let you know what works. :-)
0
 
Anthony PerkinsCommented:
1. I know that is usually the case.  I was merely pointing out that it is a bad idea.

2. As you well know the mechanism for returning values is very different in VB and T-SQL when using output parameters as opposed to a resultset.

3. I suggest you read up on the IF EXISTS T-SQL construct.  It does not look like you fully understand how it works.

6. It is not a question of working better without it.  It is more a question of allowing you to find the real problem.  That is the problem with using ON ERROR RESUME NEXT.  It is a vestige from the mid 80's and in my opinion it should never be used.  It is equivalent of saying if there is an error I don't care, ignore it.

9.  Not necessarily.  For example, it is possible that the the first two update statements could be rewritten:

Update      a
Set      SP0_Avg = v.Average,
      SP0_StdDev = Std_Dev
From      Averages a
      Inner Join dbo.SP0_Avg_StdDev_View v On a.[Type_ID] = v.[Type_ID]
Where      a.Type_ID = @Type_ID

But without seeing the Views it is difficult to say for sure what else you can do.

13.  As I commented earlier, by default Stored Procedures when executed from VB are synchronous.  In other words one stored procedure has to complete before the next one starts.  Notice I did not say complete successfully.  In your case, I suspect it is not completing successfully and because of the ON ERROR RESUME NEXT you are assuming that it did not complete.

15.  Again.  That is the way the stored procedures work.  They are procedural.  One completes before the next one starts.
0
 
Anthony PerkinsCommented:
Point 2 should have read:
2. As you well know the mechanism for returning values is very different in VB and T-SQL when using output parameters as opposed to a resultset.  Use one or the other and not both.  It causes other complications.
0
 
Elisheva_BinyaminAuthor Commented:
First of all, thank you karstieman and acperkins for all your time and assistance.  I finally got my problem fixed, but ironically it was nothing more than a permissions error.  I had not granted my application permission to execute the stored procedures.  Stupid really, but you know how sometimes you get so caught up in the details you forget about the obvious.  I hope you can forgive me for taking up your time on something so fundamental.  It was of great help, and the suggestion that basically solved my problem was acperkins's 6th comment:
acperkins wrote:
6. I believe the real cause of your problems lie with the following line:
On Error Resume Next
Here is why:  The Stored Procedure sp_Update should not complete until it has finished successfully or with an error.  In your case because you are using On Error Resume Next you do not know the difference.  In other words you are assuming that the first stored procedure has not completed before the next one starts.  This is not true.  Unless setup differently they execute synchronously.  So the first one finished executing with an error, but you have chosen to ignore it.
You were right.  I idiotically I used ON ERROR RESUME NEXT instead of finding a way to trap errors and this was the key to me finding the error.  Thank you again for all of your assistance.
My solution is as follows:
1) Add an error checking handler to see what error is occuring.  My code for this is below.
2) Correct error.  In my case it was error -2147217911 which is essentially: Execute permission denied on object. So I went into SQL Server and granted  my application rights to execute the stored proceudres I needed called by my program. Problem solved.

Private Sub SomeProgram(IDNum As Long)
 
On Error GoTo WhatIsError
 
'Your program
 
WhatIsError:
    MsgBox Err.Number
    MsgBox Err.Description
 
End Sub

Open in new window

0
 
Elisheva_BinyaminAuthor Commented:
Thank you so much for your help!  Item 6 was the key.  For anyone looking up this question, please see the steps I took on my last entry to see how I solved the ON ERROR NEXT problem.  Thanks!
0
 
Anthony PerkinsCommented:
A better approach is to use the ADO errors collection.  This should provide more info.  As in:

Dim Er
For Each Er in YourConnection.Errors
    MsgBox "Description = " & Er.Description & vbCr & _
                 "NativeError = " & CStr(Er.NativeError) & vbCr & _
                 "Number = " & CStr(Er.Number) & vbCr & _
                 "Source = " & Er.Source & vbCr & _
                 "SQLState = " & Er.SQLState
Next

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now