VB (GUI) to SQL Procedures:  Interface "not" populating to latest-month (needs to be run 2nd time).

Posted on 2008-06-11
Last Modified: 2013-11-27
I have a problem.  I need to run an Interface that picks-up data thru to the latest month.  Currently - for whatever reason - I need to simply run it twice - for it to populate data thru to the latest-month.  The 1st time around,  it picks up to the previously run month only and stops.   Then,  once I set it to run again - like I did the 1st time around - it goes thru all the way and brings in all the data inclusive thru to the current-month at hand.

Can you guys advise on how to fix this problem?  and - what could be the cause?

The GUI uses VB which then invokes SQL Procedures that pull data from a Sybase Database.  The VB is via Visual Studio 2005.

Please assist,
Thanking you for your time & help,  sincerely, Raj.
Question by:R B
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
  • 6
  • 5
LVL 11

Expert Comment

by:Dany Balian
ID: 21792136
we need a sample of your code, to know what objects are you using...
as a first estimation, i guess it is a caching issue...
if its' a crystal report object then try setting this option immediatly after loading the report file, and before sending the parameters and viewing the report


here's a full example:
Set CReport = Crystal.OpenReport("c:\myreport.rpt")
'logon to report db
CReport.Database.Tables(1).SetLogOnInfo DBServerName, DBName, DBReportingUser, DBReportingPass
'discard cached data
'show or hide group tree on the left
CRViewer1.DisplayGroupTree = True
'Set the Parameters for the Report
CReport.ParameterFields(1).AddCurrentValue startDate
CReport.ParameterFields(2).AddCurrentValue endDate
'set the report object source to current report
CRViewer1.ReportSource = CReport
'view report

Open in new window


Author Comment

by:R B
ID: 21806594
We're not using Crystal Reports here.  It's a Visual Studio 2005 using Visual Basic GUI - that then invokes SQL Procedures.  But, I'm having a problem finding what SQL Procedures it invokes, etc.  There is no current documentation here.   Whatever I found is outdated.  I'll try to get more info....  

Any advise on how to go about finding what exactly operates behind the scenes upon the push of a button?

Thanks,  sincerely ,Raj.
LVL 11

Expert Comment

by:Dany Balian
ID: 21808140
just double click on that button, and the code behind will open..
you can paste that code here, and we'll try to assist you as much we can on resolving the problem...
Three Considerations for Containers

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read our article on Experts Exchange.


Author Comment

by:R B
ID: 21811943
I'm afraid that double clicking is the same as single clicking on the 'Execute' Button in the GUI.  It does not open the code, but just starts the execution of the Interface....

Any other way that I could get to the code that operates behind that button?

Please advise....   Thanking you for your timely help,  sincerely, Raj.
LVL 11

Expert Comment

by:Dany Balian
ID: 21817708
are you doing this in the executable?? or you do have the code!!
cos if you don't then you wont be able to see the code... :)

Author Comment

by:R B
ID: 21821274
Hi...  I do not have the code.  I'm hitting a button that says "Execute" in the GUI.  

I went to SQL Advantage and found a huge number of Procedures - but, I cannot tell at all which ones are being used by this Execute Button........

Any ideas on how I could find out which ones this execute button triggers? - and in what order?
Would this troubleshoot what's going wrong with my Interface having to be run twice to pick-up the latest pay-period data?

Thanking you for your time & feedback...
sincerely, Raj.
LVL 11

Expert Comment

by:Dany Balian
ID: 21823146
unfortunately raj, there's no easy way to know what this button executes from the binary file... (if it can be done, then it must be done by reverse engineering)

the best way to try to figure it out is.. estimate what the button is performing and checking the names of all the procedures.. the closest name to the button's function.
ex: if it's voiding a sale, try looking for procedure names like sp_voidsaleitem or anything similar...

if besides calling the procedure, the button is executing other codes, then we can't know.. if you find the procedure, then you can debug the code, by checking the parameters that are being sent to the procedure, or even maintain the code if anything wrong...

i'm sorry but this is how things are...


Author Comment

by:R B
ID: 21823860
Thanks for the detailed response...  One last question:  If I sit next to the Sybase DBA, while I hit that Execute Button,  would she/me be able to see and monitor which procedures are running?   We have a Sybase Database.  

Before I approach our Sybase DBA with this request/scenario - I want to ask you if this is at all possible? -  to watch from the DBA's view and be able to see what is happening upon hitting that Execute Button.....

I don't want to propose this - if it's not a possible venture in technicality......  I'll really look silly asking if it is not do-able..... technically.....

Please advise......  thank you,  Raj.
LVL 11

Expert Comment

by:Dany Balian
ID: 21824354
well, you cant know the executing procedure name automatically but what you can do, is the following: you can put a debug line in all the procedures that you suspect of... and make them print a line in the msg window of sybase...

you can try something like this:
print string('procedure yourprocedurenamehere was executed using parameters ',@param1name, ',', @param2name)

click the command button, and you will see in the server window something like:

procedure voidsalesdata was executed using parameters 1,2

ofcourse you'll have to get into every stored procedure and paste that line of code at the beginning of the code execution.

hope this helps...


LVL 11

Accepted Solution

Dany Balian earned 500 total points
ID: 21824409
btw when u double click on any procedure in sybase...
it will open the alter statement of that procedure...




Author Closing Comment

by:R B
ID: 31466165
Thanks for the feedback.  We'll give it a try.  I truly appreciate your time & help....   sincerely, Raj.

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month11 days, 14 hours left to enroll

623 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