Ghost21 0 Posted May 2, 2011 I need to be able to submit a query request and pass a variable to the database along with it.. $Recordset.Open('exec TombstonedDevices', $Connection, 1, 3) <-- Thats what I use for a query. But I need the part to add the variable to it ??? Share this post Link to post Share on other sites
spudw2k 231 Posted May 2, 2011 Can you elaborate a bit more? Which variable and how are you planning to use it withing the func? Spoiler Things I've Made: AOT Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX BuilderMisc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose ArrayProjects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalcCool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF Share this post Link to post Share on other sites
Ghost21 0 Posted May 2, 2011 Lets say a $pc <-- eg. a pc name then in a Query you can have a prompted variable request. like a inputbox or a predefined variable.. I want to call the query and say to run this query with the PC variable and give me the results.. Share this post Link to post Share on other sites
spudw2k 231 Posted May 2, 2011 Would you provide the variable to the TombstonedDevices procedure? Could you not append the variable to the "exec" string? Spoiler Things I've Made: AOT Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX BuilderMisc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose ArrayProjects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalcCool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF Share this post Link to post Share on other sites
Ghost21 0 Posted May 2, 2011 Here is the Sql Line.. SELECT PCData.PC, PCData.PING, PCData.Tombstoned, PCData.LASTUPDATED FROM PCData WHERE (((PCData.PC) Is Not Null) AND ((PCData.Tombstoned)=-1))ORDER BY PCData.PC, PCData.PING, PCData.LASTUPDATED; Share this post Link to post Share on other sites
spudw2k 231 Posted May 2, 2011 Is that is what is being run by executing the stored procedure? I can't tell where the variables fit in. I'm not too fresh on Stored Procedures, but here's a basic example of using an SP with arguments/variables: CREATE PROCEDURE sp_myStoredProcedure @myInput int AS Select column1, column2 From Table1 Where column1 = @myInput Go Then call it like this: sp_myStoredProcedure 0 Spoiler Things I've Made: AOT Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX BuilderMisc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose ArrayProjects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalcCool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF Share this post Link to post Share on other sites
Ghost21 0 Posted May 2, 2011 Is the " @ " sign that I need to add to the database side to do the variable submission ? Share this post Link to post Share on other sites
spudw2k 231 Posted May 2, 2011 yes, when you create a procedure the @varname designates a var. this is where I copied and pasted from. http://www.4guysfromrolla.com/webtech/111499-1.shtml There is another example on that page for multiple variables in an SP. Spoiler Things I've Made: AOT Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX BuilderMisc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose ArrayProjects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalcCool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF Share this post Link to post Share on other sites
Ghost21 0 Posted May 2, 2011 I need to see working code as I have been trying this for so long ... Share this post Link to post Share on other sites
Ghost21 0 Posted May 2, 2011 This is what I'm trying.. _dbopen() $Recordset.Open('exec Query1 192.168.10.166', $Connection, 1, 3) With $Recordset If .Recordcount Then MsgBox(0, .Recordcount & " Devices found.", "") While Not .EOF $pc = .Fields("PC" ).Value MsgBox(0, "PC", $pc) .MoveNext $pc = "" WEnd EndIf EndWith $Connection.close _dbclose() Share this post Link to post Share on other sites
spudw2k 231 Posted May 2, 2011 At a glance that looks fine too me, but since I can't see the db and query you are working with I can't troubleshoot it much further. How far does it get? Is there an error message? Spoiler Things I've Made: AOT Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX BuilderMisc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose ArrayProjects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalcCool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF Share this post Link to post Share on other sites
Ghost21 0 Posted May 2, 2011 Damn I wish sometimes there was a live chat option... this is the code I'm trying now.. All I want to do is submit the variable to the query so it can use it look for the ip and tell me back the PC.. I want to keep as much of the sql statement in the database as its not needed in the program.. expandcollapse popupGlobal $Recordset Global $Connection Global $DatabasePath = IniRead("config.ini", "Path", "DatabasePath", @ScriptDir) Global $Filelocation = IniRead("config.ini", "Path", "Filelocation", @ScriptDir) Global $PsToolsFilelocation = IniRead("config.ini", "Path", "PsToolsFilelocation", @ScriptDir) Global $UtilsFilelocation = IniRead("config.ini", "Path", "UtilsFilelocation", @ScriptDir) Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc") _dbopen() $Recordset.Open("exec Query1 WHERE IP='192.168.10.94'", $Connection, 1, 3) ;$Recordset.Open("Select * FROM WH WHERE PC='" & $pc & "'", $Connection, 1, 3) With $Recordset If .Recordcount Then MsgBox(0, .Recordcount & " Devices found.", "") While Not .EOF $pc = .Fields("PC" ).Value MsgBox(0, "PC", $pc) .MoveNext $pc = "" WEnd EndIf EndWith $Connection.close _dbclose() Func _dbOpen() $Connection = ObjCreate("ADODB.Connection") $Recordset = ObjCreate("ADODB.Recordset") If FileExists($DatabasePath & "sw.mdb") Then $Connection.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=" & $DatabasePath & "sw.mdb") Else MsgBox(0, "Database Not Found", "Please Install DataBase", 25) Exit EndIf EndFunc ;==>_dbOpen Func _dbClose() $Connection = 0 EndFunc ;==>_dbClose Func MyErrFunc() Global $HexNumber = Hex($oMyError.number, 8) If $CmdLine[0] = 0 Then MsgBox(0, "COM Error", "COM Error Details:" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $oMyError.description & @CRLF & _ "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _ "err.number is: " & @TAB & $HexNumber & @CRLF & _ "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _ "err.source is: " & @TAB & $oMyError.source & @CRLF & _ "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $oMyError.helpcontext _ ) Else EndIf Return SetError($HexNumber) EndFunc ;==>MyErrFunc Share this post Link to post Share on other sites
spudw2k 231 Posted May 2, 2011 if you run the exec query without a variable does it return results? Spoiler Things I've Made: AOT Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX BuilderMisc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose ArrayProjects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalcCool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF Share this post Link to post Share on other sites
Ghost21 0 Posted May 2, 2011 NO: If I change the query to already have a variable there then it will respond. When I run it right now I get no error's. Share this post Link to post Share on other sites
spudw2k 231 Posted May 2, 2011 Sounds like either a malformed query or you are attempting to find a record that doesn't exist. Your last post also has something strange in it. When calling a procedure you only supply the variable values (don't supply WHERE). Spoiler Things I've Made: AOT Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX BuilderMisc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose ArrayProjects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalcCool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF Share this post Link to post Share on other sites
Ghost21 0 Posted May 2, 2011 OMFG I figured it out... SO STUPID I'm GOING TO SCREAM... $Recordset.Open("exec Query1 '192.168.10.94'", $Connection, 1, 3) if you create a query and use this sql stement it works .... YEEHAW.... SELECT PCData.PC, [@IP] AS Expr1, PCData.IP FROM PCData WHERE ((([@IP])=[IP])); That makes the query and then you can push variables into the database so that all the processing is done there.. :) Share this post Link to post Share on other sites
spudw2k 231 Posted May 2, 2011 (edited) bingo. good job edit: I'm pretty sure you don't need to "SELECT" [@IP] As Expr1, but I cannot test this. CREATE PROCEDURE Query1 @IP as varchar(16) AS Select PCData.PC, PCData.IP From PCData Where IP = @IP Go Edited May 2, 2011 by spudw2k Spoiler Things I've Made: AOT Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX BuilderMisc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose ArrayProjects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalcCool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF Share this post Link to post Share on other sites
