Sign in to follow this  
Followers 0
The_Lorax

SQL headaches

4 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



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()

The cake is a lie.www.theguy0000.com is currentlyUP images.theguy0000.com is currentlyUP all other *.theguy0000.com sites are DOWN

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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 B)

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

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