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>
------------------------------------------------------------------

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)

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: vb.net,.NET Framework,csharp,Scroll,Control,Into,View,Panel,Name,sender,System,Object,MyBase,Load,Controls,TypeOf,GroupBox,Items,
DirectCast,SelectedItem,ScrollControlIntoView

Good Morning! Hope all is well in your neck of the woods. I play bass for a band tonight as well as hopefully getting to catch my daughter in a volleyball game. So today’s topic is how to format xml with vb.net. When I was grabbing and displaying the XML string, it came without line breaks and such which made it difficult to read. So my solution is here. It isn’t elegant or cute but it does work. Make it a great day!


Join me on Facebook

Private Function ParseXml(ByVal xml As String) As String
        Dim builder As New System.Text.StringBuilder()
        Dim xmlString As String = xml.Replace(">", ">" & vbCrLf). _
                Replace("</", vbCrLf & "</").Replace(vbCrLf & vbCrLf, vbCrLf).Trim()
        Dim xmlStringArray As String() = xmlString.Split(vbCrLf)
        Dim indent As Integer = 0
        Dim length As Integer = 0
        Dim bracketCount As Integer = 0
        For Each line As String In xmlStringArray
            length += line.Length + 1
            If line.IndexOf("<?xml") = -1 AndAlso _
                length < xmlString.Length Then
                If line.IndexOf("<") > -1 Then bracketCount += 1
                If bracketCount > 1 Then
                    If line.IndexOf("</") > -1 Then
                        indent -= 2
                    Else
                        indent += 2
                    End If
                End If
            End If
            builder.AppendLine(Space(indent) & line.Trim())
            If line.Trim().PadRight(2).Substring(0, 2) = "</" Then _
                indent -= 2
        Next
        Return builder.ToString()
    End Function

Technorati Tags: vb.net,csharp,.NET Framework,XML,Format,builder,System,Text,StringBuilder,Replace,Trim,Split,Integer,length,IndexOf,AppendLine

Good Morning! It was a relaxing weekend of sorts. I figured out a SQL statement for work that was really being a pain in the butt and helped a friend out. Other than that, it was pretty relaxing.

Today’s topic is how to download an image into a byte array using vb.net. Make it a great day!


Join me on Facebook

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
    Dim client As New WebClient()
    Try
        Dim imagedata As Byte() = client.DownloadData("http://images2.wikia.nocookie.net/wowwiki/images/e/ed/Icon-new-48x48.png")
        Using ms1 As New MemoryStream(imagedata )
            Dim i As New Image(ms1)
            Me.Image= i
        End Using
    Catch ex As SystemException
        Debug.WriteLine(ex.Message)
    End Try
End Sub

Technorati Tags: vb.net,.NET Framework,csharp,Download,Image,Byte,Array,Object,EventArgs,client,WebClient,DownloadData,images,Icon,MemoryStream,Catch,
SystemException,WriteLine,Message

Good Morning! Not a lot going on here today. As I had mentioned previously, I joined Facebook and I did meet quite a few friends from days gone by. I will be having lunch with one on Saturday so it will be good to see him again. It should be interesting.

Today’s topic is to fill a Microsoft Word template from a datatable. The boss had wanted me to create a email solution which would allow users to drag (or appear to drag) fields into a Microsoft Word document. It had many other applications and this was one of them. Make it a great day!


Join me on Facebook

Dim objSqlDataSet As New DataSet
        Dim myDataTable As New DataTable("mydata")
        myDataTable.Columns.Add("name", GetType(String))
        myDataTable.Columns.Add("phone", GetType(String))
        myDataTable.Columns.Add("zusatz", GetType(String))
        Dim myArray(2) As Object
        For i As Integer = 0 To 100
            myArray(0) = "Name" & i
            myArray(1) = "Phone: " & i
            If i = 10 Or i = 20 Or i = 30 Or i = 40 Or i = 50 Or i = 60 Or i = 70 Then
                myArray(2) = "zusatz: " & i
            End If
            myDataTable.Rows.Add(myArray)
        Next
        objSqlDataSet.Tables.Add(myDataTable)
        Dim myWord As New Microsoft.Office.Interop.Word.Application
        Dim myDataDoc As Microsoft.Office.Interop.Word.DocumentClass = myWord.Documents.Add
        Dim myTable As Microsoft.Office.Interop.Word.Table = myDataDoc.Tables.Add(myWord.ActiveDocument.Range(0, 0), 1, 3)
        myTable.Rows(1).Cells(1).Range.Text = "name"
        myTable.Rows(1).Cells(2).Range.Text = "phone"
        myTable.Rows(1).Cells(3).Range.Text = "zusatz"
        For Each myRow As DataRow In objSqlDataSet.Tables("mydata").Rows
            Dim wRow As Microsoft.Office.Interop.Word.Row = myTable.Rows.Add()
            wRow.Cells(1).Range.Text = myRow.Item("name")
            wRow.Cells(2).Range.Text = myRow.Item("phone")
            If myRow.IsNull("zusatz") = False Then
                wRow.Cells(3).Range.Text = myRow.Item("zusatz")
            End If
        Next
        myDataDoc.SaveAs("C:\Test\WordData.doc")
        myDataDoc.Close()
        Dim myMailMergeDoc As Document = myWord.Documents.Open("C:\Test\MMTest.doc")
        myMailMergeDoc.MailMerge.OpenDataSource(Name:="C:\Test\WordData.doc")
        myMailMergeDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToNewDocument
        myMailMergeDoc.MailMerge.Execute()
        myWord.ActiveDocument.SaveAs("c:\Test\MMResult.doc")
        myWord.Visible = True
        myMailMergeDoc.Close()
        myWord.Quit()
        MsgBox("Complete")


