Problem Updating SQL server via remote view/odbc

[rlw1839]

I have a visual foxpro 6.0 application that contains a database that has a local table and several remote views with odbc connections with an sql server database via the internet.

The application allows the user to download new leads from the sql server and maintain them in the local table. The user can also add records to the local table. Periodically the user will upload changes/additions to the sql server.

I've experience a problem in the upload process. The process moves as follows (detailed code below):

1. create a cursor of leads that have been changed and need to be updated (C_UPDATE)

2. open the remote view GLEADS_VW

3. SCAN the cursor C_UPDATE

4. Locate the appropriate record in the remote view by customer #

5. if found execute a method/procedure that stuffs the remote view's record with new info (replace commands)

6. if not found; append blank and call the 'stuff' procedure.

The problem occurs when the attempt is made to update the first record; ie. between step 4 & 5. It appears that even though the appropriate record has been found in the remote view the remote view is 'NOT READY' to be updated; i.e. a
error message is pitched 'connection is busy' which effectively crashes the process. By trial & error I discovered that putting a 6 second delay in the code before the 'stuffing procedure' executes for the FIRST UPDATE and a smaller .25 second delay before subsequent updates eliminates the crashing related to 'connection busy'.

I'd like to understand WHY the connection is busy in this situation and if there is another piece of SYSTEM INFO. that I could tap/trap that would tell me it's ready for an update. I'm uncomfortable with the trial & error bandaid!!!


DETAILED CODE
*****************************************************************
* do update of acg leads (customer) table via remote view
* gleads_vw
*****************************************************************
SELECT * , recno() as recno ;
FROM gleads ;
WHERE DOUPDATE ;
INTO CURSOR C_UPDATE

IF _TALLY > 0
SELECT 0

wait window "Opening Leads On The Server..." nowait
USE amgazebo!GLEADS_VW
wait window "Leads Opened." timeout 2.0

SELECT C_UPDATE
ln_ucnt = 1
*
* do the ones with a customer # first; rlw fix 11/08/02
*
SCAN for CUSTNO <> 0

SELECT GLEADS_VW
WAIT WINDOW &quot;Updating Lead # &quot; + STR(C_UPDATE.CUSTNO) timeout 1
LOCATE FOR CUSTOMER_ID = C_UPDATE.CUSTNO
IF FOUND()
if ln_ucnt = 1
*
* a delay is necessary before the first replace or a
* message that connection is busy is pitched
*
ln_sec = seconds()
do while seconds() < ln_sec + 5
enddo
wait window &quot;Updating Item: &quot; + str(ln_ucnt) timeout 6
else
if mod(ln_ucnt,5) = 0
wait window &quot;Updating Item: &quot; + str(ln_ucnt) timeout .25
endif
endif

thisformset.stuffremote()

ELSE
*
* 11/08/02; rlw; adding is a problem; california crashed in the second of two items that had been
* added by the user which of course had customer_id = 0
*
APPEND BLANK
THISFORMSET.stuffremote()

ENDIF
ln_ucnt = ln_ucnt + 1
ENDSCAN
*
* RLW; 10/08/02; update new items with no customer #;
*
SELECT C_UPDATE
SCAN FOR CUSTNO = 0
SELECT GLEADS_VW
*
* rlw; 03/21/03; this pitched a connection busy error because
* all the records in the local table were entered
* locally so this code executed right away;
* put some delays in it before stuffing; not sure
* if all the delays are necessary but it works now.
*
wait window &quot;Inserting New Lead In Host Database&quot; timeout 2.0
INSERT INTO GLEADS_VW (FIRST_NAME) VALUES (C_UPDATE.NETSOURCE)
wait window &quot;Inserting New Lead In Host Database&quot; timeout 2.0
USE amgazebo!GLEADS_VW
wait window &quot;Re-opening Leads On Host Server.&quot; timeout 2
LOCATE FOR ALLTRIM(FIRST_NAME) = ALLTRIM(C_UPDATE.NETSOURCE)
IF FOUND()
SELECT GLEADS
GOTO C_UPDATE.RECNO
IF FOUND()
REPLACE GLEADS.CUSTNO WITH GLEADS_VW.CUSTOMER_ID

