Saturday, August 07, 2010

SQL Pad Left Functionality (Oracle / SQL Server)

If you needs to pad a column value with a character or symbol in order to conform the resulting value to some standard. For example, a company may represent their stores by a maximum of 4 digits and each character in that 4-digit store number must contain a value as to not be confused with any other values.. In other words, store #99 would be represented as 0099 instead of just 99.

This is not uncommon practice. Often times, similar functionality is required when pulling data from the mainframe where “super keys” are paramount. Our good friends at MS have set up SQL Server so that this is universally simple. The code snippet in its basic form looks like this:

Wednesday, August 04, 2010

How to load csv file into a datagrid in VB.net

Goal:
Load a csv file of any dimension into a datagridview. This example reads a csv file and adds rows and columns to the datagrid as needed. You can specify your own delimiter ("," or ";" or whatever).


Instructions:
Open a new Windows Forms Application and add 1 button, an openfile dialog, a datagridview and a richtextbox to the form. Click the button to browse for a *.csv file to load it into the datagrid.
Replace all the automatically generated code in your project with the code below.
This isn't my code, so all credit goes to the original author whose name I can't recall.

Code:

Public Class Form1

  Private FileName As String
  Private ds As New DataSet()

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

      'Configure the openfile dialog
      OpenFileDialog1.Filter = "csv files|; *.csv"
      OpenFileDialog1.Title = "Select a csv file"
      OpenFileDialog1.FileName = ""

      Try
          With OpenFileDialog1
              If .ShowDialog() = DialogResult.OK Then
                  FileName = .FileName
                  'Set chosen filename as contents of the RichTextBox
                  RichTextBox1.Text = FileName

                  Dim myData4 As DataTable = BuildDataTable(FileName, ";") 'Specify the delimiting character. I work with semicolon delimited files.
                  ds = New DataSet()
                  ds.Tables.Add(myData4)
                  DataGridView1.DataSource = myData4

                  'Count rows loaded into the datagrid. Useful for looping through the rows
                  Dim totalrows As Integer = DataGridView1.RowCount - 1
                  MessageBox.Show(totalrows & " rows were loaded into the datagrid!", "www.interloper.nl", MessageBoxButtons.OK, MessageBoxIcon.Information)

              End If
          End With

      Catch
      End Try

  End Sub

  'FUNCTION: Load csv into datagrid
  Private Function BuildDataTable(ByVal fileFullPath As String, ByVal separator As Char) As DataTable
      Dim myTable As DataTable = New DataTable("MyTable")
      Dim i As Integer
      Dim myRow As DataRow
      Dim fieldValues As String()
      Dim f As IO.File = Nothing
      Dim myReader As New IO.StreamReader(fileFullPath, System.Text.Encoding.UTF8)    'You might to change the encoding type

      Try
          fieldValues = myReader.ReadLine().Split(separator)

          For i = 0 To fieldValues.Length() - 1
              myTable.Columns.Add(New DataColumn("Field" & i))    'Column headers get Field# as name
          Next

          myRow = myTable.NewRow
          For i = 0 To fieldValues.Length() - 1
              myRow.Item(i) = fieldValues(i).ToString
          Next
          myTable.Rows.Add(myRow)
          While myReader.Peek() <> -1
              fieldValues = myReader.ReadLine().Split(separator)
              myRow = myTable.NewRow
              For i = 0 To fieldValues.Length() - 1
                  myRow.Item(i) = fieldValues(i).ToString
              Next
              myTable.Rows.Add(myRow)
          End While
      Catch ex As Exception
          MsgBox("Error building datatable: " & ex.Message)
          Return New DataTable("Empty")
      Finally
          myReader.Close()
      End Try

      Return myTable


  End Function
End Class



-------------------------------------------------------
Small Short Code

Dim myArray() As String = "asma , tehmina , sumera , sana , sohail"myArray = Split(line, ",")

Dim i As Integer
dim val as
For i = 0 To myArray.Length - 1
   val = myArray(i)                'read the value of the items in the array
   dg.Item(row, column) = val      'place it in the datagrid
Next

Monday, August 02, 2010

VB.NET Tab Page Change from code

Code:
mainTab.SelectedTab = mainTab.TabPages(0)


mainTab TAB NAME

SelectedTab

Code description:
TabPages(0) = where you want to Jump Index Number ( 0 ,1 , 2 )