Join me on Facebook

Technorati Tags: vb.net,.NET Framework,csharp,Fill,Microsoft,Word,Template,Datatable,Facebook,
DataSet,GetType,Object,Integer,Name,Office,Interop,Application,
DocumentClass,Documents,Table,ActiveDocument,Range,Text,Item,IsNull,
False,SaveAs,WordData,Close,Document,Open,MailMerge,OpenDataSource,
Destination,WdMailMergeDestination,Execute,Visible,Quit,MsgBox,Complete,Join,
users,Columns,Cells

Good Morning! I did not sleep well last night. That happens to me often but it was particularly bad last night. And on top of that I have a long list of meetings I have to do today so I can’t be sleepy, disinterested or crabby or even appear to be those. So hoping this day is over quickly is my prayer!

Today’s topic is how to read a binary file with vb.net. I was asked so I decided to make it a blog post. Make it a great day!


Join me on Facebook

Imports System.IO

Public Class IO_Library

  Public Shared Function ReadBinaryData(ByVal path As String) As Byte()

    ' Open the binary file.
    Dim streamBinary As New FileStream(path, FileMode.Open)

    ' Create a binary stream reader object.
    Dim readerInput As BinaryReader = New BinaryReader(streamBinary)

    ' Determine the number of bytes to read.
    Dim lengthFile As Integer = FileSize(path)

    ' Read the data in a byte array buffer.
    Dim inputData As Byte() = readerInput.ReadBytes(lengthFile)

    ' Close the file.
    streamBinary.Close()
    readerInput.Close()

    Return inputData

  End Function 'ReadBinaryData'

  Public Shared Function FileSize(ByVal path As String) As Integer

    Dim info As New FileInfo(path)

    Return info.Length

  End Function 'FileSize'

End Class 'IO_Library'

Join me on Facebook

Technorati Tags: vb.net,.NET Framework,csharp,IO,Read,Binary,File,System,Public,Class,Function,path,Byte,Open,FileStream,FileMode,Create,reader,BinaryReader,
Integer,FileSize,data,ReadBytes,Close,Return,info,FileInfo,Length

Good Morning! Last night my daughter totally kicked but at volleyball. She rocked. She really does has potential to be good at this game. As for me, I wrote code to produce a template class for work, where many of the classes generated follow a similar pattern. It was easier to do it this way than to type out the class by hand which had been done previously. Just insane no doubt about it.

Today’s topic requires one line of code! That’s right just one line. Lets say you have downloaded a web page and you want to remove the links from the string but leave the text of the link behind. This little regular expression will do the trick! Don’t forget to import System.RegularExpressions! Make it a great day!

Imports System.Text.RegularExpressions

System.Text.RegularExpressions.Regex.Replace(yourstring, "</?a[^>]*>", String.Empty)


Join me on Facebook

Technorati Tags: vb.net,csharp,.NET Framework,Web,string,Remove,Links,From,Page,code,template,classes,Just,topic,text,System,RegularExpressions,Make,Regex,Replace

Good Morning! Last night I played with the band from Celebrate Recovery. It was okay considering we had never played together and I didn’t know the material. I am not usually in the habit of this, but hey its not rocket science we are doing here. And I only played bass on my keyboard which is pretty easy for me.

Today’s topic is how to get the number of pages in a Microsoft Word document with vb.net. Not complicated, but I was asked so here it is. Make it a great day!


Join me on Facebook

Function GetWordPages

Try
            word_server = New Word.Application
            Dim Doc As Word.Document
            Doc=word_server.Documents.Open(FilePath)
            return Doc.ComputeStatistics(wdStatisticPages)           
        Catch ex As Exception

        Finally
            word_server.Application.Quit()
            word_server = Nothing
        End Try

