Query to sqlserver 2018

[fulvio_fabiani]

Hi,

i wanna create queries versus sqlserver, what library you suggest to perform in the best way?

The types of the queries are insert, select, delete and update, not create or drop tables.

Thanks for the suggestions!

[robertocm]

This is my "template"

expandcollapse popup

#include <Array.au3>
#include <File.au3>
#include <Excel.au3>
#include <WinAPIFiles.au3>
;#include "..\..\..\SmtpMailer_mLipok\SmtpMailer.au3"

Opt("MustDeclareVars", 1)
Opt("TrayIconDebug", 1)
OnAutoItExitRegister("OnAutoItExit")

;If MsgBox(324, "", "Do you want to proceed?") <> 6 Then Exit

#===== CONFIG =====
Global $sTxt
Global $CleanLog = True
Global $sFilePath2 =  @DesktopDir & "\Test.xlsx"
Global $hFile = FileOpen(@DesktopDir & "\Events_Test.log", 2)

#===== ADODB =====
;Evitar errores en OnAutoItExit
Global $cn, $rst, $sSQL, $SubSQL

;Help: COM Error Handling
;_ErrADODB From spudw2k
;https://www.autoitscript.com/forum/topic/105875-adodb-example/
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 1 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
$cn = ObjCreate("ADODB.Connection") ; Create a connection object
$rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
;~ ;https://accessexperts.com/blog/2011/03/24/sql-server-connections-strings-for-microsoft-access/
;~ ;You should bypass the ODBC layer altogether when connecting to SQL Sever by using a connection string similar to this one in your code:
;~ ;stConnect = "Provider=SQLOLEDB;Data Source=...
;~ ;Or if you’re using native client:
;~ ;stConnect = "Provider=SQLNCLI10;Data Source=...
Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=10.0.0.99;Initial Catalog=MyDatabaseName;User Id=sa;Password=MyPassword;'

;https://www.w3schools.com/asp/prop_rs_cursorlocation.asp
;A Recordset object inherits this setting from the associated Connection object.
;This property is read-only on an open Recordset object, and read/write on a Connection object or on a closed Recordset object.
$cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient
$cn.CommandTimeout = 30

;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode
;try Mode = adModeRead instead
;By the way, do not put adModeRead in the connections string, but just before openning your connection, add this line: rsConn.Mode = adModeRead
;I tried your suggestion, however since in VBA we do not have direct access to the ADODB built-in constants, I set rsCon.Mode = 1
;as defined in the file adovbs.inc located in the folder "C:\Program Files\Common Files\System\ado"
;and although I watched the rsCon.Mode value being set to adModeRead while debugging, I still have the same problem and the application tries to access the file in Write/Edit mode.
;https://www.w3schools.com/asp/prop_rec_mode.asp
$cn.Mode = 1 ;Read-only

$cn.Open($sADOConnectionString) ; Open the connection
;MsgBox(0,"",$cn.ConnectionString)

;https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb221186(v=office.12)
;UPDATE does not generate a result set. Also, after you update records using an update query, you cannot undo the operation.
;If you want to know which records were updated, first examine the results of a select query that uses the same criteria, and then run the update query.
;~ $sSQL = "UPDATE MYTABLE SET MYFIELD = ' ' WHERE REFERENCIA IS MYFIELD"
;~ $cn.Execute($sSQL, Default, 1 + 0x80)  ;adCmdText = 1 , adExecuteNoRecords = 0x80
;~ ;$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions)

;~ $cn.Close ;Close the connection
;~ $cn = 0  ;Release the connection object

$sSQL = "SELECT ..." _
    & " FROM MYTABLE a" _
    & " WHERE ..." _
    & " ORDER BY ...;"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

