Problems with SQL Query, ODBC

[sidepart]

I'm having a little trouble translating an SQL query that previously worked in VBScript into AutoIT. I tried searching the forums a little for a decent guide or post on making a simple SQL query with ODBC. A few of the posts I found didn't make much sense and seemed way too complex.

Here's what I did in vb script:

'Input Text Box to input a ticket number
ticket_id = inputbox("Enter the ROC Ticket ID","Field Solutions Auto Filler") 

'Connect to database
set MyDB=CreateObject("ADODB.Connection") 
MyDB.Open "mydatabase", "myusername"         'There is no password for this connection (blank)

'query tblTicket at ticket_id
ticketquery = "SELECT * FROM tblTicket WHERE TicketID = '" & ticket_id & "'"
set rstblTicket = MyDB.Execute(ticketquery)

'job_code = value stored in JobCode for TicketID
job_code = rstblTicket.Fields("JobCode")
output = "job_code = " & job_code         'When echo'd this returned a value

Here's my port to AutoIT:

$ticket_id = InputBox("Field Solutions Auto Filler", "Please enter the ROC Ticket ID...","")
MsgBox(1,"Debug",$ticket_id) 'Debug to make sure this works

'Connect to database
$myDB = ObjCreate("ADODB.Connection")
$myDB.Open ("DSN=database; UID=username;") 'There is no password

'Query tblTicket at ticket_id
$ticketquery = "SELECT * FROM tblTicket WHERE TicketID = '" & $ticket_id & "'"
$rstblTicket = $MyDB.Execute($ticketquery)

'job_code = value of JobCode for TicketID
$job_code = $rstblTicket.Fields("JobCode")
MsgBox(1,"Debug2",$job_code)                 'Debug to see if this worked.  Returns a blank, should return a value.

I'm going to pin this on my low understanding of AutoIT. I'm not sure how to debug whether or not the connection was actually made or anything, so maybe I'm just using the object incorrectly. Any thoughts?

[PsaltyDS]

I'm having a little trouble translating an SQL query that previously worked in VBScript into AutoIT. I tried searching the forums a little for a decent guide or post on making a simple SQL query with ODBC. A few of the posts I found didn't make much sense and seemed way too complex.

Here's what I did in vb script:

'Input Text Box to input a ticket number
ticket_id = inputbox("Enter the ROC Ticket ID","Field Solutions Auto Filler") 

'Connect to database
set MyDB=CreateObject("ADODB.Connection") 
MyDB.Open "mydatabase", "myusername"         'There is no password for this connection (blank)

'query tblTicket at ticket_id
ticketquery = "SELECT * FROM tblTicket WHERE TicketID = '" & ticket_id & "'"
set rstblTicket = MyDB.Execute(ticketquery)

'job_code = value stored in JobCode for TicketID
job_code = rstblTicket.Fields("JobCode")
output = "job_code = " & job_code         'When echo'd this returned a value

Here's my port to AutoIT:

$ticket_id = InputBox("Field Solutions Auto Filler", "Please enter the ROC Ticket ID...","")
MsgBox(1,"Debug",$ticket_id) 'Debug to make sure this works

'Connect to database
$myDB = ObjCreate("ADODB.Connection")
$myDB.Open ("DSN=database; UID=username;") 'There is no password

'Query tblTicket at ticket_id
$ticketquery = "SELECT * FROM tblTicket WHERE TicketID = '" & $ticket_id & "'"
$rstblTicket = $MyDB.Execute($ticketquery)

'job_code = value of JobCode for TicketID
$job_code = $rstblTicket.Fields("JobCode")
MsgBox(1,"Debug2",$job_code)                 'Debug to see if this worked.  Returns a blank, should return a value.

I'm going to pin this on my low understanding of AutoIT. I'm not sure how to debug whether or not the connection was actually made or anything, so maybe I'm just using the object incorrectly. Any thoughts?

Strange you didn't mention all the syntax errors you get when you try to run that with single quotes instead of semicolons for comments. Try it like this:

; Input Text Box to input a ticket number
$ticket_id = InputBox("Field Solutions Auto Filler", "Please enter the ROC Ticket ID...", "")

; Connect to database
$myDB = ObjCreate("ADODB.Connection")
$myDB.Open("mydatabase", "username"); There is no password

; Query tblTicket at ticket_id
$ticketquery = "SELECT * FROM tblTicket WHERE TicketID = '" & $ticket_id & "'"
$rstblTicket = $myDB.Execute($ticketquery)

;job_code = value of JobCode for TicketID
$job_code = $rstblTicket.Fields("JobCode")

; Debug to see if this worked.
MsgBox(1, "Debug2", $job_code)

Edit: Looking at it again, you might also need this change:

;job_code = value of JobCode for TicketID
$job_code = $rstblTicket.Fields.Item("JobCode")

.Fields is a collection, so .Item should be used to get a single field's value, but I might be missing something if that worked in VBS.

Edited April 9, 2009 by PsaltyDS

[sidepart]

Strange you didn't mention all the syntax errors you get when you try to run that with single quotes instead of semicolons for comments. Try it like this:

; Input Text Box to input a ticket number
$ticket_id = InputBox("Field Solutions Auto Filler", "Please enter the ROC Ticket ID...", "")

; Connect to database
$myDB = ObjCreate("ADODB.Connection")
$myDB.Open("mydatabase", "username"); There is no password

; Query tblTicket at ticket_id
$ticketquery = "SELECT * FROM tblTicket WHERE TicketID = '" & $ticket_id & "'"
$rstblTicket = $myDB.Execute($ticketquery)

;job_code = value of JobCode for TicketID
$job_code = $rstblTicket.Fields("JobCode")

; Debug to see if this worked.
MsgBox(1, "Debug2", $job_code)

Edit: Looking at it again, you might also need this change:

;job_code = value of JobCode for TicketID
$job_code = $rstblTicket.Fields.Item("JobCode")

.Fields is a collection, so .Item should be used to get a single field's value, but I might be missing something if that worked in VBS.

Sorry, hah no I put those comments in for this post only, pretend like they actually don't exist in my code cause they in fact aren't there. I was kind of out of my mind when posting this and basically did VBS comments instead of ;~ comments used in autoit. I assure you I can compile with no errors. The code runs, it takes my input but it just produces a blank when I try to grab the desired field from my table.

Sorry about that. I will try adding a .Item though

[sidepart]

OK I figured it out. I just needed to add a .Value at the end of my request for the contents of the field "JobCode". Here's the corrected code:

$ticket_id = InputBox("Field Solutions Auto Filler", "Please enter the ROC Ticket ID...","")
MsgBox(1,"Debug",$ticket_id)

$myDB = ObjCreate("ADODB.Connection")
$myDB.Open ("mydatabase","myusername")

$ticketquery = "SELECT * FROM tblTicket WHERE TicketID = '" & $ticket_id & "'"
$rstblTicket = $MyDB.Execute ($ticketquery)

$job_code = $rstblTicket.Fields ("JobCode").Value             ;added .Value
MsgBox(1,"Debug2",$job_code)
$myDB.Close

Managed to find a resource that prompted me to try this out:

http://www.arejae.com/blog/autoit-connecti...sql-server.html

I don't really know why line 4 of his code is there (maybe there's a reason for some SQL servers, but I didn't need it), and line 5 is just a different way of doing the same thing I did. Line 6 was where I saw him use .Value at the end of the request. My debug box now shows me the "JobCode" field.

Thanks! Hope this helps someone else.