End Function


Join me on Facebook

Technorati Tags: vb.net,.NET Framework,csharp,Pages,Number,Microsoft,Word,Document,Celebrate,
Recovery,keyboard,Application,Documents,Open,FilePath,ComputeStatistics,Catch,
Exception,Quit

Good Morning! I watched my daughter triumph in her first volleyball game of the year. And she is really GOOD! Actually the whole team had their fundamentals down pretty good. They are going to be quite good if they play together a lot.

Today’s topic comes courtesy of a reader who requested help to make a label …. multi colored. They want to be able to change the fonts and colors of words within the label. I didn’t really get into exactly why because I didn't;t really want to know.  Coding like this usually turns me off because it serves no useful purpose. So instead of using a label, I recommended a rich text box using the code below. Make it a great day!


Join me on Facebook

''' <summary>
    ''' sets a form's title to a standard - Dual Color title
    '''
    ''' for each form that you want to have a standard title on, you only need to:
    ''' a) drop a RichTextBox on your form, with a meaningful name and suitable properties [Appearance(Font)] / [Layout(Anchor),(Dock),(Size)], and
    ''' b) call this subroutine which uses armor plate text and colors found in the application's settings.
    '''
    ''' To use, place this subroutine in a standard module to which you have access from a given form's code
    ''' 1. add an Application setting called 'FormTitle', type of 'String', value of whatever you like
    ''' 2. add an Application setting called 'FormTitleForeColor', type of 'System.Drawing.Color', value of whatever color you like
    ''' 3. add an Application setting called 'FormTitleSubstring', type of 'String', value of the substring in the title to have a new color
    ''' 4. add an Application setting called 'FormTitleSubstringForeColor', type of 'System.Drawing.Color', value of substring's color
    ''' 5. Add a RichText Box control to your form. 
    ''' 6. give it a meaningful name for use in your code, such as: rtbSample
    ''' 6. set rtbSample properties as you normally would., i.e. some text, sizing, etc.  don't forget to perhaps dock it.
    ''' 7. in the form's load procedure:
    ''' InitializeFormTitle(Me.rtbSample)
    ''' </summary>
    ''' <param name="pRtb"></param>
    ''' <remarks></remarks>

    Public Sub InitializeFormTitle(ByVal pRtb As RichTextBox)
        If (My.Settings.PropertyValues("FormTitle") IsNot Nothing) AndAlso (My.Settings.PropertyValues("FormTitleForeColor") IsNot Nothing) Then
            pRtb.Text = My.Settings.FormTitle.Trim
            pRtb.ReadOnly = True
            pRtb.Enabled = False
            pRtb.BorderStyle = BorderStyle.None
            pRtb.SelectAll()
            pRtb.SelectionAlignment = HorizontalAlignment.Center
            pRtb.SelectionColor = My.Settings.FormTitleForeColor
            pRtb.SelectionFont = New Font(pRtb.SelectionFont, FontStyle.Bold)
            If (My.Settings.PropertyValues("FormTitleSubstring") IsNot Nothing) AndAlso (My.Settings.PropertyValues("FormTitleSubstringForeColor") IsNot Nothing) Then
                If My.Settings.FormTitleSubstring.Trim.Length > 0 Then
                    Dim m_x As Integer
                    m_x = My.Settings.FormTitle.IndexOf(My.Settings.FormTitleSubstring.Trim)
                    If m_x > -1 Then
                        pRtb.Select(m_x, My.Settings.FormTitleSubstring.Trim.Length)
                        pRtb.SelectionColor = My.Settings.FormTitleSubstringForeColor
                        pRtb.SelectionFont = New Font(pRtb.SelectionFont, FontStyle.Bold)
                    End If
                End If
            End If
            pRtb.DeselectAll()
        End If
    End Sub


Join me on Facebook

Technorati Tags: vb.net,.NET Framework,csharp,Multi,Color,Label,RichTextBox,daughter,team,
fundamentals,topic,courtesy,reader,words,purpose,text,code,
Dual,Appearance,Font,Layout,Anchor,Dock,Size,module,
Application,FormTitle,FormTitleForeColor,System,FormTitleSubstringForeColor,RichText,procedure,remarks,
Public,PropertyValues,AndAlso,Trim,BorderStyle,SelectAll,SelectionAlignment,HorizontalAlignment,
Center,SelectionColor,SelectionFont,FontStyle,Bold,Length,Integer,IndexOf,
Select,DeselectAll,subroutine,param

Hey everyone I joined Facebook. Yep that’s right I went over to the dark side. This will still be my primary focus but I figured why not? Anyway those of you who would like please feel free to add me as a friend. The link is

