| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • Work with all your cloud files (Drive, Dropbox, and Slack and Gmail attachments) and documents (Google Docs, Sheets, and Notion) in one place. Try Dokkio (from the makers of PBworks) for free. Now available on the web, Mac, Windows, and as a Chrome extension!

View
 

COP 4834 Summer 2012 Assignment 4 - Forms that Update

Page history last edited by Dr. Ron Eaglin 9 years, 5 months ago

 Assignment 4 - Forms that Update

 

Objectives

 

Create Forms that Update Data

 

Assignment

 

We will take the form from previous Assignment 3 and now modify it to be able to Update and also know when to update and when to save data.

 

Here are the tasks - you will be modifying the existing form from Assignment 3.

 

1. Add an Update button to the page.

2. When updating - pass the TraumaFormID to the page either through the session or Request variable. If this value is set then you will be using Update, if not you will be submitting new information.

3. Users should only have the ability to Submit NEW information.

4. Users should only have the ability to Update EXISTING information.

5. When in Update Mode the existing values of the data should be in the correct fields.

6. Updates will be done through a separate Update stored procedure (given below)

 

Here is the modification to the stored procedure sp_CreateForm that allows for the return of the TraumaFormID upon insertion. The video Lecture - How To - Get an Insert Identity from a SqlDataSource demonstrates how to get this value for future use.

 

ALTER PROCEDURE [dbo].[sp_CreateForm] (
  @PatientName VARCHAR(200) = 'John Doe',
  @TimeOfArrival VARCHAR(30) = NULL,
  @Age VARCHAR(10) = NULL,
  @DateOfArrival VARCHAR(50) = NULL,
  @Allergies VARCHAR(200) = NULL,
  @Meds VARCHAR(200) = NULL,
  @ModeOfArrival INT = NULL,
  @EvacUnit VARCHAR(50) = NULL,
  @PMH VARCHAR(200) = NULL,
  @TraumaFormID INT OUTPUT)
AS
BEGIN
  
INSERT INTO TraumaForm
 (name, Arrival)
VALUES
 (@PatientName, GETDATE())  

SET @TraumaFormID = @@IDENTITY

-- sp_InsertMedicaldata
-- Arg 1 - The Form ID
-- Arg 2 - The XML String identifying the data.
-- Arg 3 - CodeID if this is data from a code (drop down)
-- Arg 4 - ValueText if this is User Entered Data

EXEC sp_InsertMedicalData @TraumaFormID, 'TimeOfArrival', NULL, @TimeOfArrival
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientCurrentAge', NULL, @Age
EXEC sp_InsertMedicalData @TraumaFormID, 'DateOfArrival', NULL, @DateOfArrival
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientReportedAllergies', NULL, @Allergies
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientReportedMedications', NULL, @Meds
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientModeOfArrival', @ModeOfArrival, NULL
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientTransportEvacUnit', NULL, @EvacUnit
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientPreviousMedicalHistory', NULL, @PMH

END  

GO

 

 

In addition to being able to retrieve this ID - we also need a couple of views to retrieve data. I've split the data retrieval into 2 views

 

CREATE VIEW [dbo].[view_CodedMedicalData]
AS
SELECT    tf.id                AS 'id',
        tf.name                AS 'Patient',
        nr.NameText            AS 'XMLName', 
        cat.id                 AS 'CategoryID',
        cat.DisplayText        AS 'Category',
        md.ValueText           AS 'Value',
        c.id                   AS 'CodeID',
        c.DisplayText          AS 'Code',
        tf.Arrival             AS 'Date'
       
FROM    TraumaForm tf, 
        MedicalData md,        
        Codes c,
        Categories cat,
        NameResolution nr
        
WHERE    md.TraumaFormID = tf.id        
        AND cat.id = md.CategoryID         
        AND c.id = md.CodeID
        AND nr.CategoryID = md.CategoryID
GO 
CREATE VIEW [dbo].[view_TextMedicalData]
AS
SELECT    tf.id                AS 'id',
        tf.name                AS 'Patient',
        nr.NameText            AS 'XMLName', 
        cat.id                 AS 'CategoryID',
        cat.DisplayText        AS 'Category',
        md.ValueText           AS 'Value',
        tf.Arrival             AS 'Date'
       
FROM    TraumaForm tf, 
        MedicalData md,        
        Categories cat,
        NameResolution nr
        
WHERE    md.TraumaFormID = tf.id                
        AND cat.id = md.CategoryID         
        AND nr.CategoryID = md.CategoryID
        
GO 

 

Here is the Stored Procedure to do the Update
CREATE PROCEDURE [dbo].[sp_UpdateForm] (
  @TraumaFormID INT,
  @PatientName VARCHAR(200) = NULL,
  @TimeOfArrival VARCHAR(30) = NULL,
  @Age VARCHAR(10) = NULL,
  @DateOfArrival VARCHAR(50) = NULL,
  @Allergies VARCHAR(200) = NULL,
  @Meds VARCHAR(200) = NULL,
  @ModeOfArrival INT = NULL,
  @EvacUnit VARCHAR(50) = NULL,
  @PMH VARCHAR(200) = NULL)
AS
BEGIN
 
UPDATE TraumaForm
 SET name = @PatientName
 WHERE id = @TraumaFormID     
-- sp_InsertMedicaldata
-- Arg 1 - The Form ID
-- Arg 2 - The XML String identifying the data.
-- Arg 3 - CodeID if this is data from a code (drop down)
-- Arg 4 - ValueText if this is User Entered Data
EXEC sp_InsertMedicalData @TraumaFormID, 'TimeOfArrival', NULL, @TimeOfArrival
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientCurrentAge', NULL, @Age
EXEC sp_InsertMedicalData @TraumaFormID, 'DateOfArrival', NULL, @DateOfArrival
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientReportedAllergies', NULL, @Allergies
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientReportedMedications', NULL, @Meds
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientModeOfArrival', @ModeOfArrival, NULL
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientTransportEvacUnit', NULL, @EvacUnit
EXEC sp_InsertMedicalData @TraumaFormID, 'PatientPreviousMedicalHistory', NULL, @PMH
END  
GO 

 

Information

 

Here are some screen shots from my forms

 

 

 

 

 

Estimated Completion Time

 

This will take 5-10 hours to work through the video's and also complete assignment.

 

Supporting Lectures 

 

I demonstrate the use of a GridView and Update in Part 2 of GridView here - Lecture - Form Objects - GridView

 

You will also need to know how to capture a identity on insertion using a stored procedure. Here is some help Lecture - How To - Get an Insert Identity from a SqlDataSource

 

This Video will get you most of the way there. Please be sure you understand all steps in the video Lecture - How To - Create Forms that Update and make sure you post questions on the bulletin board. There are some tricky steps to getting this to work. I will post my code once everyone has attempted to complete the assignment.

 

Questions and Answers

 

 

 

External Resources

 

 

 

Grading Criteria

 

Must meet and demonstrate system works for credit.

 

Comments (0)

You don't have permission to comment on this page.