If Not $rst.EOF = True Then
    Global $rstArray = $rst.GetRows()
    ;_ArrayDisplay ($rstArray, "", "", $ARRAYDISPLAY_NOROW, "", "NomProv|Contrato|Fecha|Aceite|Eur|Kg")
    $rst.Close
    Global $RecCount = UBound($rstArray)

    $rst = 0    ;Release the recordset object
    ;$cmd = 0
    $cn.Close ;Close the connection
    $cn = 0 ;Release the connection object

    #===== EXCEL =====
    Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler
    Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling.

    ;_DebugSetup()
    ;_DebugCOMError()

    ;water: force the Excel UDF to always start up a new instance by using: _Excel_Open(False, Default, Default, Default, True)
    Global $oAppl = _Excel_Open(True, False, False, Default, True)
    ;Global $oAppl = _Excel_Open() ;_Excel_Open(Default, Default, False, Default, Default)
    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;https://www.autoitscript.com/forum/topic/185789-solved-excel_bookopen-without-wait/?do=findComment&comment=1334509

    $oAppl.EnableEvents = False
    $oAppl.DisplayAlerts = False

    ;Global $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath2, False, True)
    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;Create a new workbook with only 1 worksheet
    Global $oWorkbook = _Excel_BookNew($oAppl, 1)
    ;If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.")

    ;Mellor facer unha pausa para asegurar que cargue Ribbon, etc
    Sleep(3000)
    $oWorkbook.UpdateLinks = 2 ;xlUpdateLinksNever


    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263179
    ;water
    ;Seems Excel doesn't like double dot notation. "chaining" object references seems to be a problem.
    ;So I changed the code to only have single dot notation. Could you please test this version?
        ;https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/

    ;Global $oSheets = $oWorkbook.Sheets
    Global $oSheet = $oWorkbook.ActiveSheet
    ;Global $oSheet = $oWorkbook.Sheets("Main")
    ;MsgBox(0, "", $oSheet.Name)

    ;http://www.siddharthrout.com/index.php/2018/02/10/find-last-row-and-last-column/
    ;Find Last Row and Last Column. Siddharth Rout, Feb 2018
    ;~ Global $LastRow = $oSheet.Cells(5000, 1).End(-4162).Row
    ;$UltimaFila = $oSheet.Cells(23, 1).End(-4121).Row
    ;MsgBox(0, "", $UltimaFila)
    ;$oWorkbook.Sheets.Item(2).Activate

    ;$oSheet.Range("I4").value = "ABC"
    ;$oSheet.Range("J25:K25").Value = 100
    ;$oSheet.PrintOut

    ;~ Global $aResult = _Excel_RangeRead($oWorkbook, Default, $oSheet.Range("A9:E" & $LastRow), 1)
    ;If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    $oAppl.ActiveWindow.DisplayZeros = False
    $oAppl.ActiveWindow.DisplayGridlines = False

    $oSheet.Columns("A:B").ColumnWidth = 8
    $oSheet.Columns(3).ColumnWidth = 10
    If $iEventError Then
        Consolewrite("+--> Error ColumnWidth" & @CRLF)
        $iEventError = 0 ; Reset after displaying a COM Error occurred
    EndIf

    Global $Rows = $RecCount + 1
    $oSheet.Range("A2:B" & $Rows).HorizontalAlignment = -4108
    $oSheet.Range("B2:B" & $Rows).NumberFormat = "dd/mm;@"
    $oSheet.Range("C2:C" & $Rows).NumberFormat = "#.##0,00"

    Global $aArray1D[3] = ["Fra", "Fecha", "Total"]
    Global $oRange = $oSheet.Range("A1:C1")
    If $iEventError Then
        Consolewrite("+--> Error: $oSheet.Range('A1:C1')" & @CRLF)
        $iEventError = 0 ; Reset after displaying a COM Error occurred
    EndIf
    With $oRange
        .HorizontalAlignment = -4108
        .value = $aArray1D
        ;.Font.Bold = True
        ;.AutoFilter
    EndWith

    ;~ ;https://www.autoitscript.com/forum/topic/195252-_excel_rangewrite-doesnt-write-array-from-adodb-getrows/
    ;~ ;Global $RecCount = UBound($rstArray)
    ;~ Global $TrstArray = $rstArray
    ;~ _ArrayTranspose($TrstArray)
    ;~ $oSheet.Range("A2:P" & $RecCount + 1).Value = $TrstArray

    _Excel_RangeWrite($oWorkbook, $oSheet, $rstArray, $oSheet.Cells(2, 1))

    ;water, Aug 2016
    ;https://www.autoitscript.com/forum/topic/184041-excel-formatting-cellsborders/?do=findComment&comment=1321855
    ;XlListObjectSourceType Enumeration - https://msdn.microsoft.com/en-us/library/ff820815(v=office.14).aspx
    Global $xlSrcRange = 1
    With $oSheet
        .ListObjects.Add($xlSrcRange, .Range("A1:C" & $Rows), Default, $xlYes).Name = "TB_Datos"
        .ListObjects("TB_Datos").TableStyle = "TableStyleMedium6"
    EndWith

    Global $oPageSetup = $oSheet.PageSetup
    With $oPageSetup
        ;.PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .PrintArea = ""
        .LeftHeader = "&D"
        .CenterHeader = "My Title"
        ;.RightHeader = "&P of &N"
        ;.LeftFooter = "&F {&A}"
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin =  28
        .RightMargin =  28
        .TopMargin =  28
        .BottomMargin =  28
        .HeaderMargin =  15
        .FooterMargin =  15
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = -4142
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = 1
        .Draft = False
        .FirstPageNumber = -4105
        .Order = 1
        .BlackAndWhite = False
        .Zoom = False ;100
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    EndWith

    _Excel_BookSaveAs($oWorkbook, $sFilePath2, $xlOpenXMLWorkbook, True) ;$xlOpenXMLWorkbook  51  ;$xlExcel8  56
    _Excel_BookClose($oWorkbook, False)
    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    While _WinAPI_FileInUse($sFilePath2)
        Sleep(1000)
    Wend
    Sleep(3000)

    $oAppl.EnableEvents = True
    $oAppl.DisplayAlerts = True

    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;https://www.autoitscript.com/forum/topic/136414-excel-close-problem/?do=findComment&comment=953433
    ;$oAppl.Application.Quit
    ;$oAppl = ""
    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/
    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262478
    ;Run(@ComSpec & " /c " & 'taskkill /im excel.exe /f /t', "", @SW_HIDE)
    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262830
    ;water / Ok. Let's see if the problem is caused by open/close or by working with a workbook. Could you please try:
    ;#include <Excel.au3>
    ;$oExcel = _Excel_Open(False, False, False, False, True)
    ;$oExcelClose = _Excel_Close($oExcel, False, True)
    _Excel_Close($oAppl, False, Default)
    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    Sleep(5000) ;deixar tempo para cerrar proceso

    ;~    ;Check if excel is closed
    ;~    Local $aProcesses = ProcessList("Excel.exe")
    ;~    ;_ArrayDisplay($aProcesses)
    ;~    If $aProcesses[0][0] > 0 Then
    ;~    Local $sFilePath = @DesktopDir &  "\error_Excel_Close_COMPRAS.txt"
    ;~       ;~ ;Open the file for write access.
    ;~       ;~ Local $hFileOpen = FileOpen($sFilePath, 2)
    ;~       ;~ ;If $hFileOpen = -1 Then
    ;~       ;~   ;MsgBox(0, "", "An error occurred when reading/writing the file.")
    ;~       ;~ ;EndIf

    ;~       ;~ FileWrite($hFileOpen, "Error:" & @CRLF & _
    ;~       ;~    "Excel_Close COMPRAS" _
    ;~       ;~    )
    ;~       ;~ ;Close the handle returned by FileOpen.
    ;~       ;~ FileClose($hFileOpen)
    ;~    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263191
    ;~    ;@water, thanks for your help so far, at least we pinned down that it's not a UDF bug. :)
    ;~    ;For now I will use a crude workaround by closing the most recent Excel.exe instance:
    ;~    ProcessClose($aProcesses[$aProcesses[0][0]][1])
    ;~    Sleep(100) ;just to allow some time for the process to definitely close (if it does close)
    ;~    EndIf

    Global $oStream = ObjCreate("ADODB.Stream")
    $oStream.Type = 2 ; adTypeText
    $oStream.Charset = "utf-8"
    $oStream.LineSeparator = -1 ; adCRLF
    $oStream.Open
    ;https://msdn.microsoft.com/en-us/library/ms678072(v=vs.85).aspx
    ;StreamWriteEnum: 1 adWriteLine
    $oStream.WriteText("<html>", 1)
    $oStream.WriteText("<head>", 1)
    $oStream.WriteText("<style>", 1)
    $oStream.WriteText(".table-PedProd {table-layout: fixed; border-collapse: separate; border-spacing: 1px;}", 1)
    ;$oStream.WriteText(".table-PedProd {border: 1px solid #ddd; table-layout: fixed; border-collapse: collapse;}", 1)
    ;border: 1px solid #999
    $oStream.WriteText(".table-PedProd th {border: 1px solid #e7e7e7; text-align:center; background-color: #f3f3f3; padding: 0.5rem 0.5rem; word-wrap: break-word;}", 1)
    ;$oStream.WriteText(".table-PedProd th, .table-PedProd td {border: 1px solid #ddd; padding: 0.5rem 0.5rem; word-wrap: break-word;}", 1)
    ;text-align: left; padding-top: 0.1rem; padding-bottom: 0.1rem;
    ;$oStream.WriteText(".table-PedProd tr {vertical-align: top;}", 1)
    $oStream.WriteText(".table-PedProd tr:nth-child(even){background-color: #f5f5f0}", 1)
    ;$oStream.WriteText("#Pedidos tr:nth-child(even){background-color: #f2f2f2}", 1)
    ;$oStream.WriteText("tr td:nth-child(1) {text-align:left;}", 1)
    ;$oStream.WriteText("tr table td:nth-child(2) {text-align:right;}", 1)
    ;$oStream.WriteText("tr.r1r {background-color:#F5F5F0}", 1)
    ;$oStream.WriteText("tr.r2r {background-color:#FFFFFF}", 1)
    ;$oStream.WriteText("tr.r3r {background-color:#f2f2f2}", 1)
    $oStream.WriteText(".highlight {background: yellow;}", 1)
    $oStream.WriteText("</style>", 1)
    $oStream.WriteText("</head>", 1)
    $oStream.WriteText("<body>", 1)

    $oStream.WriteText("<table class='table-PedProd' style='width: 300px;'>", 1)
    $oStream.WriteText("<thead><tr>" _
        & "<th style='width: 30%'>Factura</th>" _
        & "<th style='width: 30%'>Fecha</th>" _
        & "<th style='width: 40%'>Importe</th>" _
        & "</tr></thead>", 1)
    $oStream.WriteText("<tbody>", 1)

    For $i = 0 To UBound($rstArray) - 1
        $oStream.WriteText("<tr><td style='text-align:center; word-wrap: break-word;'>" & $rstArray[$i][0] & "</td>", 1)
        $oStream.WriteText("<td style='text-align:center; word-wrap: break-word;'>" & StringLeft($rstArray[$i][1], 5) & "</td>", 1)
        $oStream.WriteText("<td style='text-align:right; word-wrap: break-word; padding-right: 4px;'>" & $rstArray[$i][2] & "</td></tr>", 1)
    Next

    $oStream.WriteText("</tbody>", 1)
    $oStream.WriteText("</table>", 1)

    $oStream.WriteText("</body>", 1)
    $oStream.WriteText("</html>", 1)
    ;Local $sFilePathHTML = @DesktopDir & "\temp.html"
    ;$oStream.SaveToFile($sFilePathHTML, 2) ;adSaveCreateOverWrite
    ;After a call to this method, the current position in the stream is set to the beginning of the stream (Position=0).
    ;Noutro caso sería necesario esto para ReadText
    $oStream.Position = 0

