Thursday, March 26, 2015

Simple SQL Audit Trail

Snow Covered WV State Capitol
"We have a problem!", my boss exclaimed.

Our online system was showing a charge to an account that was different than what it was supposed to show.

I pulled up the relevant section of code.  There was no logical way this code could have provided that value.

"Did you change the record manually?"  I asked.   Every so often, he has to manually intervene to fix an entry error.   I was guessing this was one of those times.

But he didn't remember, and there was no audit records on the database.  Luckily, I was able to confirm with another coworker that he had modified the record, so I was spared hours of combing through code, looking for an error that didn't exist.

Since the next time, I might not be so lucky, I decided it was time to start auditing, using a trigger.

For those who don't know, a database trigger is a subroutine that the database runs on an event, like inserting or updating a record.  It doesn't matter how the record changed, the system will execute the trigger.

I found the perfect solution here.  This trigger will record all inserts, updates and deletes with user name, date and fields.

The trigger pulls the SYSTEM_USER property to get the user that made the change, and does some really nice work to pull the field names, old and new values together.  It is excellent!

https://www.simple-talk.com/content/article.aspx?article=301

Thursday, November 20, 2014

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.

Wednesday, October 08, 2014

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.

Monday, December 16, 2013

Have you seen my cookie?

I received a call from one of our partners telling me they can't log into the website.

Since today is Monday, my first inclination is "forgot password."  I checked my log, and I see the user has validated.

I've been having  problems with Internet Explorer for a few months along these lines.  The use logs in, yet forms authentication doesn't recognize them.  Using any other browser avoids the problem.

I've looked and looked, but haven't been able to find a solution, until today.

The problem is that when the user logs in, the system creates a session cookie, which holds the authentication ticket.  Without said cookie, the user can't look at protected content.

I found the solution.  By adding a single line to the web.config file,  the problem goes away.


     
       

Monday, August 12, 2013

What's the point?

Got asked an interesting question.  In a GPS longitude/latitude  measurement, how much resolution does each decimal give us?


Since each line of longitude is 68.71 Miles then
  • each tenth represents 6.9 miles (or 36,279 feet)
  • each hundredth represents .69 miles (or 36279 feet)
  • each thousandths represents 362 feet (or .07 miles)
  • each then thousandth represents 36.3 feet (or .0069 miles)

Friday, January 18, 2013

Pretty URLs


At work, we are moving to SQL 2008 from SQL 2000.  As part of the move I had to test our Umbraco website against the new server.

So, I copied the contents of our website to the test server, changed the connection strings to point to the new server, and loaded the page.

When clicking on any links, I get a 404 error.

After some investigation, I learned that the issue was with IIS.  Our website uses PrettyURLs.  So if you want the trauma section you would go to www.wvstecs/trauma

The problem is that going to that location was generating a 404, not found error.

The reason is that you have to configure IIS to handle this type of request.  There are two steps.

  1. Make sure that you have configured Umbraco to do the pretty URLs by modifying the umbracoUseDirectoryUrls to true in web.cofig
  2. Make sure you have added the aspnet_isapi.dll handler to your website (Inetmgr - Home Directory tab, Configuration)
More informaton can be found here.
http://our.umbraco.org/wiki/install-and-setup/setting-up-umbraco-for-friendly-urls