VFP Problem Updating SQL DB 1

[MikeMV]

I am using Visual FoxPro 9.0 trying to create a program to update a field in a MS SQL 2000 DB from another field in another DB. The InvMaster DB contains inventory information with a field named AlternateKey1 used to store UPC codes. The UPCSTORE DB contains a list of codes to be used in the inventory DB. The idea is to insert an un-used code in the inventory DB for fields that are blank and then mark the used code in the UPCSTORE DB as used.

My problem is that no updates are actually happening, I know I am missing something with allowing VFP to update the SQL files, but I have not been able to figure it out.

I will greatly appreciate any input you can provide.

Note: For now I have the code setup to only update one record in each DB for testing, eventually once I work this problem out I will set it up with a DO WHILE loop to do a batch update.

* Program.: UPCFox.PRG V1.0
*
CLOSE ALL
SET DEFAULT TO C:\Data\UPCFOX\Dbfs
*
DELETE FILE C:\Data\UPCFOX\Dbfs\vupcstore*.*
DELETE FILE C:\Data\UPCFOX\Dbfs\vinventory*.*
*
CREATE DATABASE vupcstore
CREATE SQL VIEW vupcstore;
REMOTE CONNECTION LocalSyspro;
AS SELECT UPC, USED, DATEUSED FROM UPCSTORE
DBSETPROP ("vupcstore", "View", "SendUpdates", .T.)
DBSETPROP("vupcstore.used", "Field", "Updatable", .T.)
DBSETPROP("vupcstore.dateused", "Field", "Updatable", .T.)
DBSETPROP("vupcstore.used", "Field","KeyField",.T.)

SELECT 1
USE vupcstore
INDEX ON UPC TO UPCSTORE
SET FILTER TO USED <>'Y'
GO TOP
m.upc=upc
*
CREATE DATABASE vinventory
CREATE SQL VIEW vinventory;
REMOTE CONNECTION LocalSyspro;
AS select StockCode, Description, AlternateKey1 FROM InvMaster
DBSETPROP ("vinventory", "View", "SendUpdates", .T.)
DBSETPROP("vinventory.alternatekey1", "Field", "Updatable", .T.)
*
SELECT 2
*
USE vinventory
INDEX on StockCode TO vinventory
SET FILTER TO LEFT(AlternateKey1,2) <>"64"
GO TOP
m.stock=StockCode
*
REPLACE AlternateKey1 WITH m.upc FOR StockCode=m.stock
*
TABLEUPDATE()
*
SELECT 1
*
REPLACE used WITH "Y" FOR upc=m.upc
REPLACE dateused WITH DATE() FOR upc=m.upc
*
TABLEUPDATE()
*
CLOSE DATABASES
SET DEFAULT TO C:\Data\UPCFOX\Prgs
*
RETURN
*
* EOF: UPCFox.PRG

 

[bborissov]

Try:

* Program.: UPCFox.PRG V1.0
*
CLOSE ALL
SET DEFAULT TO C:\Data\UPCFOX\Dbfs
*
DELETE FILE C:\Data\UPCFOX\Dbfs\vupcstore*.*
DELETE FILE C:\Data\UPCFOX\Dbfs\vinventory*.*
*
CREATE DATABASE vupcstore
CREATE SQL VIEW vupcstore;
REMOTE CONNECTION LocalSyspro;
AS SELECT UPC, USED, DATEUSED FROM UPCSTORE WHERE Used <> 'Y'
DBSETPROP ("vupcstore", "View", "SendUpdates", .T.)
DBSETPROP("vupcstore.used", "Field", "Updatable", .T.)
DBSETPROP("vupcstore.dateused", "Field", "UpdateName", [UPCSTORE.dateused])
DBSETPROP("vupcstore.used", "Field","KeyField",.T.)

SELECT 0
USE vupcstore
INDEX ON UPC TO UPCSTORE
GO TOP
m.upc=upc
*
CREATE DATABASE vinventory
CREATE SQL VIEW vinventory;
REMOTE CONNECTION LocalSyspro;
AS select StockCode, Description, AlternateKey1 FROM InvMaster WHERE LEFT(AlternateKey1,2) <>"64"
DBSETPROP ("vinventory", "View", "SendUpdates", .T.)
DBSETPROP("vinventory.alternatekey1", "Field", "Updatable", .T.)
DBSETPROP("vinventory.alternatekey1", "Field", "UpdateName", [InvMaster.alternatekey1])

