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

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

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.


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

          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()
                  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!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)

              End If
          End With

      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

          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

          myRow = myTable.NewRow
          For i = 0 To fieldValues.Length() - 1
              myRow.Item(i) = fieldValues(i).ToString
          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
          End While
      Catch ex As Exception
          MsgBox("Error building datatable: " & ex.Message)
          Return New DataTable("Empty")
      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

Monday, August 02, 2010

VB.NET Tab Page Change from code

mainTab.SelectedTab = mainTab.TabPages(0)

mainTab TAB NAME


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