Recently came across a problem with nicely formatted excel file(with all pictures, bolded colorful text headings, etc), I wanted to import the important data into our database using SSIS, however with all these junk info at the start. SSIS didn't behave as I wanted, instead of importing all the prices figures it imported all null values instead. 

I did some search on the net, found that SSIS was defaulted using first 8 rows in an Excel file to determine the datatype, which in my case, all first 8 lines were blank. So problem found, but to fix it, how?

There are couple solutions to this issue:

1. Simple and maybe the most easier way to do it. Change the 'Openrowset' Property under Excel Data Source's Properties, from 'Sheet1$', to Sheet1$A1:G1000. basically manually set the range you want, which handles this problem perfectly.

2. Use Script component to code for filtering, Read the file in as a whole inside script component, then looping through each individual rows to filter which to keep. 

3. Manually change the property field type of source Excel file column, in my case, set "price " column from 'number' to 'text'

4. MSDN suggests there's an registry key you may change to determine how many rows to check by SSIS before determination of the column type. Named: TypeGuessRows under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel.

Return Maximum Number in an array

I was asked to write a function to return the maximum number in a given array once in a job interview when I have little programming knowledge. I didn't make the move there but after I studied a bit, I found it is actually a very easy task. There are more than one way of doing this, I'll just illustrate two here:

Solution 1:

    Private Function GetMaxNumber() As Integer
        Dim ages() As Integer = {12, 53, 14, 26, 35, 24, 39, 45}
        'set the age of the first element for comparison
        Dim oldest As Integer = ages(0)

        'now loop through all the ages in the array
        For i As Integer = 0 To UBound(ages) - 1
            'check if the current age is greater than the highest age
            'that has been found so far
            If oldest < ages(i) Then
                'store the value of the element in the array
                oldest = ages(i)
            End If
        Next
        Return oldest
    End Function

Solution 2:


    Private Function GetMax() As Integer
        Dim nums() As Integer = {12, 53, 14, 26, 35, 24, 39, 45}
        Dim largest = (From num In nums Select num).Max

        Return largest
    End Function

Using LINQ in Visual studio 2008 you can tell it is much easier to do this

SSIS Package error handling

Sometimes when you running a SSIS package with hundreds of dataflow tasks you simply don't want to have frustrations of one error at pre-validation ruins the whole package, as it might just be a tiny error but cost you hours of debugging and more importantly, may even slow down the business process.

Recently I've come across similar problems with one of my packages, and i digged a little which I found a few approaches that could cater such issue.

Approach 1: my case, I have dozens of dataflow tasks running parallel and I want to allow some file changes and format changes to cause error but package still runs and I could get back to the error later on after I receive the error Email I set for each individual task.

To do this is really simple, just change the main control flow's property "maximumerrorcount" to whatever size you like but 1. If you wish, you can also set individual dataflow task's max error count number as well. but all these must based on that you have a very good understanding of what your package is doing and what sort of error you're expecting. If you're not sure of the errors, then you should also add some error handling in event handlers' of tasks.

Approach 2: A more sophisticated solution for this kind of issue is to write a script task to do the file validations yourself before the package does, and set variables whether the following child task should run or not based on your script task result. here's a link of showing an example: http://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/

Personally I found first one is really simple, but as I'm aware of, if error handling isn't good enough you might end up spending more time when weird error occurs.

“Cannot generate SSPI context” error message

I ran into this problem when i'm doing testings on my local machine, after a little bit search i found out the actual problem was I set my local time to 2 months ago and there has been a mismatch of time clock between server and client, therefore this error pop up.
so after changing my local time back to current time, it's all good again.

Outlook blocked access to the following potentially unsafe attachments

Sometimes you may receive this message when receiving email from others with a file attached it.
To deal with it if changing security settings of your exchange server admin isn't an option, there are a few steps to get rid of this message:

1. Exit Outlook if it is running.
2. Click Start, and then click Run. Copy and paste (or type) the following command in theOpen box, and then press ENTER:
   regedit
3. Verify that the following registry key for your version of Outlook exists. 
   Microsoft Office Outlook 2010
   HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Outlook\Security
   Microsoft Office Outlook 2007
   HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Security
   Microsoft Office Outlook 2003
   HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Security
   Microsoft Outlook 2002
   HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Outlook\Security
   Microsoft Outlook 2000
   HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Outlook\Security
   If the registry key exists, go to step 5.
   
   If the registry key does not exist, follow these steps to create it:
   1. Locate, and then click the following registry key:
      HKEY_CURRENT_USER\Software\Microsoft
   2. Under Edit, click New, and then click Key.
   3. Type Office, and then press ENTER.
   4. Under Edit, click New, and then click Key.
   5. For Outlook 2010, type 14.0, and then press ENTER. 
      For Outlook 2007, type 12.0, and then press ENTER. 
      For Outlook 2003, type 11.0, and then press ENTER. 
      For Outlook 2002, type 10.0, and then press ENTER. 
      For Outlook 2000, type 9.0, and then press ENTER.
   6. Under Edit, click New, and then click Key.
   7. Type Outlook, and then press ENTER.
   8. Under Edit, click New, and then click Key.
   9. Type Security, and then press ENTER.
4. Under Edit, click New, and then click String Value.
5. Copy and paste (or type) the following name for the new value:
   Level1Remove
