Sign in to follow this  
Followers 0
JamesDover

MSSQL Query and GUI

6 posts in this topic

#1 ·  Posted (edited)

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 by JamesDover

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

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

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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 :P

Share this post


Link to post
Share on other sites

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 :unsure:

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.

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Nice I'll give it a go. Cheers

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0