Page 1 of 1
ADO update() error
Posted: Sat Jun 05, 2010 3:17 pm
by wzaf
Hi All .
I use use ADO to access MySql DB. I try modify a row using :
oRecordSet := TOleAuto():New( "ADODB.Recordset" )
oRecordSet:CursorType := adOpenDynamic
oRecordSet:CursorLocation := adUseClient
oRecordSet:LockType := adLockOptimistic
TRY
oRecordSet:Open( "SELECT filename FROM testi WHERE iddoc = 217 " , oConnection )
CATCH oErr
MsgAlert( "Error to Open testi" )
END TRY
oRecordset:Fields("filename" ):Value = "new value"
oRecordset:update()
[/code]
but I get :
WINOLE/1007 Argument error: UPDATE
any idea ?
Best regards.
Wzaf
Re: ADO update() error
Posted: Sat Jun 05, 2010 3:47 pm
by Rick Lipkin
Perhaps in the select statement filename is a reserved word .. you might try :
oRecordSet:Open( "SELECT [filename] FROM testi WHERE iddoc = 217 " , oConnection
You can test the reserved by "select * from ......" then see if you update() fails .. if it does not fail ... filename may need to be in brackets ..
Just a quick guess ..
Rick Lipkin
Re: ADO update() error
Posted: Sun Jun 06, 2010 7:53 pm
by wzaf
Tank you Rick ,
But I tried with different fields of the table, and I get always the same error:
If I modify the field value, I get error in update method.
In I modify nothing, the update() succeed !.
Best regards
Wzaf
Re: ADO update() error
Posted: Sun Jun 06, 2010 8:27 pm
by James Bott
Maybe you don't have rights to write to the table?
Re: ADO update() error
Posted: Sun Jun 06, 2010 8:47 pm
by wzaf
No, James, I have all rights for all DB .
Tank you
Wzaf
Re: ADO update() error
Posted: Sun Jun 06, 2010 9:08 pm
by James Bott
Is the recordset empty?
James
Re: ADO update() error
Posted: Sun Jun 06, 2010 9:20 pm
by wzaf
No, it is filled with the result of Query: "SELECT filename FROM testi...."
Wzaf
Re: ADO update() error
Posted: Sun Jun 06, 2010 10:08 pm
by Daniel Garcia-Gil
wzaf...
did you verify if the SELECT return some value, i means
the select is not empty...
Re: ADO update() error
Posted: Sun Jun 06, 2010 10:36 pm
by wzaf
Daniel :
Correct ! the select is not empty....
Wzaf
Re: ADO update() error
Posted: Mon Jun 07, 2010 2:21 am
by Daniel Garcia-Gil
kzaf..
test this samples... work fine to me...
download test.mdb
http://www.sitasoft.net/fivewin/samples/testado.rar
Code: Select all
#include "FiveWin.Ch"
#include "ado.Ch"
//----------------------------------------------------------------------------//
function main()
execConnection()
return( 0 )
//----------------------------------------------------------------------------//
FUNCTION execConnection
LOCAL oCon, oRs, oError
oCon := MySQLConnect()
oRs := MySQLRecordSet( oCon )
RETURN( .T. )
//----------------------------------------------------------------------------//
FUNCTION MySQLConnect
LOCAL oError, oCon, cConnectionString
TRY
oCon := TOleAuto():new( "ADODB.Connection" )
CATCH oError
msgInfo( "No conection" )
RETURN( .F. )
END
cConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\prg\usuarios\wzaf\test.mdb;User Id=admin;Password=;"
oCon:connectionString := cConnectionString
TRY
oCon:open()
? "Connection ok!"
CATCH oError
msgInfo( "No conection" )
RETURN( .F. )
END
RETURN( oCon )
//----------------------------------------------------------------------------//
FUNCTION MySQLRecordSet( oCon )
LOCAL oRs, oError, cQuerySql
TRY
oRs := TOleAuto():new( "ADODB.RecordSet" )
CATCH oError
msgStop( "No hay RS" )
oRs := NIL
RETURN( .F. )
END
cQuerySql := "SELECT first FROM TEST WHERE id = 3"
oRs:cursorLocation := adUseClient // 3
oRs:lockType := adLockOptimistic // 3
oRs:cursorType := adOpenDynamic // 2
TRY
oRs:Open( cQuerySql , oCon )
CATCH oError
msgStop( "No Query '" + cQuerySql + "'" )
RETURN( .F. )
END
oRs:Fields("first" ):Value := "testing"
oRs:Update()
RETURN( oRs )
Re: ADO update() error
Posted: Mon Jun 07, 2010 9:44 am
by wzaf
Daniel,
your code works fine in my environment.
I tried also to recompile and link , and it works!
The only difference is that I use Mysql with "MySQL ODBC 5.1 Driver" and connection string is :
cConnectionString := "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=mydb; User=myuser;Password=mypassw"
If I use this connection string in your file , and modify the Select statement to work in my DB, I get the usual error: update error() !
Tank you
Best regards
Wzaf.
Re: ADO update() error
Posted: Mon Jun 07, 2010 10:10 am
by anserkk
Try this,
ConnectionString := "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=mydb;User=myuser;Password=mypassw;Option=3;"
The below given code to update a recordset is working fine for me
Code: Select all
TRY
oRecSet:= CreateObject("ADODB.RecordSet")
CATCH oError
MsgInfo("Failed to Create Recordset")
ShowSqlError(oError)
oRecSet:=NIL
Return .F.
END
cSql:="Select Branch_ID, User_ID, UserName, Password, User_Level, Active from users where Branch_ID="+ltrim(str(oApp:nBranchID))+" and User_Id ="+Alltrim(str(oTmp:nUserId2))
oRecSet:CursorLocation := adUseClient
oRecSet:LockType := adLockOptimistic
oRecSet:CursorType := adOpenDynamic
oRecSet:Source :=cSQL
oRecSet:ActiveConnection(oApp:oConnection)
TRY
oRecSet:Open()
CATCH oError
MsgInfo("Failed to Open Recordset of User Details")
ShowSqlError(oError)
oRecSet:=NIL
Return .F.
END
If oRecSet:Eof() .and. oRecSet:Bof()
MsgInfo("Failed to update the changes, No Records found in retrieved recordset")
oRecSet:Close()
Return .F.
Endif
oRecSet:Fields("Password"):Value:=Alltrim(oTmp:cPassword2)
oRecSet:Fields("Active"):Value:=If(oTmp:nStatus2 == 1,"T","F")
oRecSet:UpDate()
SysRefresh()
oRecSet:Close()
Regards
Anser
Re: ADO update() error
Posted: Mon Jun 07, 2010 4:35 pm
by wzaf
Tank you Anser
I tried your code , but now when I try the following :
Code: Select all
RecSet:Source :=cSQL
oRecSet:ActiveConnection(oCon)
oRecSet:Open()
I get the following error:
WINOLE/1007 Argument error: ACTIVECONNECTION
while if I do :
the creation of RecordSet succeed .
I think there is something wrong in the installation of ADO in my pc.
May Be ?
Best regards
WZaf
Re: ADO update() error
Posted: Mon Jun 07, 2010 6:08 pm
by Daniel Garcia-Gil
wzaf...
try change to this line
Code: Select all
oRecordSet:Open( "SELECT iddoc, filename FROM testi WHERE iddoc = 217 " , oConnection )
i added iddoc (columns) inside select
Re: ADO update() error
Posted: Tue Jun 08, 2010 8:28 am
by wzaf
Tank you very mutch, Daniel.
Now the update works!
It seems that ADO wants in RecordSet the primary key , otherwise it fails when update the DB.
Not only, but when I tried to update (and getting errors), the table where modified in more than one row, not only the row holding the '217' key!!! .
Best regards,
Wzaf