Jump to content
Sign in to follow this  
arcticpup

SQL Select

Recommended Posts

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

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
jvanegmond

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

Edited by Manadar

Share this post


Link to post
Share on other sites
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 by arcticpup

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

:D

Edit: Fixed annoying mixed cases on $RS/$rs.

Edited 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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.