SEARCH ALL TABLES IN SQL PROCEDURE

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + 'WITH (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM #Results

END

BLINKING TEXT COLOR CHANGE

Dim i As Integer = 0

Dim j As Integer

// on form load timer1.start() or timer1.enable=true

 Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick

        Dim str As String = "2017-01-01"
        Dim c(str.Length) As Char
        c = str.ToCharArray
        If j = 0 Then
            TextBox1.ForeColor = Color.Blue
            TextBox2.ForeColor = Color.Blue
            If i < str.Length Then
                'TextBox1.Text = TextBox1.Text & c(i)
                i = i + 1
            Else
                i = 0
                'TextBox1.Text = ""
            End If
            j = 1
        Else
            j = 0

            TextBox1.ForeColor = Color.Red
            TextBox2.ForeColor = Color.Red
        End If
    End Sub

AUTOSIZE COLUMNS IN DATAGRIDVIEW

 //COPY AND PASTE IT ON FORM_LOAD EVENT

DataGridView1.AutoResizeColumns()

             DataGridView1.AutoSizeColumnsMode =
        DataGridViewAutoSizeColumnsMode.AllCells

Time Difference Between Columns

SUBSTRING(CONVERT(VARCHAR(20),(enddatetime - startdatetime),120),12,8)

SQL ConnectionString with IP ADDRESS

<appSettings>
 
 <add key="ConnectionStringsql" value="Server=103.245.119.115,1433;Network Library=DBMSSOCN;Initial Catalog=mydatabase;User Id=sa;Password=pass100;"/>
 
     </appSettings>

Copy system files - visual studio 2015

     

 My.Computer.FileSystem.CopyFile("c:\myfiles\test.txt", "z:\process\documents\test.txt", FileIO.UIOption.OnlyErrorDialogs, FileIO.UICancelOption.DoNothing)

view PDF document on a cell click on datagridview - visual studio 2015

//first of all load pdf reader from toolbox->addtab->choose items->com components

 Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
       
        Dim ofd As New OpenFileDialog
        ofd.Filter = "PDF| *.pdf"
        ofd.FileName = "z:\process\documents\" & DataGridView1.CurrentRow.Cells(1).Value 
         Me.AxAcroPDF1.LoadFile(ofd.FileName)
    End Sub

view a PDF document on button click in a Windows Form - Visual studio 2015

Me.AxAcroPDF1.LoadFile("C:\Temp\PDF_DemoFile_2.pdf")

VBA pdf export from excel

//developer tab for create button, alt+f11 for vba open

Sub PDFActiveSheet()
'www.contextures.com
'for Excel 2010 and later
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    'confirmation message with file info
    MsgBox "PDF file has been created: " _
      & vbCrLf _
      & myFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

EXPORT TO EXCEL Visual Studio 2015

//go to project -> add reference -> Microsoft office 16.0 object library and Microsoft Excel Library

// Imports Excel = Microsoft.Office.Interop.Excel
// copy paste this code for EXPORT BUTTON

 Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")


        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                For k As Integer = 1 To DataGridView1.Columns.Count
                    xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                    xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                Next
            Next
        Next

        xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\vbexcel.xlsx")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub