DZone Snippets: Will_rickards's Code Snippets

Programmatically Change a Crystal Reports datasource location

Will_Rickards (Will Rickards) — Wed, 16 May 2007 20:30:09 GMT

I had an aspx.net project where I needed to generate a pdf.
I used crystal to do the heavy lifting of geneating the pdf for me.
But as I needed to deploy it to multiple databases/clients I needed a way of programmatically changing the datasource location of the report. So with a bit of help from the crystal knowledgebase here is the code I use.
There is a lot of debugging information in here and trust me when it fails you need it.
The .dbo of the location reference obviously means I was working with sql server.



   ' --------------------------------------------------------------------------

   ' SetupReport

   '

   ' Description:

   '    sets up the crystal report

   '

   ' Arguments:

   '    objCrystalReportDocument             - crystal report document object

   '

   ' Dependencies:

   '    GetConnnectionInfo

   '    CrystalDescisions

   '

   ' History:

   ' 03/17/2006 - WSR : created

   '

   Private Function SetupReport(ByRef objCrystalReportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument) As System.Boolean



      ' a heck of a lot of objects used

      Dim crParameterDiscreteValue As CrystalDecisions.Shared.ParameterDiscreteValue

      Dim crParameterFieldDefinitions As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions

      Dim crParameterFieldLocation As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition

      Dim crParameterValues As CrystalDecisions.Shared.ParameterValues

      Dim crSections As CrystalDecisions.CrystalReports.Engine.Sections

      Dim crSection As CrystalDecisions.CrystalReports.Engine.Section

      Dim crReportObjects As CrystalDecisions.CrystalReports.Engine.ReportObjects

      Dim crReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject

      Dim crSubreportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument

      Dim crSubreportObject As CrystalDecisions.CrystalReports.Engine.SubreportObject

      Dim crConnectionInfo As CrystalDecisions.Shared.ConnectionInfo

      Dim crDatabase As CrystalDecisions.CrystalReports.Engine.Database

      Dim crTables As CrystalDecisions.CrystalReports.Engine.Tables

      Dim aTable As CrystalDecisions.CrystalReports.Engine.Table

      Dim bTable As CrystalDecisions.CrystalReports.Engine.Table

      Dim crTableLogOnInfo As CrystalDecisions.Shared.TableLogOnInfo

      Dim blnTest As System.Boolean

      Dim strLocation As System.String

      Dim blnErrors As System.Boolean



      ' instantiate the debug page

      m_strDebugPage = New System.Text.StringBuilder(4096)

      blnErrors = False



      crConnectionInfo = GetConnectionInfo()



      crDatabase = objCrystalReportDocument.Database



      crTables = crDatabase.Tables



      'For intCounter = 0 To objCrystalReportDocument.Database.Tables.Count - 1

      For Each aTable In crTables



         crTableLogOnInfo = aTable.LogOnInfo



         OutputDebugLine("BEFORE")

         OutputDebugLine("TABLE NAME: " & aTable.Name)

         OutputDebugLine("TABLE LOC: " & aTable.Location)

         OutputDebugLine("SERVER: " & crTableLogOnInfo.ConnectionInfo.ServerName)

         OutputDebugLine("DB: " & crTableLogOnInfo.ConnectionInfo.DatabaseName)

         OutputDebugLine("UID: " & crTableLogOnInfo.ConnectionInfo.UserID)

         OutputDebugLine("PWD: " & crTableLogOnInfo.ConnectionInfo.Password)

         OutputDebugLine("RN: " & crTableLogOnInfo.ReportName)

         OutputDebugLine("TN: " & crTableLogOnInfo.TableName)



         crTableLogOnInfo.ConnectionInfo = crConnectionInfo

         aTable.ApplyLogOnInfo(crTableLogOnInfo)

         strLocation = crConnectionInfo.DatabaseName & ".dbo." & aTable.Location.Substring(aTable.Location.LastIndexOf(".") + 1)

         OutputDebugLine("New Location: " & strLocation)

         Try

            aTable.Location = strLocation

         Catch ex As Exception

            OutputDebugLine("Set Location Error: " & ex.ToString)

            blnErrors = True

         End Try



         OutputDebugLine("AFTER")

         OutputDebugLine("TABLE NAME: " & aTable.Name)

         OutputDebugLine("TABLE LOC: " & aTable.Location)

         OutputDebugLine("SERVER: " & crTableLogOnInfo.ConnectionInfo.ServerName)

         OutputDebugLine("DB: " & crTableLogOnInfo.ConnectionInfo.DatabaseName)

         OutputDebugLine("UID: " & crTableLogOnInfo.ConnectionInfo.UserID)

         OutputDebugLine("PWD: " & crTableLogOnInfo.ConnectionInfo.Password)

         OutputDebugLine("RN: " & crTableLogOnInfo.ReportName)

         OutputDebugLine("TN: " & crTableLogOnInfo.TableName)

         Try

            blnTest = aTable.TestConnectivity()

            OutputDebugLine("CONNECTED? " & blnTest.ToString())

         Catch ex As Exception

            OutputDebugLine("CONNECTED? NO")

            OutputDebugLine(ex.ToString)

            blnErrors = True

         End Try



         '// THIS STUFF HERE IS FOR REPORTS HAVING SUBREPORTS 

         '// set the sections object to the current report's section 

         crSections = objCrystalReportDocument.ReportDefinition.Sections



         '// loop through all the sections to find all the report objects 

         For Each crSection In crSections



            crReportObjects = crSection.ReportObjects



            '//loop through all the report objects in there to find all subreports 

            For Each crReportObject In crReportObjects



               If crReportObject.Kind = ReportObjectKind.SubreportObject Then



                  crSubreportObject = CType(crReportObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)

                  '//open the subreport object and logon as for the general report 

                  crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

                  crDatabase = crSubreportDocument.Database

                  crTables = crDatabase.Tables



                  For Each bTable In crTables



                     crTableLogOnInfo = bTable.LogOnInfo



                     OutputDebugLine("BEFORE")

                     OutputDebugLine("TABLE NAME: " & bTable.Name)

                     OutputDebugLine("TABLE LOC: " & bTable.Location)

                     OutputDebugLine("SERVER: " & crTableLogOnInfo.ConnectionInfo.ServerName)

                     OutputDebugLine("DB: " & crTableLogOnInfo.ConnectionInfo.DatabaseName)

                     OutputDebugLine("UID: " & crTableLogOnInfo.ConnectionInfo.UserID)

                     OutputDebugLine("PWD: " & crTableLogOnInfo.ConnectionInfo.Password)

                     OutputDebugLine("RN: " & crTableLogOnInfo.ReportName)

                     OutputDebugLine("TN: " & crTableLogOnInfo.TableName)



                     crTableLogOnInfo.ConnectionInfo = crConnectionInfo

                     bTable.ApplyLogOnInfo(crTableLogOnInfo)

                     strLocation = crConnectionInfo.DatabaseName & ".dbo." & bTable.Location.Substring(bTable.Location.LastIndexOf(".") + 1)

                     OutputDebugLine("New Location: " & strLocation)

                     Try

                        bTable.Location = strLocation

                     Catch ex As Exception

                        OutputDebugLine("Set Location Error: " & ex.ToString)

                        blnErrors = True

                     End Try



                     OutputDebugLine("AFTER")

                     OutputDebugLine("TABLE NAME: " & bTable.Name)

                     OutputDebugLine("TABLE LOC: " & bTable.Location)

                     OutputDebugLine("SERVER: " & crTableLogOnInfo.ConnectionInfo.ServerName)

                     OutputDebugLine("DB: " & crTableLogOnInfo.ConnectionInfo.DatabaseName)

                     OutputDebugLine("UID: " & crTableLogOnInfo.ConnectionInfo.UserID)

                     OutputDebugLine("PWD: " & crTableLogOnInfo.ConnectionInfo.Password)

                     OutputDebugLine("RN: " & crTableLogOnInfo.ReportName)

                     OutputDebugLine("TN: " & crTableLogOnInfo.TableName)

                     Try

                        blnTest = bTable.TestConnectivity()

                        OutputDebugLine("CONNECTED? " & blnTest.ToString())

                     Catch ex As Exception

                        OutputDebugLine("CONNECTED? NO")

                        OutputDebugLine(ex.ToString)

                        blnErrors = True

                     End Try



                  Next bTable



               End If



            Next crReportObject



         Next crSection



      Next aTable



      ' get parameter fields from report

      crParameterFieldDefinitions = objCrystalReportDocument.DataDefinition.ParameterFields



      '    ' Set the first parameter

      '    ' - Get the parameter, tell it to use the current values vs default value.

      '    ' - Tell it the parameter contains 1 discrete value vs multiple values.

      '    ' - Set the parameter's value.

      '    ' - Add it and apply it.

      '    ' - Repeat these statements for each parameter.

      '    '

      crParameterFieldLocation = crParameterFieldDefinitions.Item("@psindex")

      crParameterValues = crParameterFieldLocation.CurrentValues

      crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue

      crParameterDiscreteValue.Value = m_intReportID

      crParameterValues.Add(crParameterDiscreteValue)

      crParameterFieldLocation.ApplyCurrentValues(crParameterValues)



      ' if there were errors

      If blnErrors Then



         ' display debug page

         OutputDebugPage()



      End If



   End Function

   ' --------------------------------------------------------------------------





   ' --------------------------------------------------------------------------

   ' GetConnectionInfo

   '

   ' Description:

   '    retrieves the connection information from the data layer object

   '

   ' Arguments: none

   '

   ' Dependencies:

   '    DataLayer.GetConnectInfo

   '

   ' History:

   ' 03/17/2006 - WSR : created

   '

   Private Function GetConnectionInfo() As CrystalDecisions.Shared.ConnectionInfo



      Dim objConnectionInfo As CrystalDecisions.Shared.ConnectionInfo

      Dim strDSN As System.String

      Dim strDB As System.String

      Dim strUID As System.String

      Dim strPWD As System.String

      Dim blnTrust As System.Boolean



      ' get connection information from data layer

      m_objDataLayer.GetConnectInfo(strDSN, strDB, strUID, strPWD, blnTrust)



      ' create new crystal connection info object

      objConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo



      ' populate it

      objConnectionInfo.IntegratedSecurity = False

      objConnectionInfo.ServerName = strDSN

      objConnectionInfo.UserID = strUID

      objConnectionInfo.Password = strPWD

      objConnectionInfo.DatabaseName = strDB



      ' return object

      GetConnectionInfo = objConnectionInfo



   End Function

   ' --------------------------------------------------------------------------



   ' --------------------------------------------------------------------------

   ' OutputDebugLine

   '

   ' Description: appends a line to the debug string builder

   '

   ' Arguments: text to add

   '

   ' Dependencies:

   '    m_strDebugPage

   '

   ' History:

   ' 03/17/2006 - WSR : created

   ' 2007.04.25 - WSR : revised to use string builder

   '

   Function OutputDebugLine(ByVal strLine As System.String) As System.Boolean



      m_strDebugPage.Append("" & Server.HtmlEncode(strLine) & "")



   End Function

   ' --------------------------------------------------------------------------





   ' --------------------------------------------------------------------------

   ' OutputDebugPage

   '

   ' Description: sends debug string builder to response

   '

   ' Arguments: none

   '

   ' Dependencies:

   '    m_strDebugPage

   '

   ' History:

   ' 2007.04.25 - WSR : created

   '

   Function OutputDebugPage() As System.Boolean



      With Response

         .ClearHeaders()

         .ClearContent()

         .ContentType = "text/html"

      End With



      Response.Write("Debug Page")

      Response.Write(m_strDebugPage.ToString())

      Response.Write("")



      Response.Flush()

      Response.End()



   End Function

   ' --------------------------------------------------------------------------

ASP.Net Serve File For Download

Will_Rickards (Will Rickards) — Tue, 13 Feb 2007 16:52:16 GMT

Function I use to serve a file on the webserver to the client machine.



   ' sends file to browser for download

   Private Sub SendFile(ByVal strPath As System.String, ByVal strSuggestedName As System.String)



      Dim strServerPath As String

      Dim objSourceFileInfo As System.IO.FileInfo



      ' convert relative path to path on server machine

      strServerPath = Me.Server.MapPath(strPath)



      ' get fileinfo of source file

      objSourceFileInfo = New System.IO.FileInfo(strServerPath)



      ' if the file exists

      If objSourceFileInfo.Exists Then



         With Me.Response



            ' tell the browser what content type to expect

            .ContentType = "application/octet-stream"

 

            ' tell the browser to save rather than display inline

            .AddHeader("Content-Disposition", "attachment; filename=" & strSuggestedName)



            ' tell the browser how big the file is

            .AddHeader("Content-Length", objSourceFileInfo.Length.ToString)



            ' send the file to the browser

            .WriteFile(objSourceFileInfo.FullName)



            ' make sure response is sent

            .Flush()



            ' end response

            .End()



         End With



      ' if the file does not exist

      Else



         ' show error page

         ThrowError("Application Error", "File Missing From Server")



      End If



   End Sub

Javascript Manipulate Class Names

Will_Rickards (Will Rickards) — Wed, 20 Sep 2006 21:34:35 GMT

I often have to manipulate class names of objects in javascript.
But className can have multiple classes in it.
These functions deal with that.



// ----------------------------------------------------------------------------

// HasClassName

//

// Description : returns boolean indicating whether the object has the class name

//    built with the understanding that there may be multiple classes

//

// Arguments:

//    objElement              - element to manipulate

//    strClass                - class name to add

//

function HasClassName(objElement, strClass)

   {



   // if there is a class

   if ( objElement.className )

      {



      // the classes are just a space separated list, so first get the list

      var arrList = objElement.className.split(' ');



      // get uppercase class for comparison purposes

      var strClassUpper = strClass.toUpperCase();



      // find all instances and remove them

      for ( var i = 0; i < arrList.length; i++ )

         {



         // if class found

         if ( arrList[i].toUpperCase() == strClassUpper )

            {



            // we found it

            return true;



            }



         }



      }



   // if we got here then the class name is not there

   return false;



   }

// 

// HasClassName

// ----------------------------------------------------------------------------





// ----------------------------------------------------------------------------

// AddClassName

//

// Description : adds a class to the class attribute of a DOM element

//    built with the understanding that there may be multiple classes

//

// Arguments:

//    objElement              - element to manipulate

//    strClass                - class name to add

//

