Query Not displaying

[moxasya]

On 6/27/2020 at 10:30 AM, Nas said:

When I ran your script I got this error : Unable to open MSSQL connection.  @error = 5.

I found that the $oConnection is missing  so I added this line :

Global $oConnection = ObjCreate("ADODB.Connection")
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Unable to open MSSQL connection.  @error = " & @error)

I was able to open the connection with SQL and then I got this error :

Unable to begin transactions.  @error = 6.

Update:

For some reason it considers every _ADO_Execute as an error, so I removed those lines and it worked like a charm, @TheXman you're the best, can't thank you enough, thank you @jchd I appreciate your help.

expandcollapse popup

#include <ADO.au3>
#include <Constants.au3>

Local $sDriver = 'SQL Server'
Local $sDatabase = 'AdventureWorks2016' ; change this string to YourDatabaseName
Local $sServer = 'Localhost' ; change this string to YourServerLocation
Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & ';PWD=' & ';'

Global $sQUERY

Global $aRecordset

Local $oConnection = ObjCreate("ADODB.Connection")
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Unable to open MSSQL connection.  @error = " & @error)


_ADO_EVENTS_SetUp(True)

;Open connection
_ADO_Connection_OpenConString($oConnection, $sConnectionString)
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Unable to open MSSQL connection.  @error = " & @error)
ConsoleWrite("Connection opened." & @CRLF)

;Begin transaction
_ADO_Execute($oConnection, "BEGIN TRANSACTION", True)
ConsoleWrite("BEGIN TRANSACTION successful." & @CRLF)

;Update table
_ADO_Execute($oConnection, "UPDATE HumanResources.Department SET GroupName = 'Research && Development' WHERE Name = 'Engineering'", True)
ConsoleWrite("UPDATE successful." & @CRLF)

;Query table
$aRecordset = _ADO_Execute($oConnection, "SELECT * FROM HumanResources.Department WHERE Name = 'Engineering'", True) ; setting $aRecordset with the result before rolling back
ConsoleWrite("SELECT successful." & @CRLF)

;Rollback the update
_ADO_Execute($oConnection, "ROLLBACK TRANSACTION", True)
ConsoleWrite("ROLLBACK TRANSACTION successful." & @CRLF)

;Display query result (This is result set captured before rollback)
_ADO_Recordset_Display($aRecordset, 'Query Result')

$aRecordset = _ADO_Execute($oConnection, "SELECT * FROM HumanResources.Department WHERE Name = 'Engineering'", True) ; setting $aRecordset with the original result after rolling back

;Display query result (This is result set captured after rollback)
_ADO_Recordset_Display($aRecordset, 'Query Result')

;Close connection
_ADO_Connection_Close($oConnection)
If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Unable to close MSSQL connection.  @error = " & @error)
ConsoleWrite("Connetion closed." & @CRLF)

 

hi.. im using Microsoft SQL Server 2008 and try this but generate error 4 unable to open MSSQL connection. is it correct my declare as per image below?

[Danp2]

@moxasya SQL Server 2008 requires a different driver. Have you tried using one of the following?

; example 1
Local $sDriver = _ADO_MSSQL_GetDriverVersion()

; example 2
Local $sDriver = 'SQL Server Native Client 10.0'

 

[moxasya]

17 hours ago, Danp2 said:

@moxasya SQL Server 2008 requires a different driver. Have you tried using one of the following?

; example 1
Local $sDriver = _ADO_MSSQL_GetDriverVersion()

; example 2
Local $sDriver = 'SQL Server Native Client 10.0'

 

i try both still same error.

[Nas]

Without using Autoit, are you able to login with that username? do you have the sa account? try that one instead, make sure all your data is correct if it's a remote server do not write localhost.

[mLipok]

On 6/25/2020 at 7:42 PM, Nas said:

It doesn't show anything even with just the code below:

Global $Query = _
"UPDATE Table" & @CRLF & _
"SET HOUR = 4" & @CRLF & _
"WHERE CUST = 'TEST'" & @CRLF & _
"SELECT * FROM Table" & @CRLF & _
"WHERE CUST = 'TEST'"

Yes the changes were made but nothing was displayed.

Try recent ADO.au3 UDF update (2.1.18)

 

and here is an example how to work with transaction: