| 
  • 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
 

Lecture - Trauma Flow - Forms That Update

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

Trauma Flow - Forms That Update

 

 


 

Prerequisites

 

This is a solution to Assignment 4 of Trauma Flow System. COP 4834 Summer 2012 Assignment 4 - Forms that Update

 

Summary

 

I cover how to create forms that update in the Trauma Flow System. This demonstrates using a GridView to select data to edit, and then allowing edit by clicking on the form.

 

Video 

 

http://online1.daytonastate.edu/player2.php?id=56cb94cb34617aeadff1e79b53f38354 

 

Reference Materials

 

Database Code as Used in Video

ALTER 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  
 
ALTER PROCEDURE [dbo].[sp_InsertMedicalData] (
  @TraumaFormID INT,
  @NameText VARCHAR(200),
  @CodeID INT = NULL,
  @ValueText VARCHAR(MAX) = NULL
)
AS
BEGIN 

DECLARE @CategoryID INT
EXEC @CategoryID = dbo.func_GetCategoryID @CategoryText = @NameText

-- N determines if data exists in database
DECLARE @N INT
SELECT @N = COUNT(*) FROM MedicalData
       WHERE TraumaFormID = @TraumaFormID
       AND CategoryID = @CategoryID

-- If data does not exist in database insert data
IF (@N = 0)
BEGIN
 INSERT INTO MedicalData
  (TraumaFormID, CategoryID, TakenDateTime, CodeID, ValueText)
 VALUES
  (@TraumaFormID, @CategoryID, GETDATE(), @CodeID, @ValueText)
END

-- If n does exist then update it
IF (@N != 0) AND (@TraumaFormID != 0)
BEGIN
UPDATE MedicalData
 SET   CodeID = @CodeID,
       ValueText = @ValueText     
       WHERE TraumaFormID = @TraumaFormID
       AND CategoryID = @CategoryID
END 
END
GO 

 

TraumaFlowHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Data;
namespace TraumaFlow.Code
{
    public class TraumaFlowHelper
    {
        public static string connectionName = "TraumaFlowConnectionString";
        public static string getConnectionString()
        {
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.getConnectionString(connectionName);
        }
        public static void fillTextBox(TextBox tb, string XMLName, int TraumaFormID)
        {
            tb.Text = getValue(XMLName, TraumaFormID);
        }
        public static string getValue(string XMLName, int TraumaFormID)
        {
            if (XMLName == "PatientReportedName") return TraumaFlowHelper.getPatientName(TraumaFormID);
            string sql = "SELECT Value FROM view_TextMedicalData ";
            sql += "WHERE XMLName = '" + XMLName + "'";
            sql += " AND id = " + Convert.ToString(TraumaFormID);
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnString(sql);
        }
        public static string getPatientName(int TraumaFormID)
        {
            string sql = "SELECT name FROM TraumaForm WHERE id = '" + TraumaFormID.ToString() + "'";
            
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnString(sql);
        }
        public static string getTraumaFormDate(int TraumaFormID)
        {
            string sql = "SELECT Arrival FROM TraumaForm WHERE id = '" + TraumaFormID.ToString() + "'";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnString(sql);
        }
        public static int getCodeID(string XMLName, int TraumaFormID)
        {
            string sql = "SELECT CodeID FROM view_CodedMedicalData ";
            sql += "WHERE XMLName = '" + XMLName + "'";
            sql += " AND id = " + Convert.ToString(TraumaFormID);
            try
            {
                DatabaseHelper dh = new DatabaseHelper(connectionName);
                return Convert.ToInt32(dh.executeSqlReturnString(sql)); 
            }
            catch
            { 
                return 0; 
            }
        }
        public static DataTable matchStaffNames(string pattern)
        {
            string sql = "SELECT StaffName FROM StaffNames WHERE UPPER(StaffName) LIKE '%" + pattern.ToUpper() + "%'";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnDataTable(sql);
        }
        public static DataTable getAllXMLNames()
        {
            string sql = "SELECT DISTINCT(NameText) FROM NameResolution";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnDataTable(sql);
        }
        public static string[] traumaTeamMembers()
        {
            string[] s = {"TRAUMA_A_NURSE","RUNNER","LAB_TECH","ED_MD","TRAUMA_B_NURSE","OR_NURSE_TECH", 
                          "RADIOLOGY_TECH", "ORTHO_TECH", "SCRIBE", "RESP_THERAPY", "CHAPLAIN", "OTHER"};
            return s;
        }
        public static void insertMedicalData(string nameText, string value)
        {
            // stored procedure has logic for insert OR update
            updateMedicalData(0, nameText, value);
        }
        public static void updateMedicalData(int traumaFormID, string nameText, string value)
        {
            if (doesMedicalDataExist(traumaFormID, nameText, value)) return;
            string sql = "EXEC [sp_InsertMedicalData] " + Convert.ToString(traumaFormID) + ", '" + nameText + "', NULL , '" + value + "'";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            dh.executeSqlNonQuery(sql);
        }
        public static bool doesMedicalDataExist(int traumaFormID, string nameText, string value)
        {
            string categoryID = getCategoryIDFromNameText(nameText);
            string sql = "SELECT COUNT(*) FROM MedicalData WHERE TraumaFormID = " + Convert.ToString(traumaFormID);
            sql += " AND CategoryID = " + getCategoryIDFromNameText(nameText);
            sql += " AND ValueText = '" + value + "'";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            int c = dh.executeSqlReturnInt(sql);
            return (c >= 1);
        }
        public static string getCategoryIDFromNameText(string nameText)
        {
            string sql = "SELECT CategoryID FROM NameResolution WHERE NameText = '" + nameText +"'";
            DatabaseHelper dh = new DatabaseHelper(connectionName);
            return dh.executeSqlReturnString(sql);
        }
    }
} 

 