6. Press ENTER.
7. Right-click the new string value name, and then click Modify.
8. Type the file name extension of the file type that you want to open in Outlook. For example:
   .exe
   To specify multiple file types, use the following format:
   .exe;.com
9. Click OK.
10. Exit Registry Editor.
11. Restart your computer.

the source link for this solution is :http://support.microsoft.com/kb/829982

Searching through a datagridview control in Visual Studio

There are lots of ways of searching a datagridview in VB. Depending on different situations you might want to choose the one that most suitable to your situation.
Today I was asked by someone how to do a search if he doesn't have a databinding(hence, can't search in bindingsource), meanwhile, he also knows that which column his searching target will be, which makes it quite easy. heres the code:


        For i As Integer = 0 To Datagridview1.RowCount - 1
        'Cell(2) here is the column our results located
            If Datagridview1.Rows(i).Cells(2).Value.ToString.Contains("Cake") Then
                Datagridview1.Rows(i).Selected = True           'Move Cursor to this row
            End If
        Next

If you want to search an entire datagridview without knowing which column, a nested for loop will solve this quite handy as well:

        For i As Integer = 0 To Datagridview1.RowCount - 1
            For k As Integer = 0 To dgvAccural.ColumnCount - 1
                If Datagridview1.Rows(i).Cells(k).Value.ToString.Contains("Cake") Then
                    Datagridview1.Rows(i).Selected = True       'Move Cursor to this row
                End If
            Next
        Next

Above solutions are handy for small recordsets, if you have a very large recordset, it is still recommended to use search in bindingsource, or use LINQ to search through your list

String split problem

sometimes string split works well on single characters, but when trying to using a string to split, problems may occur, to overcome this issue, use the following code will do the job:



Dim linecol() As String
linecol = line.Split(New String() {"&|&"}, StringSplitOptions.None)  'define a new string to use string as delimiter

Populating combbox using a List with two columns

        uList = SybizDB.GetUserList
        cbUser.DataSource = uList
        cbUser.DisplayMember = "Name"
        cbUser.ValueMember = "UserNumber"

easy as that.
displaying the name field while selecting the actual value of usernumber field.

SQL Selection Queries

To compare two table values and select the ones that aren't common, say common field is ProductID:


        select ProductID from Table1 where ProductID not in (select ProductID from Table2)

Dynamically create tab control in VB.NET

Creating a tab control with textbox/label/buttons:


    Private tabSupplierArray As System.Windows.Forms.TabControl
    Private lableArray(3) As Label
    Private txtSuppArray(3) As TextBox


      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Create and set position of tab control
        Dim tabControl As New TabControl()
        tabControl.Location = New System.Drawing.Point(0, 10)
        tabControl.Size() = New Size(900, 200)

        Dim fnt As New Font("Courier New", 9.75)

        'Loop 8 times to create 8 tab pages---------------------------------------
        For c As Integer = 1 To 8
            Dim tabPage As New TabPage()
            Dim btnSup As New Button()

            If c = 1 Then
                tabPage.Text = "Preferred Supplier"
            Else
                tabPage.Text = c.ToString()
            End If

            Dim txtCode As New TextBox
            'Creating labels and textbox horizontally twice----------------------
            For i As Integer = 0 To 1
                lableArray(i) = New System.Windows.Forms.Label
                With lableArray(i)
                    .Size() = New Size(40, 22)
                    .Font = fnt
                    .Location = New System.Drawing.Point(30 + (i * 350), 30)
                    .TextAlign = HorizontalAlignment.Right
                End With
                txtSuppArray(i) = New System.Windows.Forms.TextBox
                With txtSuppArray(i)
                    If i = 0 Then
                        .Size() = New Size(150, 22)
                    Else
                        .Size() = New Size(367, 22)
                    End If
                    .ReadOnly = True
                    .Location = New System.Drawing.Point(130 + (i * 350), 30)
                    .TextAlign = HorizontalAlignment.Left
                End With
            Next
            'Creating labels and textbox horizontally twice----------------------
            For i As Integer = 2 To 3
                lableArray(i) = New System.Windows.Forms.Label
                With lableArray(i)
                    .Size() = New Size(100, 22)
                    .Font = fnt
                    .Location = New System.Drawing.Point(30 + ((i - 2) * 350), 70)
                    .TextAlign = HorizontalAlignment.Right
                End With
                txtSuppArray(i) = New System.Windows.Forms.TextBox
                With txtSuppArray(i)
                    If i = 2 Then
                        .Size() = New Size(200, 22)
                    Else
                        .Size() = New Size(367, 22)
                    End If
                    .MaxLength = 30
                    .Location = New System.Drawing.Point(130 + ((i - 2) * 350), 70)
                    .TextAlign = HorizontalAlignment.Left
                End With
            Next
            lableArray(0).Text = "Code"
            lableArray(1).Text = "Name"
            lableArray(2).Text = "Part Number"
            lableArray(3).Text = "Notes"

            'Create button-----------------------------------
            With btnSup
                .Size() = New Size(45, 22)
                .Location = New System.Drawing.Point(285, 30)
                .Font = fnt
                .Text = "Btn"
            End With

            'Add controls onto tab control---------
            tabPage.Controls.Add(btnSup)
            tabPage.Controls.AddRange(lableArray)
            tabPage.Controls.AddRange(txtSuppArray)
            tabControl.Controls.Add(tabPage)
        Next

        Me.Controls.Add(tabControl)

    End Sub