Showing Inserted Records in CursorAdapter Cursor

[Mickbw]

Hi,

When I use a CursorAdapter to insert records into a mySQL table, the new records do not show in the cursor but do get inserted into the database.

I think I need to see these records in order to get the last ID inserted into the table unless there is a better way I can do this.

The First Part of my code:

LPARAMETERS lCloseAll
TRY
	IF lCloseAll = .T.
		CLOSE DATABASES ALL
	ENDIF
	IF VARTYPE(oVar) # 'O'
&&Creation of Empty Object to store Variable Values
		MakeoVar(SYS(16))  &&Adding a reference to the calling program for log purposes
	ENDIF
	PUBLIC lo
	lo = NEWOBJECT("caUnit")
	OPEN DATABASE webbanking
	USE lBankUnit EXCLUSIVE
	ALTER TABLE lBankUnit ALTER COLUMN UnitID i(4)
	SELECT lBankUnit
	SCAN
		INSERT INTO cUnit (UnitNumber, ;
			UnitName,;
			TranDate, ACTIVE) ;
			VALUES (lBankUnit.UnitNumber, ;
			PADR(lBankUnit.UnitName,60), 
                        TTOD(lBankUnit.TranDate),1)

It is at this point that I would like to get the value of the UnitID from cUnit . UnitID is a auto_incremented integer field in the mySQL database. When I look at cUnit the record I just inserted is not there.

My code to create the CA:

DEFINE CLASS caUnit AS CURSORADAPTER
	TAG = "dsn=WebBanking;uid=root;trusted_connection=no;"
	HEIGHT = 22
	WIDTH = 23
	SELECTCMD = "See Init"
	CURSORSCHEMA = "UNITID I, UNITNUMBER I, UNITNAME C(60), TRANDATE D, ACTIVE I, USERID I"
	ALIAS = "cUnit"
	DATASOURCETYPE = "ODBC"
	FLAGS = 0
	KEYFIELDLIST = "See Init"
	TABLES = "lbankunit"
	UPDATABLEFIELDLIST = "See Init"
	UPDATENAMELIST = "See Init"
	NAME = "caUnit"
	PROCEDURE INIT
		LOCAL llReturn
		DO CASE
			CASE NOT pemstatus(THIS, '__VFPSetup', 5)
				THIS.ADDPROPERTY('__VFPSetup', 0)
			CASE THIS.__VFPSetup = 2
				THIS.__VFPSetup = 0
				RETURN
		ENDCASE
		llReturn = DODEFAULT()
		*** Select connection code: DO NOT REMOVE
		SET MULTILOCKS ON
		***<DataSource>
		THIS.DATASOURCE = SQLSTRINGCONNECT([dsn=WebBanking;uid=root;trusted_connection=no;])
		***</DataSource>
		*** End of Select connection code: DO NOT REMOVE
		*** Setup code: DO NOT REMOVE
		***<SelectCmd>
		TEXT to This.SelectCmd noshow
		select lbankunit.UNITID, lbankunit.UNITNUMBER, lbankunit.UNITNAME, lbankunit.TRANDATE, lbankunit.ACTIVE, lbankunit.USERID from lbankunit
		ENDTEXT
				***</SelectCmd>
				***<KeyFieldList>
		TEXT to This.KeyFieldList noshow
		UNITID
		ENDTEXT
				***</KeyFieldList>
				***<UpdateNameList>
		TEXT to This.UpdateNameList noshow
		UNITID lbankunit.UNITID, UNITNUMBER lbankunit.UNITNUMBER, UNITNAME lbankunit.UNITNAME, TRANDATE lbankunit.TRANDATE, ACTIVE lbankunit.ACTIVE, USERID lbankunit.USERID
		ENDTEXT
				***</UpdateNameList>
				***<UpdatableFieldList>
		TEXT to This.UpdatableFieldList noshow
		UNITNUMBER, UNITNAME, TRANDATE, ACTIVE, USERID
		ENDTEXT
		***</UpdatableFieldList>
		*** End of Setup code: DO NOT REMOVE
		THIS.ALIAS = 'cUnit'
		THIS.CURSORFILL()
		IF THIS.__VFPSetup = 1
			THIS.__VFPSetup = 2
		ENDIF
		RETURN llReturn

	ENDPROC
	PROCEDURE AUTOOPEN
		*** Setup code: DO NOT REMOVE
		IF NOT pemstatus(THIS, '__VFPSetup', 5)
			THIS.ADDPROPERTY('__VFPSetup', 1)
			THIS.INIT()
		ENDIF
		*** End of Setup code: DO NOT REMOVE

	ENDPROC

ENDDEFINE

I'm sure it is something stupid I am forgetting. I appreciate any assistance you can provide.

Michael


Michael Brennan-White
New Hampshire Treasury Department

http://www.nh.gov/treasury

 

[Mike Lewis]

Michael,

Isn't there some system variable that MySQL will return to give you the most recent ID? I don't know MySQL well enough, but I know that in SQL Server, you would request @@INDENTITY.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com