Ranmaru Posted October 24, 2007 Share Posted October 24, 2007 (edited) I need help with this. I am able to connect to my database, retrieve and insert data, but my problem is.. If in the data like an account name or so there's a ; or ' then I will get an error. I heard about preparing the data, but I couldn't get that to work since I found hardly nothing on it.. I'd be very grateful to you if you were to solve this problem for me and perhaps give me an example of preparing the data that is sent to the SQL database through ODBC using ADO. (ADODB.Connection object) Edited October 24, 2007 by Ranmaru Link to comment Share on other sites More sharing options...
Ranmaru Posted October 24, 2007 Author Share Posted October 24, 2007 Bump. Link to comment Share on other sites More sharing options...
Ranmaru Posted October 24, 2007 Author Share Posted October 24, 2007 Bump. Link to comment Share on other sites More sharing options...
ChrisL Posted October 24, 2007 Share Posted October 24, 2007 Using the _SQL.au3 stuff in my signature, I'm not seeing the problem expandcollapse popup#include <array.au3> #include <_sql.au3> Opt ("trayIconDebug",1) Msgbox(0,"","Start") $con = _SQLStartup() If @error then Msgbox(0,"Error","Error starting ADODB.Connection") ;Create a database called test _sqlConnect(-1,"localhost","","sa","Superartcore") if @Error then Msgbox(0,"Line 20",$SQLErr) _SQLExecute(-1,"Create database My_SQL_Test;") if @Error then Msgbox(0,"Line 22",$SQLErr) _SQLClose() Msgbox(0,"","Created datatbase log out and back in") $con = _SQLStartup() If @error then Msgbox(0,"Error","Error starting ADODB.Connection") _sqlConnect(-1,"localhost","My_SQL_Test","sa","Superartcore") if @Error then Msgbox(0,"LINE 30",$SQLErr) _SQLExecute(-1, "CREATE TABLE BBKS (ID INT NOT NULL IDENTITY(1,1),ComputerName VARCHAR(20) UNIQUE,Status VARCHAR(10),Error VARCHAR(10)Primary Key (ID));") if @Error then Msgbox(0,"",$SQLErr) _SQLExecute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('"& @computername & "','On;li''ne','None');");Online with a semi colon and apostrophe if @Error then Msgbox(0,"",$SQLErr) _SQLExecute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('1"& @computername & "','On;li''ne','None');");Online with a semi colon and apostrophe if @Error then Msgbox(0,"",$SQLErr) _SQLExecute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('2"& @computername & "','On;li''ne','None');");Online with a semi colon and apostrophe if @Error then Msgbox(0,"",$SQLErr) Msgbox(0,"","Created table and added data so get some data out") $data = _SQLExecute(-1,"SELECT ComputerName AS Expr1, Status AS Expr2 FROM BBKs WHERE (ComputerName = '2inept')") If Not @error then $aData = _SqlGetData2D($data) _ArrayDisplay($aData) Else Msgbox(0,"",$SQLErr) EndIf $data = _SQLExecute(-1,"SELECT * FROM BBKS;") If Not @error then $aData = _SQLGetData2D($data) _arrayDisplay($aData) Else Msgbox(0,"",$SQLErr) EndIf $data = _SQLExecute(-1,"SELECT * FROM BBKS;") If Not @error then $sData = _SqlGetDataAsString($data) Msgbox(0,"Data as a string",$sData) Else Msgbox(0,"",$SQLErr) EndIf _SQLExecute(-1, "DROP TABLE BBKS;") if @Error then Msgbox(0,"",$SQLErr) _SQLClose() Msgbox(0,"","dropped the table will drop the database next") $con = _SQLStartup() If @error then Msgbox(0,"Error","Error starting ADODB.Connection") _sqlConnect(-1,"localhost","","sa","Superartcore") if @Error then Msgbox(0,"",$SQLErr) _SQLExecute(-1,"DROP database My_SQL_Test;") if @Error then Msgbox(0,"",$SQLErr) _SQLClose() [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
Ranmaru Posted October 24, 2007 Author Share Posted October 24, 2007 Yes I see it works by adding another ' before the ', and for some reason I didn't even get errors on the ; this time. Link to comment Share on other sites More sharing options...
ChrisL Posted October 25, 2007 Share Posted October 25, 2007 because you are already using ' in to enclose the data you need to double it up when it is part of the data [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
Ranmaru Posted October 25, 2007 Author Share Posted October 25, 2007 because you are already using ' in to enclose the data you need to double it up when it is part of the dataThat is what I don't understand..' starts the string and ' ends it.. So this is a string data with '''''To me that is.. Starting the string, ending it, starting it, ending it.So are there other characters that can't be used like that and which this character or the same character twice works on? Link to comment Share on other sites More sharing options...
ChrisL Posted October 25, 2007 Share Posted October 25, 2007 That is what I don't understand..' starts the string and ' ends it.. So this is a string data with '''''To me that is.. Starting the string, ending it, starting it, ending it.So are there other characters that can't be used like that and which this character or the same character twice works on?It's Autoit that is taking it as the end of the string which is why you need to double it up [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire 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