SQL headaches

[The_Lorax]

Hello,

I created a simple query in access using design view:

SELECT person_tbl.FirstName FROM person_tbl WHERE (((person_tbl.personID)="12"));

The query works if it is run in access, but It crashes when I try run it from autoit:

$namequery = 'SELECT person_tbl.FirstName FROM person_tbl WHERE (((person_tbl.personID)="12"));'
msgbox(0,"",_SQLQuery($oDBid,$namequery))

Does anyone have an working autoit SQL SELECT FROM... WHERE statement that I can use to check mine for syntax errors?

Also:

for $i= 1 to 20
$query = "INSERT INTO person_tbl (personID) VALUES('" & $array[0][$i-1] & "')"
SQLQuery($OdBid,$query)
Next
_SQLclose($oDBid)

this code adds data from an array into a primary key field in the database. Sometimes that array contains duplicate data, which causes the script to crash.

I need to add some error handling to sql.au3 or my code so when _SQLQuery() throws a 'key already exists' error the loop continues.

Does anyone know how to do this?

Thanks

Edited September 15, 2005 by The_Lorax

[theguy0000]

download my sql.au3 [link] and put it in your include directory. then use this

#include <sql.au3>
_SQLConnect("server", "database", "username", "password")
_SQLExecute('SELECT person_tbl.FirstName FROM person_tbl WHERE (((person_tbl.personID)="12"));')
_SQLClose()

[Idea]

download my sql.au3 [link] and put it in your include directory. then use this

#include <sql.au3>
_SQLConnect("server", "database", "username", "password")
_SQLExecute('SELECT person_tbl.FirstName FROM person_tbl WHERE (((person_tbl.personID)="12"));')
_SQLClose()

<{POST_SNAPBACK}>

How do I use _SQLConnect when it's an access database? Is it possible?

Do I have to create a System DSN? At the moment I just have the database...

This should work right? It doesn't

$db = "C:\db.mdb"
    Dim $adCN;<==Declare variable
    $adCN = ObjCreate ("ADODB.Connection");<==Create SQL connection
    $adCN.Open("DRIVER={Microsoft Access Driver *.mdb}; DBQ=" & $db)
    $adCN.Close

Edited September 15, 2005 by Idea

[dougie]

download my sql.au3 [link] and put it in your include directory. then use this

#include <sql.au3>
_SQLConnect("server", "database", "username", "password")
_SQLExecute('SELECT person_tbl.FirstName FROM person_tbl WHERE (((person_tbl.personID)="12"));')
_SQLClose()

Added this function to your sql.au3 functions - Thanks for the UDF

Func _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut=600)
    Local $o_ADOCmd, $o_ADOrs
    $o_ADOCmd = ObjCreate("ADODB.Command")
    $o_ADOCmd.ActiveConnection = $o_ADOcn
    $o_ADOCmd.CommandText = $s_Query
    $o_ADOCmd.CommandTimeOut = $cmd_TimeOut
    $o_ADOrs = $o_ADOCmd.Execute

    if @error Then return 0
    return $o_ADOrs
EndFunc

When performing looonnngg queries, the _SQLQuery times out. By using the _SQLCommand, you can set the time period.

Hope this is of some use.

Dougie