DisplayTraumaForms.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DisplayTraumaForms.aspx.cs" Inherits="TraumaFlow.InputForms.DisplayTraumaForms" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Select Trauma Form to Edit</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Select Current Trauma Form to Edit</h1>
        <asp:Label ID="lblCurrent" runat="server" Text=""></asp:Label>
        <br />
        <asp:GridView ID="gvForms" runat="server" AutoGenerateColumns="False" 
            CellPadding="4" DataKeyNames="id" DataSourceID="SqlDataSource1" 
            EnableModelValidation="True" ForeColor="#333333" GridLines="None" OnRowCommand="gvForms_OnRowCommand">
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" 
                    ReadOnly="True" SortExpression="id" />
                <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
                <asp:BoundField DataField="Arrival" HeaderText="Arrival" 
                    SortExpression="Arrival" />
                <asp:TemplateField HeaderText="Edit">
                <ItemTemplate>
                    <asp:LinkButton ID="lbEdit" runat="server" CommandName="EditPatient" CommandArgument='<%#Eval("id") %>'>Edit This Patient</asp:LinkButton> | 
                    <asp:LinkButton ID="lbData" runat="server" CommandName="EditData" CommandArgument='<%#Eval("id") %>'>Edit Form Data</asp:LinkButton>
                </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
            SelectCommand="SELECT * FROM [TraumaForm]"></asp:SqlDataSource>
        <br />
        <br />
        <asp:HyperLink ID="HyperLink1" runat="server" 
            NavigateUrl="~/InputForms/CreateTraumaFlow.aspx">Create New Trauma Flow</asp:HyperLink>
    </div>
    </form>
</body>
</html>
 

 

DisplayTraumaForms.aspx.cs

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace TraumaFlow.InputForms
{
    public partial class DisplayTraumaForms : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            { lblCurrent.Text = "Current Trauma Form ID: " + Convert.ToString(Session["TraumaFormID"]); }
            catch
            { lblCurrent.Text = "No Current Trauma Form  "; }

        }

        protected void gvForms_OnRowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "EditPatient")
            {
                int TraumaFormID = Convert.ToInt32(e.CommandArgument);
                Session.Add("TraumaFormID", TraumaFormID);
                Response.Redirect("CreateTraumaFlow.aspx");
            }

            if (e.CommandName == "EditData")
            {
                int TraumaFormID = Convert.ToInt32(e.CommandArgument);
                Session.Add("TraumaFormID", TraumaFormID);
                Response.Redirect("TabbedEntryBasicInfo.aspx");
            }
        }
    }
}

 

CreateTraumaFlow.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CreateTraumaFlow.aspx.cs" Inherits="TraumaFlow.InputForms.CreateTraumaFlow" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Input Trauma Flow for New Form</title>
    <link rel="Stylesheet" href="/../styles.css" type="text/css" />
