GreatWest Posted March 10, 2006 Share Posted March 10, 2006 (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 ExitSimple 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.957No 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 March 10, 2006 by GreatWest Link to comment Share on other sites More sharing options...
cdkid Posted March 10, 2006 Share Posted March 10, 2006 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 More sharing options...
cdkid Posted March 10, 2006 Share Posted March 10, 2006 (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 March 10, 2006 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 More sharing options...
GreatWest Posted March 10, 2006 Author Share Posted March 10, 2006 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.351I'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 More sharing options...
GreatWest Posted March 10, 2006 Author Share Posted March 10, 2006 this worked for me... I installed msde 2000... Thanks Larry! Sure is pointing to a server security issue now. I'll try it myself this weekend so I can prove it to our admin. Thanks again!GW Link to comment Share on other sites More sharing options...
GreatWest Posted March 11, 2006 Author Share Posted March 11, 2006 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 More sharing options...
GreatWest Posted March 11, 2006 Author Share Posted March 11, 2006 There are some sql ports that need opening... lik 1433 or something... google it.Lar. 1433 and 34. A netstat -an shows both ports open and either listening or established. No issues there.GW Link to comment Share on other sites More sharing options...
GreatWest Posted March 11, 2006 Author Share Posted March 11, 2006 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 EndFuncThanks 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now