Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Pete Sass
  Where is Pete Sass?
 Marathon, Ontario
 Canada
 Pete Sass
 Tags
Subject: SQL Server Update Conflict
Thread ID: 372535 Message ID: 372535 # Views: 66 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Monday, March 11, 2013 12:56:31 AM         
   


Hi Foxitians,


Here is one I do not seem to be able to pin-point the issue with.


Below is code example to update SQL Server backend table being called in my form's LOAD event:

* --- Disable the SQL Server's auto login screen if the connection failed.
= SQLSETPROP(0, "DispLogin", 3)  

* --- SQL Server ADO string is working as well.
gnSQLConn = SQLStringConnect("DRIVER=SQL Server;SERVER=70.35.67.88,1433;UID=sa;PWD=xxxxxx;DATABASE=LicensingMgr")

* --- Verify if connection was successful or not.
IF gnSQLConn < 0
= MESSAGEBOX("Connection failed.  Possible reasons: You have entered in the incorrect IP ;
address or listening port.  You may not have entered in the correct database name your are trying ;
connect into.  The Internet could be down or the SQL Server maybe down!",0+16,"Status - Connection Failure!")
   * --- To release a form in it's own LOAD event Thisform.Release will not work, you must use RETURN .F.
   RETURN .F.
ENDIF


* --- /// THIS IS THE SELECTION PROCESS AND THE SETUP OF THE AUTOMATIC UPDATE VIEW FOR THE
* --- /// LICENSINGHEADER TABLE IN THE SQL-SERVER.

* --- Perform a SQL SPT selection command. 
lnSQLSuccess = SQLEXEC(gnSQLConn,"SELECT * FROM LICENSINGHEADER ORDER BY CLIENTID","curLicensingHeader")

* --- Verify if the SQL Selection statement was successful or not.
IF lnSQLSuccess < 0
   = MESSAGEBOX("Connection problem and SQL Select statement to the SQL Server LicensingHeader table failed!",0+16,"Status")

   * --- Disconnect from the SQL Server database.
   IF gnSQLConn > 0
	   = SQLDISCONNECT(0)
       * --- To release a form in it's own LOAD event Thisform.Release will not work, you must use RETURN .F.
       RETURN .F.
   ENDIF

ENDIF

* --- Set the local VFP cursor so it will automatically update the underlying SQL Server table.
= cursorsetprop("KeyFieldList","IDkey","curLicensingHeader")
= cursorsetprop("tables","LicensingHeader","curLicensingHeader")
* --- = cursorsetprop("Buffering",5,"curLicensingHeader") 
= cursorsetprop("SendUpdates",.T.)

LOCAL lcPrimKeyName, lnFields, lnCount, lnPos, llUpdateWorked, lnIdentity

* --- Make sure to set the Primary key as all upper case as the VFP array will create all
* --- field names in upper case values.
lcPrimKeyName = "IDKEY"

* --- Initialize the array with the field information out of the local cursor.
lnFields = AFIELDS(laFields,"curLicensingHeader")
lcUpdatableFieldList = []
lcUpdateNameList = []

* --- NOTE: If this is an Add or Edit operation we are going to delete the primary key out
* ---       of the laFields array completely so it is not processed as it is a SQL Server
* ---       IDkey Integer field that is autoincrementing with seed value of 1. 
lnPos = ASCAN(laFields,lcPrimKeyName)

IF lnPos != 0
   * --- IDkey found so remove it from the array.
   = ADEL(laFields,lnPos)
   lnFields = lnFields - 1
ENDIF

* --- Loop to process all the fields except the Primary key only during the Add process.
* --- The process below need the IDkey in the process if performing an edit or a delete operation.
FOR lnFor = 1 TO m.lnFields
    = MESSAGEBOX("Adding:" + laFields[lnFor,1])
    lcUpdatableFieldList = lcUpdatableFieldList + laFields[lnFor,1]+","
    lcUpdateNameList     = lcUpdateNameList     + laFields[lnFor,1] + " LicensingHeader."+laFields[lnFor,1]+","
NEXT

* --- Update the field listing and namelist.
lcUpdatableFieldList = LEFT(lcUpdatableFieldList, LEN(lcUpdatableFieldList) -1)
lcUpdateNameList     = LEFT(lcUpdateNameList, LEN(lcUpdateNameList)-1)
= cursorsetprop("UpdatableFieldList", m.lcUpdatableFieldList,"curLicensingHeader")
= cursorsetprop("UpdateNameList", m.lcUpdateNameList,"curLicensingHeader")

LOCAL lcPrimKeyName, lnFields, lnCount, lnPos, llUpdateWorked, lnIdentity

* --- Make sure to set the Primary key as all upper case as the VFP array will create all
* --- field names in upper case values.
lcPrimKeyName = "IDKEY"

* --- Initialize the array with the field information out of the local cursor.
lnFields = AFIELDS(laFields2,"curLicensingHeader")
lcUpdatableFieldList = []
lcUpdateNameList = []

* --- NOTE: If this is an Add operation we are going to delete the primary key out
* ---       of the laFields array completely so it is not processed. 
lnPos = ASCAN(laFields2,lcPrimKeyName)

IF lnPos != 0
   * --- IDkey found so remove it from the array.
   = ADEL(laFields2,lnPos)
   lnFields = lnFields - 1
ENDIF

* --- Set the local VFP cursor so it will automatically update the underlying SQL Server table.
= cursorsetprop("KeyFieldList","IDKEY","curLicensingHeader")
= cursorsetprop("tables","LicensingHeader","curLicensingHeader")
* --- = cursorsetprop("Buffering",5,"curLicensingHeader") 
* --- Must have the line below so local VFP cursor changes send the updates into the backend SQL Server table.
* --- Do you want to send and process automatic updates or do you want to issue an = TABLEUPDATE(.T.)
* --- In order for the automatic update to take place you must move the cursor record pointer
* --- of off your changed record.
= cursorsetprop("SendUpdates",.T.)

* --- Loop to process all the fields including the the Primary key so we know what record to delete.
FOR lnFor = 1 TO m.lnFields
    lcUpdatableFieldList = lcUpdatableFieldList + laFields2[lnFor,1]+","
    lcUpdateNameList     = lcUpdateNameList     + laFields2[lnFor,1] + " LicensingHeader."+laFields2[lnFor,1]+","
NEXT

* --- Update the field listing and namelist.
lcUpdatableFieldList = LEFT(lcUpdatableFieldList, LEN(lcUpdatableFieldList) -1)
lcUpdateNameList     = LEFT(lcUpdateNameList, LEN(lcUpdateNameList)-1)
= cursorsetprop("UpdatableFieldList", m.lcUpdatableFieldList,"curLicensingHeader")
= cursorsetprop("UpdateNameList", m.lcUpdateNameList,"curLicensingHeader")

* --- Validate that the TableUpdate was successful.
IF .NOT. TABLEUPDATE(.T.)
   = AERROR(ArrayError)
   = MESSAGEBOX("Error: " + ArrayError[2])
   = MESSAGEBOX("There was an error updating your systems data.  Any changes will not be reflected in your system!",0+16,"Update Error")
   = TABLEREVERT()
   Thisform.refreshheader
   Thisform.txtClientID.Setfocus
ENDIF



Now the insert and delete using the above code works 100% all the time. So I can add a record into
my local VFP cursor and update to the underlying SQL Server table. As mentioned, Delete works as well.


Now to my issue. When I try to update I get the following error message:


I error trapped using the AERROR()function above.

So I look at my line of code at the beginning and I have:
= cursorsetprop("KeyFieldList","IDkey","curLicensingHeader")


I clearly bove have set the KeyFieldList for my primary "IDkey". So where is this error
coming from???

Pete "the IceMan", from the Great White North of Canada.
www.marathongriffincomputers.com
Home of the Canadian and US download for Chen's VFP C++ Compiler
http://www.marathongriffincomputers.com/VFP-C++-Compiler.

COMPLETE THREAD

SQL Server Update Conflict Posted by Pete Sass @ 3/11/2013 12:56:31 AM
RE: SQL Server Update Conflict Posted by Cetin Basoz @ 3/11/2013 12:34:13 PM
RE: SQL Server Update Conflict Posted by Pete Sass @ 3/11/2013 2:51:09 PM
RE: SQL Server Update Conflict Posted by Cetin Basoz @ 3/11/2013 4:04:51 PM
RE: SQL Server Update Conflict Posted by Pete Sass @ 3/11/2013 11:45:04 PM