DBSETPROP("????????", "Field", "KeyField", .t.)

*
SELECT 0
USE vinventory
INDEX on StockCode TO vinventory
GO TOP
m.stock=StockCode
*
REPLACE AlternateKey1 WITH m.upc FOR StockCode=m.stock
*
IF NOT TABLEUPDATE(1, .t., [vinventory])
   AERROR(laError)
   MessageBox([Can not Update vinventory ]+laError[1,2])
ENDIF
*
SELECT vupcstore
REPLACE used     WITH "Y",;
        dateused WITH DATE() FOR upc=m.upc
*
IF NOT TABLEUPDATE(1, .t., [vupcstore])
   AERROR(laError)
   MessageBox([Can not Update vupcstore ]+laError[1,2])
ENDIF

*
CLOSE DATABASES
SET DEFAULT TO C:\Data\UPCFOX\Prgs
*
RETURN
*
* EOF: UPCFox.PRG

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[JimWWinter]

A couple of things that jump out are you haven't set a WhereType property and you haven't specified the primary keys for the tables.

DBSetProp('vupcstore', 'View', 'WhereType', 1)
* You do have one keyfield indicated, but, from your description its either 0 or 1 - thus not enough to identify the record to update
DBSETPROP("vupcstore.used", "Field","KeyField",.T.)

Regards,
Jim

 

[MikeMV]

Thanks for the quick reply Borislav,

When I run your code I get a connectivity error:

Invalid column name '64'

On the USE vinventory

"SELECT 0
USE vinventory"

If I change the line:

WHERE LEFT(AlternateKey1,2) <>"64"

to '64' I get past that error, but then I get another connectivity error of Invalid Column name 'alternatekey1'

On:

REPLACE AlternateKey1 WITH m.upc FOR StockCode=m.stock

Any ideas?

I also replaced your:

DBSETPROP("????????", "Field", "KeyField", .t.)

With:

DBSETPROP "vinventory.alternatekey1", "Field", "KeyField", .T.)

Is this what you inteded, to look for which should be the key field in vinventory?

Thanks again,

Mike.

 

[bborissov]

* Program.: UPCFox.PRG V1.0
*
CLOSE ALL
SET DEFAULT TO C:\Data\UPCFOX\Dbfs
*
DELETE FILE C:\Data\UPCFOX\Dbfs\vupcstore*.*
DELETE FILE C:\Data\UPCFOX\Dbfs\vinventory*.*
*
CREATE DATABASE Test
CREATE SQL VIEW vupcstore;
REMOTE CONNECTION LocalSyspro;
AS SELECT UPC, USED, DATEUSED FROM UPCSTORE WHERE Used <> 'Y' ORDER BY Upc
DBSETPROP("vupcstore"         , "View", "SendUpdates", .T.)
DBSETPROP("vupcstore"         , "VIEW","WhereType",3)
DBSETPROP("vupcstore.used"    , "Field", "Updatable", .T.)
DBSETPROP("vupcstore.dateused", "Field", "UpdateName", [UPCSTORE.dateused])
DBSETPROP("vupcstore.used"    , "Field","KeyField",.T.)

SELECT 0
USE vupcstore
GO TOP
m.upc=upc

*
CREATE SQL VIEW vinventory;
REMOTE CONNECTION LocalSyspro;
AS select StockCode, Description, AlternateKey1 FROM InvMaster WHERE LEFT(AlternateKey1,2) <>'64' ORDER BY StockCode
DBSETPROP ("vinventory", "View", "SendUpdates", .T.)
DBSETPROP("vinventory" , "VIEW","WhereType",3)
DBSETPROP("vinventory.alternatekey1", "Field", "Updatable", .T.)
DBSETPROP("vinventory.alternatekey1", "Field", "UpdateName", [InvMaster.alternatekey1])
DBSETPROP("vinventory.alternatekey1", "Field", "KeyField", .t.)
DBSETPROP("vinventory.alternatekey1", "Field", "KeyField", .t.)

