Posted by: Red Souldier | January 15, 2010

VB.NET Date Time Format Patterns

Standard Format Example List:

Format Code Result
MM/dd/yyyy Date.Now().ToString(“d”) 1/15/2010
dddd, dd MMMM yyyy Date.Now().ToString(“D”) Friday, January 15, 2010
dddd, dd MMMM yyyy hh:mm tt Date.Now().ToString(“f”) Friday, January 15, 2010 11:06 AM
dddd, dd MMMM yyyy HH:mm:ss Date.Now().ToString(“F”) Friday, January 15, 2010 11:06:46 AM
MM/dd/yyyy h:mm tt Date.Now().ToString(“g”) 1/15/2010 11:06 AM
MM/dd/yyyy HH:mm:ss Date.Now().ToString(“G”) 1/15/2010 11:06:46 AM
MMMM dd Date.Now().ToString(“m”) January 15
dd MMM yyyy HH’:’mm’:’ss ‘GMT’ Date.Now().ToString(“r”) Fri, 15 Jan 2010 11:06:46 GMT
yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss Date.Now().ToString(“s”) 2010-01-15T11:06:46
yyyy’-‘MM’-‘dd HH’:’mm’:’ss’Z’ Date.Now().ToString(“u”) 2010-01-15 11:06:46Z
dddd, MMMM dd yyyy HH:mm:ss Date.Now().ToString(“U”) Friday, January 15, 2010 3:06:46 AM
yyyy MMMM Date.Now().ToString(“y”) January, 2010

Read More Complete List >>

Posted by: Red Souldier | December 2, 2009

ASP.NET: How to read XML Data String to Dataset

The output of XML String:

<?xml version="1.0" encoding="utf-8" ?> 
- <NewDataSet> 
- <Table> 

In order to convert from XML to dataset, this is the code:

     Dim MyDS as new Dataset("Table1")     
     Dim strReader As StringReader = New StringReader(YourXmlString)
Posted by: Red Souldier | December 2, 2009

ASP.NET: Convert String XML to XML Node

For example I have string value like this:

    <?xml version="1.0"?>
           <Product type="Condom">

Since this this is a string value, so we need to convert to xml node to be able to process.
So this is the code:

        Imports System.Xml
        Imports System.Xml.Serialization

        Dim xmlNodeList As System.Xml.XmlNodeList
        Dim xmlNode As System.Xml.XmlNode
        Dim XmlDoc As New XmlDocument
	Dim _condomName as String
	Dim _ExpDate as String
	Dim _Type as String
	Dim _color as String
	Dim _qty as String

	'Select the 1st Xml Element
        xmlNodeList = XmlDoc.GetElementsByTagName("Product")
	'Because only 1 Product Element, so select index by 0
        xmlNode = xmlNodeList.Item(0)
        _condomName = xmlNode.SelectSingleNode("CondomName").InnerText
        _ExpDate = xmlNode.SelectSingleNode("ExpDate").InnerText
        _Type = xmlNode.SelectSingleNode("Type").InnerText
        _color = xmlNode.SelectSingleNode("Color").InnerText
        _qty = False 'xmlNode.SelectSingleNode("Qty").InnerText

If you have more than 1 product, you need to loop the xmlList:

For Each xmlNode As XmlNode In xmlNodeList
        _condomName = xmlNode.SelectSingleNode("CondomName").InnerText
        _ExpDate = xmlNode.SelectSingleNode("ExpDate").InnerText
        _Type = xmlNode.SelectSingleNode("Type").InnerText
        _color = xmlNode.SelectSingleNode("Color").InnerText
        _qty = False 'xmlNode.SelectSingleNode("Qty").InnerText
Posted by: Red Souldier | December 2, 2009

ASP.NET: How to use Javascript alert inside AJAX UpdatePanel

