Ganesh S. Chatraband (Proffessional Blog)

Tuesday, 19 January 2016

PIVOT in SQL

USING UNION CLAUSE

SELECT SUM(MALE) MALE,SUM(FEMALE) FEMALE

FROM

(select COUNT(Gender) as Male,0 AS'FEMALE' from Workers WHERE Gender='MALE'

UNION

select 0 AS 'MALE',COUNT(Gender) AS FEMALE FROM Workers WHERE Gender='FEMALE'

)AA

PIVOT

SELECT SUM(MALE) MALE,SUM(FEMALE) FEMALE FROM Workers

pivot

(

COUNT(GENDER)

for Gender in (MALE, FEMALE)

) piv

Friday, 21 November 2014

public class clsGUIHandler
{

private static clsGUIHandler instance;
private clsGUIHandler()
{
}
public static clsGUIHandler Instance
{
get
{
if (instance == null)
{
instance = new clsGUIHandler();
}
return instance;
}
}

public void display()
{
}

}

Consumer :

clsGUIHandler.Instance.display();

Wednesday, 19 November 2014

SQL- Row Number, Rank, Dense Rank, Partition by

Tuesday, 22 October 2013

Single Instance Application VB.NET

'How to check / Allow only one instance of Application is Running.

Imports System.Diagnostics
Module mdlMain

    Public Sub main()
        Application.EnableVisualStyles()
        Dim tempProcess As Process
        tempProcess = checkInstance()
        If tempProcess Is Nothing Then
            Application.Run(New Form1)
        Else
            MessageBox.Show("Application is already running")
        End If
    End Sub

    'Return Value : Returns the Process if the exe
    'is already running or else returns nothing
    Public Function checkInstance() As Process
        Dim cProcess As Process = Process.GetCurrentProcess()
        Dim aProcesses() As Process = Process.GetProcessesByName(cProcess.ProcessName)
        'loop through all the processes that are currently running on the
        'system that have the same name
        For Each process As Process In aProcesses
            'Ignore the currently running process
            If process.Id <> cProcess.Id Then
                'Check if the process is running using the same EXE as this one
                If Reflection.Assembly.GetExecutingAssembly().Location = cProcess.MainModule.FileName Then
                    'if so return to the calling function with the instance of the process
                    Return process
                End If
            End If
        Next
        'if nothing was found then this is the only instance, so return null
        Return Nothing
    End Function

End Module

Tuesday, 3 September 2013

Sending SMS Via PC (VB6)

(VB sms application with source code. Mobile/USB connection. AT command for sending SMS.)

Private Sub cmdSend_Click()
With MSComm1
.CommPort = Val(Trim(txtPortNo))
.Settings = "9600,N,8,1"
.Handshaking = comRTS
.RTSEnable = True
.DTREnable = True
.RThreshold = 1
.SThreshold = 1
.InputMode = comInputModeText
.InputLen = 0
.PortOpen = True 'must be the last
End With
' Send an 'AT' command to the phone
MSComm1.Output = "AT" & vbCrLf
Sleep 500
MSComm1.Output = "AT+CMGF=1" & vbCrLf 'Set Modem always in Text Mode...
Sleep 500
MSComm1.Output = "AT+CMGS=" & Chr(34) & TxtNumber.Text & Chr(34) & vbCrLf
Sleep 1000
MSComm1.Output = TxtMessage.Text & Chr(26)
Sleep 2000

Dim Result As String
Result = MSComm1.Input

If MSComm1.PortOpen = True Then
MSComm1.PortOpen = False
End If

MsgBox "Message Send" & Result

End Sub

Wednesday, 31 July 2013

How to use ControlParameter in asp.net

Example: Country-->State-->City selection in asp.net

Asp.net Page Source:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

#form1

{ height: 283px;

width: 218px; }

</style>

</head>

<body>

<asp:SqlDataSource ID="SqldataSourceCountry" runat="server"

ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>"

SelectCommand="SELECT [countryID], [countryName] FROM [tblCountry] ORDER BY [countryName]">

</asp:SqlDataSource>

<asp:SqlDataSource ID="SqlDataSourceState" runat="server"

ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>"

SelectCommand="SELECT [stateID], [stateName] FROM [tblState] WHERE ([countryID] = @countryID) ORDER BY [stateName]">

<asp:ControlParameter ControlID="cmbCountry" DefaultValue="0" Name="countryID"

PropertyName="SelectedValue" Type="Int32" />

</SelectParameters>

</asp:SqlDataSource>

<asp:SqlDataSource ID="SqlDataSourceCity" runat="server"

ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>"

SelectCommand="SELECT [cityId], [cityName] FROM [tblCity] WHERE ([stateID] = @stateID) ORDER BY [cityName]">

