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

`80040e21` is the dreaded `DB_E_ERRORSOCCURRED`, which means "something went wrong, figure it out for yourself". Updating a view with an `INSTEAD OF` trigger using the location of a server-side dynamic cursor *is* possible (I tried it in T-SQL), and that *should* be what ADO uses. To know for sure, try tracing the statement with SQL Profiler to see what it's *really* doing. Include "error" events for maximum helpfulness. If your recordset is client-side or disconnected, the problem will likely be that ADO "knows" you can't update computed columns, but the basic scenario should be possible. — Jeroen Mostert, Dec 12 '16 at 15:54
I'll have a look tommorrow to look into your suggestion but i think the problem is client side. I don't expect a roundtrip to the server already a the point where the assignment is taking place. Very helpfull comment thank you. — Philip Stuyck, Dec 12 '16 at 16:20
If the problem is computer columns, a potential but possibly very resource-costly workaround is to copy it into a new recordset that does not have the columns set to computed, which will convince ADO to allow updates -- as long as they make it to SQL Server, the trigger should take care of the rest. However, at that point you are down to modifying code (even if it's just to wrap the recordset) and it's worth considering if things shouldn't be rewritten anyway. (I also have no idea if this actually works. Been a few years since I had to work with ADO. :-)) — Jeroen Mostert, Dec 12 '16 at 16:29

score 0 · Answer 1 · answered Dec 12 '16 at 20:02

0

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.

answered Dec 12 '16 at 20:02

Wes H

4,186
2
13
24

I explained i used instead of triggers to make a view updatable. These triggers handle the same thing but reverse as what the view is doing. – Philip Stuyck Dec 12 '16 at 21:48
Just so I am clear, you're using an instead of trigger on the view. When a statement attempts to update the view, the trigger is taking over and running an update statement instead. – Wes H Dec 13 '16 at 20:03
If my statement above is correct, can you post the definition of your trigger? – Wes H Dec 13 '16 at 20:03
done, both insert and update trigger translate the space into a null, the view translates the space into a null. For width, length and the like, if stored in a string, if there is no value, (0 is a value) an empty string looks acceptable. Well, not for me, because storing numbers into strings is not my first choice ;-) – Philip Stuyck Dec 14 '16 at 22:11

updatable view gives runtime error in visual basic

1 Answers1