http://www.facebook.com/people/Kelly-Martens/1470181959 

Technorati Tags: Facebook,friend,Martens

Good Morning! Nothing much exciting to report today except that I finally was able to automate charts to Excel for all my plans at work including the plan’s budget information. It had driven me crazy because I had turned the realization into a dollar figure instead of a percent when creating this specific to the employee. When doing the entire plan I had to turn it back into a percent and to make a long story short it wasn't working. The good news is it is now though I have to admit it looked better when I had not included goal information in the chart and had just left it showing realization and volume.

Today’s topic is how to print an entire panel with vb.net. Make it a great day!


Join me on Facebook

Imports System.Runtime.InteropServices
Public Class Form1
    Private Enum DrawingOptions
        PRF_CHECKVISIBLE = &H1
        PRF_NONCLIENT = &H2
        PRF_CLIENT = &H4
        PRF_ERASEBKGND = &H8
        PRF_CHILDREN = &H10
        PRF_OWNED = &H20
    End Enum
    Private Const WM_PRINT As Integer = &H317
    <DllImport("user32.dll", CharSet:=CharSet.Auto)> _
    Private Shared Function SendMessage(ByVal handle As IntPtr, ByVal msg As Integer, ByVal wParam As IntPtr, ByVal lParam As Integer) As IntPtr
    End Function
    Private bmp As Bitmap
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        bmp = New Bitmap(Panel1.DisplayRectangle.Width, Panel1.DisplayRectangle.Height)
        Dim G As Graphics = Graphics.FromImage(bmp)
        Dim Hdc As IntPtr = G.GetHdc()
        SendMessage(Panel1.Handle, WM_PRINT, Hdc, DrawingOptions.PRF_OWNED Or DrawingOptions.PRF_CHILDREN Or DrawingOptions.PRF_CLIENT Or DrawingOptions.PRF_NONCLIENT)
        G.ReleaseHdc(Hdc)
        G.Dispose()
        PrintPreviewDialog1.Document = PrintDocument1
        PrintPreviewDialog1.ShowDialog()
    End Sub
    Private Sub PrintDocument1_PrintPage(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
        e.Graphics.DrawImage(bmp, 0, 0)
    End Sub
End Class

Join me on Facebook

Technorati Tags: vb.net,.NET Framework,csharp,Print,Panel,Excel,plans,realization,dollar,employee,goal,System,Runtime,InteropServices,Class,Private,Enum,DrawingOptions,

PRF_CHECKVISIBLE,PRF_NONCLIENT,PRF_CLIENT,PRF_ERASEBKGND,PRF_CHILDREN,Const,WM_PRINT,Integer,DllImport,CharSet,Auto,Function,

SendMessage,IntPtr,Bitmap,sender,Object,EventArgs,Click,DisplayRectangle,Width,Graphics,FromImage,GetHdc,Handle,ReleaseHdc,

Dispose,Document,ShowDialog,PrintPageEventArgs,PrintPage,DrawImage

Good Morning! I had a pretty good night sleep for a change and am getting ready for my trip to Madison WI this week. Otherwise not a whole lot going on. At work, I just completed the graphing portion of an application automating Excel. But like I said not a whole lot going on. I am looking for a graphing component I can use in an ASP.NET web page but it is not urgent.

Today’s topic is if you wish to shut down your MDI application and control the closing order of the forms. Make it a great day!


Join me on Facebook

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms

Namespace WindowsFormsApplication1
    Public Partial Class Form1
        Inherits Form
        Public Sub New()
            InitializeComponent()
        End Sub
        Public Const SC_CLOSE As Integer = &Hf060
        Public Const WM_SYSCOMMAND As Integer = &H112
        Protected Overloads Overrides Sub WndProc(ByRef m As Message)
            Select Case m.Msg
                Case WM_SYSCOMMAND
                    If CInt(m.WParam) = SC_CLOSE Then
                        ' <--- the USER initiated the close
                        If MessageBox.Show("Quit?", "Closing...", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

= DialogResult.No Then
                            Exit Sub
                            ' don't allow "base.WndProc(ref m)" to execute below
                        End If
                    End If
                    Exit Select
            End Select
            MyBase.WndProc(m)
        End Sub
        Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
            ' just FYI...
            ' programmatically closing the form will NOT trigger the messagebox in WndProc() above
            Me.Close()
        End Sub
    End Class
End Namespace

Technorati Tags: vb.net,.NET Framework,csharp,Closing,MDI,Control,Form,Order,Application,
Madison,Excel,topic,Make,System,Generic,ComponentModel,Data,
Linq,Text,Public,Partial,Class,InitializeComponent,SC_CLOSE,Integer,
WM_SYSCOMMAND,WndProc,Message,Select,Case,CInt,WParam,USER,
MessageBox,Quit,MessageBoxButtons,YesNo,MessageBoxIcon,Question,DialogResult,Exit,
MyBase,Private,sender,Object,EventArgs,Close,Collections

This unusual Saturday blog post is to discuss yesterday’s issue and demonstrate what I did to solve it. It is ugly. It is not “clean”. But I was happy to get it out the door because I almost didn’t!

I also wanted to thank you as a group all of you who came to help me when I was facing a task I was not sure how to handle. I knew how to do it but I didn’t know how to do it well is the best way to describe what happened here.

First a little background. I build a SQL statement on the fly with which the “Operators” they choose are things like Period = 2009. This is a good example here in XML format.

<Operators>
    <ID>-1</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>465</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>0</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>476</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>1</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>482</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>2</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>471</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>3</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>473</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>4</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanID</ColumnName>
    <DataType>int</DataType>
    <Operator>= (Numeric)</Operator>
    <OperatorValue>474</OperatorValue>
    <ADV>OR</ADV>
    <IsSort>True</IsSort>
    <Hide>false</Hide>
  </Operators>

  <Operators>
    <ID>5</ID>
    <DBName>DB</DBName>
    <TableName>Result</TableName>
    <ColumnName>PlanShortName</ColumnName>
    <DataType>varchar</DataType>
    <Operator>= (Non Numeric)</Operator>
    <OperatorValue>PAM</OperatorValue>
    <ADV>AND</ADV>
    <IsSort>false</IsSort>
    <Hide>false</Hide>
  </Operators>

These rows are populated by user choices.

Up until Wednesday I thought I was done. What i had forgotten was if the columns that were the same but had the OR ADV were not grouped together with a parenthesis at the beginning and end the SQL statement would not be accurate.This became critical this month as MTD reports that were being generated also included the previous months totals because I had not given SQL the correct logic. What was being generated by my code was this: (That that was in italics is what thsi code was generating. )

CREATE VIEW [20090321143859]
AS
SELECT [DB].[dbo].[Employee].[EMPID]AS C0,
[DB].[dbo].[Employee].[Lastname]AS C1,
[DB].[dbo].[Employee].[Firstname]AS C2,
[DB].[dbo].[Result].[Period]AS C3,
[DB].[dbo].[Result].[PlanID]AS C4,
[DB].[dbo].[Result].[ElementShortName]AS C5,
[DB].[dbo].[Result].[AllocatorShortName]AS C6,
[DB].[dbo].[Result].[MTD]AS C7,
[DB].[dbo].[Result].[YTD]AS C8,
[DB].[dbo].[Result].[PlanShortName] AS C9,
[DB].[dbo].[Employee].[Ending] as OE0,
[DB].[dbo].[Employee].[Starting] as OE1 FROM  [DB].[dbo].[Employee] INNER JOIN
[DB].[dbo].[Result] ON
[DB].[dbo].[Result].[EmployeeID] =
[DB].[dbo].[Employee].[ID]
WHERE [DB].[dbo].[Employee].[Ending] >= 200902 AND
[DB].[dbo].[Employee].[Starting] <= 200902 AND
[DB].[dbo].[Result].[Period] = 200902 AND
[DB].[dbo].[Result].[PlanID] = 474 OR [DB].[dbo].[Result].[PlanID] = 473 OR [DB].[dbo].[Result].[PlanID] = 471 OR
[DB].[dbo].[Result].[PlanID] = 482 OR [DB].[dbo].[Result].[PlanID] = 476 OR [DB].[dbo].[Result].[PlanID] = 465
AND [DB].[dbo].[Result].[PlanShortName] = 'PAM'

when want I wanted was this:

CREATE VIEW [20090321143859]
AS
SELECT [DB].[dbo].[Employee].[EMPID]AS C0,
[DB].[dbo].[Employee].[Lastname]AS C1,
[DB].[dbo].[Employee].[Firstname]AS C2,
[DB].[dbo].[Result].[Period]AS C3,
[DB].[dbo].[Result].[PlanID]AS C4,
[DB].[dbo].[Result].[ElementShortName]AS C5,
[DB].[dbo].[Result].[AllocatorShortName]AS C6,
[DB].[dbo].[Result].[MTD]AS C7,
[DB].[dbo].[Result].[YTD]AS C8,
[DB].[dbo].[Result].[PlanShortName] AS C9,
[DB].[dbo].[Employee].[Ending] as OE0,
[DB].[dbo].[Employee].[Starting] as OE1 FROM  [DB].[dbo].[Employee] INNER JOIN
[DB].[dbo].[Result] ON
[DB].[dbo].[Result].[EmployeeID] =
[DB].[dbo].[Employee].[ID]
WHERE [DB].[dbo].[Employee].[Ending] >= 200902 AND
[DB].[dbo].[Employee].[Starting] <= 200902 AND
[DB].[dbo].[Result].[Period] = 200902 AND
( [DB].[dbo].[Result].[PlanID] = 474 OR [DB].[dbo].[Result].[PlanID] = 473 OR [DB].[dbo].[Result].[PlanID] = 471 OR
[DB].[dbo].[Result].[PlanID] = 482 OR [DB].[dbo].[Result].[PlanID] = 476 OR [DB].[dbo].[Result].[PlanID] = 465 )
AND [DB].[dbo].[Result].[PlanShortName] = 'PAM'

These statements obviously produce wildly different results. The first virtually ignores the dating parameters I set and now that we were in February this bug was discovered because it was seen picking up January’s results too.

So I thought no big deal I will just loop through the table find the columns with the same name, write those values first then write the other rows that weren’t written (that ADV has AND values) and it will be an easy fix. NOT!! First there was the issue what if there was more than one group of column that had multiple OR statements? Then there was the maddening logic required to determine where my For and Next statements were going. A co worker suggested a dataview. Well I would have loved to do that except I was running out of time. Even if I had accomplished by the dataview sort method what I wanted I was still going to have to determine what row had already been added and what had not been. 

What I thought would be easy turned out to be ….. well kind of a nightmare.

Anyway so here’s the code solution I came up with. Like I said, it ain’t pretty and it ain’t clean. But it seems to work. Feel free to leave suggestions on how to improve it. And I am sorry I was not more clear initially to those of you who were confused. I was in a rush and sometimes we don’t explain things too well when in that situation. Feel free to knock this code or improve on it. I know it is bad.

You will notice a function called “FormatOperator” in this code. That is in reference to another function that based on the Operator value of the dtOperators table row it returns a string determing how it should be stated in the SQL statement (if its a date, integer, double etc).

Well tonight I am off to have some Chicago style pizza at UNO’s and go to a coffee house and enjoy the music there. It has been a long week. Again thank you all for your help!

Sub HelpMe()

        Dim i As Integer

        Dim j As Integer

        Dim holdtable As New DataTable

        holdtable = Me.dtOperators.Clone

        For i = Me.dtOperators.Rows.Count - 1 To 0 Step -1

            If Me.dtOperators.Rows(i).Item("ADV") = "AND" Then

            Else

                holdtable.Rows.Clear()

                holdtable.AcceptChanges()

                Dim row As DataRow = holdtable.NewRow

                row.Item("DBName") = Me.dtOperators.Rows(i).Item("DBName")

                row.Item("TableName") = Me.dtOperators.Rows(i).Item("TableName")

                row.Item("ColumnName") = Me.dtOperators.Rows(i).Item("ColumnName")

                row.Item("Operator") = Me.dtOperators.Rows(i).Item("Operator")

                row.Item("OperatorValue") = Me.dtOperators.Rows(i).Item("OperatorValue")

                row.Item("ADV") = "OR"

                holdtable.Rows.Add(row)

                For j = Me.dtOperators.Rows.Count - 1 To 0 Step -1

                    If Me.dtOperators.Rows(j).Item("ADV") = "AND" Then

                    Else

                        If j = i Then

                        Else

                            If dtOperators.Rows(i).Item("DBName") = dtOperators.Rows(j).Item("DBName") _
                            And dtOperators.Rows(i).Item("TableName") = dtOperators.Rows(j).Item("TableName") _
                            And dtOperators.Rows(i).Item("ColumnName") = dtOperators.Rows(j).Item("ColumnName") Then

                                Dim row1 As DataRow = holdtable.NewRow

                                row1.Item("DBName") = Me.dtOperators.Rows(j).Item("DBName")

                                row1.Item("TableName") = Me.dtOperators.Rows(j).Item("TableName")

                                row1.Item("ColumnName") = Me.dtOperators.Rows(j).Item("ColumnName")

                                row1.Item("Operator") = Me.dtOperators.Rows(j).Item("Operator")

                                row1.Item("OperatorValue") = Me.dtOperators.Rows(j).Item("OperatorValue")

                                row1.Item("ADV") = "OR"

                                holdtable.Rows.Add(row1)

                                Me.dtOperators.Rows(j).Delete()

                            End If ' Matching If of seeing if j and i meet

                        End If 'If j = i if statement

                    End If 'Second Adv loop j

                Next 'second for next j

                'write rows to string actions then delete row

                Me.dtOperators.Rows(i).Delete()

                Me.dtOperators.AcceptChanges()

                i = i - (holdtable.Rows.Count - 1)

            End If 'if it matched ADV loop i
            Dim k As Integer
            If holdtable.Rows.Count - 1 > -1 Then
                sql = sql & "( "
                For k = 0 To holdtable.Rows.Count - 1
                    'If i <> holdtable.Rows.Count - 1 Then
                    sql = sql & Me.FormatOperator(holdtable.Rows(k).Item("DBName"), holdtable.Rows(k).Item("TableName"), _
                                      holdtable.Rows(k).Item("ColumnName"), holdtable.Rows(k).Item("Operator"), _
                                      holdtable.Rows(k).Item("OperatorValue").ToString) & " " & holdtable.Rows(k).Item("ADV").ToString & " "
                Next

                sql = sql & ") "
                sql = sql.Replace("OR ) ", ") " & vbCrLf)

                If Me.dtOperators.Rows.Count - 1 <> -1 Then
                    sql = sql & " AND "
                End If
            End If

        Next 'i loop
        Me.dtOperators.AcceptChanges()

        'Now writing the "AND" values that are left
        For i = 0 To Me.dtOperators.Rows.Count - 1
            If i <> Me.dtOperators.Rows.Count - 1 Then
                sql = sql & Me.FormatOperator(Me.dtOperators.Rows(i).Item("DBName"), Me.dtOperators.Rows(i).Item("TableName"), _
Me.dtOperators.Rows(i).Item("ColumnName"), Me.dtOperators.Rows(i).Item("Operator"), _
Me.dtOperators.Rows(i).Item("OperatorValue").ToString) & " " & Me.dtOperators.Rows(i).Item("ADV").ToString & vbCrLf
            Else
                sql = sql & Me.FormatOperator(Me.dtOperators.Rows(i).Item("DBName"), Me.dtOperators.Rows(i).Item("TableName"), _
Me.dtOperators.Rows(i).Item("ColumnName"), Me.dtOperators.Rows(i).Item("Operator"), _
Me.dtOperators.Rows(i).Item("OperatorValue")) & vbCrLf
            End If
        Next
    End Sub

Technorati Tags: vb.net,.NET Framework,csharp,Solution,Problem,door,task,background,statement,example,DataType,Operator,Numeric,IsSort,True,Hide,user,parenthesis,reports,logic,code,CREATE,VIEW,SELECT,Lastname,Firstname,ElementShortName,AllocatorShortName,FROM,INNER,JOIN,WHERE,results,February,January,loop,column,worker,method,Anyway,Feel,situation,FormatOperator,reference,integer,Chicago,music,Again,HelpMe,DataTable,Count,Step,Item,Else,Clear,AcceptChanges,Delete,Second,actions,Replace,Operators,columns,statements,parameters,suggestions,dataview,dtOperators,vbCrLf

Hi everyone I have got a coding issue that is giving me fits.

Here is what’s up.

I have a datatable called dtoperators. It has columns

DBName

TableName

ColumnName

Operator

OperatorValue

ADV

What i need to do is loop through the rows of this table. All rows that have the same value in DBName, TableName, ColumnName and ADV = “OR” should be written to a string with a parenthesis around it. Those with ADV = “AND” do not need parenthesis and simply get written with an “AND” between each string.

If anyone can help I would be appreciative! It’s kind of due soon.

Technorati Tags: Help,Time,fits,loop,parenthesis,columns

Today a project I worked on came to an end. This is the story of how it came about.

When I first started working for my employer in October of 2007, I came in not knowing any of the jargon familiar to the others that worked there.  I certainly had no knowledge of their databases or how their data works together to give them what they want. What made matters worse is that my boss does not really train people. He throws people in and lets them figure it out on their own. When you know nothing, that can be quite a challenge.

I decided in order to learn their databases, I would write an application that would force me to study the intricacies of their databases and how they work together. It became a report writer, which would allow prospective users to build custom reports. I started out with the basic tables. As this thing grew, it really became apparent to me this was a far better alternative to report writing then what was happening there. Often people would sit around and say they were awaiting a report from so and so before they could do their job. Or a few people were handling many many requests for mundane custom reports. And finally, within the main application was a treeview like control which hardcoded reports existed that users could use. Quite frankly, it was slow, buggy and not very user friendly. I knew I could make something that would make a difference here while learning a great deal about this company and how it worked.

As I started to write it I began putting the word out that this existed. I didn’t expect people to greet it with open arms. Change is hard for anyone, and a concept like this would require people to be willing to think on their own. What I didn’t anticipate was the resistance I would encounter from my own co workers. One was afraid his territory was being threatened, another thinking a third party solution would be a better answer. Not once did any of them take the time to look at it. When I asked to review it for the team and users my boss always said no.

As far as the users out there it got used sporadically. Those that used it loved it. But their reaction even caused problems. One of the senior analysts complained they were using a tool that she had not been trained on and therefore was not supported. i tried one last email to her to ask her to review it which like the others got no response.

So tonight after work, I pulled the plug. I took it out of SourceSafe, rebuilt the project and checked it back in. I felt a sense of sadness actually. I had worked on this on nights and weekends since my arrival there and was pretty proud of it quite frankly. But I was tired of fighting to get this on the map. Those that used it might complain. I will be graceful and tell them it just isn’t supported and sorry for the inconvenience. Nothing good comes out of being nasty about things.

But then I thought, what was the original purpose of what I had done here? Was it not to learn the databases this company uses? Was it not to learn the data structure used, the custom types of variables used? For this I could be grateful. I knew those data tables than most of the developers long my senior did. And this didn’t have to be the end of a practical use for the code. I decided I would transfer it to an independent application that I would use as a starter for my SQL statements (cross database queries can get quite long). And this one would use the latest Framework version (3.5) instead of being struck in the purgatory of .NET Framework 1.1.

I guess what I am saying is just because things don’t turn out the way you want them to, you sometimes have to go back and look at the real purpose of what you are doing and not get caught up in the other things going on that weren’t part of  what it is you were trying to accomplish.

Make it a good night!


Join me on Facebook

Technorati Tags: Lesson,employer,October,jargon,knowledge,data,intricacies,writer,custom,
reports,requests,buggy,user,difference,word,Change,concept,
resistance,workers,territory,solution,team,reaction,problems,tool,
response,SourceSafe,arrival,purpose,code,starter,database,Framework,
version,databases,users,analysts,statements

Good morning! Well things have slowed down at work a bit and on the home front. For that I am grateful. Last night I watched Samuel Jackson in “Lakeview Terrace”. Quite the awesome movie! He plays such a good bad guy!

Today’s topic is to automate a Microsoft Word Mail Merge. This is a question I get asked pretty frequently but really it isn’t that hard. Make it a great day!

Dim objSqlConnection As New SqlConnection("user id=user_id_goes_here;password=password_goes_here;initial catalog=database_name_goes_here;data source=server_name_goes_here;Connect Timeout=30")
        Dim objSQLDataAdapter As New SqlDataAdapter("select [name], [phone] from database_name_goes_here.dbo.testtable", objSqlConnection)
        Dim objSqlDataSet As New DataSet
        objSQLDataAdapter.Fill(objSqlDataSet, "mydata")
Dim myWord As New Microsoft.Office.Interop.Word.Application
        Dim myDataDoc As Microsoft.Office.Interop.Word.DocumentClass = myWord.Documents.Add
        Dim myTable As Microsoft.Office.Interop.Word.Table = myDataDoc.Tables.Add(myWord.ActiveDocument.Range(0, 0), 1, 2)
        myTable.Rows(1).Cells(1).Range.Text = "name"
        myTable.Rows(1).Cells(2).Range.Text = "phone"
        For Each myRow As DataRow In objSqlDataSet.Tables("mydata").Rows
            Dim wRow As Microsoft.Office.Interop.Word.Row = myTable.Rows.Add()
            wRow.Cells(1).Range.Text = myRow.Item("name")
            wRow.Cells(2).Range.Text = myRow.Item("phone")
        Next
        myDataDoc.SaveAs("C:\Test\WordData.doc")
        myDataDoc.Close()
        Dim myMailMergeDoc As Document = myWord.Documents.Open("C:\Test\MMTest.doc")
        myMailMergeDoc.MailMerge.OpenDataSource(Name:="C:\Test\WordData.doc")
        myMailMergeDoc.MailMerge.Destination = WdMailMergeDestination.wdSendToNewDocument
        myMailMergeDoc.MailMerge.Execute()
        myWord.ActiveDocument.SaveAs("c:\Test\MMResult.doc")
        myWord.Visible = True
        myMailMergeDoc.Close()
        myWord.Quit()

Technorati Tags: vb.net,.NET Framework,csharp,Automate,Microsoft,Word,Mail,Merge,Samuel,
Jackson,Lakeview,Terrace,movie,SqlConnection,user,password,catalog,
data,Connect,Timeout,SqlDataAdapter,DataSet,Fill,Office,Interop,
Application,DocumentClass,Documents,Table,ActiveDocument,Range,Text,SaveAs,
WordData,Close,Document,Open,MailMerge,OpenDataSource,Name,Destination,
WdMailMergeDestination,Execute,Visible,Quit,Cells