fulvio_fabiani Posted May 17, 2022 Share Posted May 17, 2022 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! Link to comment Share on other sites More sharing options...
robertocm Posted May 18, 2022 Share Posted May 18, 2022 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  Link to comment Share on other sites More sharing options...
fulvio_fabiani Posted May 19, 2022 Author Share Posted May 19, 2022 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! Link to comment Share on other sites More sharing options...
mLipok Posted May 19, 2022 Share Posted May 19, 2022 Take a look on my ADO.au3 UDF in my signature. Signature beginning:* Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules ** ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Code * for other useful stuff click the following button: Spoiler Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API * ErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *  My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF * SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane * Useful links: * Forum Rules * Forum etiquette * Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * Wiki: * Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler * IE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related: * How to get reference to PDF object embeded in IE * IE on Windows 11 * I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions * EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *I also encourage you to check awesome @trancexx code: * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuff * OnHungApp handler * Avoid "AutoIt Error" message box in unknown errors  * HTML editor * winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/ "Homo sum; humani nil a me alienum puto" - Publius Terentius Afer"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"  , be   and    \\//_. Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty." Signature last update: 2023-04-24 Link to comment Share on other sites More sharing options...
fulvio_fabiani Posted May 26, 2022 Author Share Posted May 26, 2022 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) Â Â Â Link to comment Share on other sites More sharing options...
mLipok Posted May 26, 2022 Share Posted May 26, 2022 * ADO.au3 UDF * give it a try. Signature beginning:* Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules ** ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Code * for other useful stuff click the following button: Spoiler Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API * ErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *  My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF * SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane * Useful links: * Forum Rules * Forum etiquette * Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * Wiki: * Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler * IE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related: * How to get reference to PDF object embeded in IE * IE on Windows 11 * I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions * EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *I also encourage you to check awesome @trancexx code: * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuff * OnHungApp handler * Avoid "AutoIt Error" message box in unknown errors  * HTML editor * winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/ "Homo sum; humani nil a me alienum puto" - Publius Terentius Afer"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"  , be   and    \\//_. Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty." Signature last update: 2023-04-24 Link to comment Share on other sites More sharing options...
robertocm Posted May 27, 2022 Share Posted May 27, 2022 (edited) 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 Link to comment Share on other sites More sharing options...
fulvio_fabiani Posted June 8, 2022 Author Share Posted June 8, 2022 i've solved with ADO_CONSTANTS.au3 Link to comment Share on other sites More sharing options...
mLipok Posted June 8, 2022 Share Posted June 8, 2022 Can you be more specific ? How exactly you solved this issue ? Signature beginning:* Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules ** ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Code * for other useful stuff click the following button: Spoiler Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API * ErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 *  My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF * SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane * Useful links: * Forum Rules * Forum etiquette * Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * Wiki: * Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskScheduler * IE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related: * How to get reference to PDF object embeded in IE * IE on Windows 11 * I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions * EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *I also encourage you to check awesome @trancexx code: * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuff * OnHungApp handler * Avoid "AutoIt Error" message box in unknown errors  * HTML editor * winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/ "Homo sum; humani nil a me alienum puto" - Publius Terentius Afer"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"  , be   and    \\//_. Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty." Signature last update: 2023-04-24 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now