updatable view gives runtime error in visual basic

Question

I am trying to convert an old visual basic program which is using legacy database tables into a more modern and robust implementation. It uses a lot of varchars when it should have used floats to give a concrete example, it makes the code very hard to read as a lot of conversions from float to string and reverse are needed, which I want to avoid in the new application)

To preserve downward compatibility i create updatable views on my new tables so that the old application in vb can still work or at least that is the intention.

I have a view like follows :

SELECT        
ArtikelNummer, 
CataloogID, 
Artikel, 
isnull(CONVERT(nvarchar(5), breedte),'') Breedte,
isnull(CONVERT(nvarchar(5), Hoogte),'') Hoogte,
isnull(CONVERT(nvarchar(5), Diepte),'') Diepte,
isnull(CONVERT(nvarchar(5), Aantal),'') Aantal,
FROM            
Master.Orders_Catalogen_Artikels

and then i have created instead of insert and instead of update triggers on this view to make it updatable and manually tested these triggers to validate that they are ok, and they work.

However when as a final test I try to run the VB6 program it fails to do any inserts even before actually doing the insert. The code fails in VB itself in the OLE stuff:

With MyDE.rsSelectedArtikel
.Fields("CataloogID").Value = Orders.cCataloogID
.Fields("Artikel").Value = Orders.cbArtikel
.Fields("Aantal").Value = Orders.cAantal  ---> fails here

The query behind the rsSelectedArtikel is a simple select * from Orders_Catalogen_artikels which is the name of the view I showed above.

¨The runtime error I get is -2147217887(8004e21) usually that would identify a wrong type usage or something like that. But the Aantal is correctly identified as a nvarchar(5) when I lookup the columns of the view.

Is there a fix or workaround for this kind of problem ?

I could of course keep using the old table definition using strings for lengths and widths and the like, and create an updateble view for the new application, but that seems for me the reverse of the right thing to do.

Or i slightly modify the VB application, i have the sources, and first modify the datamodel to use floats for widths, heights and lengths but my preference is to leave it untouched.

I would have hoped that updatable views were the answer for this kind of problem.

UPDATE 1 : adding update trigger and insert trigger

ALTER TRIGGER [dbo].[V_Orders_Catalogen_Artikels_Update] ON  [dbo].[Orders_Catalogen_Artikels] 
INSTEAD OF UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
UPDATE [Master].[Orders_Catalogen_Artikels]
SET [CataloogID] = inserted.CataloogID
   ,[Artikel] = inserted.Artikel
   ,[Breedte] = iif(inserted.Breedte = '',null,inserted.Breedte)
   ,[Hoogte] = iif(inserted.Hoogte = '',null,inserted.Hoogte)
   ,[Diepte] = iif(inserted.Diepte = '',null,inserted.Diepte)
   ,[Aantal] = iif(inserted.Aantal = '',null,inserted.Aantal)
   ,[OmschrijvingNL] = inserted.OmschrijvingNL
   ,[Positie] = inserted.Positie
   ,[EenheidsPrijs] = inserted.EenheidsPrijs
   ,[Opmerking] = inserted.Opmerking
   ,[PosNr] = inserted.PosNr
   ,[Binnenkleur] = inserted.Binnenkleur
   ,[BKleurFront] = inserted.BKleurFront
   ,[Frontkantdikte] = inserted.Frontkantdikte
   ,[Poothoogte] = inserted.Poothoogte
   ,[BTWcode] = inserted.BTWcode
   ,[Korpuskantdikte] = inserted.Korpuskantdikte
   ,[ManuelePrijs] = inserted.ManuelePrijs
   ,[OpmerkingFr] = inserted.OpmerkingFr
   ,[OmschrijvingFr] = inserted.OmschrijvingFr
   ,[ArtikelGroepID] = inserted.ArtikelGroepID
   ,[ArtikelID] = inserted.ArtikelID
   ,[VolgNr] = inserted.VolgNr
   ,[Klaar] = inserted.Klaar
   ,[ScanDatum] = inserted.ScanDatum
   ,[ScanOpm] = inserted.ScanDatum
   ,[OpZaaglijst] = inserted.OpZaaglijst
FROM inserted
WHERE [Master].[Orders_Catalogen_Artikels].ArtikelNummer = inserted.ArtikelNummer

END

ALTER TRIGGER [dbo].[V_Orders_Catalogen_Artikels_Insert] ON  [dbo].[Orders_Catalogen_Artikels] 
INSTEAD OF INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
INSERT INTO [Master].[Orders_Catalogen_Artikels]
       ([CataloogID]
       ,[Artikel]
       ,[Breedte]
       ,[Hoogte]
       ,[Diepte]
       ,[Aantal]
       ,[OmschrijvingNL]
       ,[Positie]
       ,[EenheidsPrijs]
       ,[Opmerking]
       ,[PosNr]
       ,[Binnenkleur]
       ,[BKleurFront]
       ,[Frontkantdikte]
       ,[Poothoogte]
       ,[BTWcode]
       ,[Korpuskantdikte]
       ,[ManuelePrijs]
       ,[OpmerkingFr]
       ,[OmschrijvingFr]
       ,[ArtikelGroepID]
       ,[ArtikelID]
       ,[VolgNr]
       ,[Klaar]
       ,[ScanDatum]
       ,[ScanOpm]
       ,[OpZaaglijst])
       SELECT 
  [CataloogID]
  ,[Artikel]
  ,iif(ISNUMERIC(breedte+'e0')=1,convert(float,[Breedte]),null) as newbreedte
  ,iif(ISNUMERIC([Hoogte]+'e0')=1,convert(float,[Hoogte]),null) as newhoogte
  ,iif(ISNUMERIC([Diepte]+'e0')=1,convert(float,[Diepte]),null) as newdiepte
  ,iif(ISNUMERIC([Aantal]+'e0')=1,convert(float,[Aantal]),null) as newaantal
  ,[OmschrijvingNL]
  ,[Positie]
  ,[EenheidsPrijs]
  ,[Opmerking]
  ,[PosNr]
  ,[Binnenkleur]
  ,[BKleurFront]
  ,[Frontkantdikte]
  ,[Poothoogte]
  ,[BTWcode]
  ,[Korpuskantdikte]
  ,[ManuelePrijs]
  ,[OpmerkingFr]
  ,[OmschrijvingFr]
  ,[ArtikelGroepID]
  ,[ArtikelID]
  ,[VolgNr]
  ,[Klaar]
  ,[ScanDatum]
  ,[ScanOpm]
  ,[OpZaaglijst]
FROM inserted

END

Answer 1

An "updatable" view is a misnomer. The view doesn't store anything. The aliased columns return a modified look at the data in the table.

In your example, you have two possible code paths for a single value (null or not null). You are trying to send code back down one of the code paths to arrive at a base value. If, for example, you had sent '' down the path, how would SQL know if that should be a literal '' or a null value that was replaced.

For this reason, you cannot update any columns that have an expression. The expression is effectively read-only in the view.