Posted by: Red Souldier | June 5, 2009

Connecting Crystal Report ASP.NET using Dataset

Do you have so many problems with user logon database when you try to connecting to crystal report using SQL Server 2005 store procedure or table.

By using dataset, you dont have to worries about connection to sql server in crystal report.

First, you need to add or create new item of dataset object. Dataset object format as .xsd
Inside the dataset object, right click and add new datatable, after that right click on datatable and add column.
You can set the datatype as well.

Second step is creat new crystalreport, In field explorer, right click on database field and click database expert. In project data – ADO.NET Datasets choose dataset that you just created and add to the selected table. And you will have coulmn shows in the field explorer.

Thirs step, after you done with report, now we start in coding

This is the code
Dont forget to add this code first

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

   Private rpt As New ReportDocument
   Private Function GetData(ByVal strQuery As String, ByVal tableName As String) As Data.DataSet

        Dim ds As New DataSet

        'strConnection is you connection string
        Dim strConnString As String = strConnection
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand(strQuery, con)
        Dim sda As New SqlDataAdapter

        cmd.CommandType = CommandType.Text

            sda.SelectCommand = cmd
            sda.Fill(ds, tableName)
        Catch ex As Exception
            Return ds
        End Try
        Return ds
    End Function

   Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        rpt = New ReportDocument()
        rpt.Load(ServerPath & "CrystalReport2.rpt")

        rpt.SetDataSource(GetData("Select * from ctbankstatement0", "ctBankStatement0").Tables("ctBankStatement0"))

        'To pass paramater value with parameter name "field2"
        Dim field2 = New ParameterField
        field2.Name = "field2"
        Dim par_field2_value As New ParameterDiscreteValue
        par_field2_value.Value = 2

        If Not rpt Is Nothing Then
            CrystalReportViewer1.ReportSource = rpt
        End If
    End Sub
Posted by: Red Souldier | June 4, 2009

Read Excel Cell Value from ASP.NET 2008 – VB.NET

In this article I will show how to read value from excel per cell,]

First Im using FileUpload component from

                <asp:FileUpload ID="uplFile" runat="server"  Width="367px"/>

Don’t Forget to add triger to set autopostback, or else some people may counter error when the want to get the file name after they click browse button. Normally you put this code at the buttom

              <asp:PostBackTrigger ControlID="btnUpload" />

At behind code import
Imports Excel = Microsoft.Office.Interop

