Create Sql Function To Calculate Total Salary of Employee Based on Present Day in particular Month

--Function
create function dbo.funemp
(
@eid bigint,@name varchar(300),@sal float,@no_Of_day int,@month varchar(100))
returns float as
begin
declare @avgSal float
set @avgSal=(@sal/@month)*@no_of_day
return @avgSal  
end
==============

Table


CREATE TABLE [dbo].[emp](
[Eid] [int] NOT NULL,
[Name] [varchar](30) NULL,
[Salary] [int] NULL,
[No_of_day] [int] NULL,
[MonthOfYear] [varchar](200) NULL,
)
==========
Query To Display Calculated Record Of Employee
--
select *,dbo.funemp(eid,name,salary,no_of_day,Monthofyear) as 'Total Salary' from emp

To delete selected Row by Key Down in GridView

private sub Grid1_keyDown()
if e.keycode=keys.Delete then

For Each row as DataGridViewRow in Grid1.SelectedRows
Grid1.Rows.Remove(row)
Next

end if

Open PDF file by Clicking Link Button using ASP.NET

protected sub LInk_click()
Dim pdfPath As String = Server.MapPath("~/AM54640303_2014-05-12_18-51-10.pdf")
        Dim client As New WebClient()
        Dim buffer As [Byte]() = client.DownloadData(pdfPath)
        Response.ContentType = "application/pdf"
        Response.AddHeader("content-length", buffer.Length.ToString())
        Response.BinaryWrite(buffer)

end sub


--"pdf file should be present in solution explorer."

Open Word file by clicking link button in ASP.NET

 Protected Sub ll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ll.Click
        Response.Clear()
        Response.ContentType = "application/doc"
        Response.AddHeader("content-disposition", "attachment;filename=1.doc")
        Response.TransmitFile("1.doc")
        Response.End()
    End Sub


''1.doc" should be present in solution explorer of VS

Delete Duplicates Row in SQL SERVER

DELETE FROM table WHERE col1 IN (
    SELECT id FROM table GROUP BY id HAVING ( COUNT(col1) > 1 )
)

Shatrughana Kumar Gupta: DateTime Formating Using VB.NET

Shatrughana Kumar Gupta: DateTime Formating Using VB.NET:  Dim str As String = "2014-06-10 17:41:38.760"         Dim str1 As Date = Convert.ToDateTime(str)         Dim strres As String ...

DateTime Formating Using VB.NET

 Dim str As String = "2014-06-10 17:41:38.760"
        Dim str1 As Date = Convert.ToDateTime(str)
        Dim strres As String = Format(str1, "yyyy-MMM-dd hh:mm:ss")


result : 2014-Jun-10 05:41:38

Date Time Format of date

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)
===========================================

return:

Nov 04 2011 11:45 PM
11-04-11
11-04-2011
04 Nov 11
04 Nov 2011
04 Nov 2011 11:45:34:243

For particular column then:

select convert(varchar(10),enter_date,110),college_name_removesc from affidavitdata

''enter_date is column

Remove Duplicates from String

 Function rduplicatestring(ByVal str As String) As String
        rduplicatestring = ""
        Dim myarr() As String = Split(str, Space(1))
        myarr = myarr.Distinct().ToArray
        myarr = myarr.DefaultIfEmpty().ToArray
        rduplicatestring = Replace(Join(myarr, Space(1)), Space(2), Space(1))
        Return rduplicatestring
    End Function

Alphabetical Sorting Of any String Using vb.net

 Function alphabetsorting(ByVal str As String) As String
        alphabetsorting = ""
        Dim arrayLetters As Array = str.ToCharArray()
        Array.Sort(arrayLetters)
        For i As Integer = 0 To arrayLetters.Length - 1
            If alphabetsorting = "" Then
                alphabetsorting = arrayLetters(i)
            Else
                alphabetsorting = alphabetsorting & arrayLetters(i)
            End If
        Next
        Return alphabetsorting
    End Function

Add Images in Listbox and copy from one directory to another directory using vb.net

 Dim path1 As String = "D:\scan"
                My.Computer.FileSystem.CreateDirectory(path1)
                Dim info As New DirectoryInfo(path1)
                If (info.Exists) Then
                    Dim file As FileInfo = Nothing
                    file = info.GetFiles.Last
                    If (file.Extension = ".png") Then
                        ListBox1.Items.Add(file.FullName)
                    End If
                    PictureBox1.ImageLocation = fileName
                    i = 0