;~  ;Send mail
;~  Global $sSmtpServer = "mail.mydomain.com"
;~  Global $sUsername = "myname@mydomain.com"
;~  Global $sPassword = "MyPassword"
;~  Global $sFromName = "My Name"
;~  Global $sFromAddress = "myname@mydomain.com"
;~  Global $sToAddress, $sSubject, $sBody, $sAttachFiles, $sCcAddress, $sBccAddress
;~  Global $sImportance = "High" ;Send message priority: "High", "Normal", "Low"
;~  Global $iIPPort = 25
;~  Global $bSSL = False
;~  ; Local $iIPPort = 465 ; GMAIL port used for sending the mail
;~  ; Local $bSSL = True ; GMAIL enables/disables secure socket layer sending - set to True if using https
;~  Global $bIsHTMLBody = True
;~  Global $iDSNOptions = $g__cdoDSNDefault
;~  Global $sResultDescription

;~  $sToAddress = "toaddress@somedomain.com"
;~  $sSubject = "Report"
;~  $sBody = $oStream.ReadText
;~  $sAttachFiles = $sFilePath2 ;seperated with a ; (Semicolon) - leave blank if not needed
;~  $sCcAddress = "myaddress@mydomain.com"
;~  $sBccAddress = ""
;~  $sResultDescription = _SMTP_SendEmail($sSmtpServer, $sUsername, $sPassword, $sFromName, $sFromAddress, $sToAddress, $sSubject, $sBody, $sAttachFiles, $sCcAddress, $sBccAddress, $sImportance, $iIPPort, $bSSL, $bIsHTMLBody, $iDSNOptions)
;~  If Not @error Then
;~      MsgBox(262144, "Sent", "Sent", 2)
;~      SoundPlay(@WindowsDir & "\media\tada.wav", 0) ; Chimes.wav
;~      $sTxt = "OK"
;~      Consolewrite($sTxt & @CRLF)
;~      _FileWriteLog($hFile, $sTxt)
;~      $sSms = $sTxt
;~  ElseIf @error = $SMTP_ERR_SEND Then
;~      ConsoleWrite("! Number: " & _SMTP_COMErrorHexNumber() & "  UDF Script Line: " & _SMTP_ComErrorScriptLine() & "   Description:" & _SMTP_COMErrorDescription() & @LF)
;~      MsgBox(262144, "Error sending email", "_SMTP_SendEmail()" & @CRLF & "Error code: $SMTP_ERR_SEND" & @CRLF & "Description:" & $sResultDescription & @CRLF & "COM Error Number: "& _SMTP_COMErrorHexNumber())
;~      $sTxt = "Err email"
;~      Consolewrite($sTxt & @CRLF)
;~      _FileWriteLog($hFile, $sTxt)
;~      $sSms = $sTxt
;~      $CleanLog = False
;~  EndIf

    $oStream.Close
    $oStream = 0

    FileRecycle($sFilePath2)
