Jump to content

MS SQL and AutoIt driving me nuts

GreatWest

By GreatWest,
in AutoIt Technical Discussion

 Share

Recommended Posts

GreatWest Seeker

GreatWest

Posted
Posted (edited)

Okay this is just driving me nuts :) .

I've written a lot of scripts, some very lengthy in AutoIt but when it comes to using AutoIt with an MS SQL database I'm in major confusion mode. I have asked for help here and been pointed in some good directions, but I'm still not getting it. What's more is that a SQL admin co-worker hasn't been able to figure out what I'm doing wrong and a friend who does .asp and .net work with SQL databases isn't able to figure out what's wrong either. So hopefully someone can explain this in a way that I can understand, because I'm just not getting it. I started with a fairly basic database and I've been able to read from it, but can't seem to insert or update any records. So I've blown it away and started with a very simple database.

Database

-----------------------------------------

| Index | Name |

-----------------------------------------

| 1 | Todd |

-----------------------------------------

| 2 | Brian |

----------------------------------------

| 3 | Eric |

-----------------------------------------

| 4 | Daryl |

----------------------------------------

The name of the database is AMSUD. The name of the table above is TestTable. I have checked all permissions and I have read/write/update access.

Here's the code that my friend gave me. He thought that this would work without any problem.

$SQLQuery = "INSERT INTO TestTable(Index,Name) VALUES(5,'Mike')"

$DSN = "Provider=SQLOLEDB.1;Password=******;Persist Security Info=True;User ID=xf021209;Initial Catalog=AMSUD;Data Source=dles322"

Func ExecuteQuery ($DSN,$SQLQuery)

$adoSQL = $SQLQuery

$adoCon = ObjCreate ("ADODB.Connection")

$adoCon.Open ($DSN)

$adoCon.Execute($adoSQL)

$adoCon.Close

Return 0

EndFunc

Exit

Simple enough. It doesn't work. Here's what comes up in the SciTE window.

>"C:\Program Files\AutoIt3\SciTE\CompileAU3\CompileAU3.exe" /run /beta /ErrorStdOut /in "C:\SQL\Test_SQL-1.au3" /autoit3dir "C:\Program Files\AutoIt3\beta" /UserParams

>Running AU3Check C:\Program Files\AutoIt3\SciTE\Defs\Unstable\Au3Check\au3check.dat

>AU3Check Ended. No Error(s).

>Running: (3.1.1.111):C:\Program Files\AutoIt3\beta\autoit3.exe "C:\SQL\Test_SQL-1.au3"

>AutoIT3.exe ended.

>Exit code: 0 Time: 0.957

No errors, no problems but the database does not add a record with the name of Mike. I have also tried this by going directly through the DSN which is setup to point at the database, still no luck. Can someone please point out what's missing here so I can make this simple insert work?

Thanks,

GW

Edited by GreatWest
Link to comment
Share on other sites
cdkid Universalist

cdkid

Posted
Posted

Well, i dont deal much with MS SQL but i do with MySQL... i'd say look at Randallc's SQL functions in Scripts & Scraps

~cdkid

AutoIt Console written in C#. Write au3 code right at the console :D_FileWriteToLineWrite to a specific line in a file.My UDF Libraries: MySQL UDF Library version 1.6 MySQL Database UDF's for AutoItI have stopped updating the MySQL thread above, all future updates will be on my SVN. The svn location is:kan2.sytes.net/publicsvn/mysqlnote: This will still be available, but due to my new job, and school hours, am no longer developing this udf.My business: www.hirethebrain.com Hire The Brain HireTheBrain.com Computer Consulting, Design, Assembly and RepairOh no! I've commited Scriptocide!
Link to comment
Share on other sites
cdkid Universalist

cdkid

Posted
Posted (edited)

edit:

Wait wait wait wait, you're never calling ExecuteQuery...

try

$SQLQuery = "INSERT INTO TestTable(Index,Name) VALUES(5,'Mike')"

$DSN = "Provider=SQLOLEDB.1;Password=******;Persist Security Info=True;User ID=xf021209;Initial Catalog=AMSUD;Data Source=dles322"
executequery($dsn, $SQLQuery)

Func ExecuteQuery ($DSN,$SQLQuery)

$adoSQL = $SQLQuery 
$adoCon = ObjCreate ("ADODB.Connection")
$adoCon.Open ($DSN)
$adoCon.Execute($adoSQL)

$adoCon.Close 
Return 0 
EndFunc 
Exit

~cdkid