*
SELECT 0
USE vinventory
BROWSE NORMAL
*** Did you see AlternateKey1 key here?
GO TOP

m.stock=StockCode
*
REPLACE AlternateKey1 WITH m.upc FOR StockCode=m.stock
*
IF NOT TABLEUPDATE(1, .t., [vinventory])
   AERROR(laError)
   MessageBox([Can not Update vinventory ]+laError[1,2])
ENDIF
*
SELECT vupcstore
REPLACE used     WITH "Y",;
        dateused WITH DATE() FOR upc=m.upc
*
IF NOT TABLEUPDATE(1, .t., [vupcstore])
   AERROR(laError)
   MessageBox([Can not Update vupcstore ]+laError[1,2])
ENDIF

*
CLOSE DATABASES ALL
SET DEFAULT TO C:\Data\UPCFOX\Prgs
*
RETURN
*
* EOF: UPCFox.PRG

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[MikeMV]

Thanks again for your help Borislav

SELECT 0
USE vinventory
BROWSE NORMAL
*** Did you see AlternateKey1 key here?

Yes, I do see the AlternateKey1 in the browse window, that part is working fine. I do have 2 problems left which I am having trouble finding a work around:

"AS SELECT UPC, USED, DATEUSED FROM UPCSTORE WHERE USED <> 'Y' ORDER BY UPC"

The WHERE USED <> 'Y' is not working, even though there are 93k+ records with NULL in the field. I tried checking for NULL but that did not work, but I am not sure if I am using the correct syntax from within FoxPro.

If I remove the WHERE clause and do

AS SELECT UPC, USED, DATEUSED FROM UPCSTORE ORDER BY UPC
m.upc=upc
@10,10 SAY m.upc

Then it shows me that it picked up a record from the DB and the REPLACE of AlternateKey1 works fine, the selected UPC code gets inserted into the field.

The second problem I am having is that when the program goes to:

SELECT vupcstore
REPLACE used WITH "Y",;
dateused WITH DATE() FOR upc=m.upc

"I get a Connection LocalSyspro is Busy" error.

Would setting up a shared named connection resolve this problem?

Thanks again,

Mike.

 

[TamarGranor]

You can't compare Nulls with the regular operators. You have to use the ISNULL() function:

WHERE ISNULL(USED) OR USED<>"Y"

BTW, Used is not a good choice for a field name, since it's a reserved word.

Tamar

 

[MikeMV]

Thanks a lot Tamar,

I'll work on the ISNULL selection on Monday and I fully agree, the choice of USED is not a good one, this started out as a simple informational DB and it has turned into a production issue.

BTW: Are you Tamar Granor? Your name rings a bell from back in the days of Compuserve.

 

[TamarGranor]

Yep, that's me.

Tamar

 

[bborissov]

I put all your orders and filters to be in SELECT becuase that way you get a smaller resultset (faster) IF you have NULLs in that fields you must use:

AS SELECT UPC, USED, DATEUSED FROM UPCSTORE WHERE ISNULL(Used,'N') <> 'Y' ORDER BY Upc

and

AS select StockCode, Description, AlternateKey1 FROM InvMaster WHERE ISNULL(LEFT(AlternateKey1,2).'77) <>'64' ORDER BY StockCode

Remember ISNULL() function in SQL Server is not the same with one in VFP.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[MikeMV]

Thanks Borislav,

"AS select StockCode, Description, AlternateKey1 FROM InvMaster WHERE ISNULL(LEFT(AlternateKey1,2).'77) <>'64' ORDER BY StockCode"

This change creates a "Connection Internal Consistency Error" I tried putting a second quote around ('77') and putting a comma in front of .77 (,'77') but neither help. On the other hand for the AlternateKey1 all I care is that is does not start with 64 which means that part number already has a UPC code assigned, anything else, empty fields, NULLs, etc. are fair game for assigning a UPC code. Would the old LEFT(AlternateKey1,2) <> "64" by itself create a problem?

I am still getting the busy connection error on the second update of the UPCSTORE DB. Any ideas? Maybe I should start a separate thread with this?

Thanks again for all your excellent help and also the input of others.

Mike.