Help update sql with string

[ahmish]

Good Day masters,

 

i am currently doing a project that requires me to update a value of an sql database. the value i get is coming from a string. can somebody help me on this? the sql table looks like this

Id will be incremented automatically so no need to update. i just need to always update first row only for Remarks & Last update. i don't need to create a new row.

ID | Remarks | Lastupdate

[argumentum]

https://www.google.com/search?q=sql+update   

[Nine]

What database is this ?  Show us what you have tried so far !

[ahmish]

15 minutes ago, Nine said:

What database is this ?  Show us what you have tried so far !

im using MSSQL. so i haven't done anything yet as im like a lost cat right now. i know vb.net using visual studio we can bind controls to sql database. but i don't know how to do it in autoit.

hope you can help this noob T_T. maybe just guide me in the right way and let me do the coding while you check if im doing ok

 

Update

 

so here's what im checking right now

 

Local $ServerAddress = "   INSERT YOUR SQL SERVER IP HERE    "
Local $ServerUserName = "   INSERT YOUR USERNAME HERE      "
Local $ServerPassword = "    INSERT YOUR PASSWORD HERE  "
Local $DatabaseName = "    INSERT YOUR DATABASE NAME HERE   "

_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error
$OADODB = _SQL_Startup()
If $OADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
If _sql_Connect(-1, $ServerAddress, $DatabaseName, $ServerUserName, $ServerPassword) = $SQL_ERROR Then
MsgBox(0 + 16 + 262144, "Error 1", _SQL_GetErrMsg())
_SQL_Close()
Exit
EndIf

below is quering which i think i dont need coz i need to push values to table

Local $fullSQL
If _Sql_GetTableAsString(-1, "SELECT Brand, Description, UPC, Price, Updated FROM Inventory ORDER BY Brand, Description;", $fullSQL) = $SQL_OK Then
Else
MsgBox(0 + 16 + 262144, "SQL Error", _SQL_GetErrMsg())
EndIf

 

Edited February 3, 2020 by ahmish
update

[mLipok]

Welcome to the AutoIt Forum.

 

One recomendation:
I see you are using old _SQL.au3
Try to use:

 

[ahmish]

On 2/4/2020 at 2:17 AM, mLipok said:

Welcome to the AutoIt Forum.

 

One recomendation:
I see you are using old _SQL.au3
Try to use:

 

Hi,

 

so i downloaded the latest version of ADO.au3. i have checked the examples as well but they are mostly querying sql database. getting value from sql to autoit.

 

i want to be able to pass values from autoit to sql. which i don't know how to do

 

Func _Example_MSSQL_SQLServerAuthorization()
    Local $sDriver = 'SQL Server' ;<----- I dont know this but i left it as is and it works
    Local $sDatabase = '######' ; change this string to YourDatabaseName
    Local $sServer = '######' ; change this string to YourServerLocation
    Local $sUser = '######' ; change this string to YourUserName
    Local $sPassword = '######' ; change this string to YourPassword

    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from TEST_TABLE")
    ;_Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
    ;_Example_3_ConnectionProperties($sConnectionString)

    ;_Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, "Select * from SOME_TABLE")

EndFunc   ;==>_Example_MSSQL

So i was able to figure out how to make this work.. i'm leaving my codes below if in case somebody else want the same thing

expandcollapse popup

Func QueryDatabase()    
    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
    SQLConnection($sConnectionString, "Select * from [Test Table]")
    if $aRecordsetContent[0][2] = "Start" Then
    EndIf
 EndIf
EndFunc   ;==>QueryDatabase

Func WriteDatabase()
    Local $Date = "@MDAY & "-" & @MON  & "-" &  @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC"
    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
    SQLConnection($sConnectionString, "UPDATE [Test Table] SET TestRemarks = '" & $Date & "' WHERE ID = 1")
 EndIf
EndFunc   ;==>WriteDatabase

Func SQLConnection($sConnectionString, $sQUERY)
   
    Global $sDriver = 'SQL Server'
    Global $sDatabase = '#######' ; change this string to YourDatabaseName
    Global $sServer = '#######' ; change this string to YourServerLocation
    Global $sUser = '#######' ; change this string to YourUserName
    Global $sPassword = '#######' ; change this string to YourPassword

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; Executing some query
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

    ; Get recordset to array of arrays (Conent and ColumnNames)
    Local $aRecordsetAsArray = _ADO_Recordset_ToArray($oRecordset, False)
    ; Get inner array - only conent of Recordset
    Global $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRecordsetAsArray)

    ; Clean Up
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc   ;==>SQLConnection

 

Edited February 5, 2020 by ahmish