Teeter-Totter

Observation Tower at Antietam National Battlefield

While hunting down the solution to a web forms problem, I found I needed a way to change the database such that I knew an event happened.  I was changing the record status to completed, but I had to keep going back and resetting them to active to continue testing. What I wanted was to toggle a field.  

So 

DECLARE @anameVarchar(100);
SELECT @aname= CASE
WHEN LEFT([Hospital],1)='_' THEN SUBSTRING([Hospital],2,len([Hospital])-1)
ELSE '_' + [Hospital]
END
FROM [dbo].[aTable]
WHERE  ID  =@ID;
                        UPDATE dbo.aTable
                        SET  Hospital=@aname
                        WHERE  ID  =@ID

What this does is toggle the Hospital Field by inserting and removing an underscore.  A quick and easy way to 

Two for the Road

Ever have one of those "little" requests.  You know the simple ones?  This simple request took the better part of three hours.

A random pretty picture from my phone

I have a very simple asp.net web form that displays a table of information.  This is a work queue.  There is a button in each row that says "done", and when the button is clicked, it invokes an event that changes the "active" property of the record in the database.

It worked OK, except it had one little glitch.  When clicking on the button, you had to click twice in order for the event to fire..  It didn't even have to be the same button.  Any of the buttons would work.  The event would be called every other click.

I started to investigate.  By looking at the __EVENTTARGET variable from the Request.Form, I was able to ascertain that the control ID's of the buttons where changing.  There was something in the long list of master pages, user controls and Umbraco CMS that lacked an ID and was causing my problem.

Eventually, I gave up and decided to go a different way.

The buttons were causing a postback, as they should, but the event wire up wasn't working, so I did my own wiring.

The buttons are created like so

...TableCell ControlCell = new TableCell();
LinkButton DoneButton = new LinkButton();
DoneButton.Text = "Done";
DoneButton.ID = "Done_" + aReader["ID"].ToString();
DoneButton.EnableViewState =false;
DoneButton.CausesValidation =false;
ControlCell.ID="Control" + ID;
ControlCell.Controls.Add(DoneButton);
arow.Cells.Add(ControlCell);
NRSTable.Rows.Add(arow);

 So what I did was to capture the __EVENTTARGET on the Page_Load event, parse out the control name, find it in the list of page controls, then pass the object to the event handler function.  Like so.

protected void Page_Load(object sender, EventArgs e)
     {  string ControlName =Request.Form["__EVENTTARGET"];
      if (!string.IsNullOrEmpty(ControlName))
      {
    if (ControlName.Contains("$Done_"))
    {
      int x=ControlName.IndexOf("$Done_");
      ControlName = ControlName.Substring(x+1);
      LinkButton DoneButton = (LinkButton) this.FindControl(ControlName);
      if (DoneButton !=null)
    MarkDone((object)DoneButton);
    else
    NotFoundLiteral.Text=string.Format("The control named {0} could not be found.  This should never happen.  Tell Andrew.",ControlName);

      }
      }

Not pretty, but it does work.

Ever need to pull up all columns for all rows with a duplicate field?  Try this query .

Select * from dbo.Table where ID in
    (SELECT ID
     FROM
        (SELECT ID,COUNT(*) AS ItemCount
         FROM dbo.Table
         GROUP BY ID
         HAVING COUNT (*) >1
         )AS a
    )
ORDER BY ID

Basically we have three nested queries.  The inner-most pulls up a count grouped by the ID field which have more than one occurrence.  Note the "AS a" tag.  This alias is required on MS SQL for this to work.

The next query (next as the next inner-most) selects only the ID field from our inner query.  This is so we can use this in the last query, which selects all rows that have ids which match the returned set of ID's.