CursorAdaper Insert

[gaelin]

I'm testing a procedure to update a table on a SQL Server device. The code below what I've come up with to accomplishe the update. It does give an error but does not work, that is the SQL Server table does not display the added record. My first time playing with CursorAdapter...can anyone help?


lcConnString = "driver={SQL Server};server=ESCDB1;UID=;APP=Microsoft Visual FoxPro;DATABASE=FINCON;Trusted_Connection=Yes"

LOCAL loCursor AS CURSORADAPTER, laErrors[1]

loCursor = CREATEOBJECT("CursorAdapter")
WITH loCursor
.DATASOURCETYPE = "ODBC"
.DATASOURCE = SQLSTRINGCONNECT(lcConnString)
.AllowUpdate = .t.
.AllowInsert = .t.
.tables = "PASCODE"
.KeyFieldList = "pal, wing, unit, pas4, pas8m, parentpas, location"

.INSERTCMD = ;
"INSERT INTO PASCODE ( pal, wing, unit, pas4, pas8m, parentpas, location) VALUES ('V2', '38 EIG', 'F9XN', 'TE1MF9XN', 'FB59', 'Tinker')"

IF AERROR(laErrors) > 0
MESSAGEBOX(laErrors[2])
ENDIF
ENDWITH

 

[TamarGranor]

YOu also need to specify the UpdatableFieldList and UpdateNameList properties. In addition, SendUpdates must be set to .T.

Tamar

 

[gaelin]

As you suggested I added the updatablefieldlist and updatenamelist properties and set sendupdates to true. I also changed the keyfield to reflect the primary key field which is set to increment by 1.
It still does not error, but does not update. What am I missing?

-----

lcConnString = "driver={SQL Server};server=ESCDB1;UID=;APP=Microsoft Visual FoxPro;DATABASE=FINCON;Trusted_Connection=Yes"

LOCAL loCursor AS CURSORADAPTER, laErrors[1]
loCursor = CREATEOBJECT("CursorAdapter")
WITH loCursor
.DATASOURCETYPE = [ODBC]
.DATASOURCE = SQLSTRINGCONNECT(lcConnString)
.ALLOWUPDATE = .T.
.ALLOWINSERT = .T.
.SENDUPDATES = .T.
.TABLES = [PASCODE]

.KeyFieldList = [passcode_id]

.UPDATABLEFIELDLIST = [pal, wing, unit, pas4, pas8, parentpas, location]
.UPDATENAMELIST = [pal, wing, unit, pas4, pas8, parentpas, location]

.INSERTCMD = [insert into Pascode (pal, wing, unit, pas4, pas8, parentpas, location) ] ;
+ [values ] + [('V2', '38 EIG', 'F9XN', 'TE1MF9XN', 'FB59', 'Tinker')]

IF AERROR(laErrors) > 0
MESSAGEBOX(laErrors[2])
ENDIF
ENDWITH

 

[TamarGranor]

UpdateNameList has to pair the fields in the cursor with the fields in the original table. For example:

"IID AUTHOR.IID, CALPHA AUTHOR.CALPHA, CFULLNAME AUTHOR.CFULLNAME"

Tamar

 

[gaelin]

Tamar,

I discovered that error yesterday and made the correction. That did not resolve the problem. To ensure I didn't have an insertcmd error, I ran a selectcmd to extract records and then added a record to the cursor and called tableupdate with buffermodeoverride set to 5. No change...it does not work...yet no errors are recorded by cursoradapter.

I assumed cursoradapter would return an error if no update was made. This has me very concerned...how can you depend on cursoradapter?

 

[TamarGranor]

Is there a reason you're not using the CursorAdapter Builder to handle all this, so you don't have to worry about the exact settings?

Also, looking at your code a few messages up, I don't see you doing an actual update. Setting the InsertCmd property doesn't do an insert, it just specifies the command to run when Insert is requested. To update through a CursorAdapter, you need to issue TableUpdate on the cursor it creates.

oCA = CREATEOBJECT("MyCursorAdapter")
oCA.CursorFill()
* Make some changes
IF TABLEUPDATE(.T.)
  * the original table should be updated
ELSE
  * something went wrong
ENDIF

Tamar