In my last Blog entry I wrote about how to clear all text boxes in a windows form. But what if that form has tabs? Then you will need to set up a loop inside of loop inside of a loop. I know it seems confusing, but it is much better than the alternative of coding every text box name on every tab page into a reset or clear button. Especially if you have hundreds of text boxes. Here is the code to do it.

        Dim x As Integer
        For x = 0 To Me.Controls.Count - 1

            If TypeOf Me.Controls.Item(x) Is TextBox Then
                Me.Controls.Item(x).Text = ""
            End If

            If TypeOf Me.Controls.Item(x) Is TabControl Then

                Dim tabControl As TabControl = Me.Controls.Item(x)
                Dim z As Integer

                For z = 0 To tabControl.TabCount() - 1

                    Dim TabPage As TabPage = tabControl.TabPages(z)
                    Dim y As Integer

                    For y = 0 To TabPage.Controls.Count - 1
                        If TypeOf TabPage.Controls.Item(y) Is TextBox Then
                            TabPage.Controls.Item(y).Text = ""
                        End If
                    Next

                Next

            End If

        Next

We start out with a loop on the current page controls and clearing any text boxes there, then we detect if the control is a tab control. Then we start a loop inside of that for each tab page. Then a loop in that for each control on the tab page.  I created variables with the name of the control to make it easier to read. You can leave this out and just use the name in the precdeing loop but it can get ugly, like:  Me.Controls.Item(x).TabPages(z).Controls.Item(y).Text = "". This would be the end results if we did not you the variables.

 You can use this same concept with any container on a form.

When you have a reset button on a vb form and you want that button to clear all of the text box fields, you have two options. Write code for each field to clear them or loop through each control on the form.

Here is an example of clearing each field individually:

Private Sub resetForm()

       ServerName.text = ""
       PortNumber.text = ""
      and so on ....

End Sub

As you can see this give you complete control of each control individually. But if you have dozens of fields this can be a headache. Also, maintaining this becomes a hassle. Each time you add a control you have to update the resetform method.

Here is the loop through example:

Private Sub resetForm()

    'Set an integer to loop through the controls
    Dim x As Integer
    ' Set the loop for one less than the count because the fields start their index at 0
    For x = 0 To Me.Controls.Count - 1

       'if it is a text box then clear it 
        If TypeOf Me.Controls.Item(x) Is TextBox Then
            Me.Controls.Item(x).Text = ""
        End If

    Next

End Sub

As you can see, this method never needs to be updated. You can add or remove controls to your form and the method with react accordingly.

You could also do the same thing for radio buttons, check boxes or any other editable field. You just need a case for each type of control.

 Hope this helps someone.

I will be speaking at the SQL server 2008 Launch in Jacksonville, FL.

My sessions are:

Converting DTS to SSIS : 11-Noon

Reporting Services : 1-2pm

http://www.sqlsaturday.com/eventhome.aspx?eventid=10

Join us on Friday, September 12, 2008, for a one day look at the new features of SQL Server 2008 as we celebrate the launch of the latest version. Our event will be located at the Modis Building, 1 Independent Dr, Jacksonville, FL. We'll have 15 sessions on SQL Server that will be announced in the new few days. There is a fee for parking for the event that will range from $5-10 depending on the parking location you select. Breakfast and lunch will be provided by our event sponsors. This event is being coordinated by members of the Jacksonville SQL Server Users Group (www.jssug.com).

We had a customer that had time saved as a string and needed it converted to a date time field. They wanted to combine it with another date field. Here is a simple script to convert in situaions like this in an SSIS transformaion script. By the way some of the time strings were null and some had bogus times in them. After writing it I should have added a check to make sure the time is between 0000 and 2400.

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim NewDateTime As Date
        Dim HourStr As String
        Dim MinuteStr As String

        'validate time is not null
        If Not (Row.NATIME_IsNull) Then

            'Validate  time is a 4 digit number
            If IsNumeric(Row.NATIME) And Row.NATIME.ToString.Length = 4 Then

                ' break time into hours and minutes
                HourStr = Row.NATIME.Substring(0, 2)
                MinuteStr = Row.NATIME.Substring(2, 2)

            Else ' iff time is not a 4 digit number
                HourStr = "00"
                MinuteStr = "00"
            End If

        Else ' if time is null

            HourStr = "00"
            MinuteStr = "00"

        End If

        'combine date and time
        NewDateTime = CDate(CStr(DateSerial(Year(Row.Date), Month(Row.Date), DatePart("d", Row.Date))) + " " + _
           CStr(TimeSerial(CInt(HourStr), CInt(MinuteStr), 0)))

        'output row
        Row.NewDateTime = NewDateTime

    End Sub

It can be a pain to launch an SSIS package remotely. So I developed a vb.net standalone application to remotely launch a package. It does require the host computer to have SSIS installed. This is just a beginning program. I am going to be adding other features like variables and config files. Eventually it will allow you to run the package remotely where the package will run on the remote machine. But this will probably require creating a web service.  

Please leave comments, this is my first blog and my first program I have posted online.

Thanks

Mike Davis