</head>
<body>
<h1 style="text-align:center">Enter Data for New Trauma Tracking</h1>
    <form id="form1" runat="server">
    <div>
    <table>
    <tr>
    <td>
        <asp:Label ID="Label1" runat="server" CssClass="inputLabel" Text="Patient Name:" Width="150"></asp:Label></td>
    <td>
        <asp:TextBox ID="tbPatientName" CssClass="inputTextBox" runat="server" 
            Width="200"></asp:TextBox></td>
    <td>
        <asp:Label ID="Label2" runat="server" CssClass="inputLabel" Text="Time Of Arrival:" Width="150"></asp:Label></td>
    <td>
        <asp:TextBox ID="tbArrivalTime" CssClass="inputTextBox" runat="server" Width="200"></asp:TextBox></td>
    </tr>
    <tr>
    <td>
        <asp:Label ID="Label3" runat="server" CssClass="inputLabel" Text="Age:" Width="150"></asp:Label></td>
    <td>
        <asp:TextBox ID="tbAge" runat="server" CssClass="inputTextBox" Width="200" ></asp:TextBox></td>
    <td>
        <asp:Label ID="Label4" runat="server" CssClass="inputLabel" Text="Date:" Width="150"></asp:Label></td>
    <td>
        <asp:TextBox ID="tbDate" runat="server" CssClass="inputTextBox"  Width="200"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td>
        <asp:Label ID="Label5" runat="server" CssClass="inputLabel" Text="Mode Of Arrival:" Width="150"></asp:Label></td>
    <td>
        <asp:DropDownList ID="ddlModeOfArrival" runat="server" 
            CssClass="inputTextBox" Width="200px" 
            DataSourceID="SqlDataSourceModeOfArrival" 
            DataTextField="DisplayText" 
            DataValueField="CodeID">
        </asp:DropDownList></td>
    <td>
        <asp:Label ID="Label6" runat="server" CssClass="inputLabel" Text="Evac Unit" Width="150"></asp:Label></td>
    <td>
        <asp:TextBox ID="tbEvacUnit" runat="server" CssClass="inputTextBox"  Width="200"></asp:TextBox></td>
    </tr>
    <tr>
    <td>
        <asp:Label ID="Label7" runat="server" CssClass="inputLabel" Text="Allergies" Width="150"></asp:Label></td>
    <td colspan = '3'>
        <asp:TextBox ID="tbAllergies" runat="server" Width='550' CssClass="inputTextBox" ></asp:TextBox></td>
    </tr>
    <tr>
    <td>
        <asp:Label ID="Label8" runat="server" CssClass="inputLabel" Text="Meds" Width="150"></asp:Label></td>
    <td colspan = '3'>
        <asp:TextBox ID="tbMeds" runat="server" Width='550' CssClass="inputTextBox" ></asp:TextBox></td>
    </tr>
    <tr>
    <td>
        <asp:Label ID="Label9" runat="server" CssClass="inputLabel" Text="PMH" Width="150"></asp:Label></td>
    <td colspan = '3'>
        <asp:TextBox ID="tbPMH" runat="server" Width='550' CssClass="inputTextBox" ></asp:TextBox></td>
    </tr>
    </table>
        <center>
            <asp:Button ID="btnSubmit" runat="server" CssClass="submitButton" 
                Text="Submit" onclick="btnSubmit_Click" />                
             <asp:Button ID="btnUpdate" runat="server" CssClass="submitButton" 
                Text="Update" onclick="btnUpdate_Click" />
            <asp:SqlDataSource ID="SqlDataSourceModeOfArrival" runat="server" 
                ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
                SelectCommand="SELECT Categories.id AS CategoryID, Codes.id AS CodeID, Codes.DisplayText FROM Categories INNER JOIN Codes ON Categories.id = Codes.CategoryID WHERE (Codes.CategoryID = 10)">
            </asp:SqlDataSource>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                ConnectionString="<%$ ConnectionStrings:TraumaFlowConnectionString %>" 
                InsertCommand="sp_CreateForm" InsertCommandType="StoredProcedure" OnInserted="SqlDataSource2_OnInserted"
                UpdateCommand="sp_UpdateForm" UpdateCommandType="StoredProcedure">
                <InsertParameters>
                    <asp:ControlParameter ControlID="tbPatientName" Name="PatientName" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbArrivalTime" Name="TimeOfArrival" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbAge" Name="Age" PropertyName="Text" 
                        Type="String" />
                    <asp:ControlParameter ControlID="tbArrivalTime" Name="DateOfArrival" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbAllergies" Name="Allergies" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbAllergies" Name="Meds" PropertyName="Text" 
                        Type="String" />
                    <asp:ControlParameter ControlID="ddlModeOfArrival" Name="ModeOfArrival" 
                        PropertyName="SelectedValue" Type="Int32" />
                    <asp:ControlParameter ControlID="tbEvacUnit" Name="EvacUnit" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbPMH" Name="PMH" PropertyName="Text" 
                        Type="String" />
                    <asp:Parameter Direction="Output" Name="TraumaFormID" Type="Int32" />
                </InsertParameters>
                 <UpdateParameters>
                    <asp:SessionParameter Name="TraumaFormID" Type="Int32" SessionField="TraumaFormID" 
                         ConvertEmptyStringToNull="False" />
                    <asp:ControlParameter ControlID="tbPatientName" Name="PatientName" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbArrivalTime" Name="TimeOfArrival" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbAge" Name="Age" PropertyName="Text" 
                        Type="String" />
                    <asp:ControlParameter ControlID="tbDate" Name="DateOfArrival" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbAllergies" Name="Allergies" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbMeds" Name="Meds" PropertyName="Text" 
                        Type="String" />
                    <asp:ControlParameter ControlID="ddlModeOfArrival" Name="ModeOfArrival" 
                        PropertyName="SelectedValue" Type="Int32" />
                    <asp:ControlParameter ControlID="tbEvacUnit" Name="EvacUnit" 
                        PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbPMH" Name="PMH" PropertyName="Text" 
                        Type="String" />
                </UpdateParameters>                
            </asp:SqlDataSource>           
        </center>
    </div>
    </form>
