Gorby7

That UDF would've saved me some time had I known about it earlier! Maybe it's my lack of MSSQL expertise, but I don't see how any of those functions in ADO.au3 would allow me to execute a stored procedure with a parameter that is a table/recordset.

I have an Autoit-based client GUI that uses a single MS SQL Server database. I have no problem connecting to and executing queries against this database from my AutoIT code, with the below exception: One of the database's stored procedures I need to execute requires a parameter that is a table. I've set up the appropriate table_type on the database and everything works fine on the database itself when I execute the stored proc with a table variable for the parameter (no AutoIT involved). The problem is that I can't figure out how to pass a table parameter from AutoIT, assuming it's even possible. The code below shows the two methods I've tried, both methods return a RecordSet object that is at EOF. #include <Array.au3> Opt("MustDeclareVars", 0) $sDBSrv = "ITSQL01.domain.com" $sDBName = "INVDEV" $sADOName = "ADODB.Connection" $oSQLConn = ObjCreate($sADOName) $sConnStr = "Driver={SQL Server};Server=" & $sDBSrv & ";Database=" & $sDBName & ";Trusted_Connection=yes;" $oSQLConn.Open ($sConnStr) $rsoFacFriendly = $oSQLConn.Execute("SELECT TOP 5 FriendlyName FROM Facility") If Not $rsoFacFriendly.EOF Then $aFacFriendly = $rsoFacFriendly.GetRows() _ArrayDisplay($aFacFriendly) $rsoSPResults = $oSQLConn.Execute("EXEC spTableParameterPassTest " & $aFacFriendly) Select Case $rsoSPResults.EOF = False MsgBox(0, "sp exec1", "not end of file") $aSPResults = $rsoSPResults.GetRows() _ArrayDisplay($aSPResults) Case IsObj($rsoSPResults) = 0 MsgBox(0, "sp exec1", "rso isn't even an object") Case $rsoSPResults.EOF = True MsgBox(0, "sp exec1", "At end of file") Case Else MsgBox(0, "sp exec1", "Something else happened") EndSelect $rsoSPResults = 0 $rsoSPResults = $oSQLConn.Execute("EXEC spTableParameterPassTest " & $rsoFacFriendly) Select Case $rsoSPResults.EOF = False MsgBox(0, "sp exec2", "not end of file") $aSPResults = $rsoSPResults.GetRows() _ArrayDisplay($aSPResults) Case IsObj($rsoSPResults) = 0 MsgBox(0, "sp exec2", "rso isn't even an object") Case $rsoSPResults.EOF = True MsgBox(0, "sp exec2", "At end of file") Case Else MsgBox(0, "sp exec2", "Something else happened") EndSelect $rsoSPResults = 0 $rsoFacFriendly = 0 $oSQLConn.Close $oSQLConn = 0 Exit

I know this is old and a workaround exists, but I've found a simpler workaround that may help others. Whatever variable you use with _ExcelBookNew() or _ExcelBookOpen() must be set to zero after using _ExcelBookClose(). For example: $oExobject=_ExcelBookNew() ;...do stuff _ExcelBookClose() $oExobject=0 This will immediately end the EXCEL.EXE process and allow you to maniputlate the saved Excel file with other functions.

That works perfectly! I can't believe I didn't think to try something with the EOF property after a whole day of Googling. Thanks a bunch.

By the way, I'm still on version 3.3.6.1.

I'm using 3 easy SQL functions written by Locodarwin found here: They work great, I use the GetRows method to extract the results into a 2D array. The problem comes when there are no records/rows returned. The GetRows method fails with a console output error of "The requested action with this object has failed" and "$aResults=$oResults.GetRows()^ ERROR". I cannot figure out a way to check to see if there no records before calling the GetRows method. I tried using the RecordCount method but it always returns -1 because the recordset is forward-only/read-only. I tried to UBound the results object, but of course that didn't work. Any ideas? Here's my code: $oOpen=_SQLConnect("server","database",1,"sa","password") $oResults=_SQLQuery($oOpen,"EXEC Stored_Procedure_Name") $iColumns=$oResults.Fields.Count MsgBox(0,"","Rows: " & UBound($oResults)) MsgBox(0,"","Rows: " & $oResults.RecordCount) $aResults=$oResults.GetRows() $iIndex=UBound($aResults) _SQLDisconnect($oOpen)

