Sign in to follow this  
Followers 0
Omniroot

My Database Script Isn't Working

2 posts in this topic

For some reason, I can't get this script to work. I'm trying to pull some info from a database and write it to a file for later retrieval by another program. Here's my script:

#include<Date.au3>
#include <Array.au3>

; first, we set up the parameters for out sql connection
$serverName = "SERVERMAIN\POSITIVESOFTWARE"
$databaseName = "zeli"
$serverUser = "sa"
$serverPass = "jmelamed"
$constrim="DRIVER={SQL Server};SERVER=" & $serverName & ";DATABASE=" & $databaseName & ";uid=" & $serverUser & ";pwd=" & $serverPass & ";"
$adCN = ObjCreate ("ADODB.Connection") ;<==Create SQL connection
$adCN.Open ($constrim) ;<==Connect to the database
MsgBox(0,"",$constrim )
if @error Then
MsgBox(0, "ERROR", "Failed to connect to the database")
Exit
Else
MsgBox(0, "Success!", "Connection to database successful!")
EndIf

$sqlQuery="SELECT [zeli].[dbo].[CUSDET].[CDT_FIELD2], [zeli].[dbo].[CUSMER].[CUS_FNAM], [zeli].[dbo].[CUSMER].[CUS_LNAM], [zeli].[dbo].[CUSMER].[CUS_PHN1], [zeli].[dbo].[CUSMER].[CUS_BCIT], [zeli].[dbo].[CUSMER].[CUS_BAD1], [zeli].[dbo].[CUSMER].[CUS_BAD2], [zeli].[dbo].[CUSMER].[CUS_BSTA], [zeli].[dbo].[CUSMER].[CUS_BZIP], [zeli].[dbo].[CUSMER].[CUS_EMAIL] FROM [zeli].[dbo].[CUSMER] INNER JOIN [zeli].[dbo].[CUSDET] ON [zeli].[dbo].[CUSMER].[CUS_CustID] = [zeli].[dbo].[CUSDET].[CDT_CUSTID] WHERE CAST([zeli].[dbo].[CUSDET].[CDT_FIELD2] AS Integer) >= " & @YEAR & @MON & " AND [zeli].[dbo].[CUSMER].[CUS_PRICEID] = 2 AND [zeli].[dbo].[CUSMER].[CUS_EMAIL] != ''"

;execute the query and store the resulting RecordSet
$result= $adCN.Execute($sqlQuery)
Dim $arr[Int($result.PageCount)][14]

For $i = 0 To ($result.PageCount - 1) Step 1
; 0 - Email
; 1 - First Name
; 2 - Last Name
; 3 - Phone Number
; 4 - Country = Canada
; 5 - State/Province
; 6 - City
; 7 - Address Line 1
; 8 - Address Line 2
; 9 - Zip/Postal Code
; 10 - Expiry Year
; 11 - Expiry Month
; 12 - Joomla User ID
; 13 - VirtueMart User ID
$arr[$i][0] = $result.Fields("CUS_EMAIL").Value
$arr[$i][1] = $result.Fields("CUS_FNAM").Value
$arr[$i][2] = $result.Fields("CUS_LNAM").Value
$arr[$i][3] = $result.Fields("CUS_PHN1").Value
$arr[$i][4] = "Canada"
$arr[$i][5] = $result.Fields("CUS_BSTA").Value
$arr[$i][6] = $result.Fields("CUS_BCIT").Value
$arr[$i][7] = $result.Fields("CUS_BAD1").Value
$arr[$i][8] = $result.Fields("CUS_BAD2").Value
$arr[$i][9] = $result.Fields("CUS_BZIP").Value
$temp = $result.Fields("CDT_FIELD2").Value
$arr[$i][11] = Mod($temp, 100)
$temp -= $arr[$i][11]
$arr[$i][10] = $temp / 100
$arr[$i][12] = ""
$arr[$i][13] = ""
$result.MoveNext()
Next
$adCN.Close ;==>Close the database

; parse array as string
$bigString = _ArrayToString($arr, "|")

; write string to file
$file = FileOpen("C:\AutoIt\ZeliSync.txt", 2)
If $file = -1 Then
MsgBox(0, "Error", "Unable to open file.")
Exit
EndIf
FileWrite($file, $bigString)
FileClose($file)