Else
    $rst.Close
    $rst = 0 ; Release the recordset object
    $cn.Close ; Close the connection
    $cn = 0 ; Release the connection object

    $sTxt = "Not found"
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False
    MsgBox(262144, "", $sTxt & @CRLF & "script line: " & @ScriptLineNumber , 5)
EndIf

;Melba23, Jul 2014, https://www.autoitscript.com/forum/topic/162514-how-to-add-the-thousand-separator/?do=findComment&comment=1181643
Func _AddPoint($iNumber)
    Local $iLen = StringLen($iNumber)
    If $iLen < 7 Then
        Return StringMid($iNumber, 1, $iLen - 3) & "." & StringMid($iNumber, $iLen - 2)
    Else
        Return StringMid($iNumber, 1, $iLen - 6) & "." & StringMid($iNumber, $iLen - 5, 3) & "." & StringRight($iNumber, 3)
    EndIf
EndFunc

Func _ErrADODB()
    $sTxt = "Err ADO: " & $errADODB.description
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False

    Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
        "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
        "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
        "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
        "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
        "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
        "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
        "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
        "err.helpcontext is: "    & @TAB & $errADODB.helpcontext, 5)

    Local $err = $errADODB.number
    If $err = 0 Then $err = -1

    Local $sFilePath = @DesktopDir &  "\error.txt"
    ;Open the file for write access.
    Local $hFileOpen = FileOpen($sFilePath, 2)
    ;If $hFileOpen = -1 Then
        ;MsgBox(0, "", "An error occurred when reading/writing the file.")
    ;EndIf

    FileWrite($hFileOpen, "ADODB COM Error" & Chr(1) & _
        "err.description is: "    & @TAB & $errADODB.description    & Chr(1) & _
        "err.windescription:"     & @TAB & $errADODB.windescription & Chr(1) & _
        "err.number is: "         & @TAB & hex($errADODB.number,8)  & Chr(1) & _
        "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & Chr(1) & _
        "err.scriptline is: "     & @TAB & $errADODB.scriptline     & Chr(1) & _
        "err.source is: "         & @TAB & $errADODB.source         & Chr(1) & _
        "err.helpfile is: "       & @TAB & $errADODB.helpfile       & Chr(1) & _
        "err.helpcontext is: "    & @TAB & $errADODB.helpcontext _
        )

    ;Close the handle returned by FileOpen.
    FileClose($hFileOpen)

    $rst = 0
    ;$cmd = 0
    $cn.Close
    $cn = 0

    Exit
EndFunc

;This is a custom error handler
Func ErrFunc()
    Local $HexNumber = Hex($oMyError.number, 8)
