Solved

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

Posted on 2008-06-11
11
160 Views
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.
0
Comment
Question by:R B
  • 6
  • 5
11 Comments
 
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

CReport.DiscardSavedData

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

CReport.DiscardSavedData

'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

CRViewer1.ViewReport

Open in new window

0
 

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.
0
 
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...
0
 

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.
0
 
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... :)
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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.
0
 
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...

0
 

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.
0
 
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...

cheers,

dan
0
 
LVL 11

Accepted Solution

by:
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...

ALTER PROCEDURE DBA.YOURPROCEDURENAMEHERE (@DATE1 INTEGER) ....



BEGIN
//SOME CODE HERE
 END

JUST ADD THE LINE ABOVE BETWEEN THE BEGIN AND END STATEMENTS...
0
 

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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

707 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

14 Experts available now in Live!

Get 1:1 Help Now