====================================================

For index As Integer = 0 To ListBox1.Items.Count - 1
            Dim src1 As String = ListBox1.Items(index.ToString())
            If Directory.Exists("D:MyScan\") Then
                Dim f1 As String = Path.GetFileName(src1)
                dest1 = Convert.ToString("D:\MyScan\") & f1
                File.Copy(src1, dest1)
                PictureBox1.Image = Nothing
            Else
                Directory.CreateDirectory("D:\MyScan\")
                Dim f1 As String = Path.GetFileName(src1)
                dest1 = Convert.ToString("D:\MyScan\") & f1
                File.Copy(src1, dest1)
                PictureBox1.Image = Nothing
            End If

Stop Sorting In Data Grid View using vb.net

Private Sub gridshot_ColumnAdded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs) Handles gridshot.ColumnAdded
        gridshot.Columns.Item(e.Column.Index).SortMode = DataGridViewColumnSortMode.NotSortable
    End Sub

How to count files in Specific Folder using vb.net

Imports System.IO
=====================
Public Class Form2
    Dim Files() As String
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim path1 As String = "D:\scan"
        Dim dr As New DirectoryInfo(path1)
        If dr.Exists Then
            Dim res As Integer = FileCount(path1)
            For i As Integer = 0 To res - 1
                Dim st As String = (Replace(Files(i), path1 & "\", ""))
                Dim re As String = Replace(st, ".jpg", "")
            Next
        End If
    End Sub
    Public Function FileCount(ByVal FolderName As String) As Integer
        Files = IO.Directory.GetFiles(FolderName)
        Return Files.Length
    End Function
End Class




rs return:1,2,3

Validate Phone number

 Shared Function removestdextra(ByVal phoneno As String, Optional ByVal stdcode As String = "", Optional ByVal compphno As String = "") As String
        If phoneno = "" Or phoneno Is Nothing Then Exit Function
        While phoneno.ToUpper.Contains("EXT") = True
            Dim expos = InStr(phoneno.ToUpper, "EXT")
            Dim expi = Mid(phoneno, expos, Len(phoneno))
            Dim expose1 = InStr(expi, ",")
            If expose1 = 0 Then expose1 = Len(expi) + 1
            phoneno = Replace(phoneno, Mid(phoneno, expos, expose1), ",")
        End While
        phoneno = Replace(phoneno.ToUpper, "+91", "")
        phoneno = phoneno.Replace("-", ",").Replace("/", ",")
        Dim newphoneno(Len(phoneno)) As String
        Dim j As Integer = 0
        For inct As Integer = 1 To Len(phoneno)
            If IsNumeric(Mid(phoneno, inct, 1)) Then
                newphoneno(j) = Mid(phoneno, inct, 1).Trim
                j = j + 1
            ElseIf Mid(phoneno, inct, 1) = "," Then
                newphoneno(j) = Mid(phoneno, inct, 1).Trim
                j = j + 1
            End If
        Next
        phoneno = Join(newphoneno, "")
        If phoneno.Contains("123456") Then phoneno = ""
        Dim extractno As String = ""
        Dim newvalid As String = ""
        newvalid = phoneno.Replace("-", ",").Replace("/", ",")
        Dim abc() As String
        abc = newvalid.Split(",")
        removestdextra = compphno
        Dim actualph As String = ""
        Dim lstno As String = ""
        Dim newno As String = ""
        For icnt As Integer = 0 To abc.Length - 1
            If abc(icnt).StartsWith(stdcode) And stdcode <> "" Then
                actualph = Val(Replace(abc(icnt), stdcode, "", 1).Trim)
            Else
                actualph = Val(abc(icnt))
            End If
            If Len(actualph) >= 6 Then
                extractno = validintergerphne(actualph).Trim
                If removestdextra = "" And extractno <> "" Then
                    removestdextra = extractno
                ElseIf removestdextra <> "" And extractno <> "" And InStr(removestdextra, extractno) = 0 Then
                    removestdextra = removestdextra & "," & extractno
                End If
            ElseIf Len(actualph) = 2 And icnt >= 1 And removestdextra <> "" And extractno <> "" Then
                If removestdextra <> "" Then
                    removestdextra = removestdextra & "," & Mid(extractno, 1, Len(extractno) - 2) & abc(icnt)
                End If
            ElseIf Len(actualph) = 3 And icnt >= 1 And removestdextra <> "" And extractno <> "" Then
                If removestdextra <> "" Then
                    removestdextra = removestdextra & "," & Mid(extractno, 1, Len(extractno) - 3) & abc(icnt)
                End If
            End If
        Next
        Return removestdextra
    End Function
=============================

   Shared Function validintergerphne(ByVal phno As String) As String
        validintergerphne = ""
        Dim newno As String = ""
        For validi As Integer = 1 To Len(phno)
            newno = Mid(phno, validi, 1)
            If IsNumeric(newno) Then
                validintergerphne = validintergerphne & newno
            End If
        Next
        If Len(validintergerphne) < 6 Then
            validintergerphne = ""
        ElseIf Val(validintergerphne) = 0 Then
            validintergerphne = ""
        End If
    End Function

Alphabet Sort Of Any Word

 Function sortalphabet(ByVal wrd As String)
        sortalphabet = ""
        Dim myarr() As String
        Dim Length As Integer = Len(wrd)
        ReDim myarr(Length)
        For i = 1 To Length
            Dim charac As String = Mid(wrd, i, 1)
            myarr(i - 1) = charac
        Next
        Dim Sorted As Boolean = False
        Do While Not Sorted
            Sorted = True
            For x = 0 To UBound(myarr) - 1
                If myarr(x) > myarr(x + 1) Then
                    Dim temp As String = myarr(x + 1)
                    myarr(x + 1) = myarr(x)
                    myarr(x) = temp
                    Sorted = False
                End If
            Next
        Loop
        sortalphabet = Join(myarr, Space(0))
    End Function

Find Last Character of Given String using VB.NET

======================This is the Code
Dim str As String = "abcd"
        Dim ss As String = str(str.Length - 1)
===========================================
output :d
=============if we want 'c' of given string then we use-
Dim str As String = "abcd"
        Dim ss As String = str(str.Length - 2)
==================================
output :c
======================================

Tick Rows In Datagridview Value

 Private Sub DataGridView2_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView2.CellClick
        If e.RowIndex < 0 Then Exit Sub
        If e.ColumnIndex < 0 Then Exit Sub
        If DataGridView2.Columns(e.ColumnIndex).Name = "Select" Then
            If DataGridView2.Rows(e.RowIndex).Cells("Select").Value = "P" Then
                DataGridView2.Rows(e.RowIndex).Cells("Select").Value = ""
            Else
                DataGridView2.Rows(e.RowIndex).Cells("Select").Style.Font = New Font("Wingdings 2", 10, FontStyle.Bold)
                DataGridView2.Rows(e.RowIndex).Cells("Select").Value = "P"
            End If
        End If
    End Sub

Filter Value By KeyWord Matching

  Private Sub filterbykeyword()
        If cmbstate.Text = "" Then Exit Sub
        If DataGridView3.Rows.Count = 0 Then Exit Sub
        For i As Integer = 1 To DataGridView3.Rows.Count - 1
            If DataGridView3.Rows(i).Cells(1).Style.BackColor = Color.White Then
                For j As Integer = LBound(newcolegeprin) To UBound(newcolegeprin)
                    If Trim(UCase(DataGridView3.Rows(i).Cells(1).Value)) Like "*" & Trim(UCase(newcolegeprin(j))) & "*" And newcolegeprin(j) <> "" Then
                        If newexactmatchingdata(Trim(UCase(DataGridView3.Rows(i).Cells(1).Value)), (j + 1)) = True Then
                            DataGridView3.Rows(i).Cells(2).Value = "4"
                            DataGridView3.Rows(i).Cells(1).Style.BackColor = Color.Cyan
                            Exit For
                        Else
                            Exit For
                        End If
                    End If
                Next
            End If
        Next

    End Sub
=================
 Function newexactmatchingdata(ByVal wrdsting As String, ByVal arrno As Integer)
        newexactmatchingdata = False
        Dim kkkk As Integer
        Dim nn As Integer
        Dim mk As Integer
        Dim newwrd() As String = Split(Trim(wrdsting), " ")
        kkkk = UBound(newcolegeprin)
        Dim match() As Boolean
        ReDim match(Val(kkkk))
        Dim abc As Boolean
        For mk = 0 To UBound(newcolegeprin)
            If Trim(UCase(newcolegeprin(mk))) <> "" Then
                For nn = 0 To UBound(newwrd)
                    abc = False
                    If Trim(UCase(newwrd(nn))) = Trim(UCase(newcolegeprin(mk))) Then
                        abc = True
                        Exit For
                    End If
                Next
                If abc = True Then
                    match(mk) = True
                Else
                    match(mk) = False
                End If
            End If
        Next
        For mk = 0 To UBound(match)
            If match(mk) = False Then
                Exit Function
            End If
        Next
        newexactmatchingdata = True
    End Function

Filter Value By Key Matching

  Private Sub FilterExactName()
        For i = 0 To DataGridView3.Rows.Count - 1
            If Trim(UCase(DataGridView3.Rows(i).Cells(1).Value)) Like Trim(UCase(txtcollegename.Text)) Then
                DataGridView3.Rows(i).Cells("sort").Value = "5"
                DataGridView3.Rows(i).Cells(1).Style.BackColor = Color.Lime
            End If
        Next
    End Sub

Use SoundEX Function (Retrieve Sound Matching Data from Sql Server)

   Dim newcolegeprin() As String
  Private Sub cmdloaddata()
  Me.Cursor = Cursors.WaitCursor
        Dim data As DataTable = objdb.getdata("select director_name,no_of_occurances,sno,statecode from frequency where  statecode='" & cmbstate.Tag & "' order by no_of_occurances desc  ", objdb.localconnection)
        If data.Rows.Count > 0 Then

            For i As Integer = 0 To data.Rows.Count - 1
                DataGridView3.Rows.Add()
                DataGridView3.Rows(i).Cells("principal_name").Style.BackColor = Color.White
                DataGridView3.Rows(i).Cells("principal_name").Value = data.Rows(i).Item("director_name")
                DataGridView3.Rows(i).Cells("Frequncy").Value = data.Rows(i).Item("no_of_occurances")
                DataGridView3.Rows(i).Cells("sr_no").Value = data.Rows(i).Item("sno")
                DataGridView3.Rows(i).Cells("statecode").Value = data.Rows(i).Item("statecode")
            Next
        End If

        Dim dt As DataTable = objdb.getdata("select  college_name,principalname,college_code from tmphistory where state_code='" & cmbstate.Tag & "'order by college_name", objdb.localconnection)
        If dt.Rows.Count > 0 Then
            For i As Integer = 0 To dt.Rows.Count - 1
                DataGridView1.Rows.Add()
                DataGridView1.Rows(i).Cells("Principal_name").Value = dt.Rows(i).Item("principalname")
                DataGridView1.Rows(i).Cells("college_name").Value = dt.Rows(i).Item("college_name")
                DataGridView1.Rows(i).Cells("collegecode").Value = dt.Rows(i).Item("college_code")
            Next

        End If
        Dim j As Integer = 1

        txtcollegename.Text = DataGridView3.CurrentRow.Cells(1).Value
        txtcollegename.Tag = DataGridView3.CurrentRow.Cells(0).Value
        Dim chh() As String = Split(txtcollegename.Text, Space(1))
        Dim z As Integer = 0
        For i = LBound(chh) To UBound(chh)
            DataGridView2.Rows.Add()
            DataGridView2.Rows(z).Cells("Keyword").Value = Trim(chh(i))

            DataGridView2.Rows(z).Cells("Select").Style.Font = New Font("Wingdings 2", 10, FontStyle.Bold)
            DataGridView2.Rows(z).Cells("Select").Value = "P"
            z = z + 1
        Next
        DataGridView2.Rows.Add()
        DataGridView2.Rows(z).Cells("Keyword").Value = txtcollegename.Text
        Dim ubndarry As Integer
        ReDim newcolegeprin(0)
        newcolegeprin(0) = ""
        ubndarry = 0
        For i = 0 To DataGridView2.Rows.Count - 1
            If DataGridView2.Rows(i).Cells("Select").Value = "P" And DataGridView2.Rows(i).Cells("select").Value <> "" Then
                ubndarry = Val(ubndarry) + 1
            End If
        Next
        ReDim newcolegeprin(Val(ubndarry) - 1)

        j = 0

        For i = 0 To DataGridView2.Rows.Count - 1
            If DataGridView2.Rows(i).Cells("Select").Value = "P" And DataGridView2.Rows(i).Cells("keyword").Value <> "" Then

                newcolegeprin(j) = Trim(UCase(DataGridView2.Rows(i).Cells("keyword").Value))
                j = j + 1
            End If
        Next
        If j <> 0 Then
            FilterExactName()
            filterbykeyword()
            sound_fun()
            DataGridView3.Sort(DataGridView3.Columns(2), System.ComponentModel.ListSortDirection.Descending)

        End If
        DataGridView3.ClearSelection()
        Me.Cursor = Cursors.Default
End sub
===========================
  Private Sub sound_fun()
        Dim subkey As String = ""
        For i = LBound(newcolegeprin) To UBound(newcolegeprin)
            If UBound(newcolegeprin) And Len(newcolegeprin(i)) > 1 Then
                subkey = subkey & "" & Trim(newcolegeprin(i))
            End If
        Next
        soundpatterndata(subkey)
    End Sub
===============================================
    Sub soundpatterndata(ByVal subkey As String)
        Dim abc As Boolean
        For i = 1 To DataGridView3.Rows.Count - 1
            If DataGridView3.Rows(i).Cells("Principal_Name").Style.BackColor = Color.White Then
                abc = False
                If getsound(Trim(UCase(DataGridView3.Rows(i).Cells(1).Value))) = getsound(Trim(UCase(subkey))) Then
                    abc = True
                End If
                If abc = True Then
                    DataGridView3.Rows(i).Cells(2).Value = "3"
                    DataGridView3.Rows(i).Cells(1).Style.BackColor = Color.Yellow
                End If
            End If
        Next
    End Sub
======================
    Private Function getsound(ByVal mystring As String) As String
        getsound = ""
        Dim dt As DataTable = objdb.getdata(" select soundex('" & Replace(mystring, "'", " ''") & "') as sound", objdb.localconnection)
        If dt.Rows.Count > 0 Then
            getsound = dt.Rows(0).Item("sound")
        End If
    End Function

Fill ComboBox using Sub Query(Two Table Based On Condition)

  Private Sub showstate()
        Dim dt As DataTable = objdb.getdata("select distinct college_new_state_code from AffidavitData where college_new_state_code is not null and college_name_removesc is not null order by college_new_state_code asc ", objdb.localconnection)
        If dt.Rows.Count > 0 Then
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim dt1 As DataTable = objdb.getdata("select distinct state from state_master  where state_code='" & dt.Rows(i).Item("college_new_state_code") & "' and state is not null", objdb.localconnection)
                If IsDBNull(dt1.Rows(0).Item("state")) = False Then
                    cmbstate.Items.Add(dt1.Rows(0).Item("state"))
                Else
                    i = i - 1
                End If
            Next
        End If

Dynamically Design Data Grid View

 Public Sub disgnGrd()
        DataGridView1.Columns.Clear()
        DataGridView1.Rows.Clear()
        DataGridView1.ColumnHeadersDefaultCellStyle.ForeColor = Color.Blue
        DataGridView1.Columns.Add("state", "State")
        DataGridView1.Columns.Add("Total_collegeInaffidavit", "Total Colleges In Affidavit")
        DataGridView1.Columns.Add("Totalcollegeinmaster", "Total Colleges in Master")
        DataGridView1.Columns.Add("totalaffidavit", "Total Affidavits")
 DataGridView3.Columns("Frequncy").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        '***SET DATAGRIDVIEW COLUMNS WIDTH***
        DataGridView1.Columns("state").Width = 170
        DataGridView1.Columns("Total_collegeInaffidavit").Width = 108
        DataGridView1.Columns("Totalcollegeinmaster").Width = 108
        DataGridView1.Columns("totalaffidavit").Width = 108
    End Sub

Do not Allow Sorting in Data Grid View

Private Sub gridshot_ColumnAdded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs) Handles gridshot.ColumnAdded
        gridshot.Columns.Item(e.Column.Index).SortMode = DataGridViewColumnSortMode.NotSortable
    End Sub

click first column content in datagridview vb.net

If gridshot.Columns(e.ColumnIndex).Name = "Select" Then

Mobile Number Validation

Private Sub TxtNo_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TxtNo.KeyPress
        If Asc(e.KeyChar) <> 8 Then
            If Asc(e.KeyChar) < 48 Or Asc(e.KeyChar) > 57 Or TxtNo.Text.Length > 9 Then
                e.Handled = True
            End If
        End If
    End Sub

Create Excel File in vb.net

 Imports System.Data.OleDb
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO

 Private Sub btnexcelreport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnexcelreport.Click
        Dim proc As System.Diagnostics.Process
        For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
            proc.Kill()
        Next
        Dim xlapp As Microsoft.Office.Interop.Excel.Application
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim xlworkbook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlworksheet As Microsoft.Office.Interop.Excel.Worksheet
        If DataGridView1.Rows.Count = 0 Then Exit Sub
        If DataGridView1.Rows.Count >= 1 Then
            Dim path = "d:\Affidavit Data Report(State-Wise)"
            Dim dr As New DirectoryInfo(path)
            If Not dr.Exists Then
                Directory.CreateDirectory(path)
            End If
            Dim finalpath As String = path & "\" & "Statewise Report.xls"
            xlapp = New Microsoft.Office.Interop.Excel.Application
            xlworkbook = xlapp.Workbooks.Add(misValue)
            'xlworksheet = xlworkbook.Sheets("sheet1")
            xlworkbook = xlapp.Workbooks.Add(misValue)
            xlworksheet = xlworkbook.Worksheets(1)
            xlworksheet.Cells(1, 1).Font.Size = 20
            xlworksheet.Cells(1, 1).Font.Bold = True
            '  xlworksheet.Cells(1, 1).Font.Color = Color.Red
            xlworksheet.Cells(1, 1) = "State Wize Affidavit Data ;"
            ' xlworksheet.Cells(1, 1).Font.Color = Color.Red
            xlworksheet.Cells(2, 1) = "State"
            xlworksheet.Cells(2, 2) = "Total Collges In Affidavit"
            xlworksheet.Cells(2, 3) = "Total Colleges In Master"
            xlworksheet.Cells(2, 4) = "Total Affidavits"
            xlworksheet.Rows(2).Font.Bold = True
            xlworksheet.Rows(2).Font.Size = 15
            ' xlworksheet.Rows(2).Font.Color = Color.Blue
            xlworksheet.Rows(2).WrapText = True
            xlworksheet.Columns(1).ColumnWidth = 23
            xlworksheet.Columns(2).ColumnWidth = 17
            xlworksheet.Columns(3).ColumnWidth = 17
            xlworksheet.Columns(4).ColumnWidth = 17
            Dim j As Integer = 3
            For i = 0 To DataGridView1.RowCount - 1
                xlworksheet.Cells(j, 1) = DataGridView1.Rows(i).Cells(0).Value.ToString()
                xlworksheet.Cells(j, 1).WrapText = True
                xlworksheet.Cells(j, 2) = DataGridView1.Rows(i).Cells(1).Value.ToString()
                xlworksheet.Cells(j, 3) = DataGridView1.Rows(i).Cells(2).Value.ToString()
                xlworksheet.Cells(j, 4) = DataGridView1.Rows(i).Cells(3).Value.ToString()
                j = j + 1
            Next
            xlworksheet.Rows(j - 1).Font.Bold = True
            xlworksheet.Rows(j - 1).Font.Size = 11
            ' xlworksheet.Rows(j - 1).Font.Color = Color.Blue
            xlworksheet.Rows(2).AutoFilter()
            xlworksheet.Rows(3).Activate()
            xlworksheet.Rows(2).WrapText = True
            xlworksheet.PageSetup.PrintGridlines = True
            xlapp.ActiveWindow.FreezePanes = True
            xlworksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            Dim fileinfo As New DirectoryInfo(path)
            For Each ff In fileinfo.GetFiles()
                If ff.Exists Then
                    ff.Delete()
                End If
            Next
            xlworkbook.SaveAs(finalpath)
            Dim proc1 As System.Diagnostics.Process
            For Each proc1 In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc1.Kill()
            Next
            Dim res As MsgBoxResult
            res = MsgBox("Process Completed.Would you like to Open File?", MsgBoxStyle.YesNo)
            If res = MsgBoxResult.Yes Then
                Process.Start(finalpath)
            End If
        End If
    End Sub

Get Day of Week in SQL 2005/2008

select  DATENAME(dw,comtype) AS OrderDay from tblcom_type where s_no=6
   --Result  Friday

Do not Allow Sorting in Data Grid View

Private Sub gridshot_ColumnAdded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs) Handles gridshot.ColumnAdded
        gridshot.Columns.Item(e.Column.Index).SortMode = DataGridViewColumnSortMode.NotSortable
    End Sub