function AddClassName(objElement, strClass, blnMayAlreadyExist)

   {



   // if there is a class

   if ( objElement.className )

      {



      // the classes are just a space separated list, so first get the list

      var arrList = objElement.className.split(' ');



      // if the new class name may already exist in list

      if ( blnMayAlreadyExist )

         {



         // get uppercase class for comparison purposes

         var strClassUpper = strClass.toUpperCase();



         // find all instances and remove them

         for ( var i = 0; i < arrList.length; i++ )

            {



            // if class found

            if ( arrList[i].toUpperCase() == strClassUpper )

               {



               // remove array item

               arrList.splice(i, 1);



               // decrement loop counter as we have adjusted the array's contents

               i--;



               }



            }



         }



      // add the new class to end of list

      arrList[arrList.length] = strClass;



      // add the new class to beginning of list

      //arrList.splice(0, 0, strClass);

      

      // assign modified class name attribute

      objElement.className = arrList.join(' ');



      }

   // if there was no class

   else

      {



      // assign modified class name attribute      

      objElement.className = strClass;

   

      }



   }

// 

// AddClassName

// ----------------------------------------------------------------------------





// ----------------------------------------------------------------------------

// RemoveClassName

//

// Description : removes a class from the class attribute of a DOM element

//    built with the understanding that there may be multiple classes

//

// Arguments:

//    objElement              - element to manipulate

//    strClass                - class name to remove

//

function RemoveClassName(objElement, strClass)

   {



   // if there is a class

   if ( objElement.className )

      {



      // the classes are just a space separated list, so first get the list

      var arrList = objElement.className.split(' ');



      // get uppercase class for comparison purposes

      var strClassUpper = strClass.toUpperCase();



      // find all instances and remove them

      for ( var i = 0; i < arrList.length; i++ )

         {



         // if class found

         if ( arrList[i].toUpperCase() == strClassUpper )

            {



            // remove array item

            arrList.splice(i, 1);



            // decrement loop counter as we have adjusted the array's contents

            i--;



            }



         }



      // assign modified class name attribute

      objElement.className = arrList.join(' ');



      }

   // if there was no class

   // there is nothing to remove



   }

// 

// RemoveClassName

// ----------------------------------------------------------------------------

Javascript QuickSelect (Coordinated Textbox And ListBox)

Will_Rickards (Will Rickards) — Wed, 20 Sep 2006 21:31:48 GMT

In HTML, say you have a select element with all the states.
Unfortunately typing in this select box only works with the first letter.
So if you type P then A you don't select pennsylvania.
What I commonly do in this situation is to have what I call a quickselect textbox on the page as well.
This is a textbox and listbox that are coordinated.



// in your onload event handler you need this



// hookup event handlers and control references

var objElement = document.getElementById('cboState');

if ( objElement )

   {

   objElement.onchange = QuickSelect_Change;

   objElement.textbox = document.getElementById('txtState');

   if ( objElement.textbox )

      {

      objElement.textbox.onchange = QuickSelect_TextChange;

      objElement.textbox.onblur = QuickSelect_TextChange;

      objElement.textbox.onkeypress = QuickSelect_KeyPress;

      objElement.textbox.listbox = objElement;

      }

   }       



// and now the functions



// ----------------------------------------------------------------------------

// QuickSelect_KeyPress

//

// Description : event handler for quick select textbox's key press event

//    selects the appropriate item in the associated listbox control

//

// Arguments : none

//

// Dependencies : 

//

// History :

// 2006.09.20 - WSR : created

//

function QuickSelect_KeyPress( e )

   {





   var strCompare = '';

   var numEntryLen;

   var strEntry;

   var objSelect = this.listbox;

   var numOptions = objSelect.options.length;



   var numCharCode;

   var elTarget;



   // get event if not passed

   if (!e) var e = window.event;



   // get character code of key pressed

   if (e.keyCode) numCharCode = e.keyCode;

   else if (e.which) numCharCode = e.which;



   // get target

   if (e.target) elTarget = e.target;

   else if (e.srcElement) elTarget = e.srcElement;

                                              

   // if form input field & it is a printable character

   if ( elTarget.nodeName.toUpperCase() == 'INPUT' && numCharCode >= 32 && numCharCode <= 126 )

      {



      strEntry = this.value + String.fromCharCode(numCharCode);

      numEntryLen = strEntry.length;



      // cycle through options

      for (var i = 0; i < numOptions; i++)

         {



         // get compare string from value same length as entered string

         strCompare = objSelect.options[i].value.substring(0, numEntryLen);



         // if value matches what is entered

         if (strEntry == strCompare)

            {



            // select this option

            objSelect.options[i].selected = true;



            // end loop

            break;



            }



         }



      // I usually add an error class when input is required and not filled in

      // this removes the class so the input is no longer marked as invalid

      // commented out as it isn't necessary for general purpose

      // RemoveClassName( this, 'error' );



      }



   }

//

// QuickSelect_KeyPress

// ----------------------------------------------------------------------------





// ----------------------------------------------------------------------------

// QuickSelect_TextChange

//

// Description : event handler for quick select textbox's change & onblur events

//    makes sure item in textbox is a value from list

//

// Arguments : none

//

// Dependencies : none

//

// History :

// 2006.08.09 - WSR : created

//

function QuickSelect_TextChange()

   {



   var strCompare = '';

   var numEntryLen = this.value.length;

   var strEntry = this.value;

   var objSelect = this.listbox;

   var numOptions = objSelect.options.length;



   // cycle through options

   for (var i = 0; i < numOptions; i++)

      {



      // get compare string from value same length as entered string

      strCompare = objSelect.options[i].value.substring(0, numEntryLen);



      // if value matches what is entered

      if (strEntry == strCompare)

         {



         // select this option

         objSelect.options[i].selected = true;



         // end loop

         break;



         }



      }



   // copy code to textbox

   this.value = this.listbox.options[this.listbox.selectedIndex].value;



   // I usually add an error class when input is required and not filled in

   // this removes the class so the input is no longer marked as invalid

   // commented out as it isn't necessary for general purpose

   // RemoveClassName( this, 'error' );



   }

//

// QuickSelect_TextChange

// ----------------------------------------------------------------------------





// ----------------------------------------------------------------------------

// QuickSelect_Change

//

// Description : event handler for quick select list box's change event

//    updates the textbox with the selected value

//

// Arguments: none

//

// Dependencies :

//

// History :

// 2006.08.09 - WSR : created

//

function QuickSelect_Change()

   {



   // copy code to textbox

   this.textbox.value = this.options[this.selectedIndex].value;



   }	

//

// QuickSelect_Change

// ----------------------------------------------------------------------------

Javascript LookupControl

Will_Rickards (Will Rickards) — Fri, 14 Jul 2006 22:28:13 GMT

A lookup control is a common thing to have on many web pages. In this case we assume a textbox, a command button, and a label of some sort to display a description.

The HTML might look like this



Client

Here is a javascript function to call in the onload to setup that lookup control. And examples of the functions required.



// ----------------------------------------------------------------------------

// SetupLookupControl

//

// Description : sets up a lookup control by linking it to its 

//    associated controls and hooking up the event handlers

//

// Arguments :

//    strTextBoxID               : input type=text id

//    strLookupButtonID          : input type=button id

//    strLabelID                 : span of div id

//    funcTextBoxOnChange        : function for textbox's onchange and onblur event

//    funcLookupButtonOnClick    : function for lookup button's onclick event

//

// Dependencies : none

//

// History :

// 2006.07.14 - WSR : created

//

function SetupLookupControl( strTextBoxID, strLookupButtonID, strLabelID, funcTextBoxOnChange, funcLookupButtonOnClick )

   {



   // get reference to lookup button

   var ctlLookupButton = document.getElementById(strLookupButtonID);



   // if lookup button was found

   if (ctlLookupButton)

      {



      // hookup event handlers and control references

      ctlLookupButton.onclick = funcLookupButtonOnClick;

      ctlLookupButton.textbox = document.getElementById(strTextBoxID);



      // if textbox was found

      if (ctlLookupButton.textbox)

         {



         // hookup event handlers and control references

         ctlLookupButton.textbox.onchange = funcTextBoxOnChange;

         ctlLookupButton.textbox.onblur = funcTextBoxOnChange;

         ctlLookupButton.textbox.label = document.getElementById(strLabelID);



         // init last value property of textbox

         if ( typeof ctlLookupButton.textbox.lastvalue == 'undefined' )

            ctlLookupButton.textbox.lastvalue = ''; 



         }



      }



   }

//

// SetupLookupControl

// ----------------------------------------------------------------------------



// ----------------------------------------------------------------------------

// cmdTimekeeperLookup_click

// Description: handler for requesting timekeeper selection button click event

// Arguments: none

// Dependencies:

//    TimekeepQuery (lookup.js)

//

function cmdTimekeeperLookup_click()

   {



   // show timekeeper query window

   TimekeepQuery( this.textbox );



   // update timekeeper information

   this.textbox.onchange();



   // process default action

   return true;



   }

//

// cmdTimekeeperLookup_click

// ----------------------------------------------------------------------------





// ----------------------------------------------------------------------------

// txtTimekeeper_change

// Description: event handler for timekeeper textbox change event

// Arguments: none

// Dependencies:

//    window.strTkprMask (masking.js)

//    trim

//    ApplyMask (masking.js)

//    TimekeepLookup (lookup.js)

//    UpdateElementText (shared.js)

//

function txtTimekeeper_change()

   {



   var strTimekeepName = '';

   var objXMLDOM;

   var nodField;

 

   // trim input

   this.value = this.value.trim();

  

   // if there was input

   if ( this.value.length > 0 )

      {



      // apply timekeep mask to textbox input

      ApplyMask( this, window.strTkprMask );



      // if inputted timekeeper is different from current timekeeper

      if ( this.lastvalue != this.value )

         {



         // request timekeeper information

         objXMLDOM = TimekeepLookup( this.value );



         // if XML DOM object returned

         if ( typeof objXMLDOM == 'object' )

            {



            // get field values

            nodField = objXMLDOM.selectSingleNode('//lastname');

            if (nodField != null)

               strTimekeepName = nodField.firstChild.nodeValue;



            nodField = objXMLDOM.selectSingleNode('//firstname');

            if (nodField != null)

               strTimekeepName = strTimekeepName + ', ' + nodField.firstChild.nodeValue;



            // update timekeeper

            this.lastvalue = this.value;



            }

         // if XML DOM object wasn't returned

         else

            {



            // indicate timekeeper was not found

            strTimekeepName = 'Timekeep \'' + this.value + '\' was not found.';



            // update timekeeper

            this.lastvalue = '';



            }



         // update document

         UpdateElementText( this.label, strTimekeepName );



         }



      }

   // if there was no input

   else

      {



      // clear the timekeep and display

      this.lastvalue = '';

      UpdateElementText( this.label, '' );



      }



   // process default action

   return true;



   }

//

// txtTimekeeper_change

// ----------------------------------------------------------------------------

Javascript: Preventing Form Submit When Enter is Pressed

Will_Rickards (Will Rickards) — Thu, 13 Jul 2006 20:48:33 GMT

In a web page, normally pressing enter when any form element has focus will submit the form. This is often premature. When you have a checkbox selected you probably just want to check the checkbox, not submit the form. So I wrote some javascript to translate the enter into a tab when possible (IE) and prevent the form submit when translation isn't possible (non-IE).

First is the code in your onload event handler.
This sets up onkeydown if window.event is supported (IE) and onkeypress if not.



var frmRequest = document.getElementById('frmRequest');

if (frmRequest)

   {

   if (window.event)

      frmRequest.onkeydown = frmRequest_KeyDown;

   else

      frmRequest.onkeypress = frmRequest_KeyPress;

   }

Next are the actual event handlers.
Nothing really special here, just dealing with the different ways of accessing event properties.



// ----------------------------------------------------------------------------

// frmRequest_KeyDown

//

// Description: event handler for request form key down event

//    translates returns on option buttons to a tab

//    this works only for IE, the keypress event is used for other browsers

//

// Arguments : 

//    e - the event object

//

// Dependencies : none

//

// History :

// 2006.07.13 - WSR : adapted to this project

//

function frmRequest_KeyDown( e )

   {



   var numCharCode;

   var elTarget;

   var strType;



   // get event if not passed

   if (!e) var e = window.event;



   // get character code of key pressed

   if (e.keyCode) numCharCode = e.keyCode;

   else if (e.which) numCharCode = e.which;



   // get target

   if (e.target) elTarget = e.target;

   else if (e.srcElement) elTarget = e.srcElement;

                                              

   // if form input field

   if ( elTarget.tagName.toLowerCase() == 'input' )

      {



      // get type

      strType = elTarget.getAttribute('type').toLowerCase();



      // based on type

      switch ( strType )

         {

         case 'checkbox' :

         case 'radio' :

         case 'text' :



            // if this is a return - change to tab

            if ( numCharCode == 13 )

               {

               if (e.keyCode) e.keyCode = 9;

               else if (e.which) e.which = 9;

               }



            break;

            

         }



      }



   // process default action

   return true;



   }

//

// frmRequest_KeyDown

// ----------------------------------------------------------------------------





// ----------------------------------------------------------------------------

// frmRequest_KeyPress

//

// Description: event handler for request form key press event

//    cancels returns on form elements that would prematurely submit the form

//

// Arguments : 

//    e - the event object

//

// Dependencies : none

//

// History :

// 2006.07.13 - WSR : adapted to this project

//

function frmRequest_KeyPress( e )

   {



   var numCharCode;

   var elTarget;

   var strType;



   // get event if not passed

   if (!e) var e = window.event;



   // get character code of key pressed

   if (e.keyCode) numCharCode = e.keyCode;

   else if (e.which) numCharCode = e.which;



   // get target

   if (e.target) elTarget = e.target;

   else if (e.srcElement) elTarget = e.srcElement;

                                              

   // if form input field

   if ( elTarget.tagName.toLowerCase() == 'input' )

      {



      // get type

      strType = elTarget.getAttribute('type').toLowerCase();



      // based on type

      switch ( strType )

         {

         case 'checkbox' :

         case 'radio' :

         case 'text' :



            // if this is a return

            if ( numCharCode == 13 )

               {

               // cancel event to prevent form submission

               return false;

               }



            break;

            

         }



      }



   // process default action

   return true;



   }

//

// frmRequest_KeyPress

// ----------------------------------------------------------------------------

Output DataSet as XML

Will_Rickards (Will Rickards) — Wed, 03 May 2006 09:18:51 GMT

I was having problems with my ajax lookups when the database fields returned weird characters like the copyright symbol. The responseXML would be blank. After many searches I found you need to output in utf-8 and add an xml declaration to the output that says it is utf-8. Here is the code below in vb.net for asp.net v1.1



   Private Sub OutputDataSetAsXML(ByRef dsSource As System.Data.DataSet)



      Dim xmlDoc As System.Xml.XmlDataDocument

      Dim xmlDec As System.Xml.XmlDeclaration

      Dim xmlWriter As System.Xml.XmlWriter



      ' setup response

      Me.Response.Clear()

      Me.Response.ContentType = "text/xml"

      Me.Response.Charset = "utf-8"

      xmlWriter = New System.Xml.XmlTextWriter(Me.Response.OutputStream, System.Text.Encoding.UTF8)



      ' create xml data document with xml declaration

      xmlDoc = New System.Xml.XmlDataDocument(dsSource)

      xmlDoc.DataSet.EnforceConstraints = False

      xmlDec = xmlDoc.CreateXmlDeclaration("1.0", "UTF-8", Nothing)

      xmlDoc.PrependChild(xmlDec)



      ' write xml document to response

      xmlDoc.WriteTo(xmlWriter)

      xmlWriter.Flush()

      xmlWriter.Close()

      Response.End()



   End Sub

javascript image swap using DOM

Will_Rickards (Will Rickards) — Tue, 28 Feb 2006 04:31:16 GMT

Here is a javascript image swap that uses the DOM methods.
It uses them to support swapping images with different sizes.
Just changing the src property causes the swapped images to be distorted in IE.
This script uses replaceChild instead.



var blnDOMSUPPORT = (document.getElementById) ? true : false;

window.onload = window_load;



function window_load()

   {



   if ( blnDOMSUPPORT )

      {



      document.getElementById('icon1').onclick = swapimage;

      document.getElementById('icon2').onclick = swapimage;

      document.getElementById('icon3').onclick = swapimage;

      

      }

   else

      {



      document.images['icon1'].onclick = swapimage;

      document.images['icon2'].onclick = swapimage;

      document.images['icon3'].onclick = swapimage;



      }



   }



function swapimage()

   {

   

   if ( blnDOMSUPPORT )

      {



      var imgMain = document.getElementById('mainimage');

      var divParent;



      // create new image element

      var imgNew = document.createElement('img');



      // give it an image

      // here I use the src of the icon just for convenience

      // you'd want to somehow determine the src of your desired image

      imgNew.src = this.src;

   

      // give it an id

      imgNew.id = 'mainimage';



      // replace image

      divParent = imgMain.parentNode;

      divParent.replaceChild(imgNew, imgMain);



      }

   else

      {



      // old school image swap

      document.images['mainimage'].src = this.src;



      alert( 'old school' );



      }

   

   }

It would be used with HTML like this

splitting a text list in sql

Will_Rickards (Will Rickards) — Fri, 24 Feb 2006 04:12:02 GMT

This goes along with my integer split procedure.
http://www.bigbold.com/snippets/posts/show/774

Often times I have a list of integers I need to pass to the database to get worked on. Such as checkboxes on a web page or some other list. I needed some TSQL that would take a text string and split it by a separator, in this case a comma. The following is the result of that need.
The way I normally use it is in a stored procedure like the one below with several text type arguments. This is a variation designed to split a list of strings separated by a special character sequence. Image two lists, one of the ids and one of the data. You parse the first list to get a table of the ids and you parse the second list to get the data and insert/update as appropriate.
http://www.bigbold.com/snippets/posts/show/774



IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitTextList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

   DROP PROCEDURE [dbo].[uspSplitTextList]

GO

                                      

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO





/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

-- uspSplitTextList

--

-- Description:

--		splits a separated list of text items and returns the text items

--

-- Arguments:

--		@list_text				- list of text items

--		@Delimiter				- delimiter

--

-- Notes:

-- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks

--

-- History:

-- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary

--

CREATE PROCEDURE uspSplitTextList

	@list_text				text,

   @Delimiter				varchar(3)

AS



SET NOCOUNT ON



DECLARE @InputLen			integer			-- input text length

DECLARE @TextPos			integer			-- current position within input text

DECLARE @Chunk				varchar(8000)	-- chunk within input text

DECLARE @ChunkPos			integer			-- current position within chunk

DECLARE @DelimPos			integer			-- position of delimiter

DECLARE @ChunkLen			integer			-- chunk length

DECLARE @DelimLen			integer			-- delimiter length

DECLARE @ItemBegPos		integer			-- item starting position in text

DECLARE @ItemOrder		integer			-- item order in list

DECLARE @DelimChar		varchar(1)		-- first character of delimiter (simple delimiter)



-- create table to hold list items

-- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied

CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )



-- process list