SELECT GLEADS_VW
WAIT WINDOW &quot;Updating Lead # &quot; + STR(GLEADS.CUSTNO) timeout 2
thisformset.stuffremote()
Wait window &quot;Lead # &quot; + str(gleads.custno) + &quot; Updated. &quot; timeout 2
ENDIF
ENDIF
ENDSCAN
*
* RLW; 10/08/02; update for deleted items
*
SELECT GLEADS
SET DELETED OFF
SCAN FOR DELETED() AND CUSTNO <> 0
SELECT GLEADS_VW
LOCATE FOR CUSTOMER_ID = GLEADS.CUSTNO
IF FOUND()
WAIT WINDOW &quot;Deleting Lead # &quot; + STR(GLEADS.CUSTNO) nowait
REPLACE STATUS WITH 5 ,;
PRIORITY WITH &quot;Deleted&quot;
ENDIF
ENDSCAN

SET DELETED ON

SELECT gleads_VW
** any command that moves the pointer updates remote table
** don't need tableupdate !!!!
** locate above moved each record; put in a go bottom in
** case the last record was not yet updated
** LL_UPDATE = TABLEUPDATE(.T.)
GO BOTTOM
USE
*
* remove the update flag from orders
*
SELECT gleads
REPLACE DOUPDATE WITH .F. FOR DOUPDATE

=TABLEUPDATE(.T.)


ENDIF
SELECT C_UPDATE
USE
**********************************************




*********************************************
* STUFFREMOTE METHOD
*********************************************
*
* 03/03/03; rlw; status can't be stepped backward; this prevents
* a sales rep from downloading new leads, then updating them
* after they have already moved to status 2 thus trashing
* the changes Lori has made. Only the same status or a higher
* one will actually be updated.
*
IF ISNULL(STATUS) OR STATUS <= C_UPDATE.STATUS
REPLACE status WITH C_UPDATE.status
REPLACE date WITH C_UPDATE.datentered
REPLACE first_name WITH C_UPDATE.namefirst
REPLACE last_name WITH C_UPDATE.namelast
REPLACE company WITH C_UPDATE.company
REPLACE address WITH C_UPDATE.ad1
REPLACE city WITH C_UPDATE.city
REPLACE state_us WITH C_UPDATE.state
REPLACE zip_us WITH C_UPDATE.zip
REPLACE country WITH C_UPDATE.country
REPLACE prov WITH C_UPDATE.providence
REPLACE prov_code WITH C_UPDATE.prov_code
REPLACE home_phone WITH C_UPDATE.hphone
REPLACE work_phone WITH C_UPDATE.wphone
REPLACE extention WITH C_UPDATE.ext
REPLACE fax WITH C_UPDATE.fax
REPLACE email WITH C_UPDATE.email
REPLACE call_when WITH C_UPDATE.call_when
REPLACE call_time WITH C_UPDATE.calltime
REPLACE gaz_when WITH C_UPDATE.timeframe
REPLACE gaz_size WITH C_UPDATE.size
*
* work around for 'bad record?' customer_id 28014
* odbc error trying to update memo > 254 chars ;rlw; 10/08/02
*
IF gleads_vw.COMMENTS <> C_UPDATE.COMMENTS
IF LEN(ALLTRIM(COMMENTS)) <= 254 AND LEN(ALLTRIM(C_UPDATE.COMMENTS)) <= 254
REPLACE comments WITH C_UPDATE.comments
ENDIF
ENDIF

REPLACE info_from WITH C_UPDATE.leadsource
REPLACE pagename WITH C_UPDATE.pagefrom
REPLACE video WITH C_UPDATE.video
REPLACE teaser WITH C_UPDATE.teaser
REPLACE listname WITH C_UPDATE.listname
REPLACE callback WITH C_UPDATE.callback
REPLACE salesrep WITH C_UPDATE.salesrep
REPLACE priority WITH C_UPDATE.priority
REPLACE woodvinyl WITH C_UPDATE.woodvinyl
REPLACE netsource WITH C_UPDATE.netsource
ENDIF
*********************************************