How to debug a VB file that's supposed to perform SQL Server operations?

Question

I have a VB script that is part of an Access App that interfaces with a SQL Server database. I know what it's supposed to do, but I don't really understand VB (I'm more of a C-like language guy).

Here is the function, from what I can tell:

Private Sub btnOK_Click()
    On Error GoTo btnOK_Click_Err

    Dim result
    Dim sql As String
    Dim mc As Integer   '* metercount
    Dim mca As Integer  '* meter accumulator
    Dim PostWhat As Integer
    Dim CurPd As Integer, CurYr As Integer, NumSumHist As Integer
    Dim i As Integer
    Dim conPost As Connection
    Dim sParam As String, userId As String, WrkStnId As String
    ReDim MsgLog(10) As String
    Dim qn As Integer
    Dim adocom As ADODB.Command
    Dim gCurrencyId As String
    Dim gLocId As String
    
    
    DoCmd.Hourglass True
    'disable button to prevent multiple executions
    Me!HaveYou.SetFocus
    Me!BtnOk.Enabled = False
    
    
    

    If Not Preprocessing() Then
        DoCmd.Hourglass False
        GoTo btnOK_Click_Exit
    End If
    
    
    
    
    Set adocom = New ADODB.Command
    With adocom
        .ActiveConnection = GetADOConnection()
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.qryCfCorrespndMerge"
        .CommandTimeout = 0
        .Parameters.Refresh
        '  If Not gDevYn Then Application.Run "StartServerStatus", "EDI Create SO Transaction ", 1000
        .Execute , , adAsyncExecute
        Do While .State = adStateExecuting
            DoEvents
        Loop
        'If Not gDevYn Then Application.Run "StopServerStatus", "EDI Create SO Transaction "
     
        If .Parameters(0) <> 0 Then
            'Post Failed
            Set adocom = Nothing
            Call GenGetMsg("XXGenOpFailRef", Me.Caption & "|" & .Parameters(0), " ")
            Call MsgBox("Correspondents Merge Failed", gMsgType, gMsgTitle)
            GoTo btnOK_Click_Exit
        End If
    End With
    Set adocom = Nothing
   
   
Finish_Post:
    On Error GoTo btnOK_Click_Err
   
    
    Lock_Cleanup
    
    DoCmd.Hourglass False
    'result = GenGetMsg("ArPostTransComplete", " ", " ")
    result = MsgBox("Correspondents merge  successfully finished !", , gMsgTitle)
    DoEvents
    
   
    '* Open Report Form
    DoCmd.OpenForm "frmCfCorrMergeRpt"
    DoEvents

    

btnOK_Click_Exit:
    On Error Resume Next
    'ensure that everything gets shut down/unlocked/reset/etc.
    DoCmd.Hourglass False
    
    'reenable buttons
    Me!BtnOk.Enabled = True
    Me!BtnOk.SetFocus
    Exit Sub


btnOK_Click_Err:
    Select Case Err
        Case Else
            gErrMod = Me.Name
            gErrProc = "BtnOk_Click"
            gErrDesc = Error$
            gErrCloseForm = Me.Name
            gErrResult = GenErr(adocom.ActiveConnection)
    End Select
    Resume btnOK_Click_Exit

End Sub

What's supposed to happen is the user clicks the print button and the app gathers all the data that is needed to print from the SQL Server database, however, it either goes nowhere, does nothing, or throws an error stating there is nothing to print. Aside from the potential for this to be a database procedure issue, What is this code doing? Is there any pull from a DB somewhere?

Update:

After some analysis it's been determined that the issue is stemming from the SP on the SQL Database, the SP is as follows: (commented out code has been removed for brevity)

ALTER           proc [dbo].[qryCfCorrespndMerge]
as
declare @i int
declare @x int
declare @j int
declare @k int
declare @kk int
declare @InmateId varchar (10) 
declare @Gender char (1) 
declare @Language varchar (2)
declare @Region varchar(2)
set nocount on

create table #tmpOutsider
 (
    id int identity(1,1) not null  primary key,
    PersonId varchar (10) NOT NULL ,
    Gender char (1) NULL ,
    Language varchar (2) NULL ,
    NumAvailCont int null default 0,
    AreaRegion varchar (2) NULL,
    LastUpdated datetime ,
    MergeCount int default(0)
    
    
) 
create table #tmpInmate
 (
    id int identity(1,1) not null  primary key,
    PersonId varchar (10) NOT NULL ,
    Gender char (1) NULL ,
    Language varchar (2) NULL ,
    NumAvailCont int null default 0,
    AreaRegion varchar (2) NULL, 
    LastUpdated datetime,
    MergeCount int default(0)   
    
) 
begin tran
    set @i=0
    
    -- Outsiders (insert list of outsides who still write to fewer inmates than requested)
    insert into  #tmpOutsider(PersonId,Gender ,Language  ,NumAvailCont,AreaRegion,LastUpdated,MergeCount)
    
    select  p.PersonId,Gender ,Language ,(MaxContactNum -isnull(c.ContactNum,0)) as availCont ,AreaRegion ,ISNULL(c.LastUpdateDate,p.DateCreated),isnull(ContactNum,0)
    
    from  dbo.tblCfPerson p left outer join 
        (select CorrespId,count(CorrespId)ContactNum , Max(LastUpdateDate) LastUpdateDate 
        from dbo.tblCfContacts 
            group by CorrespId) c -- number of inmates already linked
        on p.personid=c.CorrespId
        where Type='O' and p.MaxContactNum>isnull(c.ContactNum,0)
    order by isnull(c.ContactNum,0) ,ISNULL(c.LastUpdateDate,p.DateCreated) -- use last merged date if null use date added

    -- if no outsiders than return
    select @i=count(*) from #tmpOutsider
    if  @i=0
        begin
        return 10 -- no outsiders
    end

    

        -- INMATES (insert list of insiders who still write to fewer outsiders than requested
    insert into  #tmpInmate(PersonId,Gender ,Language ,NumAvailCont,AreaRegion,LastUpdated,MergeCount)
    select p.PersonId,Gender ,Language  ,(MaxContactNum -isnull(c.ContactNum,0)) as availCont ,AreaRegion,ISNULL(c.LastUpdateDate,p.DateCreated),isnull(c.ContactNum,0) 
    from  dbo.tblCfPerson p left outer join 
        (select InmateId,count(InmateId) as ContactNum ,Max(LastUpdateDate) LastUpdateDate from dbo.tblCfContacts group by InmateId) c
    on p.personid=c.InmateId
    where Type='I' and p.MaxContactNum>isnull(c.ContactNum,0)
    order by isnull(c.ContactNum,0),isnull(c.LastUpdateDate,p.DateCreated)

    -- if no insiders than return
    select @i=0
    select @i=count(*) from #tmpInmate
    if  @i=0
        begin
        return 20 -- no inmates
    end


    WHILE (SELECT COUNT(*) FROM #tmpInmate) > 0
            BEGIN
        SELECT @I=0
            SELECT @I=(SELECT TOP 1 [ID] FROM (SELECT TOP 100 PERCENT [ID] FROM #tmpInmate ORDER BY MergeCount, LastUpdated) x)
        
        SELECT @gender=gender,@language=language,@region=arearegion,@InmateId=personid
        FROM #tmpInmate where [ID]=@i    
        SELECT @I=ISNULL(@I,0)      

            SELECT @j=0         
        SELECT @j=(SELECT TOP 1 [ID] FROM   
              (SELECT TOP 100 PERCENT [ID] FROM #tmpOutsider t
               WHERE t.gender=@gender
               and t.language=@language
               and t.arearegion <> @region
               and t.personid not in (select distinct CorrespId from tblCfContacts where inmateid=@inmateid)        
               ORDER BY t.MergeCount,t.LastUpdated) x)

                SELECT @j=ISNULL(@J,0)  
        
        IF @j<> 0 
            BEGIN
             INSERT into dbo.tblCfContacts(InmateId,CorrespId,LastUpdateDate,Lang)
             select @inmateid,t.personid,getDate(),@language
             from #tmpoutsider t where id=@j            
              
             UPDATE #tmpInmate SET NumAvailCont=NumAvailCont-1, MergeCount=MergeCount+1,LastUpdated=GetDate() WHERE [ID]=@I
             UPDATE #tmpOutsider SET NumAvailCont=NumAvailCont-1,MergeCount=MergeCount+1, LastUpdated=GetDate() WHERE [ID]=@J           

                 DELETE #tmpInmate WHERE NumAvailCont=0
             DELETE #tmpOutsider WHERE NumAvailCont=0

            END
        ELSE
            BEGIN
              DELETE FROM #tmpInmate WHERE [Id]=@I     
            END             
        
            
        END 

commit tran
return 0

Is there anything in this SQL script that could potentially cause problems? What kind of parameters is this expecting? I know it's setting two temporary tables, then running some calculations. (I've never debugged someone else's SQL before either. Any resources for that?

Erik A · Accepted Answer

The main thing you need to debug likely is the stored procedure.

These lines:

.CommandType = adCmdStoredProc
.CommandText = "dbo.qryCfCorrespndMerge"

Specify that you're running a stored procedure, and which one it is.

Then this section:

If .Parameters(0) <> 0 Then
'Post Failed
    Set adocom = Nothing
    Call GenGetMsg("XXGenOpFailRef", Me.Caption & "|" & .Parameters(0), " ")
    Call MsgBox("Correspondents Merge Failed", gMsgType, gMsgTitle)
    GoTo btnOK_Click_Exit
End If

Checks an output parameter, and displays an error if it fails.

All the more relevant code probably is inside that stored procedure. The code you shared is pretty much only calling it, waiting, and then displaying an error if it didn't run correctly.

There are a lot of unused variables, commented out function calls, and other junk in there. You could try something like RubberDuckVBA (open source, not affiliated) to refactor your code

How to debug a VB file that's supposed to perform SQL Server operations?

Update:

Answers (2)

Related Questions

How to debug a VB file that&#39;s supposed to perform SQL Server operations?

Update:

Answers (2)

Related Questions

How to debug a VB file that's supposed to perform SQL Server operations?