Archive for category Uncategorized

Email Excel Spreadsheet as Email Body Issues

Hello all. I had a production manager wanting an excel spreadsheet mailed as the body of the email. As some of you know the code generated by excel to produce the email is pretty crazy. But as a result, it showed up fine in Outlook and Android but it did not show the gridlines on the spreadsheet. So this code is based on the excellent work by Ron DeBruin over at http://www.rondebruin.nl/win/s1/outlook/bmail3.htm . I did a replacement for the HTML Range in this manner and the grid lines did appear. And the manager was happy.

Sub Mail_Selection_Range_Outlook_Body()
‘For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
‘Don’t forget to copy the function RangetoHTML in the module.
‘Working in Excel 2000-2016
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
‘MsgBox Cells(5, 9).Value
    Set rng = Nothing
    On Error Resume Next
    ‘Only the visible cells in the selection
    Set rng = Selection.SpecialCells(xlCellTypeVisible)
    ‘You can also use a fixed range if you want
    ‘Set rng = Sheets(“YourSheet”).Range(“D4:D12”).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox “The selection is not a range or the sheet is protected” & _
               vbNewLine & “please correct and try again.”, vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .BodyFormat = olFormatHTML
        .To = “you@you.com”       
           
               
       
         .CC = “”
        .BCC = “”
        .Subject = “Testing Purchase Order Email To Steve”
        .HTMLBody = RangetoHTML(rng)
        Replace .HTMLBody, “border-left:none”, “border-left:solid;border-width: 1px;border-color:black”
        Replace .HTMLBody, “border-right:none”, “border-right:solid;border-width: 1px;border-color:black”
        Replace .HTMLBody, “border-bottom:none”, “border-bottom:solid;border-width: 1px;border-color:black”
        Replace .HTMLBody, “border-top:none”, “border-bottom:solid;border-width: 1px;border-color:black”
        .Send
         ‘or use .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Leave a comment

Class to Simplify and Minimize Code for Stored Procedures and TSQL

First of all if you are using TSQL in your code you really need to get in a another line of work. It isn’t scalable, difficult to maintain and is generally a bad practice. But I know some of you still do it so I did include it. This class will allow you to in a few lines of code do your select, update, insert or delete statements in a very few lines of code. All that is required of the developer is to list the values in order (if you are using stored procedures) that are required. Also included is how to declare a new instance of a sqlparametercollection – which you aren’t suppose to be able to do. I ended up not needing it but included it just in case someone needs to do that someday. Also if you are using a strongly typed dataset there is a method included of how to do that. After the code is example of usage. Have a great day!

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;

using System.IO;

using System.Data.SqlClient;

public class clsData
{

    public SqlConnection sqlconn = new SqlConnection();
    public string Error_Message;
    public List<SqlParameter> ParamList = new List<SqlParameter>();
    public ArrayList ParamValues = new ArrayList();

    public int SQLExecuteNonQueryValue;
    public clsData()
   

    public enum SQLAction
    {
        SelectAction,
        UpdateInsertDeleteAction
    }

    public virtual DataTable DataAction(clsData.SQLAction Action, DataTable dt, string cmdtext, string ParamValues = “”)
    {
        SQLExecuteNonQueryValue = -1;
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = cmdtext;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = sqlconn;
        this.GetParameterList(cmdtext);
        if (!string.IsNullOrEmpty(Error_Message)) {
            return null;
        }
        if (!string.IsNullOrEmpty(ParamValues)) {
            this.ParseSQLParameterValues(ParamValues);
            for (i = 0; i <= this.ParamList.Count – 1; i++) {
                cmd.Parameters.AddWithValue(this.ParamList[i].ToString(), this.ParamValues[i].ToString());
            }
        }
        //Debug.WriteLine(cmd.CommandText)
        try {
            cmd.Connection.Open();
            switch (Action) {
                case SQLAction.SelectAction:
                    dt.Load(cmd.ExecuteReader());
                    break;
                case SQLAction.UpdateInsertDeleteAction:
                    //Check this value to make sure everything went ok
                    SQLExecuteNonQueryValue = cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    return null;
            }
            cmd.Connection.Close();

        } catch (Exception ex) {
            Error_Message = “There was a problem with ” + cmdtext + “. “;
            return null;
        }

        //removing duplicate table from dataset
        for (i = 0; i <= ds_Copy.Tables.Count – 1; i++) {
            if (ds_Copy.Tables(i).TableName == dt.TableName) {
                ds_Copy.Tables(i).Clear();
                ds_Copy.Tables(i).Columns.Clear();
            }
        }

        //placing datatable in the dataset
        ds_Copy.Merge(dt, false, MissingSchemaAction.Add);
        Error_Message = “”;
        return dt;
    }

    public DataTable DataActionTSQL(clsData.SQLAction Action, DataTable dt, string sqlstring)
    {
        SQLExecuteNonQueryValue = -1;
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = sqlstring;
        cmd.Connection = sqlconn;
        try {
            cmd.Connection.Open();
            switch (Action) {
                case SQLAction.SelectAction:
                    dt.Load(cmd.ExecuteReader());
                    break;
                case SQLAction.UpdateInsertDeleteAction:
                    //Check this value to make sure everything went ok
                    SQLExecuteNonQueryValue = cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    return null;
            }
            cmd.Connection.Close();

        } catch (Exception ex) {
            Error_Message = “There was a problem with ” + sqlstring + “. “;
            return null;
        }
        //removing duplicate table from dataset
        for (i = 0; i <= ds_Copy.Tables.Count – 1; i++) {
            if (ds_Copy.Tables(i).TableName == dt.TableName) {
                ds_Copy.Tables(i).Clear();
                ds_Copy.Tables(i).Columns.Clear();
            }
        }
        //placing datatable in the dataset
        ds_Copy.Merge(dt, false, MissingSchemaAction.Add);
        Error_Message = “”;
        return dt;
    }

    private List<SqlParameter> GetParameterList(string ProcName)
    {
        //Get the parameters for the selected stored procedure
        List<SqlParameter> inputParamList = new List<SqlParameter>();
        ParamList.Clear();

        using (SqlConnection cn = new SqlConnection()) {
            DispatcherTool.My.MySettings Settings = new DispatcherTool.My.MySettings();
            cn.ConnectionString = Settings.SRAConnectionString;
            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = cn;
            myCommand.CommandText = ProcName;
            myCommand.CommandType = System.Data.CommandType.StoredProcedure;
            try {
                cn.Open();
                SqlCommandBuilder.DeriveParameters(myCommand);
                cn.Close();
            } catch (Exception ex) {
                Error_Message = “There was a problem with the connection to the database.”;
                return null;
            }

            //Dim sqlparams As SqlParameterCollection = DirectCast(GetType(SqlParameterCollection).GetConstructor(BindingFlags.NonPublic Or BindingFlags.Instance, Nothing, Type.EmptyTypes, Nothing).Invoke(Nothing), SqlParameterCollection)

            foreach (SqlParameter param in myCommand.Parameters) {
                if (param.Direction == System.Data.ParameterDirection.Input || param.Direction == System.Data.ParameterDirection.InputOutput) {
                    //sqlparams.Add(param.ParameterName & ” – ” & param.SqlDbType.ToString())
                    //Debug.WriteLine(param.ParameterName & ” – ” & param.SqlDbType.ToString())
                    inputParamList.Add(param);
                    //Else
                    // sqlparams.Add(param.ParameterName & ” – ” & param.SqlDbType)
                    //Debug.WriteLine(param.ParameterName & ” -2 ” & param.SqlDbType)
                }
            }
        }
        this.ParamList = inputParamList;
        Error_Message = “”;
        return this.ParamList;
    }

    public void ParseSQLParameterValues(string ValueString)
    {
        ParamValues.Clear();
        string[] parts = ValueString.Split(new char[] { ‘,’ });
        string part = null;
        foreach (string part_loopVariable in parts) {
            part = part_loopVariable;
            ParamValues.Add(part);
        }
    }

    //’Public Shared Function ConvertToTypedDataTable(Of T As {Data.DataTable, New})(ByVal dtBase As Data.DataTable) As T
    //’    Dim dtTyped As New T
    //’    dtTyped.Merge(dtBase)
    //’    Return dtTyped
    //’End Function

}

And then it’s use…..

private void Button1_Click(System.Object sender, System.EventArgs e)
{
    Strongly_Typed_DataSet.usp_Your_Stored_Procedure dt = new Strongly_Typed_DataSet.usp_Your_Stored_Procedure();

    DataGridView1.DataSource = cn.DataAction(clsData.SQLAction.SelectAction, dt, ds.usp_Get_PrinterName_for_PickList_Printing.ToString, “Tubing”);

    //DataGridView1.DataSource = cn.DataActionTSQL(clsData.SQLAction.SelectAction, dt, “SELECT *FROM Your_Table”)

}


Join me on Facebook

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Leave a comment

Get CPU Usage of a Process using C#

This was a fun assignment. I was asked to write a piece of code that when a particular process id was passed to it it would return its CPU usage. WMI is fun. Have a great night….

public static decimal GetUssage(string pid)
{
//get the process
ManagementObjectSearcher searcher = new ManagementObjectSearcher(“SELECT * FROM Win32_Process WHERE ProcessID = ” + pid);
decimal PercentProcessorTime = 0;
foreach (ManagementObject queryObj in searcher.Get())
{
DateTime firstSample, secondSample;

//populate the process info
firstSample = DateTime.Now;
queryObj.Get();
//get cpu usage
ulong u_oldCPU = (ulong)queryObj.Properties[“UserModeTime”].Value
+ (ulong)queryObj.Properties[“KernelModeTime”].Value;
//sleep to create interval
System.Threading.Thread.Sleep(1000);
//refresh object
secondSample = DateTime.Now;
queryObj.Get();
//get new usage
ulong u_newCPU = (ulong)queryObj.Properties[“UserModeTime”].Value
+ (ulong)queryObj.Properties[“KernelModeTime”].Value;

decimal msPassed = (decimal)(secondSample – firstSample).TotalMilliseconds;

//formula to get CPU ussage
if (u_newCPU > u_oldCPU)
PercentProcessorTime = (decimal)((u_newCPU – u_oldCPU)
/ (msPassed * 100 * Environment.ProcessorCount));

Console.WriteLine(“processor time ” + PercentProcessorTime);
}
return PercentProcessorTime;
}

, , , ,

Leave a comment

How to make Linq and IList sort Dynamically using C#

I apologize that it has been a while. I have been extremely busy and in the time that I have been away I went through treatment for a medical issue. Needless to say I had other issues to deal with other than this blog. But I will try to publish more frequently. Today’s topic was a question from another developer which was to have have a Linq and IList sort dynamically. So long as you don’t force execution by using ToList, you can keep tacking on to your query until you are ready to actually run it. This is because much of LINQ uses “deferred execution”. For example, you could do something similar to:

var query = apps.AsQueryable()
.Select
(Application => new
{
ID = Application.ApplicationId,
Force = Application.Force.Description,
Function = Application.BusinessFunction.Description,
Category = Application.Category.Description,
SubCategory = Application.SubCategory.Description,
Name = Application.ApplicationName,
Description = Application.ApplicationDescription
}
).Take(10);

switch (sortType)
{
case “ID”:
query = query.Orderby(Application => Application.ApplicationId);
break;
case “Force”:
query = query.Orderby(Application => Application.Force);
break;
}

IList tempList = query.ToList();

As always it has been great getting your all feedback while I was gone. Thank You! Smile

, , , , , , , , , , , , , , , , , , , , , , , , ,

Leave a comment

POST and HTTP Protocol Violation Resolution in vb.net

Good Morning! My daughter played volleyball last night and was once again outstanding. MY eldest daughter has been released. Unfortunately that has not gone so well. She is moving out of our house no later than next Monday and to be honest I am ok with that.

Today’s topic is one that has plagued me for a while and isn’t really a coded solution but a hack. I thought I would share it in case some of you run into the same problem. Basically we want to POST data to the server, and the server saves the file and its response is a tar archive. Making the request works fine. Where I ran into trouble was the saving of the binary response to a variable and eventually a file. First some of the code to give you an idea of what happened:


Join me on Facebook

‘ Download a single binary file from a server and save it to a local folder
    Public Sub DownloadAndSaveFile(ByVal Url As String, ByVal Filename As String, ByVal User As String, ByVal Pass As String, ByVal Debug As Integer)

        ‘ The post data template
        Dim PostdataTemplate As String = _
        "handler=SOME_URLENCODED_DATA"

        Dim PostdataArray As Byte() = Encoding.ASCII.GetBytes(Postdata)

        ‘ Create a new NetworkCredential object
        Dim NetworkCredential As New NetworkCredential(User, Pass)

        Try
            ‘ Create a new WebClient instance
            Dim myWebClient As New WebClient

            ‘ Set Preauthenticate property to true
            ‘myWebClient.PreAuthenticate = True

            ‘ Associate the NetworkCrbedential object with the ‘WebRequest’ object
            myWebClient.Credentials = NetworkCredential

            ‘ Add required HTTP headers to request
            myWebClient.Headers.Add("Accept", "*/*")
            myWebClient.Headers.Add("Content-Type", "application/x-www-form-urlencoded")

            ‘ UploadData method implicitly sets HTTP POST as the request method
            Dim responseArray As Byte() = myWebClient.UploadData(Url, PostdataArray)
            ‘ The response array as byte generates an exception!

        Catch Ex As Exception
            WriteLine("Error: " & Ex.Message)

        End Try
    End Sub

I get an exception when saving the response as a byte:

Error: The underlying connection was closed: The server committed an HTTP protocol violation.

The problem was that the .NET Framework detected the server did not comply with HTTP 1.1 RFC. This problem may occur when the response contains incorrect headers or incorrect header delimiters.

 

S0 what to do? I don’t have control of the production server so I can’t fix it on that end. So here is where the hack came in. I modified the app.exe.config file in the following way. You can also modify the machine.config file this way but don’t do that.I should note that I know that the server inserting a header with no name or value is against RFC2616.  But I don’t have the ability to modify the server’s response in the production environment. Make it a great day! 

——————————————————————
<configuration>
<system.net>
<settings>
<httpWebRequest useUnsafeHeaderParsing="true" />
</settings>
</system.net>
</configuration>
——————————————————————

 

Technorati Tags: ,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,,,,,,,,,,,
,,,,,,,,,,,,,,,

1 Comment

Refresh Treeview From Another Form with vb.net

Good Morning! Well Brett Favre is at it again. He has requested his release from the New York Jets but says he has no intentions of returning “at this time”. Is there any doubt he will be in a Vikings uniform once the season starts? I have just had it with him. The Vikings can have him.

Today’s topic was requested last night. Basically the user wants to update the treeview on another form after the data is updated. Its not complicated. Basically we add an event handler to the original form that handles the update. On the calling form we call that event when the update occurs. Make it a great day!


Join me on Facebook

 

Private Sub CallingForm_AfterUpdate(ByVal Sender As CallingForm, ByVal Item As Object)

        Dim nd As TreeNode

        nd = Me.SearchNodesForHierarchyItem(Me.mytreeview.Nodes, Item.ID)

        If Not nd Is Nothing Then
            nd.Text = Item.Name
            nd.EnsureVisible()
            Me.mytreeview.SelectedNode = nd
        Else
            Me.Refresh()
            nd = Me.SearchNodesForHierarchyItem(Me.mytreeview.Nodes, Item.ID)
            If Not nd Is Nothing Then
                nd.EnsureVisible()
                Me.mytreeview.SelectedNode = nd
            End If
        End If

    End Sub

Private Sub Refresh()

    Try

      Me.mytreeview.Nodes.Clear()

      If Me.cboTree.SelectedKey = 0 Then

        For Each h As Hierarchy In Hierarchy.TMHierarchies

          Dim n As New HierarchyNode(h.Name)

          n.ImageIndex = 0
          n.SelectedImageIndex = n.ImageIndex
          n.Tag = h
          mytreeview.Nodes.Add(n)

          If h.Children.Count > 0 Then
            n.Nodes.Add(New HierarchyItemNode)
          End If

          n.Expand()
        Next

      Else

        Dim h As Hierarchy = New Hierarchy(CInt(Me.cboTree.SelectedKey))

        Dim n As New HierarchyNode(h.Name)

        n.ImageIndex = 0
        n.SelectedImageIndex = n.ImageIndex
        n.Tag = h
        mytreeview.Nodes.Add(n)

        If h.Children.Count > 0 Then
          n.Nodes.Add(New HierarchyItemNode)
        End If

        n.Expand()

      End If

      If Me.mytreeview.GetNodeCount(False) > 0 Then Me.mytreeview.SelectedNode = Me.mytreeview.Nodes(0)

    Catch ex As Exception

    End Try

  End Sub

 

and on the calling form simly call after an update:

Public Event AfterUpdate(ByVal Sender As CallingForm, ByVal Item As Object)

Technorati Tags: ,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,
,,

1 Comment

Scroll Control Into View In Panel with vb.net

Good Morning! This weekend was pretty busy. In addition to my side work and work on my current projects we had meetings with people about the formation of another band, and making plans to do some work on my house this summer.

Today’s topic was asked of me and it can be a little frustrating I guess. Basically we want to scroll within a panel to a particular control within that panel and have that control come into view. Not complicated but it works. Make it a great day!


Join me on Facebook

Public Class Form1
    Public Class MyData
        Public Sub New(ByVal ctl As Control)
            Me.Ctl = ctl
        End Sub
        Public Ctl As Control
        Public Overrides Function ToString() As String
            Return Ctl.Name
        End Function
    End Class
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        For Each ctl As Control In Panel1.Controls
            If TypeOf ctl Is GroupBox Then
                ComboBox1.Items.Add(New MyData(ctl))
            End If
        Next
    End Sub
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim ctl As Control = DirectCast(ComboBox1.SelectedItem, MyData).Ctl
        Panel1.ScrollControlIntoView(ctl)
    End Sub
End Class

Technorati Tags: ,,,,,,,,,,,,,,,,,,
,,

Leave a comment