Solved

Running SQL Stored Procedure from VB

Posted on 2011-09-15
10
323 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
Comment Utility
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]
Comment Utility
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
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:kqureshi321
Comment Utility
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
Comment Utility
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
Comment Utility
> 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
Comment Utility
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
Comment Utility
Sorry, I meant I put this two procedures together in one.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This article will show, step by step, how to integrate R code into a R Sweave document
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now