;~  MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
;~      "Number is: " & $HexNumber & @CRLF & _
;~      "WinDescription is: " & $oMyError.windescription)
    ConsoleWrite("->    We intercepted a COM Error !" & @CRLF & _
        "->    err.number is: " & @TAB & $HexNumber & @CRLF & _
        "->    err.source: " & @TAB & $oMyError.source & @CRLF & _
        "->    err.windescription: " & @TAB & $oMyError.windescription & _
        "->    err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF)

    $iEventError = 1 ; Use to check when a COM Error occurs

    $sTxt = "Err Excel"
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False
EndFunc   ;==>ErrFunc

Func OnAutoItExit()
   FileClose($hFile)
   If $CleanLog Then FileRecycle(@DesktopDir & "\Events_Test.log")

    $rst = 0    ;Release the recordset object
    If IsObj($cn) Then
        If $cn.State > 0 Then $cn.Close ;adStateOpen Close the connection
        $cn = 0 ; Release the connection object
    EndIf
EndFunc

 

[fulvio_fabiani]

23 hours ago, robertocm said:

This is my "template"

expandcollapse popup

#include <Array.au3>
#include <File.au3>
#include <Excel.au3>
#include <WinAPIFiles.au3>
;#include "..\..\..\SmtpMailer_mLipok\SmtpMailer.au3"

Opt("MustDeclareVars", 1)
Opt("TrayIconDebug", 1)
OnAutoItExitRegister("OnAutoItExit")

;If MsgBox(324, "", "Do you want to proceed?") <> 6 Then Exit

#===== CONFIG =====
Global $sTxt
Global $CleanLog = True
Global $sFilePath2 =  @DesktopDir & "\Test.xlsx"
Global $hFile = FileOpen(@DesktopDir & "\Events_Test.log", 2)

#===== ADODB =====
;Evitar errores en OnAutoItExit
Global $cn, $rst, $sSQL, $SubSQL

;Help: COM Error Handling
;_ErrADODB From spudw2k
;https://www.autoitscript.com/forum/topic/105875-adodb-example/
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 1 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
$cn = ObjCreate("ADODB.Connection") ; Create a connection object
$rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
;~ ;https://accessexperts.com/blog/2011/03/24/sql-server-connections-strings-for-microsoft-access/
;~ ;You should bypass the ODBC layer altogether when connecting to SQL Sever by using a connection string similar to this one in your code:
;~ ;stConnect = "Provider=SQLOLEDB;Data Source=...
;~ ;Or if you’re using native client:
;~ ;stConnect = "Provider=SQLNCLI10;Data Source=...
Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=10.0.0.99;Initial Catalog=MyDatabaseName;User Id=sa;Password=MyPassword;'

;https://www.w3schools.com/asp/prop_rs_cursorlocation.asp
;A Recordset object inherits this setting from the associated Connection object.
;This property is read-only on an open Recordset object, and read/write on a Connection object or on a closed Recordset object.
$cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient
$cn.CommandTimeout = 30

;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode
;try Mode = adModeRead instead
;By the way, do not put adModeRead in the connections string, but just before openning your connection, add this line: rsConn.Mode = adModeRead
;I tried your suggestion, however since in VBA we do not have direct access to the ADODB built-in constants, I set rsCon.Mode = 1
;as defined in the file adovbs.inc located in the folder "C:\Program Files\Common Files\System\ado"
;and although I watched the rsCon.Mode value being set to adModeRead while debugging, I still have the same problem and the application tries to access the file in Write/Edit mode.
;https://www.w3schools.com/asp/prop_rec_mode.asp
$cn.Mode = 1 ;Read-only

$cn.Open($sADOConnectionString) ; Open the connection
;MsgBox(0,"",$cn.ConnectionString)

;https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb221186(v=office.12)
;UPDATE does not generate a result set. Also, after you update records using an update query, you cannot undo the operation.
;If you want to know which records were updated, first examine the results of a select query that uses the same criteria, and then run the update query.
;~ $sSQL = "UPDATE MYTABLE SET MYFIELD = ' ' WHERE REFERENCIA IS MYFIELD"
;~ $cn.Execute($sSQL, Default, 1 + 0x80)  ;adCmdText = 1 , adExecuteNoRecords = 0x80
;~ ;$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions)

;~ $cn.Close ;Close the connection
;~ $cn = 0  ;Release the connection object

$sSQL = "SELECT ..." _
    & " FROM MYTABLE a" _
    & " WHERE ..." _
    & " ORDER BY ...;"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

