Solved

Running SQL Stored Procedure from VB

Posted on 2011-09-15
10
343 Views
Last Modified: 2013-12-25
I am running two stored procedures in SQL 2005 from VB. First one runs fine but the second it looks like it runs but does not do anything. It does not give me any errors and I know it works and runs in SQL but from VB the second proc just runs thru and does not execute. I am not sure why it would not work for the second command.

    Cmd1.ActiveConnection = Con1
    Cmd1.CommandType = adCmdStoredProc
    Cmd1.CommandText = "UpdateTemp"
    Cmd1.CommandTimeout = 0
    Cmd1.Execute
    Cmd2.ActiveConnection = Con1
    Cmd2.CommandType = adCmdStoredProc
    Cmd2.CommandText = "ApplyPay"
    Cmd2.CommandTimeout = 0
    Cmd2.Execute
    Con1.Close
0
Comment
Question by:kqureshi321
  • 5
  • 3
10 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 36542892
Did you try to close the connection after first proc and then open it again and run the second proc?

    Cmd1.ActiveConnection = Con1
    Cmd1.CommandType = adCmdStoredProc
    Cmd1.CommandText = "UpdateTemp"
    Cmd1.CommandTimeout = 0
    Cmd1.Execute
    Con1.Close
    con1.open
    Cmd2.ActiveConnection = Con1
    Cmd2.CommandType = adCmdStoredProc
    Cmd2.CommandText = "ApplyPay"
    Cmd2.CommandTimeout = 0
    Cmd2.Execute
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36542906
it actually runs, but " does not execute" usually means that it does not what you think it does.
seeing the procedure code might help to clarify.
adding debugging steps to the procedure(s) would be part of this
0
 

Author Comment

by:kqureshi321
ID: 36543052
Code for the executing the procedure works it executes but the procedure itself does not do what it suppose to do. When I execute the same procedure inside SQL it works fine. And same procedure runs every night on the job schedule in SQL just fine.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36543373
as I wrote above, you need to debug what is happening.

executing stored procedures does not "do nothing".
either there is an error (it might be ignored/catched in the error handling somewhere), or the procedure does run.
you might not check in the same database then where you run the procedure
you might look at the wrong rows
you might have some other process that undoes the changes by the procedure.
....
0
 

Author Comment

by:kqureshi321
ID: 36544663
The thing is both procedures run in the same database (only one connection to one db). There is no error handling for this piece of code and there is no process to undo the changes.  First procedure creates temp table with outstanding balances and runs for 3-4 min. Second one applying payments to the outstanding balances (when it is done running outstanding balances should be 0) and suppose to run even longer than the first. But when you debug  it in VB it goes thru the execute line in a 1 sec and when I check the payment is not applied.
0
 

Author Comment

by:kqureshi321
ID: 36544810
Yes, I did try to close and open the connection again. Unfortunately it did not do anything.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36547624
> First procedure creates temp table
temp table like #table name?

if that is the case, you might have the problem that the second procedure finds that table empty after quitting the first one ...

why is this not in 1 single procedure?
resp, you could create 1 procedure calling the 2 procedures?

next: why not put that into a sql agent job, and the GUI application does only start the job, so it processes async. having to wait in a gui to run something for over 3 minutes without knowing if the job is still running or simply the app crashed is no good design.
0
 

Author Comment

by:kqureshi321
ID: 36567245
Putting this two procedures was not  a bad idea. That is what I did. And it is running fine in SQL and still not applying payments from VB.
0
 

Author Comment

by:kqureshi321
ID: 36567421
Sorry, I meant I put this two procedures together in one.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB6 - Convert HH:MM into Decimal 8 56
VB6 ListBox Question 4 44
Excel Automation VBA 19 71
Need Nag Screen for Reboot if system up time is over 14 days 12 51
The purpose of this article is to demonstrate how we can use conditional statements using Python.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

810 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