Update not working after read database using CASE

• mikek 50666

  Right there with Babe

  Points: 779

  More actions

  February 6, 2017 at 7:46 am

  #1926865

  Would datagridview in virtualmode be a possible solution?

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 6, 2017 at 8:54 am

  #1926891

  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.
  Larnu.uk

• January 25, 2018 at 3:29 am

  This was removed by the editor as SPAM