If Not $rst.EOF = True Then
    Global $rstArray = $rst.GetRows()
    ;_ArrayDisplay ($rstArray, "", "", $ARRAYDISPLAY_NOROW, "", "NomProv|Contrato|Fecha|Aceite|Eur|Kg")
    $rst.Close
    Global $RecCount = UBound($rstArray)

    $rst = 0    ;Release the recordset object
    ;$cmd = 0
    $cn.Close ;Close the connection
    $cn = 0 ;Release the connection object

    #===== EXCEL =====
    Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler
    Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling.

    ;_DebugSetup()
    ;_DebugCOMError()

    ;water: force the Excel UDF to always start up a new instance by using: _Excel_Open(False, Default, Default, Default, True)
    Global $oAppl = _Excel_Open(True, False, False, Default, True)
    ;Global $oAppl = _Excel_Open() ;_Excel_Open(Default, Default, False, Default, Default)
    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;https://www.autoitscript.com/forum/topic/185789-solved-excel_bookopen-without-wait/?do=findComment&comment=1334509

    $oAppl.EnableEvents = False
    $oAppl.DisplayAlerts = False

    ;Global $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath2, False, True)
    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;Create a new workbook with only 1 worksheet
    Global $oWorkbook = _Excel_BookNew($oAppl, 1)
    ;If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.")

    ;Mellor facer unha pausa para asegurar que cargue Ribbon, etc
    Sleep(3000)
    $oWorkbook.UpdateLinks = 2 ;xlUpdateLinksNever


    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263179
    ;water
    ;Seems Excel doesn't like double dot notation. "chaining" object references seems to be a problem.
    ;So I changed the code to only have single dot notation. Could you please test this version?
        ;https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/

    ;Global $oSheets = $oWorkbook.Sheets
    Global $oSheet = $oWorkbook.ActiveSheet
    ;Global $oSheet = $oWorkbook.Sheets("Main")
    ;MsgBox(0, "", $oSheet.Name)

    ;http://www.siddharthrout.com/index.php/2018/02/10/find-last-row-and-last-column/
    ;Find Last Row and Last Column. Siddharth Rout, Feb 2018
    ;~ Global $LastRow = $oSheet.Cells(5000, 1).End(-4162).Row
    ;$UltimaFila = $oSheet.Cells(23, 1).End(-4121).Row
    ;MsgBox(0, "", $UltimaFila)
    ;$oWorkbook.Sheets.Item(2).Activate

    ;$oSheet.Range("I4").value = "ABC"
    ;$oSheet.Range("J25:K25").Value = 100
    ;$oSheet.PrintOut

    ;~ Global $aResult = _Excel_RangeRead($oWorkbook, Default, $oSheet.Range("A9:E" & $LastRow), 1)
    ;If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    $oAppl.ActiveWindow.DisplayZeros = False
    $oAppl.ActiveWindow.DisplayGridlines = False

    $oSheet.Columns("A:B").ColumnWidth = 8
    $oSheet.Columns(3).ColumnWidth = 10
    If $iEventError Then
        Consolewrite("+--> Error ColumnWidth" & @CRLF)
        $iEventError = 0 ; Reset after displaying a COM Error occurred
    EndIf

    Global $Rows = $RecCount + 1
    $oSheet.Range("A2:B" & $Rows).HorizontalAlignment = -4108
    $oSheet.Range("B2:B" & $Rows).NumberFormat = "dd/mm;@"
    $oSheet.Range("C2:C" & $Rows).NumberFormat = "#.##0,00"

    Global $aArray1D[3] = ["Fra", "Fecha", "Total"]
    Global $oRange = $oSheet.Range("A1:C1")
    If $iEventError Then
        Consolewrite("+--> Error: $oSheet.Range('A1:C1')" & @CRLF)
        $iEventError = 0 ; Reset after displaying a COM Error occurred
    EndIf
    With $oRange
        .HorizontalAlignment = -4108
        .value = $aArray1D
        ;.Font.Bold = True
        ;.AutoFilter
    EndWith

    ;~ ;https://www.autoitscript.com/forum/topic/195252-_excel_rangewrite-doesnt-write-array-from-adodb-getrows/
    ;~ ;Global $RecCount = UBound($rstArray)
    ;~ Global $TrstArray = $rstArray
    ;~ _ArrayTranspose($TrstArray)
    ;~ $oSheet.Range("A2:P" & $RecCount + 1).Value = $TrstArray

    _Excel_RangeWrite($oWorkbook, $oSheet, $rstArray, $oSheet.Cells(2, 1))

    ;water, Aug 2016
    ;https://www.autoitscript.com/forum/topic/184041-excel-formatting-cellsborders/?do=findComment&comment=1321855
    ;XlListObjectSourceType Enumeration - https://msdn.microsoft.com/en-us/library/ff820815(v=office.14).aspx
    Global $xlSrcRange = 1
    With $oSheet
        .ListObjects.Add($xlSrcRange, .Range("A1:C" & $Rows), Default, $xlYes).Name = "TB_Datos"
        .ListObjects("TB_Datos").TableStyle = "TableStyleMedium6"
    EndWith

    Global $oPageSetup = $oSheet.PageSetup
    With $oPageSetup
        ;.PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .PrintArea = ""
        .LeftHeader = "&D"
        .CenterHeader = "My Title"
        ;.RightHeader = "&P of &N"
        ;.LeftFooter = "&F {&A}"
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin =  28
        .RightMargin =  28
        .TopMargin =  28
        .BottomMargin =  28
        .HeaderMargin =  15
        .FooterMargin =  15
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = -4142
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = 1
        .Draft = False
        .FirstPageNumber = -4105
        .Order = 1
        .BlackAndWhite = False
        .Zoom = False ;100
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    EndWith

    _Excel_BookSaveAs($oWorkbook, $sFilePath2, $xlOpenXMLWorkbook, True) ;$xlOpenXMLWorkbook  51  ;$xlExcel8  56
    _Excel_BookClose($oWorkbook, False)
    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    While _WinAPI_FileInUse($sFilePath2)
        Sleep(1000)
    Wend
    Sleep(3000)

    $oAppl.EnableEvents = True
    $oAppl.DisplayAlerts = True

    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;https://www.autoitscript.com/forum/topic/136414-excel-close-problem/?do=findComment&comment=953433
    ;$oAppl.Application.Quit
    ;$oAppl = ""
    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/
    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262478
    ;Run(@ComSpec & " /c " & 'taskkill /im excel.exe /f /t', "", @SW_HIDE)
    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262830
    ;water / Ok. Let's see if the problem is caused by open/close or by working with a workbook. Could you please try:
    ;#include <Excel.au3>
    ;$oExcel = _Excel_Open(False, False, False, False, True)
    ;$oExcelClose = _Excel_Close($oExcel, False, True)
    _Excel_Close($oAppl, False, Default)
    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    Sleep(5000) ;deixar tempo para cerrar proceso

    ;~    ;Check if excel is closed
    ;~    Local $aProcesses = ProcessList("Excel.exe")
    ;~    ;_ArrayDisplay($aProcesses)
    ;~    If $aProcesses[0][0] > 0 Then
    ;~    Local $sFilePath = @DesktopDir &  "\error_Excel_Close_COMPRAS.txt"
    ;~       ;~ ;Open the file for write access.
    ;~       ;~ Local $hFileOpen = FileOpen($sFilePath, 2)
    ;~       ;~ ;If $hFileOpen = -1 Then
    ;~       ;~   ;MsgBox(0, "", "An error occurred when reading/writing the file.")
    ;~       ;~ ;EndIf

    ;~       ;~ FileWrite($hFileOpen, "Error:" & @CRLF & _
    ;~       ;~    "Excel_Close COMPRAS" _
    ;~       ;~    )
    ;~       ;~ ;Close the handle returned by FileOpen.
    ;~       ;~ FileClose($hFileOpen)
    ;~    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263191
    ;~    ;@water, thanks for your help so far, at least we pinned down that it's not a UDF bug. :)
    ;~    ;For now I will use a crude workaround by closing the most recent Excel.exe instance:
    ;~    ProcessClose($aProcesses[$aProcesses[0][0]][1])
    ;~    Sleep(100) ;just to allow some time for the process to definitely close (if it does close)
    ;~    EndIf

    Global $oStream = ObjCreate("ADODB.Stream")
    $oStream.Type = 2 ; adTypeText
    $oStream.Charset = "utf-8"
    $oStream.LineSeparator = -1 ; adCRLF
    $oStream.Open
    ;https://msdn.microsoft.com/en-us/library/ms678072(v=vs.85).aspx
    ;StreamWriteEnum: 1 adWriteLine
    $oStream.WriteText("<html>", 1)
    $oStream.WriteText("<head>", 1)
    $oStream.WriteText("<style>", 1)
    $oStream.WriteText(".table-PedProd {table-layout: fixed; border-collapse: separate; border-spacing: 1px;}", 1)
    ;$oStream.WriteText(".table-PedProd {border: 1px solid #ddd; table-layout: fixed; border-collapse: collapse;}", 1)
    ;border: 1px solid #999
    $oStream.WriteText(".table-PedProd th {border: 1px solid #e7e7e7; text-align:center; background-color: #f3f3f3; padding: 0.5rem 0.5rem; word-wrap: break-word;}", 1)
    ;$oStream.WriteText(".table-PedProd th, .table-PedProd td {border: 1px solid #ddd; padding: 0.5rem 0.5rem; word-wrap: break-word;}", 1)
    ;text-align: left; padding-top: 0.1rem; padding-bottom: 0.1rem;
    ;$oStream.WriteText(".table-PedProd tr {vertical-align: top;}", 1)
    $oStream.WriteText(".table-PedProd tr:nth-child(even){background-color: #f5f5f0}", 1)
    ;$oStream.WriteText("#Pedidos tr:nth-child(even){background-color: #f2f2f2}", 1)
    ;$oStream.WriteText("tr td:nth-child(1) {text-align:left;}", 1)
    ;$oStream.WriteText("tr table td:nth-child(2) {text-align:right;}", 1)
    ;$oStream.WriteText("tr.r1r {background-color:#F5F5F0}", 1)
    ;$oStream.WriteText("tr.r2r {background-color:#FFFFFF}", 1)
    ;$oStream.WriteText("tr.r3r {background-color:#f2f2f2}", 1)
    $oStream.WriteText(".highlight {background: yellow;}", 1)
    $oStream.WriteText("</style>", 1)
    $oStream.WriteText("</head>", 1)
    $oStream.WriteText("<body>", 1)

    $oStream.WriteText("<table class='table-PedProd' style='width: 300px;'>", 1)
    $oStream.WriteText("<thead><tr>" _
        & "<th style='width: 30%'>Factura</th>" _
        & "<th style='width: 30%'>Fecha</th>" _
        & "<th style='width: 40%'>Importe</th>" _
        & "</tr></thead>", 1)
    $oStream.WriteText("<tbody>", 1)

    For $i = 0 To UBound($rstArray) - 1
        $oStream.WriteText("<tr><td style='text-align:center; word-wrap: break-word;'>" & $rstArray[$i][0] & "</td>", 1)
        $oStream.WriteText("<td style='text-align:center; word-wrap: break-word;'>" & StringLeft($rstArray[$i][1], 5) & "</td>", 1)
        $oStream.WriteText("<td style='text-align:right; word-wrap: break-word; padding-right: 4px;'>" & $rstArray[$i][2] & "</td></tr>", 1)
    Next

    $oStream.WriteText("</tbody>", 1)
    $oStream.WriteText("</table>", 1)

    $oStream.WriteText("</body>", 1)
    $oStream.WriteText("</html>", 1)
    ;Local $sFilePathHTML = @DesktopDir & "\temp.html"
    ;$oStream.SaveToFile($sFilePathHTML, 2) ;adSaveCreateOverWrite
    ;After a call to this method, the current position in the stream is set to the beginning of the stream (Position=0).
    ;Noutro caso sería necesario esto para ReadText
    $oStream.Position = 0