Share this post


Link to post
Share on other sites



UPDATE

I changed the way I am connecting to my server. Here is the updated code:

#include<Date.au3>
#include <Array.au3>
; first, we set up the parameters for out sql connection
$serverName = "SERVERMAINPOSITIVESOFTWARE"
$databaseName = "zeli"
$constr="Provider=SQLOLEDB;Data Source="&$serverName&";Initial Catalog="&$databaseName&";Integrated Security=SSPI;"
$adCN = ObjCreate ("ADODB.Connection") ;<==Create SQL connection
MsgBox(0,"",$constr )
$adCN.Open ($constr) ;<==Connect to the database
if @error Then
  MsgBox(0, "ERROR", "Failed to connect to the database")
  Exit
Else
  MsgBox(0, "Success!", "Connection to database successful!")
EndIf
$sqlQuery="SELECT [zeli].[dbo].[CUSDET].[CDT_FIELD2], [zeli].[dbo].[CUSMER].[CUS_FNAM], [zeli].[dbo].[CUSMER].[CUS_LNAM], [zeli].[dbo].[CUSMER].[CUS_PHN1], [zeli].[dbo].[CUSMER].[CUS_BCIT], [zeli].[dbo].[CUSMER].[CUS_BAD1], [zeli].[dbo].[CUSMER].[CUS_BAD2], [zeli].[dbo].[CUSMER].[CUS_BSTA], [zeli].[dbo].[CUSMER].[CUS_BZIP], [zeli].[dbo].[CUSMER].[CUS_EMAIL] FROM [zeli].[dbo].[CUSMER] INNER JOIN [zeli].[dbo].[CUSDET] ON [zeli].[dbo].[CUSMER].[CUS_CustID] = [zeli].[dbo].[CUSDET].[CDT_CUSTID] WHERE CAST([zeli].[dbo].[CUSDET].[CDT_FIELD2] AS Integer) >= " & @YEAR & @MON & " AND [zeli].[dbo].[CUSMER].[CUS_PRICEID] = 2 AND [zeli].[dbo].[CUSMER].[CUS_EMAIL] != ''"
;execute the query and store the resulting RecordSet
$result= $adCN.Execute($sqlQuery)
Dim $arr[Int($result.PageCount)][14]
For $i = 0 To ($result.PageCount - 1) Step 1
; 0 - Email
; 1 - First Name
; 2 - Last Name
; 3 - Phone Number
; 4 - Country = Canada
; 5 - State/Province
; 6 - City
; 7 - Address Line 1
; 8 - Address Line 2
; 9 - Zip/Postal Code
; 10 - Expiry Year
; 11 - Expiry Month
; 12 - Joomla User ID
; 13 - VirtueMart User ID
$arr[$i][0] = $result.Fields("CUS_EMAIL").Value
$arr[$i][1] = $result.Fields("CUS_FNAM").Value
$arr[$i][2] = $result.Fields("CUS_LNAM").Value
$arr[$i][3] = $result.Fields("CUS_PHN1").Value
$arr[$i][4] = "Canada"
$arr[$i][5] = $result.Fields("CUS_BSTA").Value
$arr[$i][6] = $result.Fields("CUS_BCIT").Value
$arr[$i][7] = $result.Fields("CUS_BAD1").Value
$arr[$i][8] = $result.Fields("CUS_BAD2").Value
$arr[$i][9] = $result.Fields("CUS_BZIP").Value
$temp = $result.Fields("CDT_FIELD2").Value
$arr[$i][11] = Mod($temp, 100)
$temp -= $arr[$i][11]
$arr[$i][10] = $temp / 100
$arr[$i][12] = ""
$arr[$i][13] = ""
$result.MoveNext()
Next
$adCN.Close ;==>Close the database
; parse array as string
$bigString = _ArrayToString($arr, "|")
; write string to file
$file = FileOpen("C:AutoItZeliSync.txt", 2)
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf
FileWrite($file, $bigString)
FileClose($file)

My problem is that the ADODB.Connection Object isn't working properly. How do I fix this?

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  
Followers 0