IF @list_text IS NOT NULL

   BEGIN



	-- initialize

   SET @InputLen = DATALENGTH(@list_text)

   SET @TextPos = 1

	SET @DelimChar = SUBSTRING(@Delimiter, 1, 1)

	SET @DelimLen = DATALENGTH(@Delimiter)

   SET @ItemBegPos = 1

   SET @ItemOrder = 1

   SET @ChunkLen = 1



   -- cycle through input processing chunks

   WHILE @TextPos <= @InputLen AND @ChunkLen <> 0

      BEGIN



      -- get current chunk

      SET @Chunk = SUBSTRING(@list_text, @TextPos, 8000)



      -- setup initial variable values

      SET @ChunkPos = 1

      SET @ChunkLen = DATALENGTH(@Chunk)

      SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)



      -- loop over the chunk, until the last delimiter

      WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0

         BEGIN



			-- see if this is a full delimiter

         IF SUBSTRING(@list_text, (@TextPos + @DelimPos - 1), @DelimLen) = @Delimiter

            BEGIN



				-- insert position

	         INSERT INTO #list_items (item_order, item_begpos, item_endpos)

	         VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)

	         

	         -- adjust positions

	         SET @ItemOrder = @ItemOrder + 1

	         SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen

	         SET @ChunkPos = @DelimPos + @DelimLen



				END

         ELSE

            BEGIN



            -- adjust positions

            SET @ChunkPos = @DelimPos + 1



            END

      

         -- find next delimiter      

         SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)



         END



      -- adjust positions

      SET @TextPos = @TextPos + @ChunkLen



      END



	-- handle last item

   IF @ItemBegPos <= @InputLen

      BEGIN



      -- insert position

      INSERT INTO #list_items (item_order, item_begpos, item_endpos)

      VALUES (@ItemOrder, @ItemBegPos, @InputLen)



      END



	-- delete the bad items

   DELETE FROM #list_items

   WHERE item_endpos < item_begpos



   -- return list items

	SELECT SUBSTRING(@list_text, item_begpos, (item_endpos - item_begpos + 1)) AS item_text, item_order, item_begpos, item_endpos

   FROM #list_items

   ORDER BY item_order



   END



DROP TABLE #list_items



RETURN



/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */



GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO

work days between two dates without cycling through dates

Will_Rickards (Will Rickards) — Thu, 13 Oct 2005 23:24:13 GMT

I was thinking about how to optimize figuring out the work days between two dates and came up with this function. It doesn't take into account holidays. You would have to take out the workdays for holidays from the number if you want to take into account holidays, but that should be easy enough.

4GL Version

 

# workdays

# returns the number of working days between two dates

FUNCTION workdays( dt_begin, dt_end )



DEFINE

   dt_begin             DATE,

   dt_end               DATE,

   dt_first_sunday      DATE,

   dt_last_saturday     DATE,

   int_workdays         INTEGER



   # get first sunday

   LET dt_first_sunday = dt_begin + ((7 - WEEKDAY(dt_begin)) MOD 7)



   # get last saturday

   LET dt_last_saturday = dt_end + ((-1 * (WEEKDAY(dt_end) + 1)) MOD 7)



   # get work weeks between first sunday and last saturday

   LET int_workdays = (((dt_last_saturday - dt_first_sunday) + 1) / 7) * 5

   

   # if first sunday is not begin date

   IF dt_first_sunday <> dt_begin THEN



      # assume first sunday is after begin date

      # add workdays from begin date to first sunday

      LET int_workdays = int_workdays + (6 - WEEKDAY(dt_begin))



   END IF



   # if last saturday is not end date

   IF dt_last_saturday <> dt_end THEN



      # assume last saturday is before end date

      # add workdays from last saturday to end date

      LET int_workdays = int_workdays + WEEKDAY(dt_end)  

  

   END IF



   # return working days

   RETURN int_workdays



END FUNCTION

VBA Version



' WorkDays

' returns the number of working days between two dates

Public Function WorkDays(ByVal dtBegin As Date, ByVal dtEnd As Date) As Long



   Dim dtFirstSunday As Date

   Dim dtLastSaturday As Date

   Dim lngWorkDays As Long



   ' get first sunday in range

   dtFirstSunday = dtBegin + ((8 - Weekday(dtBegin)) Mod 7)



   ' get last saturday in range

   dtLastSaturday = dtEnd - (Weekday(dtEnd) Mod 7)



   ' get work days between first sunday and last saturday

   lngWorkDays = (((dtLastSaturday - dtFirstSunday) + 1) / 7) * 5



   ' if first sunday is not begin date

   If dtFirstSunday <> dtBegin Then



      ' assume first sunday is after begin date

      ' add workdays from begin date to first sunday

      lngWorkDays = lngWorkDays + (7 - Weekday(dtBegin))



   End If



   ' if last saturday is not end date

   If dtLastSaturday <> dtEnd Then



      ' assume last saturday is before end date

      ' add workdays from last saturday to end date

      lngWorkDays = lngWorkDays + (Weekday(dtEnd) - 1)



   End If



   ' return working days

   WorkDays = lngWorkDays



End Function