SQL Select

[arcticpup]

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

[Zedna]

Search for "ADODB.Connection".

there are many existing scripts on the forum.

[DaRam]

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)

[jvanegmond]

$DSN = "DRIVER={SQL Server};" & "SERVER="localhost";DATABASE=MyDB;UID=SA;PWD=Password;"oÝ÷ ÛMú
#

Edited April 2, 2009 by Manadar

[arcticpup]

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

[DaRam]

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.

[PsaltyDS]

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

[DaRam]

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?