arcticpup Posted April 2, 2009 Share Posted April 2, 2009 Hi, I'm trying to retrieve a value from SQL but it keeps coming back blank or can not perform the action. This format works in another script to retrieve a stored procedure so where am I going wrong this time? Any ideas? CODE $var1 = InputBox("Reporting", "Please enter the company name you wish to report on", "", " M254", "", "150") $conn = ObjCreate("ADODB.Connection") $oError = ObjEvent("AutoIt.Error", "ErrHandler") $DSN = "DRIVER={SQL Server};" & "SERVER="localhost";DATABASE=MyDB;UID=SA;PWD=Password;" $conn.Open($DSN) $rs = ObjCreate("ADODB.RecordSet") $rs = ObjCreate("ADODB.RecordSet") $rs.Open("SELECT BUS_ID from BUS where BUS_NAME =" & $var1 & "AS MyVersion", $conn) $sqlinfo = $rs.Fields("myVersion" ).Value $conn.close MsgBox(64, "", $sqlinfo) Many thanks Link to comment Share on other sites More sharing options...
Zedna Posted April 2, 2009 Share Posted April 2, 2009 Search for "ADODB.Connection". there are many existing scripts on the forum. Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
DaRam Posted April 2, 2009 Share Posted April 2, 2009 Wrong SQL? Change$rs.Open("SELECT BUS_ID from BUS where BUS_NAME =" & $var1 & "AS MyVersion", $conn)To $rs.Open("SELECT BUS_ID AS MyVersion from BUS where BUS_NAME=" & $var1, $conn) Or $rs.Open("SELECT BUS_ID AS MyVersion from BUS where BUS_NAME='" & $var1 & "'", $conn) Link to comment Share on other sites More sharing options...
jvanegmond Posted April 2, 2009 Share Posted April 2, 2009 (edited) $DSN = "DRIVER={SQL Server};" & "SERVER="localhost";DATABASE=MyDB;UID=SA;PWD=Password;"oÝ÷ ÛMú # Edited April 2, 2009 by Manadar github.com/jvanegmond Link to comment Share on other sites More sharing options...
arcticpup Posted April 3, 2009 Author Share Posted April 3, 2009 (edited) Right, I'm getting further than before,and the below works fine CODEGlobal $adoCn = ObjCreate("ADODB.Connection") $conn = ObjCreate("ADODB.Connection") $datasource = RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Apex Software\incTax\DataLocations", "Data Source") $var1 = InputBox("AIA Reporting", "Please enter the company name you wish to report on", "", " M254", "", "150") $sqlCon = ObjCreate("ADODB.Connection") $sqlCon.Open("Driver={SQL Server};SERVER=" & $dataSource & ";DATABASE=MyDB;UID=SA;PWD=pass;") $RS=$sqlCon.EXECUTE(" SELECT BUS_ID AS MyVersion from BUS where BUS_NAME='" & $var1 & "'") $bus = $rs("myVersion" ).Value MsgBox(64, "BUS ID is", $bus)oÝ÷ ØÚ0¶©®åzh¬!ƧßÛkzbq«^¶¬¶Þ¶¸½æ¥Ø^½©nzÇë¢kazÖì!z·ç(駫ë4ߦî²ÇyªåêÈq©àzÝkzË¥¶·º¹Þu»më.nǪ¹éí¢w¬jëh×6AutoItGlobal $adoCn = ObjCreate("ADODB.Connection") $conn = ObjCreate("ADODB.Connection") $datasource = RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Apex Software\incTax\DataLocations", "Data Source") $var1 = InputBox("AIA Reporting", "Please enter the company name you wish to report on", "Arctic Snow", " M254", "", "150") $sqlCon = ObjCreate("ADODB.Connection") $sqlCon.Open("Driver={SQL Server};SERVER=" & $dataSource & ";DATABASE=MyDB;UID=SA;PWD=Pass;") $RS=$sqlCon.EXECUTE(" SELECT BUS_ID AS MyVersion from BUS where BUS_NAME='" & $var1 & "'") $bus = $rs("myVersion" ).Value MsgBox(64, "BUS ID is", $bus) ;New code added here $DSN = "Driver={SQL Server};SERVER=" & $dataSource & ";DATABASE=TaxData1;UID=SA;PWD=$ageAdm1n;" $conn.Open($DSN) $rs = ObjCreate("ADODB.RecordSet") $rs.Open("SELECT * from AIA where AIA_BUSID =0000000001", $conn) MsgBox(0, "", $rs.Fields("AIA_Description" ).Value) Any suggestions to get further rows to be displayed or do I have to use an array (which is something else I will have to investigate as I've never used arrays before) Thanks Edited April 3, 2009 by arcticpup Link to comment Share on other sites More sharing options...
DaRam Posted April 3, 2009 Share Posted April 3, 2009 I think you have moved beyond AutoIt issues with ADO.... other than learning how to use Arrays, your issues are with learning how to use SQL There seems to be a single row because You: 1. are Restricting the rows to a single business ID (IA_BUSID =0000000001) 2. might have multiple rows, but you are displaying only the first one. Link to comment Share on other sites More sharing options...
PsaltyDS Posted April 3, 2009 Share Posted April 3, 2009 (edited) Don't you just need to iterate through the record set? Like this: $rs = $sqlCon.Execute(" SELECT BUS_ID AS MyVersion from BUS where BUS_NAME='" & $var1 & "'") While Not $rs.EOF $bus = $rs("myVersion" ).Value MsgBox(64, "BUS ID is", $bus) $rs.MoveNext WEnd Edit: Fixed annoying mixed cases on $RS/$rs. Edited April 3, 2009 by PsaltyDS Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
DaRam Posted April 3, 2009 Share Posted April 3, 2009 Yes, but OP's problem might be the SQL's WHERE clause limiting the record set to a single row.Don't you just need to iterate through the record set? 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