</body>
</html>
 

 

CreateTraumaFlow.aspx.cs

 

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using TraumaFlow.Code;

namespace TraumaFlow.InputForms
{
    public partial class CreateTraumaFlow : System.Web.UI.Page
    {
        public int TraumaFormID()
        {
            if (Session["TraumaFormID"] == null)
                return 0;
            else
                return Convert.ToInt32(Session["TraumaFormID"]);
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (TraumaFormID() != 0)
                {
                    int id = TraumaFormID();
                    btnSubmit.Visible = false;
                    btnUpdate.Visible = true;
                    tbPatientName.Text = TraumaFlowHelper.getValue("PatientReportedName", id);
                    tbArrivalTime.Text = TraumaFlowHelper.getValue("TimeOfArrival", id);
                    tbAge.Text = TraumaFlowHelper.getValue("PatientCurrentAge", id);
                    tbDate.Text = TraumaFlowHelper.getValue("DateOfArrival", id);
                    tbAllergies.Text = TraumaFlowHelper.getValue("PatientReportedAllergies", id);
                    tbMeds.Text = TraumaFlowHelper.getValue("PatientReportedMedications", id);
                    tbEvacUnit.Text = TraumaFlowHelper.getValue("PatientTransportEvacUnit", id);
                    tbPMH.Text = TraumaFlowHelper.getValue("PatientPreviousMedicalHistory", id);
                    int codeID = TraumaFlowHelper.getCodeID("PatientModeOfArrival", id);
                    if (codeID != 0) ddlModeOfArrival.SelectedValue = Convert.ToString(codeID);
                }
                else
                {
                    btnSubmit.Visible = true;
                    btnUpdate.Visible = false;
                }
            }
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            SqlDataSource2.Insert();
            Response.Redirect("DisplayTraumaForms.aspx");
        }

        protected void SqlDataSource2_OnInserted(object sender, SqlDataSourceStatusEventArgs e)
        {
            System.Data.Common.DbCommand command = e.Command;
            Session.Add("TraumaFormID", command.Parameters["@TraumaFormID"].Value.ToString());            
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {            
            SqlDataSource2.Update();
            Response.Redirect("DisplayTraumaForms.aspx");
        }
    }
}

 

 

 

Additional Information

 

 

COP 4834 Lectures Page

Comments (0)

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