Update not working after read database using CASE

  • mikek 50666

    SSChasing Mays

    Points: 639

    Would datagridview in virtualmode be a possible solution?

  • Thom A

    SSC Guru

    Points: 98214

    It's been quite some time since I've actually used VB, datagrids in virtualmode isn't something I've used so i wouldn't like to hazard a guess.

    I did wonder, instead, is it possible you can change the location that the data is coming from to a view, if the User is American? If so, I think I might have a solution using triggers instead. This is simplified version, but it should get you on the path is it can work for you. The US users would need to see the View instead, and then SQL handles the INSERTS/UPDATES/DELETES on that view using triggers. Now, I am more than happy for people to shoot me down on this, just trying to come up for a solution for your needs, so if anyone sees problems with it please nicely put me in my corner.

    USE DevTestDB;
    GO
    -- Create a simple Material Table
    CREATE TABLE Material (ID INT IDENTITY(1,1),
                           [Description] VARCHAR(30),
                           Diameter DECIMAL(8,2),
                           Thickness DECIMAL(8,2));
    GO

    --Insert some sample data
    INSERT INTO Material (Description, Diameter, Thickness)
    VALUES ('Plank', 50, 20),
           ('Shelf', 150, 30);
    GO

    --Create a View for Americans
    CREATE VIEW Material_US AS
    SELECT M.ID,
           M.Description,
           M.Diameter / 25.4 AS Diameter,
           M.Thickness / 25.4 AS Thickness
    FROM Material M;
    GO

    --Non-US Users sample data
    SELECT *
    FROM Material;
    GO

    --US Users sample data
    SELECT *
    FROM Material_US
    GO

    --Create an INSTEAD OF INSERT trigger on the view, as it has a derived field
    CREATE TRIGGER Material_US_Insert
                ON dbo.Material_US
                INSTEAD OF INSERT
    AS
    BEGIN
     SET NOCOUNT ON;

        INSERT INTO dbo.Material (Description, Diameter, Thickness)
        SELECT i.Description,
               i.Diameter * 25.4,
               i.thickness * 25.4
        FROM inserted i

    END
    GO

    --Create an INSTEAD OF UPDATE trigger on the view, as it has a derived field
    CREATE TRIGGER Material_US_Update
                ON dbo.Material_US
                INSTEAD OF UPDATE
    AS
    BEGIN
     SET NOCOUNT ON;

        UPDATE dbo.Material
        SET Description = i.Description,
            Diameter = i.Diameter * 25.4,
            Thickness = i.Thickness * 25.4
        FROM dbo.Material M
             JOIN inserted i ON M.ID = i.ID;

    END
    GO

    --Insert into US view
    INSERT INTO Material_US (Description, Diameter, Thickness)
    VALUES ('TableTop', 48, 72),
           ('Bookshelf', 12, 12);
    GO

    --Update a row in the US View
    UPDATE Material_US
    SET Thickness = 1,
        Diameter = 6
    WHERE ID = 2;
    GO

    --Delete a row in the US View
    DELETE
    FROM Material_US
    WHERE ID = 1;
    GO

    --Non-US users new data
    SELECT *
    FROM Material;

    --US users new data
    SELECT *
    FROM Material_US;
    GO

    --Clean up
    DROP VIEW Material_US;
    DROP TABLE Material;
    GO

    So you'd need to have the datagrid use the Material_US View if they are American, and the Material Table if they are not American. Are you able to put some logic in your VB to define that the data grid source be different depending on your user (again, such as using their language)?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • This was removed by the editor as SPAM

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply