Data Grid as data Manipulation tool for SQl Server View

[srajeevkumar]

I am re posting in this forum also as I didnt have any repsonse in the other forum. I expect thw Whiz kids in here to help me please. Apologies for posting again.

Hello freinds,

Most of you would have come across similar situations many times before and would have solved it in your own style and preference. let me explain the situation.

I have the

(1)ItemRefsLookup Table :

CREATE TABLE [dbo].[ItemRefsLookUp] (
[ItemRef] [varchar] (25) NOT NULL ,
[ItemDesc] [varchar] (50) NULL ,
[ItemType] [varchar] (10) NULL )
(2)ItemCostings Table :

CREATE TABLE [dbo].[ItemCostings] (
[ItemCostingsID] uniqueidentifier ROWGUIDCOL NOT NULL , [ItemRef] [varchar] (25) NOT NULL ,
[SubsidiaryCode] [varchar] (3) NOT NULL ,
[QuotedPrice] [decimal](18, 0) NULL ,
[ProdCost] [decimal](18, 0) NULL ,
[SellPrice] [decimal](18, 0) NULL ,
[Commission] [decimal](18, 0) NULL ,
[CurrencyCode] [varchar] (3) NOT NULL )

I have a foreign key declared as

ALTER TABLE [dbo].[ItemCostings]
ADD CONSTRAINT [FK_ItemCostings_ItemRefsLookUp] FOREIGN KEY ([ItemRef]) REFERENCES [dbo].[ItemRefsLookUp]
([ItemRef]) ON DELETE CASCADE ON UPDATE CASCADE ,
GO


Now I need to design a data entry screen for these two tables using Visual Basic 6.0.

(1) I created a View as follows:

CREATE VIEW dbo.VIEWItemRef
AS
SELECT dbo.ItemRefsLookUp.ItemRef AS ItemRef, dbo.ItemRefsLookUp.ItemDesc, dbo.ItemRefsLookUp.ItemType, dbo.ItemCostings.QuotedPrice, dbo.ItemCostings.SellPrice, dbo.ItemCostings.Commission, dbo.ItemCostings.ProdCost, dbo.ItemCostings.SubsidiaryCode,dbo.ItemCostings.CurrencyCode, dbo.ItemCostings.ItemCostingsID FROM dbo.ItemRefsLookUp INNER JOIN dbo.ItemCostings
ON
dbo.ItemRefsLookUp.ItemRef = dbo.ItemCostings.ItemRef

(2)Created a VB form with a DataGrid control in it with a ADODC control. The View was used as the record source for the Grid and then the ADODC was used as the source for the grid.

(3)Brilliant it does display the data properly allows me delete any rows selected and also allows me to edit the rows whichever I selected to edit.

(4)But when i try to add a new row it comes up with errors asking me to check on the status of each OLEDB.

(5)I have read in this forum that to insert using Views we have to use an Instead Of Insert Trigger.

(6) I did create a trigger in the lines what some freinds suggested as follows:

CREATE TRIGGER UTrig_INS_ItemRef ON [dbo].[ViewItemRef]
INSTEAD OF INSERT
AS
BEGIN
Declare @SubsCode As Varchar(5)
Declare @CurrCode As Varchar(5)
SET NOCOUNT ON

-- Check for duplicate ItemRef in ItemrefsLookup table . If no duplicate, do an insert.

IF (NOT EXISTS (SELECT IR.ItemRef
FROM ItemRefsLookup IR, inserted I
WHERE IR.ItemRef = I.ItemRef))
INSERT INTO ItemRefsLookup
SELECT ItemRef , ItemDesc, Itemtype
FROM inserted

IF (NOT EXISTS (SELECT IC.ItemRef
FROM ItemCostings IC, inserted
WHERE IC.ItemRef = inserted.ItemRef))
INSERT INTO ItemCostings
SELECT ItemCostingsID, ItemRef ,
'100' As SubsidiaryCode,
QuotedPrice, ProdCost, SellPrice, Commission ,
'HKD' As CurrencyCode
FROM inserted
END

(7)This trigger works fine when I directly try to do an insert using the Query Analyser. But it still wont work via the data grid.

I am really stuck and would appreciate if someone can help please. If you have different approach to design this data entry please do advice. Its only very recently I started using Stored procedures and triggers etc. So please ber with my ignorance.