Thursday, December 26, 2013

Get Date in SQL Function

select day(GETDATE()-10)  Result -- 16
select month(GETDATE())   Result -- 12

select convert(varchar(11), getdate(),100)  Result -- Dec 26 2013
select convert(varchar(11), getdate(),110)  Result -- 12-26-2013
select convert(varchar(11), getdate(),120)  Result -- 2013-12-26

Friday, December 20, 2013

Create Excel File in vb.net

Private Sub cmdexcel_Click()
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Dim row As Long
Set xlapp = New Excel.Application
Set xlbook = xlapp.Workbooks.Add
Set xlsheet = xlbook.Worksheets(1)
row = 4
With xlsheet

If optdatewise.Value = True Then
.Cells(1, 1) = "Report of Complaints"
.Cells(2, 1) = "Date : " & Format(date1.Value, "dd/MMM/yyyy") & "  To : " & Format(date2.Value, "dd/MMM/yyyy")
.Rows(2).Font.Bold = True
.Rows(2).Font.Size = 12
Else
.Cells(1, 1) = "Report of Complaints Received Till  '" & Now() & "'"
End If
.Rows(1).Font.Bold = True
.Rows(1).Font.Size = 18

.Cells(3, 1) = "Total Number Of Complaints: "
.Rows(3).Font.Bold = True
.Rows(3).Font.Size = 18

.Columns(1).ColumnWidth = 9
.Columns(2).ColumnWidth = 12
.Columns(3).ColumnWidth = 11
.Columns(4).ColumnWidth = 11
.Columns(5).ColumnWidth = 15
.Columns(6).ColumnWidth = 12
.Columns(7).ColumnWidth = 12
.Columns(8).ColumnWidth = 15
.Columns(9).ColumnWidth = 18
.Columns(10).ColumnWidth = 21

.Rows(row).Font.Bold = True
For i = 0 To grid.Rows - 1
For j = 1 To grid.Cols - 1
If j = 2 And i > 0 Then
.Cells(row, j).Select
 xlapp.ActiveSheet.Hyperlinks.Add Anchor:=xlapp.Selection, Address:=mpath & "\Log Files\" & grid.TextMatrix(i, j) & "\Log.doc", TextToDisplay:=grid.TextMatrix(i, j)
Else
.Cells(row, j) = grid.TextMatrix(i, j)
End If
.Rows.Font.Name = "Centaur"
.Rows(row).WrapText = True
Next
row = row + 1
Next
.Cells(3, 1) = .Cells(3, 1) & row - 5

.Rows(4).AutoFilter
.Rows(5).Activate
xlapp.ActiveWindow.FreezePanes = True
.PageSetup.LeftMargin = 1
.PageSetup.RightMargin = 1
.PageSetup.PrintGridlines = True
.PageSetup.Orientation = xlLandscape
.PageSetup.Zoom = 90
.PageSetup.PrintTitleRows = "$4:$4"
.PageSetup.CenterFooter = "&P of &N"

xlapp.Visible = True
End With
End Sub

Thursday, December 19, 2013

If string was found in the RichTextBox, High Light ,Replace , Skip ..

Dim start As Integer = 0
    Dim indexOfSearchText As Integer = 0
'********************Replace Select Use Replace btn****************
        Dim startindex As Integer = 0

        If txt_Name_Changed.Text.Length > 0 Then
            startindex = FindMyText(txt_Name_Changed.Text.Trim(), start, RichTextBox1.Text.Length)
        End If
        ' If string was found in the RichTextBox, highlight it
        If startindex >= 0 Then
            ' Set the highlight color as red
            RichTextBox1.SelectionBackColor = Color.Plum
            RichTextBox1.SelectedText = lblpname.Text
            ' Find the end index. End Index = number of characters in textbox
            Dim endindex As Integer = txt_Name_Changed.Text.Length
            ' Highlight the search string
            RichTextBox1.Select(startindex, endindex)
            ' mark the start position after the position of last search string
            start = startindex + endindex
        End If

 '********************Skip ^^^ Use SKIP btn**************
        Dim startindex As Integer = 0
        If txt_Name_Changed.Text.Length > 0 Then
            startindex = FindMyText(txt_Name_Changed.Text.Trim(), start, RichTextBox1.Text.Length)
        End If
        If startindex >= 0 Then
            RichTextBox1.SelectionBackColor = Color.White
            Dim endindex As Integer = txt_Name_Changed.Text.Length
            RichTextBox1.Select(startindex, endindex)
            start = startindex + endindex
        End If

