SQL Queries

Is it possible for TP to run queries and use a result for a field in Internet Explorer?

  • Verified Answer

    You are in the land of VBA so you can use any COM/ActiveX object in your script, just add a reference and use the Object Browser to see what is there.

    There are many examples out there in the Internet, just search for VBA and databases, this page gives you a list of technologies you can use.

  • Verified Answer

    The code below works for Oracle db

    Option Explicit

    Sub Main()

    ' add reference to Microsoft ActiveX Data Object Library

    ' Tools > References > Check "Microsoft ActiveX Data Object Library"

    ' ============================================================================================================

       If sPublicVariables.gblnDebug = False Then

          On Error GoTo lblErrorHandler

       End If

    ' ============================================================================================================

       sUtils.WhereAmI (Me.Name)

    ' =============================================================================================================

       sPublicVariables.gblnScriptPassed = True

       Utility.Writeini sPublicVariables.gstrPathTo_TPini, "FinalBuilder Variables", "FBCompleteProcess", "True"

    ' =============================================================================================================

       Dim lngLoopCount As Long

       Dim lngLabNo As Long

       Dim blnCheck As Boolean

       blnCheck = True

       Dim myConnection As ADODB.connection

       Dim objRecordset As ADODB.Recordset

       'Create the ADO connection

        Set myConnection = New ADODB.connection

       'Connection string for Oracle DB

        myConnection.ConnectionString = "DSN=" & sPublicVariables.DB_Name & ";UID=winpath;PWD=W1NPATH"

       'Open  the connection

        myConnection.Open

       ' Execute SQL script to retrieve data

       Set objRecordset = myConnection.Execute("SELECT * from  patient_flag_code where CODE = '6739'")

       If objRecordset.RecordCount = -1 Then

           sReports.WordDoc_Text_Msg "NO ROWS to Delete"

           GoTo lblEnd

       End If

       ' write to audit trail in word doc#

       objRecordset.MoveFirst

       sReports.WordDoc_Text_Msg "Deleting Patient flag  : " & objRecordset.Fields.Item("Code").Value & " " & objRecordset.Fields.Item("Description").Value

       wrdSelection.Range.InsertParagraph

       sReports.WordDoc_Text_Msg "from dbName: " & sPublicVariables.DB_Name

       objRecordset.Close

       Set objRecordset = myConnection.Execute("DELETE FROM patient_flag_migrate where patient_flag_migrate.PATIENT_FLAG_CODE_ID  = (select PATIENT_FLAG_CODE_ID from PATIENT_FLAG_CODE where CODE = '6739')")

       Set objRecordset = myConnection.Execute("COMMIT")

       Set objRecordset = myConnection.Execute("DELETE from patient_flag_code where CODE = '6739'")

       Set objRecordset = myConnection.Execute("COMMIT")

    lblEnd:

       myConnection.Close

       smReportGenerator.ScriptTerminationMessage Me.Name

       Exit Sub

    lblErrorHandler:

       Dim strMsg As String

       strMsg = "FAILED to delete Patient Flag : '6739'"

           sErrorReport.GlobalErrHandler Me.Name, Error.SourceLine, strMsg

    '    sReports.WordDoc_Text_Msg strMsg, , True

    '    sReports.WordDoc_Text_Msg "from dbName: " & sPublicVariables.DB_Name, , True

    '    sReports.WordDoc_Text_Msg " ", , True

    '    Err.Raise vbObjectError 999, Me.Name, strMsg

    End Sub

  • Verified Answer

    The code below works for Oracle db

    Option Explicit

    Sub Main()

    ' add reference to Microsoft ActiveX Data Object Library

    ' Tools > References > Check "Microsoft ActiveX Data Object Library"

    ' ============================================================================================================

       If sPublicVariables.gblnDebug = False Then

          On Error GoTo lblErrorHandler

       End If

    ' ============================================================================================================

       sUtils.WhereAmI (Me.Name)

    ' =============================================================================================================

       sPublicVariables.gblnScriptPassed = True

       Utility.Writeini sPublicVariables.gstrPathTo_TPini, "FinalBuilder Variables", "FBCompleteProcess", "True"

    ' =============================================================================================================

       Dim lngLoopCount As Long

       Dim lngLabNo As Long

       Dim blnCheck As Boolean

       blnCheck = True

       Dim myConnection As ADODB.connection

       Dim objRecordset As ADODB.Recordset

       'Create the ADO connection

        Set myConnection = New ADODB.connection

       'Connection string for Oracle DB

        myConnection.ConnectionString = "DSN=" & sPublicVariables.DB_Name & ";UID=winpath;PWD=W1NPATH"

       'Open  the connection

        myConnection.Open

       ' Execute SQL script to retrieve data

       Set objRecordset = myConnection.Execute("SELECT * from  patient_flag_code where CODE = '6739'")

       If objRecordset.RecordCount = -1 Then

           sReports.WordDoc_Text_Msg "NO ROWS to Delete"

           GoTo lblEnd

       End If

       ' write to audit trail in word doc#

       objRecordset.MoveFirst

       sReports.WordDoc_Text_Msg "Deleting Patient flag  : " & objRecordset.Fields.Item("Code").Value & " " & objRecordset.Fields.Item("Description").Value

       wrdSelection.Range.InsertParagraph

       sReports.WordDoc_Text_Msg "from dbName: " & sPublicVariables.DB_Name

       objRecordset.Close

       Set objRecordset = myConnection.Execute("DELETE FROM patient_flag_migrate where patient_flag_migrate.PATIENT_FLAG_CODE_ID  = (select PATIENT_FLAG_CODE_ID from PATIENT_FLAG_CODE where CODE = '6739')")

       Set objRecordset = myConnection.Execute("COMMIT")

       Set objRecordset = myConnection.Execute("DELETE from patient_flag_code where CODE = '6739'")

       Set objRecordset = myConnection.Execute("COMMIT")

    lblEnd:

       myConnection.Close

       smReportGenerator.ScriptTerminationMessage Me.Name

       Exit Sub

    lblErrorHandler:

       Dim strMsg As String

       strMsg = "FAILED to delete Patient Flag : '6739'"

           sErrorReport.GlobalErrHandler Me.Name, Error.SourceLine, strMsg

    '    sReports.WordDoc_Text_Msg strMsg, , True

    '    sReports.WordDoc_Text_Msg "from dbName: " & sPublicVariables.DB_Name, , True

    '    sReports.WordDoc_Text_Msg " ", , True

    '    Err.Raise vbObjectError 999, Me.Name, strMsg

    End Sub

  • Thank y'all for the responses. In my Available References I have 11 Microsoft ActiveX Data Objects. Should I select them all?