Omniroot Posted August 17, 2012 Share Posted August 17, 2012 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: expandcollapse popup#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) Link to comment Share on other sites More sharing options...
Omniroot Posted August 20, 2012 Author Share Posted August 20, 2012 UPDATEI changed the way I am connecting to my server. Here is the updated code:expandcollapse popup#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? Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now