MSSQL Query and GUI

[JamesDover]

I'm trying to connect to sql server 2000 and run a query and display the results in a gui output. I'm not exactly sure where to plug in the gui output. Here is what i got so far. Cheers

$SQLQuery = "SELECT MaximumPoints FROM Assignments"

$DSN = "Provider=SQLOLEDB;Server=192.168.1.10;Database=aaa;Uid=user;Pwd=12345"
ExecuteQuery($DSN, $SQLQuery)

Func ExecuteQuery($DSN, $SQLQuery)
    $adoSQL = $SQLQuery
    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open($DSN)
    $adoCon.Execute($adoSQL)
    $adoCon.Close
EndFunc ;==>ExecuteQuery

On another note would _sqlite_Query work with sql server 2000?

Edited March 5, 2009 by JamesDover

[PsaltyDS]

I'm trying to connect to sql server 2000 and run a query and display the results in a gui output. I'm not exactly sure where to plug in the gui output. Here is what i got so far. Cheers

$SQLQuery = "SELECT MaximumPoints FROM Assignments"

$DSN = "Provider=SQLOLEDB;Server=192.168.1.10;Database=aaa;Uid=user;Pwd=12345"
ExecuteQuery($DSN, $SQLQuery)

Func ExecuteQuery($DSN, $SQLQuery)
    $adoSQL = $SQLQuery
    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open($DSN)
    $adoCon.Execute($adoSQL)
    $adoCon.Close
EndFunc;==>ExecuteQuery

On another note would _sqlite_Query work with sql server 2000?

You might try some study here: W3Schools ADO Tutorial

Results of a query are usually in an ADO recordset.

P.S. The _SQLite* functions won't help because they call the SQLite DLL file for access only to SQLite database files.

Edited March 5, 2009 by PsaltyDS

[JamesDover]

Excellent!! That site helped a lot. This is what I have now which displays the first field "sde_id" but not the rest. Any ideas how I could display the rest of them and their full row.

Example.....

sde_id start_time nodename

3434 343434 John

2342 343434 Fred

2354 343324 Joe

Cheers

$conn = ObjCreate("ADODB.Connection")
$DSN = "DRIVER={SQL Server};SERVER=192.168.1.10;DATABASE=aaa;UID=user;PWD=12345;"
$conn.Open($DSN)
$rs = ObjCreate("ADODB.RecordSet")
$rs.Open("SELECT sde_id, start_time, nodename FROM SDE_process_information", $conn)
MsgBox(0, "ConnectionsSDE", $rs.Fields("sde_id" ).Value)
$conn.close

P.S. why does it have a . by $rs.open and .Value. Just wondering

[PsaltyDS]

Excellent!! That site helped a lot. This is what I have now which displays the first field "sde_id" but not the rest. Any ideas how I could display the rest of them and their full row.

Example.....

Cheers

$conn = ObjCreate("ADODB.Connection")
$DSN = "DRIVER={SQL Server};SERVER=192.168.1.10;DATABASE=aaa;UID=user;PWD=12345;"
$conn.Open($DSN)
$rs = ObjCreate("ADODB.RecordSet")
$rs.Open("SELECT sde_id, start_time, nodename FROM SDE_process_information", $conn)
MsgBox(0, "ConnectionsSDE", $rs.Fields("sde_id" ).Value)
$conn.close

P.S. why does it have a . by $rs.open and .Value. Just wondering

Your recordset $rs contains all the rows and fields requested by your SELECT query. As you see in the tutorials, you move between records (rows) with methods like .MoveNext. Another option is to get all the rows and fields into a single 2D array with .GetRows.

The recordset has a collection called Fields which is self-explanatory, and can return the .Value property of a single field for the current row.

So your reference to $rs is the recordset,

$rs.Fields is the collection of fields in the recordset,

$rs.Fields("sde_id") is that selected field in the current row (can also be a 1-based index),

and $rs.Fields("sde_id").Value returns the value of the selected field in the current row.

To read out all the field values of a single row you might do:

For $n = 1 To $rs.Fields.Count
    MsgBox(0, "ConnectionsSDE Field " & $n, $rs.Fields($n).Value)
Next

The correct reference might also be: $rs.Fields.Item($n).Value

There are minor differences in the syntax based on which "provider" (SQL Server program) is in use.

[DaRam]

James,

Please look at my post here http://www.autoitscript.com/forum/index.ph...st&p=646096

Replace the Connection string and SQL string with yours and you are off to exploring various ADO methods including AutoIt's COM error handler.

[JamesDover]

Nice I'll give it a go. Cheers