Jump to content
Sign in to follow this  
sidepart

Problems with SQL Query, ODBC

Recommended Posts

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?

Share this post


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

:D

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.

:o

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
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)

:D

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.

:o

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

Share this post


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

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.