Archive for November, 2008

Purge Old Logs

November 21, 2008

I filled a file the other day and got a lot of database errors for one of the tables.
It turned out that my purging process had stopped running (and the notifications weren’t set up).
So I revisited that process, and made two stored procedures to facilitate the process.
Here is how to call the outer procedure:
Exec PurgeOldLogs 30 , 1
30 – number of days
1 – true – actually perform the delete. 0 means just do a select and return the counts that would be deleted.
The outer stored procedure:

-- =============================================

-- input a positive number of days
-- =============================================
CREATE PROCEDURE [dbo].[PurgeOldLogs]
@purgedays int = 60,
@runit bit = 0

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

exec PurgeASingleLog @purgedays , 'Debugging' , 'dwhen' , @runit
exec PurgeASingleLog @purgedays , 'StatusLog' , 'logdate ' , @runit
exec PurgeASingleLog @purgedays , 'Events' , 'eventtime ' , @runit


The inner stored procedure. It generates some dynamic sql and executes it.

/****** Object: StoredProcedure [dbo].[PurgeOldLogs] Script Date: 11/21/2008 07:53:22 ******/
-- =============================================

-- input a positive number of days
-- =============================================
ALTER PROCEDURE [dbo].[PurgeASingleLog]
@purgedays int = 60,
@tablename varchar(50),
@fieldname varchar(50),
@runit bit = 0

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

set @purgedays = -1 * @purgedays

declare @cmdcount nvarchar(500)
declare @cmddelete nvarchar(500)

set @cmdcount = 'SELECT count(*) as NumRecords , ' + char(39) + @tablename + char(39) + ' as NameTable '
set @cmdcount = @cmdcount + ' FROM ' + @tablename
set @cmdcount = @cmdcount + ' WHERE (' + @fieldname
set @cmdcount = @cmdcount + ' < DATEADD(d, ' + convert(varchar(12),@purgedays) + ' , GETDATE() ) )'

print @cmdcount

EXECUTE sp_executesql @cmdcount

if @runit = 1

set @cmddelete = 'DELETE FROM ' + @tablename
set @cmddelete = @cmddelete + ' WHERE (' + @fieldname
set @cmddelete = @cmddelete + ' < DATEADD(d, ' + convert(varchar(12),@purgedays) + ' , GETDATE() ) )'

print @cmddelete

EXECUTE sp_executesql @cmddelete



set favorite icon from master.vb

November 17, 2008

I posted a trick a month ago about setting the favorite icon from the master page. However, it caused a problem with ajax controls “Controls collection cannot be modified because the control contains code blocks (i.e. ). ”

So I made a subroutine to set the favorite icon of a page and put it in the master page. Then in each page, I set a session variable called “Favorite”.

Here is the routine:

' ---------------------------------------------------------------------
' purpose - replace a trick we used because it caused a problem with Ajax controls
' ref
' <link id="idFavIcon" href="" rel="SHORTCUT ICON" />
Sub SetTheFavoriteIcon()
Dim ctl As New HtmlLink
ctl.ID = "idFavicon"
Dim SB As New StringBuilder()
ctl.Attributes.Add("rel", "SHORTCUT ICON")
Dim strPathToFavorite = Session("favorite") & ""
ctl.Attributes.Add("href", strPathToFavorite)
End Sub

checkboxlist and the selected item

November 12, 2008

I’ve got a lot of linking tables in my database – a linking table has keys from two tables. For example, I’m assigning employees to a project. I think of the project key as the major key, and the employee key as the minor key – since I can assign multiple employees to work on a project.

I thought it would be pretty natural to control how employess got assigned with a checkboxlist control. If an employee were assigned, then his checkbox would be checked, and if he were waiting assignment, it would not be checked. Then simply checking the box should assign him to a project by creating a record in the link table.

But, the CheckListBox.SelectedIndex is not the item I click on – it turns out it is the first in the list that happens to be checked. This may be useful for some people, but not so useful for me. Here is the code for the _selectedIndexChanged event that works for me.

    Protected Sub chkList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles chkList.SelectedIndexChanged

            'dont - sometimes throws an exception Diagnostics.Debug.Print("TEXT:" & chkList.SelectedItem.Text + " VALUE:" + chkList.SelectedItem.Value.ToString + " CHECKED?:" + chkList.SelectedItem.Selected.ToString() + " INDEX:" + chkList.SelectedIndex.ToString)

            Dim sLinkKey As String = ""
            Dim sMinorKey As String = ""
            ' note - in the link table, if the value is not selected, there is no record, therefore the link key is zero

            ' is the selected item checked or not?
            Dim iIndex As Integer = get_the_index_from_id()  ' returns the first selected item's index
            If (iIndex = 0) Then
                If (chkList.Items(iIndex).Selected) Then
                    Handle_Add(Me.m_MajorKey, sMinorKey) ' add a record to the link table with project key and employee key
                    Handle_Delete(sMinorKey)    ' delete the records from the link table where the employee key matches
                End If
            End If


            Handle_LoadData()  ' reload data and refresh the box each time

        Catch ex As Exception
            Diagnostics.Debug.Print("***************************** ERROR " & ex.Message)
        End Try
    End Sub

    ' ---------------------------------------------------------------------------------------------------------
    ' note - in a checkboxlist, the first selected item is the first one that happens to be checked, not the one that you clicked with the 
    ' mouse. To get the index of the item in the array of checkboxes, you have to split it out of EventTarget.
    Function get_the_index_from_id() As Integer
        Dim sCmd As String
        Dim sEvent As String
            sCmd = Request.Form("__EVENTTARGET")
            sEvent = Request.Form("__EVENTARGUMENT")
            Dim sA As String() = sCmd.Split("$")
            Dim n As Integer = UBound(sA)
            Dim sIndex As String = sA(n)
            Return CInt(sIndex)

        Catch ex As Exception
            Return -666
        End Try

        Return -1
    End Function

javascript – replace non printing blank space

November 4, 2008


My html displays as a span, and I pad it with spaces when the underlying data is null. It causes problems when I try to update the data without stripping away those spaces.

There were two solutions – 1) don’t use innerHtml to get the contents of the span, use innerText, and 2) get the correct Javascript syntax for the replace.

how to replace &nbsp; in javascript

  var sText = actual.innerHTML;
  // use innerText instead – it won’t have the weird spaces problem
  //sText = actual.innerText;
  // get rid of nbsp
  sText = sText.replace(/&nbsp;/g, ”);  // replace global