**************** Function For Replace And Skip ***********************
 Public Function FindMyText(ByVal txtToSearch As String, ByVal searchStart As Integer, ByVal searchEnd As Integer) As Integer
        ' Unselect the previously searched string
        ' ''If searchStart > 0 AndAlso searchEnd > 0 AndAlso indexOfSearchText >= 0 Then
        ' ''    RichTextBox1.Undo()
        ' ''End If
        ' Set the return value to -1 by default.
        Dim retVal As Integer = -1
        ' A valid starting index should be specified.
        ' if indexOfSearchText = -1, the end of search
        If searchStart >= 0 AndAlso indexOfSearchText >= 0 Then
            ' A valid ending index
            If searchEnd > searchStart OrElse searchEnd = -1 Then
                ' Find the position of search string in RichTextBox
                indexOfSearchText = RichTextBox1.Find(txtToSearch, searchStart, searchEnd, RichTextBoxFinds.None)
                ' Determine whether the text was found in richTextBox1.
                If indexOfSearchText <> -1 Then
                    ' Return the index to the specified search text.
                    retVal = indexOfSearchText
                End If
            End If
        End If
        Return retVal
    End Function

**************write TextChanged  Event on Matching String Txt Box*******************
 Private Sub txt_Name_Changed_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_Name_Changed.TextChanged
        start = 0
        indexOfSearchText = 0
    End Sub

^^^^^^^^^^^^^^^^^^^If string was found in the RichTextBox, Then  High Light .^^^^^^^^^^^^^^^^^^^
 Sub FindTxtInRTB() ' HighLight Search txt In RTF
        Dim srchterm As String = txt_Name_Changed.Text
        Dim index As Int32 = 0
        While index <> -1
            index = RichTextBox1.Find(srchterm, index, RichTextBoxFinds.None)
            If index <> -1 Then

                RichTextBox1.SelectionBackColor = Color.Orange
                index += 1
            End If
        End While
        RichTextBox1.DeselectAll()
    End Sub

Wednesday, December 11, 2013

Find integer from string

 Dim Res As String
Dim str As String = "Sandeep123Kumar"
        For Each c As Char In str
            If IsNumeric(c) Then
                Res = Res & c
            End If
        Next
        MessageBox.Show(Res)

Wednesday, December 4, 2013

Retrieve the ValueMember of the DisplayMember items in checklistbox

Public Sub chklistbox()

        Dim dr As DataTable = objdb.getdata("select district_name,district_id from district_master where state_id='" & "9" & "'")
        If dr.Rows.Count > 0 Then
            CheckedListBox1.DataSource = dr
            CheckedListBox1.DisplayMember = "district_name"
            CheckedListBox1.ValueMember = "district_id"
            CheckedListBox1.Text = Nothing
            dr = Nothing
        Else
            CheckedListBox1.DataSource = Nothing
            CheckedListBox1.Text = Nothing
        End If
    End Sub

when you assign properties value member and display member you can retrieve its value using :

 Label9.Text = CheckBoxList1.SelectedItem.ToString()
        Label8.Text = CheckedListBox1.SelectedValue.ToString()

Monday, December 2, 2013

Select/UnSelect all items in checkboxlist in vb.net

 Private Sub chk_crimeName_ItemCheck(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ItemCheckEventArgs) Handles chk_crimeName.ItemCheck
     

 If e.Index = 0 Then
            If e.NewValue = CheckState.Checked Then
                For i As Integer = 1 To chk_crimeName.Items.Count - 1
                    chk_crimeName.SetItemCheckState(i, CheckState.Checked)
                Next
            ElseIf e.NewValue = CheckState.Unchecked Then
                For i As Integer = 1 To chk_crimeName.Items.Count - 1
                    chk_crimeName.SetItemCheckState(i, CheckState.Unchecked)
                Next
            End If
        End If

    End Sub

Select/UnSelect item of Listbox select On checkboxlist
Dim item1 As String = chk_district.SelectedIndex
            If e.NewValue = CheckState.Checked Then
                ListBox1.SelectedIndex = e.Index
            Else
                ListBox1.SetSelected(e.Index, False)
            End If