Edited by cdkid
AutoIt Console written in C#. Write au3 code right at the console :D_FileWriteToLineWrite to a specific line in a file.My UDF Libraries: MySQL UDF Library version 1.6 MySQL Database UDF's for AutoItI have stopped updating the MySQL thread above, all future updates will be on my SVN. The svn location is:kan2.sytes.net/publicsvn/mysqlnote: This will still be available, but due to my new job, and school hours, am no longer developing this udf.My business: www.hirethebrain.com Hire The Brain HireTheBrain.com Computer Consulting, Design, Assembly and RepairOh no! I've commited Scriptocide!
Link to comment
Share on other sites
GreatWest Seeker

GreatWest

Posted
  • Author
Posted

Well I wish that would have worked.

---------------------------------

$SQLQuery = "INSERT INTO TestTable(Index,Name) VALUES(5,'Mike')"

$DSN = "Provider=SQLOLEDB.1;Password=******;Persist Security Info=True;User ID=xf021209;Initial Catalog=AMSUD;Data Source=dles322"

ExecuteQuery($DSN, $SQLQuery)

Func ExecuteQuery ($DSN,$SQLQuery)

$adoSQL = $SQLQuery

$adoCon = ObjCreate ("ADODB.Connection")

$adoCon.Open ($DSN)

$adoCon.Execute($adoSQL)

$adoCon.Close

Return 0

EndFunc

Exit

--------------------------------------

AutoIt responds with -

>"C:\Program Files\AutoIt3\SciTE\CompileAU3\CompileAU3.exe" /run /beta /ErrorStdOut /in "C:\SQL\Test-SQL-6.au3" /autoit3dir "C:\Program Files\AutoIt3\beta" /UserParams

>Running AU3Check C:\Program Files\AutoIt3\SciTE\Defs\Unstable\Au3Check\au3check.dat

>AU3Check Ended. No Error(s).

>Running: (3.1.1.111):C:\Program Files\AutoIt3\beta\autoit3.exe "C:\SQL\Test-SQL-6.au3"

C:\\SQL\Test-SQL-6.au3 (11) : ==> The requested action with this object has failed.:

$adoCon.Open ($DSN)

$adoCon.Open ($DSN)^ ERROR

>AutoIT3.exe ended.

>Exit code: 0 Time: 1.351

I'm really starting to think that I need to kill our ServerAdmin/SQL DBA. He's new to SQL and I don't think he has the permissions setup correctly. So this weekend I think I'm going to setup my own stand alone W2K box and put MSSQL 2000 on it. Then try and access it. If the AutoIt script works, then I know it's an issue with the box he administers. I've tried many different scripts posted on this board where the author says that they work. I can't get a one of them to work. Tried many different modifications, and no luck. I can't believe that everyone who posts here is wrong, that would just be stupid. It's more likely our newbee SQL DBA has something setup wrong on the box. After all, he won't even let me create my own database, only tables and below. I can't see my permissions at the database level, only on the tables.

Thanks for the help and suggestions. I'm determined to get this working.

GW

Link to comment
Share on other sites
GreatWest Seeker

GreatWest

Posted
  • Author
Posted

this worked for me... I installed msde 2000...

$SQLQuery = "INSERT INTO TestTable VALUES(5,'Mike')"

$DSN = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=****;Initial Catalog=TestCat;Data Source=MyPC\Instance"
ExecuteQuery($DSN, $SQLQuery)

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

Okay I'm really not liking this to much. I think I need to take a break from it. I've setup a SQL server on a W2K server. I'm able to connect to it with no issue from my laptop where I have AutoIt loaded, and where I'm running the scripts from. Larry, from the looks of it you're running SQL on your PC where you're running the script from? The error seems to be a connectivity error over the network that I'm running into. I keep getting the same stupid error I've been getting for the last week.

C:\SQL-7.au3 (9) : ==> The requested action with this object has failed.:

$adoCon.Open ($DSN)

$adoCon.Open ($DSN)^ ERROR

Any suggestions, other than a 9mm?

GW

Link to comment
Share on other sites
GreatWest Seeker

GreatWest

Posted
  • Author
Posted

Not sure what the heck it was but finally - SUCCESS! I've run this little script several times and it's placing new data into the MS SQL database table specified.

$SQLQuery = "INSERT INTO TestTable VALUES(6,'Mike')"

$DSN = "Provider=SQLOLEDB;Server=192.168.15.100;Database=AMSUD;Uid=sa;Pwd=sa"

ExecuteQuery($DSN, $SQLQuery)

Func ExecuteQuery ($DSN,$SQLQuery)

$adoSQL = $SQLQuery

$adoCon = ObjCreate ("ADODB.Connection")

$adoCon.Open ($DSN)

$adoCon.Execute($adoSQL)

$adoCon.Close

EndFunc

Thanks for everyone's persistance and help! Now to do a bit more testing and start re-writing my former script to use SQL instead of a .csv file!

GW

Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...