Cannot insert an empty row from a view into its base table(s)

[MikeMV]

Hello,

I am working with VFP 9.0 accessing an MS SQL 2000 DB. I am getting the "Cannot insert an empty row from a view into its base table(s) (Error 1547)" error message that seems to be very familiar, but I have not been able to find any suggestions to work around it.

What I am basically trying to do is pick a stockcode off a local table, create a view, find out if there is a price record in the SQL table already, if so update the price, if not add a new record and populate with the necessary information. The code I am trying to work with is below, I would greatly appreciate any feedback you can provide.

* Program.: CF-Basic.PRG V1.0
* Author..: Miguel J. Campos
* Date....: 08/07/2007
* Notice..: Copyright (c) 2007, Miguel J. Campos
* Version.: Visual FoxPro Version 9.0
* Notes...: Update Price List for Basic Compact Fluorescent
* Revision: 08/07/2007
*
* Start Price B update
*
CLOSE ALL
CLEAR
*
SET DEFAULT TO P:\Projects\pricechg
DELETE FILE P:\Projects\pricechg\vkits.idx
*
OPEN DATABASE vinventory
SET DATABASE TO vinventory
DELETE VIEW vkits
*
CREATE SQL VIEW vkits;
REMOTE CONNECTION sysprocompanyu;
AS SELECT InvPrice.StockCode, InvMaster.Description, InvPrice.PriceCode, InvPrice.SellingPrice, InvPrice.CommissionCode, InvPrice.PriceBasis FROM InvPrice;
INNER JOIN InvMaster;
ON InvMaster.StockCode=InvPrice.StockCode;
WHERE InvPrice.PriceCode='B' AND LEFT(InvPrice.StockCode,1)='B';
ORDER BY InvPrice.StockCode
DBSETPROP ("vkits", "View", "SendUpdates", .T.)
DBSETPROP("vkits" , "VIEW","WhereType",3)
DBSETPROP("vkits.StockCode", "Field", "Updatable", .T.)
DBSETPROP("vkits.StockCode", "Field", "UpdateName", [InvPrice.StockCode])
DBSETPROP("vkits.SellingPrice", "Field", "Updatable", .T.)
DBSETPROP("vkits.SellingPrice", "Field", "UpdateName", [InvPrice.SellingPrice])
DBSETPROP("vkits.PriceCode", "Field", "Updatable", .T.)
DBSETPROP("vkits.PriceCode", "Field", "UpdateName", [InvPrice.PriceCode])
DBSETPROP("vkits.CommissionCode", "Field", "Updatable", .T.)
DBSETPROP("vkits.CommissionCode", "Field", "UpdateName", [InvPrice.CommissionCode])
DBSETPROP("vkits.PriceBasis", "Field", "Updatable", .T.)
DBSETPROP("vkits.PriceBasis", "Field", "UpdateName", [InvPrice.PriceBasis])
DBSETPROP("vkits.StockCode", "Field", "KeyField", .t.)
*
SELECT 1
USE cf-basic INDEX cf-basic
GO TOP
m.stockcode=ALLTRIM(stockcode)
m.sellingprice=price_b
m.pricecode="B"
m.commissioncode="3"
m.pricebasis="S"
*
SELECT 2
USE vkits
INDEX ON stockcode TO vkits

*
DO WHILE .NOT. EOF()
*
SELECT 2
SEEK m.stockcode
*
IF .NOT. FOUND()
INSERT INTO vkits (StockCode, PriceCode, SellingPrice, CommissionCode, PriceBasis) ;
VALUES (m.stockcode, m.pricecode, m.sellingprice, m.commissioncode, m.pricebasis)
=TABLEUPDATE(.T.)
ELSE
REPLACE SellingPrice WITH m.sellingprice FOR StockCode=m.stockcode
ENDIF
*
GO BOTTOM
*
IF NOT TABLEUPDATE(1, .t., [vkits])
AERROR(laError)
MessageBox([Can not Update vkits ]+laError[1,2])
EXIT
ELSE
*
SELECT 1
IF .NOT. EOF()
SKIP
m.stockcode=ALLTRIM(stockcode)
m.sellingprice=price_b
m.pricecode="B"
m.commissioncode="3"
m.pricebasis="S"
LOOP
ELSE
EXIT
ENDIF
ENDIF
*
ENDDO
*
CLOSE DATABASES ALL
*
* End Price B update*

RETURN
*
* REVISION HISTORY
*
* EOF: Test.PRG

 

[MikeMV]

Is there a way to edit a message after it is posted? I realized I posted useless program header stuff, but cannot see an option to edit.

Thanks.

 

[Mike Gagnon]

mcampos

No way to edit here.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[Olaf Doschke]

After the insert you do a Tableupdate, after a replace not, then you GO BOTTOM and in both cases do another Tableupdate.

I'd delete the tableupdate after the insert and delete the GO BOTTOM, then it would tableupdate the record you inserted or replaced the Sellingprice and handle error via AERROR.

Besides that you could replace the DO WHILE .NOT. EOF() ...ENDDO with Select 1 Scan ... Endscan, but let's concnetrate on the tableupdate issue first.

 

[MikeMV]

Thanks Olaf,

Removing the tableupdate and go bottom did not help. Any other idea?

I like the idea of switching to scan-endscan, once I get this cleared up I'll try it. I have been doing the do while for so long that I pay attention to the new better stuff.

 

[MikeMV]

Olaf,

Turns out that the InvMaster.Description was creating the problem, since I do not really need the description removing it from the view resolved the issue.

Thanks.