;~  ;Send mail
;~  Global $sSmtpServer = "mail.mydomain.com"
;~  Global $sUsername = "myname@mydomain.com"
;~  Global $sPassword = "MyPassword"
;~  Global $sFromName = "My Name"
;~  Global $sFromAddress = "myname@mydomain.com"
;~  Global $sToAddress, $sSubject, $sBody, $sAttachFiles, $sCcAddress, $sBccAddress
;~  Global $sImportance = "High" ;Send message priority: "High", "Normal", "Low"
;~  Global $iIPPort = 25
;~  Global $bSSL = False
;~  ; Local $iIPPort = 465 ; GMAIL port used for sending the mail
;~  ; Local $bSSL = True ; GMAIL enables/disables secure socket layer sending - set to True if using https
;~  Global $bIsHTMLBody = True
;~  Global $iDSNOptions = $g__cdoDSNDefault
;~  Global $sResultDescription

;~  $sToAddress = "toaddress@somedomain.com"
;~  $sSubject = "Report"
;~  $sBody = $oStream.ReadText
;~  $sAttachFiles = $sFilePath2 ;seperated with a ; (Semicolon) - leave blank if not needed
;~  $sCcAddress = "myaddress@mydomain.com"
;~  $sBccAddress = ""
;~  $sResultDescription = _SMTP_SendEmail($sSmtpServer, $sUsername, $sPassword, $sFromName, $sFromAddress, $sToAddress, $sSubject, $sBody, $sAttachFiles, $sCcAddress, $sBccAddress, $sImportance, $iIPPort, $bSSL, $bIsHTMLBody, $iDSNOptions)
;~  If Not @error Then
;~      MsgBox(262144, "Sent", "Sent", 2)
;~      SoundPlay(@WindowsDir & "\media\tada.wav", 0) ; Chimes.wav
;~      $sTxt = "OK"
;~      Consolewrite($sTxt & @CRLF)
;~      _FileWriteLog($hFile, $sTxt)
;~      $sSms = $sTxt
;~  ElseIf @error = $SMTP_ERR_SEND Then
;~      ConsoleWrite("! Number: " & _SMTP_COMErrorHexNumber() & "  UDF Script Line: " & _SMTP_ComErrorScriptLine() & "   Description:" & _SMTP_COMErrorDescription() & @LF)
;~      MsgBox(262144, "Error sending email", "_SMTP_SendEmail()" & @CRLF & "Error code: $SMTP_ERR_SEND" & @CRLF & "Description:" & $sResultDescription & @CRLF & "COM Error Number: "& _SMTP_COMErrorHexNumber())
;~      $sTxt = "Err email"
;~      Consolewrite($sTxt & @CRLF)
;~      _FileWriteLog($hFile, $sTxt)
;~      $sSms = $sTxt
;~      $CleanLog = False
;~  EndIf

    $oStream.Close
    $oStream = 0

    FileRecycle($sFilePath2)
