Tuesday, 24 June 2014

Copy a table across two database in the same Sql Server.

select * into [destination database.dbo.destination table]

from [source database.dbo.source table]
 
Example:
select * into Mydatabase2.dbo.employee_backup
from mydatabase1.dbo.employee

Sql Server: How to copy a table?

This is the simplest way to copy a table into another (new) table in the same SQL Server database. This way of copying does not copy constraints and indexes.




select * into [destination table] from [source table]
Example:
Select * into employee_backup from employee

We can also select only a few columns into the destination table like below

select col1, col2, col3 into [destination table]from [source table]
Example:
Select empId, empFirstName, empLastName, emgAge into employee_backup fromemployee

Use this to copy only the structure of the source table.

select * into [destination table] from [source table] where 1 = 2
Example:
select * into employee_backup from employee where 1=2

Use this to copy a table across two database in the same Sql Server.





select * into [destination database.dbo.destination table] from [source database.dbo.source table]
Example:
select * into Mydatabase2.dbo.employee_backup
from mydatabase1.dbo.employee

Thursday, 8 May 2014

Export Gridview Data To Excel

 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 grdlogdetail.Rows.Count = 0 Then Exit Sub
                If grdlogdetail.Rows.Count >= 1 Then
                    Dim path = "d:\CallLog"
                    Dim dr As New DirectoryInfo(path)
                    If Not dr.Exists Then
                        Directory.CreateDirectory(path)
                    End If
                    Dim finalpath As String = path & "\" & "Operator's 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) = "Operator's CallLog "
                    ' xlworksheet.Cells(1, 1).Font.Color = Color.Red
                    xlworksheet.Cells(2, 1) = "Name"
                    xlworksheet.Cells(2, 2) = "Days On Job"
                    xlworksheet.Cells(2, 3) = "No Of Calls"
                    xlworksheet.Cells(2, 4) = "Average(Calls/Day)"
                    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
                    Dim j As Integer = 3
                    For i = 0 To grdlogdetail.RowCount - 1
                        xlworksheet.Cells(j, 1) = grdlogdetail.Rows(i).Cells(0).Value.ToString()
                        xlworksheet.Cells(j, 1).WrapText = True
                        xlworksheet.Cells(j, 2) = grdlogdetail.Rows(i).Cells(1).Value.ToString()
                        xlworksheet.Cells(j, 3) = Convert.ToString(grdlogdetail.Rows(i).Cells(2).Value)
                        xlworksheet.Cells(j, 4) = Convert.ToString(grdlogdetail.Rows(i).Cells(3).Value)
                        ' xlworksheet.Cells(j, 4) = grdlogdetail.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
            Case "U"
                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 grdlogdetail.Rows.Count = 0 Then Exit Sub
                If grdlogdetail.Rows.Count >= 1 Then
                    Dim path = "d:\CallLog"
                    Dim dr As New DirectoryInfo(path)
                    If Not dr.Exists Then
                        Directory.CreateDirectory(path)
                    End If
                    Dim finalpath As String = path & "\" & "University 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) = "University Report"
                    ' xlworksheet.Cells(1, 1).Font.Color = Color.Red
                    xlworksheet.Cells(2, 1) = "Name Of University"
                    xlworksheet.Cells(2, 2) = "State"
                    xlworksheet.Cells(2, 3) = "No Of Contacts"
                    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
                    Dim j As Integer = 3
                    For i = 0 To grdlogdetail.RowCount - 1
                        xlworksheet.Cells(j, 1) = grdlogdetail.Rows(i).Cells(0).Value.ToString()
                        xlworksheet.Cells(j, 1).WrapText = True
                        xlworksheet.Cells(j, 2) = grdlogdetail.Rows(i).Cells(1).Value.ToString()
                        xlworksheet.Cells(j, 3) = Convert.ToString(grdlogdetail.Rows(i).Cells(2).Value)
                        ' xlworksheet.Cells(j, 4) = grdlogdetail.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 Select

Saturday, 22 February 2014

How can make PDF file in Vb.net

How can make PDF file in Vb.net

Imports iTextSharp.text
Imports System.IO
Imports iTextSharp.text.html.simpleparser
Imports iTextSharp.text.pdf
Please Add Above namesapce And Add iTextSharp .dll file in Add Reference,& Edit This Event
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="pdf.aspx.vb" Inherits="pdf"
EnableEventValidation="false" %>
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Response.ContentType = "application/pdf"
            Response.AddHeader("content-disposition", "attachment;filename=SandeepPage.pdf")
            Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Dim sw As New StringWriter()
            Dim hw As New HtmlTextWriter(sw)
            Me.Page.RenderControl(hw)
            Dim sr As New StringReader(sw.ToString())
            Dim pdfDoc As New Document(PageSize.A4, 10.0F, 10.0F, 100.0F, 0.0F)
            Dim htmlparser As New HTMLWorker(pdfDoc)
            PdfWriter.GetInstance(pdfDoc, Response.OutputStream)
            pdfDoc.Open()
            htmlparser.Parse(sr)
            pdfDoc.Close()
            Response.Write(pdfDoc)
            Response.[End]()
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try
End Sub

Fetch the data from Excel File at GridView

Fetch the data from Excel File at GridView

1.use this name space:-Imports System.Globalization
2.Add the Excel file in Solution Explorer
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        fillgried()
    End Sub
  
  Function fillgrid()
        Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("Customers1.xlsx") + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'"
        Dim query As String = "SELECT * FROM [customers$]"
        Dim excelDataSet As DataSet = New DataSet()
        excelDataSet.Locale = CultureInfo.InvariantCulture
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(query, strConn)
        da.Fill(excelDataSet)
        GridView1.DataSource = excelDataSet.Tables(0)
        GridView1.DataBind()
    End Function

How do we read data from a XML file and display it in a DataGrid ?

How do we read data from a XML file and display it in a DataGrid ?

using ReadXML method
DataSet MyDataset= new DataSet ();
MyDataset.ReadXml (Server.MapPath ("sample.xml"));
MyDataGrid.DataSource =MyDataset;
MyDataGrid.DataBind();

Show date And Time in many format

Show date And Time in many format

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class CurrentDate : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = System.DateTime.Now.ToString();
Label3.Text = System.DateTime.Now.ToLongDateString();
Label4.Text = System.DateTime.Now.ToLongTimeString();
Label5.Text = System.DateTime.Now.ToShortDateString();
Label6.Text = System.DateTime.Now.ToShortTimeString();
}
}