Normally when we add Javascript Alert to our it will be like this:
Page. ClientScript.RegisterStartupScript(this.GetType(), "winPop", "alert('Update is successful')
but this code is not working if you using the code inside update panel.
So in order to add javascript in update panel you need to register you client script to your script manager as shown below:
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('Update is successfu.');",True)

Posted by: Red Souldier | November 30, 2009

SSIS: Update data from different table if data is Null

SSIS: SSIS stands for SQL Server Integration Services. It is the new data transformation standard for SQL Server 2005 and has replaced the old SQL Server Data Transformation Services.

In this post, I want to show how to update data from different table with condition that the data is Null.

1st Table is Person.Address and 2nd table is Address2

Now I want to update data in column AddressLine2 in Person.Address by using data in Address2 with condition that AddressID is same and AddressLine2 in Person.Address is Null

So 1st we create Data Flow Task in Control Flow tab

Create new Ole DB Connection in Connection manager, and add the flow that looks like below figures

In the OLE DB Source, add the table Person.Address
In the Lookup, Add table Address2. In the column tab at the lookup editor set like this:

So this Lookup transformation is actually add a new column from other table based on addressed.

The Derived Column transformation creates new column or replacing current column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input. So from we need this to replace the new column (AddressLine2) from table Address2 into column AddressLine2 in table Person.Address

First we choose Replace ‘OLE DB Source.AddressLine2’ in Derived Column, then we add expression:

(ISNULL([OLE DB Source].AddressLine2) ? Lookup.AddressLine2 : [OLE DB Source].AddressLine2)

The OLE DB Command transformation runs an SQL statement for each row in a data flow. For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table. So in this case, we need this transformation to update current data.

In Advanced Editor for Ole DB Command, in the component properties add SQL Command:

update Person.Address set AddressLine2 = ?
where AddressID = ?

Question mark indicate the parameter. Just remember the sequence of the parameter.
After we fill in the Sql Command, choose Column Mappings Tab and point the Available input columns to the Available Destination Columns based on the parameter that already created.

Then execute the package


Posted by: Red Souldier | July 17, 2009

ASP.NET – Convert CSV to Dataset

Here is the function to convert CSV file to dataset in using
First you need to upload the csv file into your web server, then save the file path.
Add this function code:

    Private Shared Function getCsvToDataset(ByVal strPath As String) As DataSet
        Dim strLine As String
        Dim strArray As String()
        Dim charArray As Char() = New Char() {","c}
        Dim ds As New DataSet()
        Dim dt As DataTable = ds.Tables.Add("TheData")
        Dim aFile As New FileStream(strPath, FileMode.Open)
        Dim sr As New StreamReader(aFile)

        strLine = sr.ReadLine()

        strArray = strLine.Split(charArray)

        For x As Integer = 0 To strArray.GetUpperBound(0)

        strLine = sr.ReadLine()
        While strLine IsNot Nothing
            strArray = strLine.Split(charArray)
            Dim dr As DataRow = dt.NewRow()
            For i As Integer = 0 To strArray.GetUpperBound(0)
                dr(i) = strArray(i).Trim()
            strLine = sr.ReadLine()
        End While
        Return ds
    End Function

I think, this is the most effective way to convert csv to dataset. Before this, I already try to use Microsoft.Jet.OLEDB.4.0 but the problem that shows an error message in finding csv file on the web server. So this method using FileStream is the best way.

Posted by: Red Souldier | July 11, 2009

Go green and Recycle design for E-mail signature V1

These are some of my vector design that can be used for e-mail signature


Image Link

<img src="" alt="GO-GREEN3" title="GO-GREEN3" width="430" height="59" class="aligncenter size-full wp-image-183" />


Image Link

<img src="" alt="GO-GREEN4" title="GO-GREEN4" width="398" height="102" class="aligncenter size-full wp-image-186" />


Image Link

<img src="" alt="recycle" title="recycle" width="247" height="106" class="aligncenter size-full wp-image-187" />


Image Link

<img src="" alt="recycle2" title="recycle2" width="328" height="74" class="aligncenter size-full wp-image-188" />
Posted by: Red Souldier | June 22, 2009

ASP.NET Cross Tab / Pivot from Data Table

Sometimes when you working with the large data, you might want to display the data in pivot table / cross tab. Crystal report have a very nice wizard to make our life easier to create cross tab, but not in

For example you have data table like Table CompanyDetail populated from the SQL Select Query below

company_code PAY_AMOUNT tax_period_month
777 3000,0000 05
777 2000,0000 08
777 1000,0000 11
1010000 4000,0000 11
777 5000,0000 11

And you want to display into this order:

company_code 05 08 11
777 3000,0000 2000,0000 6000,0000
1010000     4000,0000

So Here is the step to convert Data Table data into pivot table
Step1. Create this function:

ColumnX = Is the Column that you want to make cross tab (In this example is tax_period_month)
ColumnY = Is the column that will be put in the left column (company_code)
ColumnZ = Is the total value from combining columnX and columnY (PAY_AMOUNT)

    Function GetInversedDataTable(ByVal table As DataTable, ByVal columnX As String, ByVal columnY As String, ByVal columnZ As String, ByVal nullValue As String, ByVal sumValues As Boolean) As DataTable
        'Create a DataTable to Return
        Dim returnTable As New DataTable()

        If columnX = "" Then
            columnX = table.Columns(0).ColumnName
        End If

        'Add a Column at the beginning of the table

        'Read all DISTINCT values from columnX Column in the provided DataTale
        Dim columnXValues As New List(Of String)()

        For Each dr As DataRow In table.Rows

            Dim columnXTemp As String = dr(columnX).ToString()
            If Not columnXValues.Contains(columnXTemp) Then
                'Read each row value, if it's different from others provided, add to the list of values and creates a new Column with its value.
            End If

        'Verify if Y and Z Axis columns re provided
        If columnY <> "" AndAlso columnZ <> "" Then
            'Read DISTINCT Values for Y Axis Column
            Dim columnYValues As New List(Of String)()

            For Each dr As DataRow In table.Rows
                If Not columnYValues.Contains(dr(columnY).ToString()) Then
                End If

            'Loop all Column Y Distinct Value
            For Each columnYValue As String In columnYValues
                'Creates a new Row
                Dim drReturn As DataRow = returnTable.NewRow()
                drReturn(0) = columnYValue
                'foreach column Y value, The rows are selected distincted
                Dim rows As DataRow() = table.[Select]((columnY & "='") + columnYValue & "'")

                'Read each row to fill the DataTable
                For Each dr As DataRow In rows
                    Dim rowColumnTitle As String = dr(columnX).ToString()

                    'Read each column to fill the DataTable
                    For Each dc As DataColumn In returnTable.Columns
                        If dc.ColumnName = rowColumnTitle Then
                            'If Sum of Values is True it try to perform a Sum
                            'If sum is not possible due to value types, the value displayed is the last one read
                            If sumValues Then
                                    drReturn(rowColumnTitle) = Convert.ToDecimal(drReturn(rowColumnTitle)) + Convert.ToDecimal(dr(columnZ))
                                    drReturn(rowColumnTitle) = dr(columnZ)
                                End Try
                                drReturn(rowColumnTitle) = dr(columnZ)

                            End If
                        End If


            Throw New Exception("The columns to perform inversion are not provided")
        End If

        'if a nullValue is provided, fill the datable with it
        If nullValue <> "" Then
            For Each dr As DataRow In returnTable.Rows
                For Each dc As DataColumn In returnTable.Columns
                    If dr(dc.ColumnName).ToString() = "" Then
                        dr(dc.ColumnName) = nullValue
                    End If
        End If

        Return returnTable
    End Function

Step 2: Add gridview to the page and create connection to database to set data table:

 Dim conn As SqlConnection = Nothing
            Dim connString As String = "Data Source=;Initial Catalog=;Integrated Security=True"

            conn = New SqlConnection(connString)
            Dim query As String = "select company_code, PAY_AMOUNT, tax_period_month from ttSSP0 order by tax_period_month"
            Dim cmd As SqlCommand = New SqlCommand(query, conn)
            Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Dim dt As DataTable = New DataTable()
            Dim dt2 As DataTable = New DataTable()
            dt2 = GetInversedDataTable(dt, "tax_period_month", "company_code", "PAY_AMOUNT", "", True)
            GridView1.DataSource = dt2
        Catch ex As SqlException
            ' handle error
        Catch ex As Exception
            ' handle error
        End Try
Posted by: Red Souldier | June 12, 2009

Create Multiple row / group header in gridview ASP.NET

This article shows how to create multiple row / group header in gridview

group header

first you need to add gridview to database and set the datasource connection.
After you have done with all of that, you just add this function to your code

  Public Sub GetMultiRowHeader(ByVal e As GridViewRowEventArgs, ByVal GetCels As SortedList)

        If e.Row.RowType = DataControlRowType.Header Then
            Dim row As GridViewRow
            Dim enumCels As IDictionaryEnumerator = GetCels.GetEnumerator()

            row = New GridViewRow(-1, -1, DataControlRowType.Header, DataControlRowState.Normal)
            While enumCels.MoveNext()

                Dim count As String() = enumCels.Value.ToString().Split(Convert.ToChar(","))
                Dim Cell As TableCell
                Cell = New TableCell()
                Cell.RowSpan = Convert.ToInt16(count(2).ToString())
                Cell.ColumnSpan = Convert.ToInt16(count(1).ToString())
                Cell.Controls.Add(New LiteralControl(count(0).ToString()))
                Cell.HorizontalAlign = HorizontalAlign.Center
                Cell.ForeColor = System.Drawing.Color.White
            End While

            e.Row.Parent.Controls.AddAt(0, row)
        End If
    End Sub

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        'Everytime you want to add new rows header, you creat new formatcells variable
        Dim formatCells As New SortedList
        'Format cells format:"
        ' formatCells.Add(<Column number>, <Header Name,number of column to colspan, number of row to rowspan>)

        formatCells.Add("1", "ROW SPAN,1,2")
        formatCells.Add("2", "TopGroup,4,1")
        Dim formatcells2 As New SortedList
        formatcells2.Add("1", "Subgroup1,2,1")
        formatcells2.Add("2", "Subgroup2,2,1")
        GetMultiRowHeader(e, formatcells2)
        GetMultiRowHeader(e, formatCells)
    End Sub

The original code is taken from this blog

This article demonstrates how to use ASP.NET and ADO.NET with Visual Basic .NET to create and to call a Microsoft SQL Server stored procedure with an input parameter and an output parameter.

1 step create store procedure

Create Procedure GetAuthorsByLastName1 (@au_lname varchar(40), @RowCount int output)  

select * from authors where au_lname like @au_lname; 

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT

Call the stored procedure in code

  Private Function getDataset() As DataSet
        Dim DS As DataSet
        Dim MyConnection As SqlConnection
        Dim MyDataAdapter As SqlDataAdapter

        'Create a connection to the SQL Server.
        MyConnection = New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")

        'Create a DataAdapter, and then provide the name of the stored procedure.
        MyDataAdapter = New SqlDataAdapter("GetAuthorsByLastName", MyConnection)

        'Set the command type as StoredProcedure.
        MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

        'Create and add a parameter to Parameters collection for the stored procedure.
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@au_lname", _
       SqlDbType.VarChar, 40))

        'Assign the search value to the parameter.
        MyDataAdapter.SelectCommand.Parameters("@au_lname").Value = Trim(txtLastName.Text)

        'Create and add an output parameter to Parameters collection. 
        MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@RowCount", _
        SqlDbType.Int, 4))

        'Set the direction for the parameter. This parameter returns the Rows returned.
        MyDataAdapter.SelectCommand.Parameters("@RowCount").Direction = ParameterDirection.Output

        DS = New DataSet() 'Create a new DataSet to hold the records.
        MyDataAdapter.Fill(DS, "AuthorsByLastName") 'Fill the DataSet with the rows returned.

        'Get the number of rows returned, and then assign it to the variable
        Dim rowCount As String
        rowCount = MyDataAdapter.SelectCommand.Parameters(1).Value.ToString & " Rows Found!"

        MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
        MyConnection.Close() 'Close the connection.

        Return DS

    End Function

Older Posts »