Else
    $rst.Close
    $rst = 0 ; Release the recordset object
    $cn.Close ; Close the connection
    $cn = 0 ; Release the connection object

    $sTxt = "Not found"
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False
    MsgBox(262144, "", $sTxt & @CRLF & "script line: " & @ScriptLineNumber , 5)
EndIf

;Melba23, Jul 2014, https://www.autoitscript.com/forum/topic/162514-how-to-add-the-thousand-separator/?do=findComment&comment=1181643
Func _AddPoint($iNumber)
    Local $iLen = StringLen($iNumber)
    If $iLen < 7 Then
        Return StringMid($iNumber, 1, $iLen - 3) & "." & StringMid($iNumber, $iLen - 2)
    Else
        Return StringMid($iNumber, 1, $iLen - 6) & "." & StringMid($iNumber, $iLen - 5, 3) & "." & StringRight($iNumber, 3)
    EndIf
EndFunc

Func _ErrADODB()
    $sTxt = "Err ADO: " & $errADODB.description
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False

    Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
        "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
        "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
        "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
        "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
        "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
        "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
        "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
        "err.helpcontext is: "    & @TAB & $errADODB.helpcontext, 5)

    Local $err = $errADODB.number
    If $err = 0 Then $err = -1

    Local $sFilePath = @DesktopDir &  "\error.txt"
    ;Open the file for write access.
    Local $hFileOpen = FileOpen($sFilePath, 2)
    ;If $hFileOpen = -1 Then
        ;MsgBox(0, "", "An error occurred when reading/writing the file.")
    ;EndIf

    FileWrite($hFileOpen, "ADODB COM Error" & Chr(1) & _
        "err.description is: "    & @TAB & $errADODB.description    & Chr(1) & _
        "err.windescription:"     & @TAB & $errADODB.windescription & Chr(1) & _
        "err.number is: "         & @TAB & hex($errADODB.number,8)  & Chr(1) & _
        "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & Chr(1) & _
        "err.scriptline is: "     & @TAB & $errADODB.scriptline     & Chr(1) & _
        "err.source is: "         & @TAB & $errADODB.source         & Chr(1) & _
        "err.helpfile is: "       & @TAB & $errADODB.helpfile       & Chr(1) & _
        "err.helpcontext is: "    & @TAB & $errADODB.helpcontext _
        )

    ;Close the handle returned by FileOpen.
    FileClose($hFileOpen)

    $rst = 0
    ;$cmd = 0
    $cn.Close
    $cn = 0

    Exit
EndFunc

;This is a custom error handler
Func ErrFunc()
    Local $HexNumber = Hex($oMyError.number, 8)
;~  MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
;~      "Number is: " & $HexNumber & @CRLF & _
;~      "WinDescription is: " & $oMyError.windescription)
    ConsoleWrite("->    We intercepted a COM Error !" & @CRLF & _
        "->    err.number is: " & @TAB & $HexNumber & @CRLF & _
        "->    err.source: " & @TAB & $oMyError.source & @CRLF & _
        "->    err.windescription: " & @TAB & $oMyError.windescription & _
        "->    err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF)

    $iEventError = 1 ; Use to check when a COM Error occurs

    $sTxt = "Err Excel"
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False
EndFunc   ;==>ErrFunc

Func OnAutoItExit()
   FileClose($hFile)
   If $CleanLog Then FileRecycle(@DesktopDir & "\Events_Test.log")

    $rst = 0    ;Release the recordset object
    If IsObj($cn) Then
        If $cn.State > 0 Then $cn.Close ;adStateOpen Close the connection
        $cn = 0 ; Release the connection object
    EndIf
EndFunc

 

As soon as possible i will try this solution, unfortunatly i've others priority to solve, however thanks!

[mLipok]

Take a look on my ADO.au3 UDF in my signature.

[fulvio_fabiani]

On 5/19/2022 at 9:23 AM, fulvio_fabiani said:

As soon as possible i will try this solution, unfortunatly i've others priority to solve, however thanks!

Hi, i tried the code but there is this error, what you suggest to do?

 

"C:\AutoIt_prj\Crm_ricerca_pratica\Crm_Ricerca_Pratica.au3" (42) : ==> The requested action with this object has failed.:
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions)
$rst^ ERROR

 

The code is:

 

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <File.au3>
#include <WinAPIFiles.au3>
#include <Date.au3>
#RequireAdmin
#include "_ImageSearch_UDF.au3"

Global $cn, $rst, $sSQL, $SubSQL
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 1 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable

Opt("MustDeclareVars", 1)
Opt("TrayIconDebug", 1)
;OnAutoItExitRegister("OnAutoItExit")

$cn = ObjCreate("ADODB.Connection") ; Create a connection object
$rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=192.168.20.244;Initial Catalog=AUTOMI;User Id=swpgm;Password=swpgm;'
$cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient
$cn.CommandTimeout = 30
$sSQL = "SELECT DES_AUTOMA FROM AUTOMI WHERE ID_AUTOMA = 4"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions)

 

 

 

[mLipok]

* ADO.au3 UDF * give it a try.

[robertocm]

22 hours ago, fulvio_fabiani said:

what you suggest to do?

Open the connection:

$cn.Open($sADOConnectionString) ;<<<<<<<<<<<<< Open the connection

$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions)

 

See also "simplest testing code" from Zedna, Aug 6, 2020:

 

Edited May 27, 2022 by robertocm

[fulvio_fabiani]

i've solved with ADO_CONSTANTS.au3

[mLipok]

Can you be more specific ? How exactly you solved this issue ?