In event click

        Dim oApp As New Excel.Excel.Application()
        Dim oWBa As Excel.Excel.Workbook
        Dim oWS As Excel.Excel.Worksheet

            If uplFile.PostedFile.FileName <> "" Then
                filename = Mid(uplFile.PostedFile.FileName, InStrRev(uplFile.PostedFile.FileName, "\") + 1, Len(uplFile.PostedFile.FileName) - InStrRev(uplFile.PostedFile.FileName, "\"))
                uplFile.PostedFile.SaveAs(System.AppDomain.CurrentDomain.BaseDirectory & filename)

                oWBa = oApp.Workbooks.Open(System.AppDomain.CurrentDomain.BaseDirectory & filename)
                oWS = DirectCast(oWBa.Worksheets(1), Excel.Excel.Worksheet)
                oApp.Visible = False

                    Dim strkey2(3) As String
                    strkey2(0) = CStr(oWS.Range("A1")
                    strkey2(1) = CStr(oWS.Range("B1")
                    strkey2(2) = CStr(oWS.Range("C1")
                    strkey2(3) = CStr(oWS.Range("D1")

                   oWS = Nothing
                   oWBa = Nothing
                   oApp = Nothing

     End If

After googling the net to find anyone who post on how to create function Terbilang in crystal report, finally I found one site thats really works.

The original source is from here:

So first step is you need to create 4 function, first function is:

Function Bilangan (x as Number)
Select Case  x
Case 0
Bilangan = ""
case 1
Bilangan = "satu "
case 2
Bilangan = "dua "
case 3
Bilangan = "tiga "
case 4
Bilangan = "empat "
case 5
Bilangan = "lima "
case 6
Bilangan = "enam "
case 7
Bilangan = "tujuh "
case 8
Bilangan = "delapan "
case 9
Bilangan = "sembilan "
case 10
Bilangan = "sepuluh "
case 11
Bilangan = "sebelas "

End Select

End Function

Second Function

Function DictDown (x as Number) as String
Select Case  x
Case 0
DictDown = "ratus "
case 1
DictDown = ""
case 2
DictDown = "puluh "
End Select

End Function

Third Function

Function DictUp (x as Number) as string
Select Case  x
Case 0
DictUp = ""
case 1
DictUp = "ribu "
case 2
DictUp = "juta "
case 3
DictUp = "milyar "
case 4
DictUp = "triliun"
End Select

End Function

Last Function that we will be using to call the converter is:

Function getTerbilang (x as Number) as String
Dim out as String
dim inp as String
dim temp as string
dim temp2 as string
dim add as string
Dim pjg, j As Number
Dim flagEmpty As Boolean
flagEmpty = True
out = ""
inp = cstr (x,0,"","")
pjg = Len(inp)
temp = ""
temp2 = ""

For j = 0 To pjg - 1  Step 1
temp = Mid(inp,j+1, 1)

If (pjg - j) Mod 3 = 0 Then
flagEmpty = True
End If

If temp = "0" Then
add = ""
ElseIf temp = "1" And ((pjg - j) Mod 3 <> 1 Or (j = 0 And pjg <> 1)) Then
add = ""
If (pjg - j) Mod 3 = 2 Then
temp2 = Mid(inp,j + 2, 1) 'if 12, then read the 2
If temp2 = "1" Or temp2 = "0" Then
add = add & "Se"
add = add & bilangan(cdbl(temp2))
End If
If temp2 = "0" Then
add = add & "puluh "
add = add & "belas "
End If

j = j + 1 'This part read two number simultaneusly Example 112 it read 12 both.
add = "Se"
add = add & dictDown((pjg - j) Mod 3)
End If

flagEmpty = False
add = bilangan(CDbl(temp)) & dictDown((pjg - j) Mod 3)
flagEmpty = False
End If

If (pjg - j) Mod 3 = 1 And flagEmpty = False Then
add = add & dictUp((pjg - j - 1) \ 3)
End If

out = out & add

temp = UCase(Left(out, 1))
temp2 = Mid(out,2)
out = temp & temp2
getTerbilang = out

End Function

How to use it:

Very simple, in formula editor just call the function like this getTerbilang(25000), and it will return Dua puluh lime ribu

Posted by: Red Souldier | May 26, 2009

Function Terbilang (Indonesia Currency)

This function is used to convert from numeric to text in indonesia language. This function was coded for

Public Function Terbilang(ByVal nilai As Long) As String
Dim bilangan As String() = {"", "satu", "dua", "tiga", "empat", "lima", _
"enam", "tujuh", "delapan", "sembilan", "sepuluh", "sebelas"}
If nilai &lt; 12 Then
Return " " &amp; bilangan(nilai)
ElseIf nilai &lt; 20 Then
Return Terbilang(nilai - 10) &amp; " belas"
ElseIf nilai &lt; 100 Then
Return (Terbilang(CInt((nilai / 10))) &amp; " puluh") + Terbilang(nilai Mod 10)
ElseIf nilai &lt; 200 Then
Return " seratus" &amp; Terbilang(nilai - 100)
ElseIf nilai &lt; 1000 Then
Return (Terbilang(CInt((nilai / 100))) &amp; " ratus") + Terbilang(nilai Mod 100)
ElseIf nilai &lt; 2000 Then
Return " seribu" &amp; Terbilang(nilai - 1000)
ElseIf nilai &lt; 1000000 Then
Return (Terbilang(CInt((nilai / 1000))) &amp; " ribu") + Terbilang(nilai Mod 1000)
ElseIf nilai &lt; 1000000000 Then
Return (Terbilang(CInt((nilai / 1000000))) &amp; " juta") + Terbilang(nilai Mod 1000000)
ElseIf nilai &lt; 1000000000000 Then
Return (Terbilang(CInt((nilai / 1000000000))) &amp; " milyar") + Terbilang(nilai Mod 1000000000)
ElseIf nilai &lt; 1000000000000000 Then
Return (Terbilang(CInt((nilai / 1000000000000))) &amp; " trilyun") + Terbilang(nilai Mod 1000000000000)
Return ""
End If
End Function
Posted by: Red Souldier | May 23, 2009

SQL Server Management Objects with

SQL Serverâ„¢ Management Objects (SMO) offer developers a robust toolset for operations such as backing up and restoring databases, and issuing Data Definition Language (DDL) commands. Using SQL SMO you can also connect to SQL Server, iterate through a collection of database objects and perform a variety of tasks against them.

I have been created simple program using 2008 and sql server 2005 using SQL SMO.

The purpose of this program is to show the list of database available and show the data based on sql queries to datagrid. All the connection is using SQL SMO.

#1 step:

Add Reference Microsoft.SqlServer.SMO and Microsoft.SqlServer.ConnectionInfo

#2 step:

Create class SMOHelper:

Imports Microsoft.SqlServer.Management.Smo
Public Class SMOHelper
#Region "Fields"
<p style="padding-left:30px;">Private _serverName As String
Private _instance As String
Private _version As String
Private _isLocal As Boolean
Private _server As Server
Private _userName As String
Private _password As String
Private _useWindowsAuthentication As Boolean = True

#End Region
#Region "Properties"
<p style="padding-left:30px;">Public Property ServerName() As String
Return _serverName
End Get
Set(ByVal value As String)
_serverName = value
End Set
End Property
Public Property Instance() As String
Return _instance
End Get
Set(ByVal value As String)
_instance = value
End Set
End Property
Public Property version() As String
Return _version
End Get
Set(ByVal value As String)
_version = value
End Set
End Property
Public Property isLocal() As Boolean
Return _isLocal
End Get
Set(ByVal value As Boolean)
_isLocal = value
End Set
End Property
Public ReadOnly Property server() As Server
Return _server
End Get
End Property
Public Property userName() As String
Return _userName
End Get
Set(ByVal value As String)
_userName = value
End Set
End Property
Public Property password() As String
Return _password
End Get
Set(ByVal value As String)
_password = value
End Set
End Property
Public Property useWindowsAuthentication() As Boolean
Return _useWindowsAuthentication
End Get
Set(ByVal value As Boolean)
_useWindowsAuthentication = value
End Set
End Property

#End Region
#Region "Constructors"
<p style="padding-left:30px;">Public Sub New()
_server = New Server
End Sub
Public Sub New(ByVal serverAndInstanceName1 As String, ByVal username1 As String, ByVal password1 As String, ByVal useWindowsAuthentication1 As Boolean)
_server = New Server
ServerName = serverAndInstanceName1
userName = username1
password = password1
End Sub

#End Region
#Region "Methods"
<p style="padding-left:30px;">Public Function GetDatabaseNameList() As List(Of String)
Dim dbList As New List(Of String)()
For Each db As Database In server.Databases
Return dbList
End Function
Public Sub Connect()
server.ConnectionContext.ServerInstance = ServerName
If useWindowsAuthentication = True Then
server.ConnectionContext.LoginSecure = useWindowsAuthentication
server.ConnectionContext.LoginSecure = useWindowsAuthentication
server.ConnectionContext.Login = userName
server.ConnectionContext.Password = password
End If
Catch ex As Exception
MsgBox("ERROR" &amp; ex.Message.ToString)
End Try
End Sub

#End Region
End Class

<img class="aligncenter size-full wp-image-124" title="PRINT" src="" alt="PRINT" width="457" height="576" />

#3 Step: Add this code to the form code

Imports Microsoft.SqlServer.Management.Smo

Public Class Form1
Dim smoHelper As New SMOHelper

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim dtServers As DataTable = SmoApplication.EnumAvailableSqlServers(False)
Dim sqlServerName As String
For Each row As DataRow In dtServers.Rows

sqlServerName = row("Server").ToString
If Not row("Instance") Is Nothing And row("Instance").ToString.Length &gt; 0 Then
sqlServerName += "\" + row("Instance").ToString
End If
txtServerName.Text = sqlServerName.ToString

End Sub

Private Sub chkUseWindowsAuthentication_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkUseWindowsAuthentication.CheckedChanged
If chkUseWindowsAuthentication.Checked = True Then
txtPassword.Enabled = False
txtUserName.Enabled = False
txtPassword.Enabled = True
txtUserName.Enabled = True
End If

End Sub

Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
If txtServerName.Text &lt;&gt; "" And (chkUseWindowsAuthentication.Checked = True Or (txtPassword.Text &lt;&gt; "" And txtUserName.Text &lt;&gt; "")) Then

grpConnect.Enabled = False
SMOHelper = New SMOHelper(txtServerName.Text, txtUserName.Text, txtPassword.Text, chkUseWindowsAuthentication.Checked)
If Not smoHelper.server Is Nothing Then
cboDatabase.DataSource = smoHelper.GetDatabaseNameList()

End If

End If

End Sub

Private Sub btnExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecute.Click
If txtSQL.Text &lt;&gt; "" Then
Dim db As Database = smoHelper.server.Databases(cboDatabase.SelectedValue.ToString)
If Not db Is Nothing Then
Dim sql As String
sql = txtSQL.Text
If sql.Replace(" ", "").ToUpper.StartsWith("SELECT") Then
Dim ds As DataSet = db.ExecuteWithResults(txtSQL.Text)

dgvResults.DataSource = ds.Tables(0)

End If
Catch ex As Exception

End Try
End If
End If
End Sub
End Class

You can

In that site, you can download example of program in c#

Posted by: Red Souldier | May 19, 2009

Microsoft SQL Server 2005 Cursor Example

DECLARE @name varchar(25)
SELECT id,name From myTable
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id, @name

— do something
FETCH NEXT FROM db_cursor INTO @id, @name

CLOSE db_cursor
DEALLOCATE db_cursor

Posted by: Red Souldier | May 1, 2009

Virtuemart, Remove SKU and Price in list view

When you are having virtuemart in Product List Style = Flat Product List, you will see SKU and Price. Some people might not want to show SKU or price column. In virtuemart you cannot simply hide it, but there is other ways that you have to do it manually.

Here is step by step how to remove SKU or price column or other column in virtuemart list view.

1. search file name browse_listtable.tpl.php in /components/com_virtuemart/themes/default/templates/browse/includes

2. go to line 15 to 18:
remove code :
$tableheader[] = $VM_LANG->_('PHPSHOP_CART_SKU');
if( _SHOW_PRICES && $auth['show_prices'] ) {
$tableheader[] = $VM_LANG->_('PHPSHOP_CART_PRICE');

3. After remove go to line 36 to 39:
remove code:
$data[$row][] = $product['product_sku'];
if( _SHOW_PRICES && $auth['show_prices'] ) {
$data[$row][] = $product['product_price'];


I’m using joomla v1.5.10 and virtuemart v.1.1.3

Posted by: Red Souldier | March 5, 2009

Icare : Futuristic Motorcycle from Enzyme Design

Now be a style icon with the Icare high-end futuristic bike. With its eccentric looks and spectacular design it surely is a dream come true for all the bike lovers. The engine, which is Honda 6 cylinder flat engine and 1800cm3, is completely out of this world and will definitely keep you mesmerized. Its powerful body gives you a sense of security while driving on the tarnished highways and its design looks more that of an alien. A bike with a different look and with the unbeatable performance and strength. So move out and flaunt your Icare and unleash the beast.

online advertising

Posted by: Red Souldier | March 4, 2009

Sorry guys, MyHpf now officialy scam

This morning I check my account earning in myhpf website. I already set a target within one day I search around 30 words. I hope I can get more.

And you know what happen, this page comes out

As you can see Im from indonesia, and indonesia is the countries they are able to offer their service. but they said no longer able to offer their service .

Shit ass

Goodbye my money too, goodbye referrals
Goodbye my 10 pound, now become a piece of shit

« Newer Posts - Older Posts »