Update the result of an inner join from a SQL table

UPDATE @result
SET selectiondate=eventdatetime
FROM @result r
INNER JOIN (
  SELECT r.id, MAX(h.EventDateTime) eventdatetime
  FROM @result r
  LEFT JOIN statehistory h WITH (NOLOCK) ON h.id = r.id AND h.eventid = @CONST_SelectedEvent
  GROUP BY r.id
) d ON d.id=r.id

Page Lifecycle

Simple graphic showing an asp.net pages lifecycle

Manage XML data in SQL column when column is not of type XML

Below is a way to manage XML in SQL when the column is not of type XML

First, cast the data into a temp table as XML
DECLARE @results TABLE (Id INT, Detail XML)
--Filter results
INSERT INTO @results
select t.Id, d.extrainfo
from tableOne t
inner join into i on i.Id=t.Id
where i.institutionid=6 and t.state < 270

Combine relevent data for easier manipulation
DECLARE @x XML;
SELECT @x = ( SELECT
  [Id],
  [Detail].query('
  for $a in /*:RootNode
  return ')
FROM @results AS results
FOR XML AUTO);


Simple select statement
SELECT Id = T.Item.value('../@Id', 'INT'),
  Date = T.Item.value('@date', 'DATETIME')
FROM @x.nodes('results/detail') as T(Item)

Save DataSet as XML and then Recreate the DataSet from saved XML

In this example, the DataSet is also prepended with a distinct node that can be used to include specific data the DataSet didn't include.

using(DataSet ds = GetSpecificData(dataID)){
  XmlDocument xd = new XmlDocument();
  //Here is where the unique node is created and added to the XmlDocument
  XmlNode node = xd.CreateNode(XmlNodeType.Element, "NewRootOfXML", null);
  xd.AppendChild(node);

  using(MemoryStream ms = new MemoryStream()) {
    ds.WriteXml(ms);

    //Reset the position to the start of the stream
    ms.Seek(0, SeekOrigin.Begin);

    StreamReader sr = new StreamReader(ms);
    node.InnerXml = sr.ReadToEnd();
  }

  xd.SaveTheXML();
}

  XmlDocument xDoc = TheSavedXML();
  XmlNode nd = xDoc .SelectSingleNode("/NewRootOfXML");

  DataSet data = new DataSet();
  XmlTextReader reader = new XmlTextReader(nd.InnerXml, XmlNodeType.Element, null);
  data.ReadXml(reader);

SQL paging or selecting a specific range of rows

With a large set of data within a table, it is time consuming to query the entire set. To quickly return a specific section, follow the example below.

SELECT *
FROM
  (SELECT row_number() over (order by first, last DESC) as row, i.first, i.last, i.ex
    FROM information i
    WHERE i.infotype = @type
  ) AS Results
WHERE row BETWEEN 19000 AND 19900

Create MaxLength on Textbox

Using the simple script below, multi-line textboxes will prevent a user from exceeding the MaxLength property.

* Note - using System.Text;

StringBuilder funcStr = new StringBuilder();
funcStr.Append("function isMaxLength(txtBox) {");
funcStr.Append("if(txtBox){");
funcStr.AppendFormat("return(txtBox.value.length <= {0});", aTextBox.MaxLength);
funcStr.Append("}}");

aTextBox.Attributes.Add("onkeypress", "return isMaxLength(this);");

ClientScript.RegisterClientScriptBlock(
   this.GetType(),
   "txtLength",
   funcStr.ToString() , true);

Using hidden field to track panels visibility

To track the javascript visibility changes of a panel in code behind, follow the description below.

Within the pages Form element add a hidden input field
<input type="hidden" name="panelStatus" id="panelStatus" value="">


Javascript that was set-up to handle the onclick event
function ShowHidePanel(img, id){
var panel = document.getElementById(id);
var status = document.getElementById("panelStatus");

if(panel != null){
  if(panel.style.display == ""){
    panel.style.display = "none"
    img.src = "/images/icn_plus.gif";
    status.value = "closed";
  }
  else{
    panel.style.display = "";
    img.src = "/images/icn_minus.gif";
    status.value = "open";
  }
}}


Grab current value in code behind and adjust panel accordingly
string status = Request.Form["panelStatus"];
ViewState["panelStatus"] = StringHelper.IsNullOrEmpty(status) ? ViewState["panelStatus"] : status;
if(ViewState["panelStatus"] != null && "open".Equals(ViewState["panelStatus"]))
  this.ShowHidePanel.Style.Add("display", "");
  this.ShowHideImg.ImageUrl="/images/icn_minus.gif";
}
else{
  this.ShowHidePanel.Style.Add("display", "none");
  this.ShowHideImg.ImageUrl="/images/icn_plus.gif";
}