Hmm it's still the same result, the existing file in the archive is deleted and the new one is placed into it with the original name. I commented out lines as below: Func _Zip_AddItem($sZipFile, $sFileName, $sDestDir = "", $iFlag = 21) If Not _Zip_DllChk() Then Return SetError(@error, 0, 0) If Not _IsFullPath($sZipFile) Then Return SetError(3, 0, 0) If Not _IsFullPath($sFileName) Then Return SetError(4, 0, 0) If Not FileExists($sFileName) Then Return SetError(5, 0, 0) If _IsFullPath($sDestDir) Then Return SetError(6, 0, 0) ; clean paths $sFileName = _Zip_PathStripSlash($sFileName) $sDestDir = _Zip_PathStripSlash($sDestDir) Local $sNameOnly = _Zip_PathNameOnly($sFileName) ; process overwrite flag Local $iOverwrite = 0 If BitAND($iFlag, 1) Then $iOverwrite = 1 $iFlag -= 1 EndIf ; check for overwrite, if target exists... Local $sTest = $sNameOnly If $sDestDir <> "" Then $sTest = $sDestDir & "\" & $sNameOnly Local $itemExists = _Zip_ItemExists($sZipFile, $sTest) If @error Then Return SetError(7, 0, 0) If $itemExists Then If @extended Then ; get out, cannot overwrite folders... AT ALL Return SetError(8, 0, 0) Else If $iOverwrite Then Sleep(1) ;_Zip_InternalDelete($sZipFile, $sTest) ;If @error Then Return SetError(10, 0, 0) Else Return SetError(9, 0, 0) EndIf EndIf EndIf Local $sTempFile = "" If $sDestDir <> "" Then $sTempFile = _Zip_AddPath($sZipFile, $sDestDir) If @error Then Return SetError(11, 0, 0) $sZipFile &= "\" & $sDestDir EndIf Local $oApp = ObjCreate("Shell.Application") Local $oNS = $oApp.NameSpace($sZipFile) ; copy the file $oNS.CopyHere($sFileName, $iFlag) Do Sleep(250) $oItem = $oNS.ParseName($sNameOnly) Until IsObj($oItem) ;If $sTempFile <> "" Then _Zip_InternalDelete($sZipFile, $sTempFile) Return 1 EndFunc ;==>_Zip_AddItem I also tried running it without the '16' flag, "Respond Yes to All for any dialog that is displayed", and still no luck. I'm not too worried about it at this point since my program is working, but like you said it won't work for other situations so I may be back someday.

I wish I knew a bit more about programming , I still don't get it... are you saying that if the flags don't work correctly, it's because there's a problem with some of my Windows system files? Such as zipfldr.dll?

