Adding Columns to a DataTable

Here is how to add columns to a datatable.  You can also set the Ordinal Position when you add it, to rearrange to order of the columns.

   If the datatable has been attached to a datagridview and then you add the columns, you need to set the datagridview.datasource=null and the refresh the grid. Then add the columns and reattach the datatable to the gridview and refresh it.

   If you do not disconnect and reconnect, the new fields will show up, but they will appear at the end of the display.


[code]
       public void AddCertColumns(DataTable vdt)
        {
            if (!vdt.Columns.Contains("Certificate"))
            {
                vdt.Columns.Add("Certificate", Type.GetType("System.String")).SetOrdinal(0);
            }
            if (!vdt.Columns.Contains("BatchNum"))
            {
                vdt.Columns.Add("BatchNum", Type.GetType("System.String")).SetOrdinal(1);
            }
            if (!vdt.Columns.Contains("CancelEntryDate"))
            {
                vdt.Columns.Add("CancelEntryDate", Type.GetType("System.String")).SetOrdinal(2);
            }

        }
[/code]

SISendMail using *user in an email field in the web.config

    I added the option to specify *user as an email address in the rptEmailConf procedures. The routines will see *user and translate the email address to the current user.
    To utilize this feature you must specify the SISendMail.UserEmail before sending the email.  The programs use the UserEmail to substitute for the *user at the appropriate time.

   This will allow programs to have entries in the web.config file, but to send to the user invoking the program without having to specify a distribution list. If a distribution list is needed in the future, the web.config can be modified with the email entries without having to modify the report program.

Handling updates on a Form When Threads Are Involved

[code]
  Delegate Sub SetTextCallback(ByVal [text] As String)

    Private Sub ThreadProcSafe(ByVal vMessage As String, Optional ByVal vMessage2 As String = "")

        ' Check if this method is running on a different thread
        ' than the thread that created the control.
        If lstActions.InvokeRequired Then
            ' It's on a different thread, so use Invoke.
            Dim d As New SetTextCallback(AddressOf SetText)
            Me.Invoke(d, New Object() {Now.ToString() + "-" + vMessage + " " + vMessage2})
        Else
            ' It's on the same thread, no need for Invoke.
            lstActions.Items.Add(Now.ToString() + "-" + vMessage + " " + vMessage2)
        End If
    End Sub
    Private Sub SetText(ByVal vMessage As String)
        lstActions.Items.Add(vMessage)
    End Sub

[/code]

     There are issues when trying to update a form from a delegate or a different thread in a program. To access the form fields, you need to use something like the code above to make it "threadsafe" so that it will work.

My original program that uses this technique is the FolderWatcher project.

Handling Errors with MYSQL replication where we want to bypass a few events that cause replication to stop

   I have run into situations where I have set up some data on a replicated server, that only relates to that server, but causes replication to stop if the main server tries to update data that is not on the replicated server.

   To check to slave status;
   Go to the console and enter
show slave status;

   This will show the state of the slave.


If you need to bypass and event in the replication log, you can use this statement to bypass a record or number of records:

SET GLOBAL sql_slave_skip_counter = N 

Issue this command and then use this command to restart the slave:

start slave;

show slave status;

Repeat as many times as needed to bypass the records that cause a problem.

C# Code Behind not accessing controls in ASP .NET

  I had an issue where I would add controls to my aspx file but the control was not visible, hence not usable, from the code behind page. I noticed there was not a .aspx.designer.cs file. I right clicked on the aspx file and chose the "Convert To Web Application". This generated the .aspx.designer.cs file and gave me access to the controls on the aspx page from the code behind code.
   So look to see if you have a .aspx.designer.cs file and regenerated it if needed.

MYSQL AutoIncrement reset after restart

     I found out that the autoincrement values can change on a restart of the service.  This affected the claims checkprinting table, because it is cleared after the AP process runs, so on restart, there are no records and the autoincrement is set to 1.
   I found an option, init-file in the my.ini file, that can be set to execute a start up file when the MYSQL service starts. So I created a SQL file that updates the autoincrement value from the checkprintinghistory file.

use claim;
select max(processkey) into @maxProcessKey from checkprintinghistory;
insert into checkprinting (uniquekey,iservicer,isprocessed,isapplied) values(@maxProcesskey,2,1,1);
delete from checkprinting where uniquekey = @maxProcesskey;

  Now on restart, this init program finds the highest value and resets the autoincrement value on checkprinting to the correct value.





   

Changing a label immediately on a page with Javascript

    I was trying to give status info to users after they had selected one of many options they had available to them, by creating a Status Label and updating it with the action they were performing. i.e.  "Processing File", "Validating File", etc.  However, the label would not update immediately, but only after the processing had occurred. Of course, this was not good.
   I found that I needed to set a timeout, so the browser would have a chance to update the label with the new message.

[code]

       function callProcessValidate(file)
        {
            setStatus("Validating " + file);
            window.setTimeout("ProcessValidate('" + file + "');", 100);
           
        }
       
        function ProcessValidate(file)
        {

             (do some processing)

            setStatus("");  // reset status label back
           
            alert(xmlProcess.responseText);
        }

        function setStatus(vMessage)
        {
            var label;

            label = document.getElementById("lblStatus");
            label.innerText = vMessage;
        }
[/code]

   This will update the label, call the timeout which gives the browser a chance to update the label, and then call the function to do the actual work.

Overriding IE Compatibility mode in ASP.NET code

   We have had to set Compatibility mode for some of our programs to work in IE 8-10.  However, some of our newer programs do not work correctly in Compatibility mode. Adding this code to the .aspx file will force Compatibility mode to OFF for this particular program, thus having the program display correctly while Compatibility Mode is turned on in the browser.

[code]

<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />

[/code]

Using Custom Configuration Sections in .Net client app

     I have a custom configuration I use in my ASP .Net programs that I wanted to use in some client programs.  However, when I tried to use it, I would get an error "Could not load configuration".  I found I needed to add one thing to my SectionGroup/Section Name entry:
[code]

      <sectionGroup name="ReportEmails" >
        <section name="ReportTest" type="SICommon.rptEmailConf,SICommon" allowExeDefinition="MachineToLocalUser" requirePermission="false" />
      </sectionGroup>

[/code]

   The key piece is on the type definition. I needed to add the SICommon after SICommon.rptEmailConf.   This specifies the assembly name containing the custom configuration.  Without it specified, the system looked in the System.Configuration assembly and could not find it.

   

Listing Auto_Increment values in MYSQL Databases

  Here is a query to get the auto_increment values for tables in your databases.

[code]

 SELECT
table_schema,
table_name,
AUTO_INCREMENT,
table_rows,
information_schema.tables.*
FROM information_schema.TABLES
where
table_type = "base table"
and AUTO_INCREMENT is not null
and table_schema not like "%umbraco%"
and table_schema not like "%club"
and table_schema not like "%wi"

[/code]

Using IComparable to sort a list

     I had a list of objects that I used to attach to a datagridview to display. I wanted to be able to sort by clicking on the header field, but this list did not use the BindingList interface, so the datagridview mechanisms would not do the sorting for me. So I looked around and decided I would re-sort the array and then reattach it to the datagridview manually to get the sorts I wanted.
  I found a number of examples of how to do this on the web, but I ran into errors when I tried to implement it. I found that my definition of the list was causing the problem

I had defined my list as:

  dim myLogLines as List(Of LogLinesClass)

When I redefined mylogline to this, everything started working with the IComparable sort routines:

dim myLogLines as ArrayList

So I changed from a List(Of LogLinesClass) to just an ArrayList.



   [Code]

Imports System.Text.RegularExpressions
Imports System.Collections

Public Class LogFileClass
    Implements IComparable

    Dim sRegExp As String
    Dim myReCol As List(Of Regex) = New List(Of Regex)

    Dim myRegEx As Regex

    Function CompareTo(ByVal obj As Object) As Integer Implements IComparable.CompareTo
        Dim c As LogFileClass = CType(obj, LogFileClass)

        Return String.Compare(Me.Program, c.Program)
    End Function

    Private Class sortUserAscendingHelper : Implements IComparer
        Function Compare(ByVal a As Object, ByVal b As Object) As Integer Implements IComparer.Compare
            Dim c1 As LogFileClass = CType(a, LogFileClass)
            Dim c2 As LogFileClass = CType(b, LogFileClass)

            'Dim result As Integer
            'result = String.Compare(c1.Program, c2.Program)

            'Return result

            If (c1.User > c2.User) Then
                Return 1
            End If

            If (c1.User < c2.User) Then
                Return -1
            Else
                Return 0
            End If
        End Function
    End Class

    Private Class sortUserDescendingHelper : Implements IComparer
        Function Compare(ByVal a As Object, ByVal b As Object) As Integer Implements IComparer.Compare
            Dim c1 As LogFileClass = CType(a, LogFileClass)
            Dim c2 As LogFileClass = CType(b, LogFileClass)

            'Dim result As Integer

            'result = String.Compare(c2.Program, c1.Program)

            'Return result

            If (c1.User < c2.User) Then
                Return 1
            End If

            If (c1.User > c2.User) Then
                Return -1
            Else
                Return 0
            End If
        End Function
    End Class

    Private Class sortProgramDescendingHelper : Implements IComparer
        Function Compare(ByVal a As Object, ByVal b As Object) As Integer Implements IComparer.Compare
            Dim c1 As LogFileClass = CType(a, LogFileClass)
            Dim c2 As LogFileClass = CType(b, LogFileClass)
            'Return String.Compare(c2.Program, c1.Program)

            If (c1.Program < c2.Program) Then
                Return 1
            End If

            If (c1.Program > c2.Program) Then
                Return -1
            Else
                Return 0
            End If
        End Function
    End Class
    Private Class sortElapsedAscendingHelper : Implements IComparer
        Function Compare(ByVal a As Object, ByVal b As Object) As Integer Implements IComparer.Compare
            Dim c1 As LogFileClass = CType(a, LogFileClass)
            Dim c2 As LogFileClass = CType(b, LogFileClass)

            'Dim result As Integer
            'result = String.Compare(c1.Program, c2.Program)

            'Return result

            If (c1.Elapsed > c2.Elapsed) Then
                Return 1
            End If

            If (c1.Elapsed < c2.Elapsed) Then
                Return -1
            Else
                Return 0
            End If
        End Function
    End Class

    Private Class sortElapsedDescendingHelper : Implements IComparer
        Function Compare(ByVal a As Object, ByVal b As Object) As Integer Implements IComparer.Compare
            Dim c1 As LogFileClass = CType(a, LogFileClass)
            Dim c2 As LogFileClass = CType(b, LogFileClass)

            'Dim result As Integer
            'result = String.Compare(c1.Program, c2.Program)

            'Return result

            If (c1.Elapsed > c2.Elapsed) Then
                Return -1
            End If

            If (c1.Elapsed < c2.Elapsed) Then
                Return 1
            Else
                Return 0
            End If
        End Function
    End Class

    Public Shared Function sortUserAscending() As IComparer
        Return CType(New sortUserAscendingHelper(), IComparer)
    End Function

    Public Shared Function sortUserDescending() As IComparer
        Return CType(New sortUserDescendingHelper(), IComparer)
    End Function

    Public Shared Function sortProgramDescending() As IComparer
        Return CType(New sortProgramDescendingHelper(), IComparer)
    End Function
    Public Shared Function sortElapsedAscending() As IComparer
        Return CType(New sortElapsedAscendingHelper(), IComparer)
    End Function
    Public Shared Function sortElapsedDescending() As IComparer
        Return CType(New sortElapsedDescendingHelper(), IComparer)
    End Function

[/CODE]

code to call and sort the arraylist

[CODE]

   Private Sub SortDGV(ByVal vFieldName As String, ByVal vSortOrder As ListSortDirection)

        If vFieldName.ToLower = "user" Then

            If vSortOrder = ListSortDirection.Ascending Then

                myLogLines.Sort(LogFileClass.sortUserAscending)

            Else

                myLogLines.Sort(LogFileClass.sortUserDescending)

            End If

        ElseIf vFieldName.ToLower = "program" Then

            If vSortOrder = ListSortDirection.Ascending Then

                myLogLines.Sort()

            Else

                myLogLines.Sort(LogFileClass.sortProgramDescending)

            End If

        ElseIf vFieldName.ToLower = "elapsed" Then

            If vSortOrder = ListSortDirection.Ascending Then

                myLogLines.Sort(LogFileClass.sortElapsedAscending)

            Else

                myLogLines.Sort(LogFileClass.sortElapsedDescending)

            End If

        Else

            If vSortOrder = ListSortDirection.Ascending Then

                myLogLines.Sort(New SimpleComparer(vFieldName, SortOrder.Ascending))

            Else

                myLogLines.Sort(New SimpleComparer(vFieldName, SortOrder.Descending))

            End If

        End If

        dgvLogLines.DataSource = myLogLines

        dgvLogLines.Refresh()

    End Sub

[/CODE]

Checking if program is running in a interactive environment

        I had a class object that used MSGBOX to display an error message.  I ran into a situation where this routine was used in a Web application, and when the error was encountered, it had no where to display the message, because it was running on a server and not on the desktop.
     So, I could have just let the system generate the exception, but this would cause problems with other programs that already had been written and did not have the necessary logic to trap the error. So, I found there is an environment variable that can be checked to determine if the program is running in an interactive state. If it is running in an interactive state, you can use the MSGBOX function. And, if it not running in an interactive environment, you can let the object generate an exception

This is the variable to check:
Environment.UserInteractive



If Environment.UserInteractive Then
msgbox("ERROR")
else
   throw ex
end if

ASP.Net Controls Visible = False Problem

   If you have a control, such as a label, that you set the "VISIBLE" property to false in the designer, then the control is not rendered to the client. This means you cannot use javascript to manipulate the control, because it is not there. So, if you want to make a label visible that has the visible property set to false in the designer, you cannot do it directly.
    In order to have the control rendered, use the style attribute: display:none to make the control invisible on startup. Then, you can use javascript to set the element.style.display="inline" to make it visible when the program is running.

    Just a little annoyance until you figure out how to get around it.

Working with UpdatePanels and javascript

     I created some file maintenance programs that utilized the Updatepanel functionality on the forms. It was necessary to hook up some javascript to get the functionality I wanted in the program. So look at the fmCarrierDealerGroup program to see how I did it.
   While utilizing the updatepanel, I found that I needed to hook up an event handler to get control after ASP.net did its own updates.  I could then do what I needed to do in Javascript in order to manipulate and highlight gridview selectors and other things.

Crystal Reports : Turning Off Snap to Grid

This is something that occasionally drives me nuts. It is very typical to have a report that mimics the look of a spreadsheet (you actually just format each field to have a Border around it like cells in Excel). However, arranging precise items like individual rows and columns may not be the easiest thing in the world. What you usually get is one or two items that for whatever reason don't want to play nice and line up perfectly.

The first thing I learned to do was move Report items by using my arrow keys, not the mouse. Select any Crystal object and try it. You'll feel a lot more control over how things line up this way.

The second thing I learned to do was occasionally turn off my Snap. Simply right-click within the designer and select (de-select, actually) "Snap to Grid". When Snap is turned off, you'll find that you can now move your objects with the finest precision. Don't forget to turn Snap back on before you add any new objects to the Layout; they will behave erratically once Snap is turned back on. A good rule of thumb is to Design with Snap On, Fix with Snap Off.

These two tips can save you a lot of grief and aggravation, especially when modifying cosmetically complex reports. (Adding a column in to stack of columns, etc.)

MYSQL Location of Data Files

  You can look in this file to find which directories are used by MYSQL, including the physical path for the data files.

Go into your C:\Program Files\MySQL\MySQL Server 5.5\my.ini and find the variable "datadir". This is your Data dir ;)