<asp:ControlParameter ControlID="cmbStates" DefaultValue="0" Name="stateID"

PropertyName="SelectedValue" Type="Int32" />

</SelectParameters>

</asp:SqlDataSource>

<br />Select Country:<br>

<asp:DropDownList ID="cmbCountry" runat="server"

DataSourceID="SqldataSourceCountry" DataTextField="countryName"

DataValueField="countryID" Height="18px" Width="169px" AutoPostBack="True">

</asp:DropDownList>

<br />

<br />Select State:<br />

<asp:DropDownList ID="cmbStates" runat="server"

DataSourceID="SqlDataSourceState" DataTextField="stateName"

DataValueField="stateID" Height="50px" Width="163px" AutoPostBack="True">

</asp:DropDownList>

<br />

<br />Select City <br />

<asp:DropDownList ID="cmbCity" runat="server" Height="33px" Width="167px"

DataSourceID="SqlDataSourceCity" DataTextField="cityName"

DataValueField="cityId">

</asp:DropDownList>

</form>

</body>

</html>

SQL Queries:

Create Country/State/City Tables as follows

CREATE TABLE [dbo].[tblCountry](

[countryID] [int] IDENTITY(1,1) NOT NULL,

[countryName] [varchar](50) NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[tblState](

[stateID] [int] IDENTITY(1,1) NOT NULL,

[stateName] [varchar](50) NOT NULL,

[countryID] [int] NOT NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[tblCity](

[cityId] [int] IDENTITY(1,1) NOT NULL,

[cityName] [varchar](50) NOT NULL,

[stateID] [int] NOT NULL

) ON [PRIMARY]

Insert Data into tblCountry/State/City Tables

Friday, 26 July 2013

SQL Database Backup Restore Utility
VB.NET Code

Code :
    'Database resourecs
    Dim con As SqlConnection
    Dim cmd As SqlCommand
    Shared conn As New SqlConnection

#Region "DATABASE BACKUP"
    Public Function DatabaseBackup(ByVal ToDisk As String) As Boolean
        Try
            con = New SqlConnection(APPCONSTRING)
            cmd = New SqlCommand("backup database " & SQL_DBNAME & " to disk='" & ToDisk & ".bak'", con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            'MsgBox("Database Backup Successfull")
        Catch ex As SqlException
            MsgBox("SQlServer Exception :" & ex.Message)
            Dim frm As New FrmApplicationSettings
            frm.ShowDialog()
            Return False
        Catch ex As Exception
            MsgBox("Exception :" & ex.Message)
            Dim frm As New FrmApplicationSettings
            frm.ShowDialog()
            Return False
        Finally
            If Not (con Is Nothing) Then con.Dispose()
            con = Nothing
            If Not (cmd Is Nothing) Then cmd.Dispose()
        End Try
        Return True
    End Function
#End Region

#Region "DATABASE RESTORE"
    Public Function DatabaseRestore(ByVal FromDisk As String) As Boolean
        Try
            MsgBox(FromDisk)

            Dim sqlstr As String = "RESTORE DATABASE " & SQL_DBNAME & " " _
            & "FROM DISK='" & FromDisk & "'" _
            & " WITH RECOVERY"

            MsgBox(sqlstr)

            Dim CONSTR = "Persist Security Info=False;User ID=" & SQL_USER_ID & ";pwd=" & SQL_PASSWORD & ";Initial Catalog=master;Data Source=" & SQL_SERVER_NAME
            con = New SqlConnection(CONSTR)
            cmd = New SqlCommand(sqlstr, con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            MsgBox("Database restore Successfull", MsgBoxStyle.Information, MSG_TITLE)
        Catch ex As SqlException
            MsgBox("SQlServer Exception :" & ex.Message, MsgBoxStyle.Critical, MSG_TITLE)
            Return False
        Catch ex As Exception
            MsgBox("Exception :" & ex.Message, MsgBoxStyle.Critical, MSG_TITLE)
            Return False
        Finally
            If Not (con Is Nothing) Then con.Dispose()
            con = Nothing
            If Not (cmd Is Nothing) Then cmd.Dispose()
            cmd = Nothing
        End Try
        Return True
    End Function
#End Region

Tuesday, 19 January 2016

PIVOT in SQL

Friday, 21 November 2014

Singleton Pattern

Wednesday, 19 November 2014

SQL- Row Number, Rank, Dense Rank, Partition by

Tuesday, 22 October 2013

Single Instance Application VB.NET

Tuesday, 3 September 2013

Sending SMS Via PC (VB6)

Wednesday, 31 July 2013

How to use ControlParameter in asp.net

Friday, 26 July 2013