I'm still curious as to why it didn't work originally, but I added some code to the function and it works now. If the file already exists in the archive it will be renamed from "filename.ext" to "filename(2).ext" before being placed in the archive. This works no matter how many instances of the same filename are found. Func _Zip_AddItem($sZipFile, $sFileName, $sDestDir = "", $iFlag = 21) If Not _Zip_DllChk() Then Return SetError(@error, 0, 0) If Not _IsFullPath($sZipFile) Then Return SetError(3, 0, 0) If Not _IsFullPath($sFileName) Then Return SetError(4, 0, 0) If Not FileExists($sFileName) Then Return SetError(5, 0, 0) If _IsFullPath($sDestDir) Then Return SetError(6, 0, 0) ; if file exists in archive then rename it first $cutp=StringInStr($sFileName,"\",0,-1) $fname=StringTrimLeft($sFileName,$cutp) While _Zip_ItemExists($sZipFile,$fname) $cutp=StringInStr($sFileName,"\",0,-1) $fname=StringTrimLeft($sFileName,$cutp) $path=StringTrimRight($sFileName,StringLen($sFileName)-$cutp) $fname1=StringTrimRight($fname,(StringLen($fname)-StringInStr($fname,".",0))+1) $exten=StringTrimLeft($fname,StringInStr($fname,".",0)-1) FileMove($sFileName,$path & $fname1 & "(2)" & $exten) $sFileName=$path & $fname1 & "(2)" & $exten $fname=$fname1 & "(2)" & $exten WEnd ; clean paths $sFileName = _Zip_PathStripSlash($sFileName) $sDestDir = _Zip_PathStripSlash($sDestDir) Local $sNameOnly = _Zip_PathNameOnly($sFileName) ; process overwrite flag Local $iOverwrite = 0 If BitAND($iFlag, 1) Then $iOverwrite = 1 $iFlag -= 1 EndIf ; check for overwrite, if target exists... Local $sTest = $sNameOnly If $sDestDir <> "" Then $sTest = $sDestDir & "\" & $sNameOnly Local $itemExists = _Zip_ItemExists($sZipFile, $sTest) If @error Then Return SetError(7, 0, 0) If $itemExists Then If @extended Then ; get out, cannot overwrite folders... AT ALL Return SetError(8, 0, 0) Else If $iOverwrite Then _Zip_InternalDelete($sZipFile, $sTest) If @error Then Return SetError(10, 0, 0) Else Return SetError(9, 0, 0) EndIf EndIf EndIf Local $sTempFile = "" If $sDestDir <> "" Then $sTempFile = _Zip_AddPath($sZipFile, $sDestDir) If @error Then Return SetError(11, 0, 0) $sZipFile &= "\" & $sDestDir EndIf Local $oApp = ObjCreate("Shell.Application") Local $oNS = $oApp.NameSpace($sZipFile) ; copy the file $oNS.CopyHere($sFileName, $iFlag) Do Sleep(250) $oItem = $oNS.ParseName($sNameOnly) Until IsObj($oItem) If $sTempFile <> "" Then _Zip_InternalDelete($sZipFile, $sTempFile) Return 1 EndFunc ;==>_Zip_AddItem

Well for some reason I'm not allowed to edit my previous post again, but I entered the wrong code it should be: _Zip_AddItem("C:\Dump\072006.zip","C:\Test\drivers\R205648\Lang\HDMI\ENU\license.txt","",28) ;Flag is 4+8+16

I love the UDF! One thing though, using flag 8 in _Zip_AddItem "Rename the file if a file of the same name already exists" doesn't seem to work at all. It simply deletes the pre-existing file within the zip and then adds the new file without renaming it. There are no errors, the code I'm using is below (variables have been replaced with their values). _Zip_AddItem("C:\Test\072006.zip","C:\Test\drivers\R205648\Lang\HDMI\ENU\license.txt","",28) ;Flag is 4+8+16 To be honest I can't even tell by looking at the function code where it attempts to rename the file, or what it would rename the file to. But I am by no means an AutoIT guru... Using WinXP Pro SP3 and AutoIT v3.3.6.1

Hi everyone! I was using _Zip_AddFile() within a loop to add multiple (i.e. hundreds) of files to a single archive. Since I was not retaining folder structure, all files were being placed in the root of the archive. Eventually I canme across a file that, although containing different data, had the same file name as a file that had already been placed in the archive. Windows prompted if I wanted to overwrite, etc., but unfortunately it breaks the scripts no matter what you choose. So, I edited the _Zip_AddFile() function so that if it comes across a file that is already in the archive, it renames the file from "filename.ext" to "filename(2).ext". Works like a charm Enjoy! Func _Zip_AddFile($hZipFile, $hFile2Add, $flag = 1) Local $DLLChk = _Zip_DllChk() Local $files = _Zip_Count($hZipFile) If $DLLChk <> 0 Then Return SetError($DLLChk, 0, 0);no dll If not _IsFullPath($hZipFile) then Return SetError(4,0) ;zip file isn't a full path If Not FileExists($hZipFile) Then Return SetError(1, 0, 0) ;no zip file $list=_Zip_List($hZipFile) For $fn In $list $cutp=StringInStr($hFile2Add,"\",0,-1) $fname=StringTrimLeft($hFile2Add,$cutp) If $fname=$fn Then $path=StringTrimRight($hFile2Add,StringLen($hFile2Add)-$cutp) $fname1=StringTrimRight($fname,(StringLen($fname)-StringInStr($fname,".",0))+1) $fname2=StringTrimLeft($fname,StringInStr($fname,".",0)-1) FileMove($hFile2Add,$path & $fname1 & "(2)" & $fname2) $hFile2Add=$path & $fname1 & "(2)" & $fname2 EndIf Next $oApp = ObjCreate("Shell.Application") $copy = $oApp.NameSpace($hZipFile).CopyHere($hFile2Add) While 1 If $flag = 1 then _Hide() ;If WinExists("Confirm File Replace") Then ControlClick("Confirm File Replace","","Button1") If _Zip_Count($hZipFile) = ($files+1) Then ExitLoop Sleep(10) WEnd Return SetError(0,0,1) EndFunc ;==>_Zip_AddFile

GEOSoft, I saw where others in this thread had posted the same error I'm now getting: C:\Program Files\AutoIt3\Include\Access.au3 (72) : ==> The requested action with this object has failed.: .Fields.Item($I -1) = $rData[$I] .Fields.Item($I -1) = $rData[$I]^ ERROR >Exit code: 1 Time: 4.377 I didn't see where you had found out what the deal was with this error, any chance that's been figured out since then?