• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Server Remote ADO Debuger - A simple way to see what happend on the server if you use unstored pr

Server Remote ADO Debuger - A simple way to see what happend on the server if you use unstored pr

Server Remote ADO Debuger - A simple way to "see" what happend on the server if you use unstored procedure.In this sample code is a unstored

Rate Server Remote ADO Debuger - A simple way to see what happend on the server if you use unstored pr

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Dim MyDb As ADODB.Recordset, MyDbCmd As ADODB.Command
Dim MyDbCon As ADODB.Connection

Set MyDbCon = New ADODB.Connection
MyDbCon.ConnectionString = "DSN=MyDSN;"
MyDbCon.Open

Set MyDbCmd = New ADODB.Command
Set MyDbCmd.ActiveConnection = MyDbCon
Set MyDb = New ADODB.Recordset
'Unstored procedure
MyDbCmd.CommandText = "SET @A=0;"   'Initialize @A
MyDbCmd.CommandType = adCmdText
MyDbCmd.Execute     'If you don't expect result

MyDbCmd.CommandText = "SELECT @A=MAX(ID) from MyTable;"     'Get the MAXimum value from ID column
MyDbCmd.CommandType = adCmdText
MyDbCmd.Execute

MyDbCmd.CommandText = "SELECT CASE WHERE @A IS NULL THEN @A:=1 ELSE @A:=@A+1;" 'If A is null then A=1 else A=A+1, like autoincrement
MyDbCmd.CommandType = adCmdText
'Last query remain unexecuted because MyDebug must do this
MyDebug MyDbCmd, MyDb, MyDbCon, "Case @A IS NULL THEN @A:=1 ..."   'Now must "see" what happend on the server
MyDb.Close
MyDbCon.Close
End Sub
Public Sub MyDebug(Comanda As ADODB.Command, Record As ADODB.Recordset, Con As ADODB.Connection, Reference As String)
Dim aa As String, a As Long

Record.ActiveConnection = Con
Record.CursorLocation = adUseClient
Record.CursorType = adOpenDynamic
Set Record = Comanda.Execute()      'If you expect results

With Record
        If .EOF = True Then
            aa = "No Result !"
            Raspuns = MsgBox("SQL CODE Debug:" & Reference & vbCr & aa, vbInformation, "MyDebug")
            Exit Sub
        End If
        Do While .EOF = False
            a = (a + 1) - 1
                    aa = aa + Trim(.Fields(0)) & vbCr
            .MoveNext
        Loop
    End With
    Raspuns = MsgBox("SQL CODE Debug :" & Reference & vbCr & aa, vbInformation, "MyDebug")
End Sub