ADO & MS SQL

[Merrik]

Hi,

I'm looking for some help to create an ADO connection to a MS SQL server on a domain from a client computer.

I need to open the connection, update two fields, and close the connection.

I need to do this without functions. This is causing me grief.

Any help is appreciated.

Thanks

[LurchMan]

This uses existing ODBC connection if that's what you're looking for:

$oDb = ObjCreate("ADODB.Connection")

    $oDB.Open ("ODBC_Name")  ;;Be sure to change ODBC name here

    $oSQLRS = $oDB.Execute ("Select * From DBName") ;;Change SQL Statement here

More information on ADOB.Connection Object here

Edit: Spelling

Edited April 27, 2011 by LurchMan

[Merrik]

I was hoping to use something like this, but without the functions

Func _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", $sDriver = "{SQL Server}")
    Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2)
    Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp)
    If @error or $sVal = "" Then Return SetError(2, 0, 0)
    $oConn = ObjCreate("ADODB.Connection")
    If NOT IsObj($oConn) Then Return SetError(3, 0, 0)
    If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")
    If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)
    If @error Then Return SetError(1, 0, 0)
    Return $oConn
EndFunc   ;==>_SQLConnect

Link:

[jchd]

By mere curiosity: why do you need to avoid invoking functions at any rate?

[Merrik]

By mere curiosity: why do you need to avoid invoking functions at any rate?

:-) I am working within a function. I have been warned about recursions and so I am trying to do this without functions. I used the example included, whuich uses functyions, and it works fine. I have little experience with SQL and could use the help.

[GEOSoft]

You can't create a function within a function (Nesting) but there is no harm at all in calling another Function from within a Function and in fact it's a very common practice.

[jchd]

Recursion is a function (say Fct) invoking itself, Fct, as part of its body. Recursion is deadly only when the condition to end recursion (return from Fct without invoking Fct anymore) is not well coded. Since stack space is bounded, unbounded recursion cause stack overflow and program crash.

OTOH that a function A invokes a function B, which itself invokes a function C, for instance, is a perfectly normal situation which you can find in almost every decent piece of code. Look at almost any standard UDF (use Alt I from Scite) to convince yourself that it isn't black magic.

[Ghost21]

Ok Not often do I stop and reply to different things but I do alot with databases and I see a need.

Do all your SQL Functions / Queries etc in the database don't put it in the program..

a. It's faster

b. Cleaner

c. Once you get the just of it, much simplier

_DatabaseOpen()
    $Recordset.Open('exec OfflineDevices', $Connection, 1, 3)
    With $Recordset
        If .Recordcount Then
            .movenext
            Next
            GUICtrlSetData($Edit, .Recordcount & " Offline Devices found." & @CRLF, 1)
        Else
            MsgBox(48, "Associated Device", "Not found.", 5)
        EndIf
    EndWith
    $Connection.close
    _DataBaseclose()

This code is just running the query in the Database and returning the Good Stuff..

The only issue I run into is when I need to pass data to the Query like a variable then I need to put that whole mile long sql statement in my program.

Unless someone else knows how to call a query with a variable attached ... Hope this helps...

[mdthanh]

Hi Merrik,

Somewhere in forum help me got sqlconnect.au3 attached here.

And to use it to update your table, you need to create another file as updatedata.au3 and declare as below:

#include <sqlconnect.au3>

#include <GUIConstantsEx.au3>

;Connect to sqldatabase

$oConnectSQL = _SQLConnect('servername', 'databasename', 1, 'username', 'password') ;1 = sql authentication

;Execute the query to update your table

$oExeResult = _SQLQuery($oConnectSQL, "update yourtable set field1='A', field2 = 'B' where ID=123 "); be careful with your update script

if not IsObj($oExeResult) then

Msgbox(0,"Error","$oExeResult is not an Object.")

else

Msgbox(0,"Error","Update data successfully")

endif

;Disconnect database

_SQLDisconnect($oConnectSQL)

;Your code to create gui here

Edited May 11, 2011 by mdthanh

[hannes08]

Somewhere in forum help me got sqlconnect.au3 attached here.

Hi mdthanh,

I guess Merrik already took a look at the _SQL* functions before. See his post from 27 April 2011 - 01:47 PM

[mdthanh]

Hi mdthanh,

I guess Merrik already took a look at the _SQL* functions before. See his post from 27 April 2011 - 01:47 PM

Oopp, so let hope other could use my post.

Thanks you all,

[hannes08]

Oopp, so let hope other could use my post.

Thanks you all,

Hi mdthanh,

I think that any help is welcome.

[ChrisL]

Merrik, did you try connecting to the database by using the _SQL functions but as a separate file NOT called or using the services.au3.

You need to know if you can edit/update the database using the _SQL functions before adding the extra layer of running as a service.