Archive for March, 2009

My volleyball champ

Advertisements

Leave a comment

I Joined Facebook

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

5 Comments

Print the entire Panel with vb.net

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

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

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

,,,,,

3 Comments

Control Form Closing Order in MDI Application in vb.net

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

Leave a comment

The Solution To Yesterday’s Problem

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.

 http://cid-a0d71e1614e8dbf8.skydrive.live.com/embedrowdetail.aspx/BlogImages|52|6/rw.JPG

 

 

 

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

Leave a comment

I Need Help This Time

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

10 Comments

A Lesson Learned

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

1 Comment