Import Excel TO Sequel database BY click on asp.net page button

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Microsoft.VisualBasic;
using System.IO;
using System.Net;
using System.Text;
using System.Data.OleDb;
using System.Data.Common;
using System.Diagnostics;



public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{


}


protected void Button1_Click(object sender, EventArgs e)
{
// Connection String to Excel Workbook
//string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Book1.xls;Extended Properties=""Excel 8.0; HDR=YES;""";
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\tblprod.xls;" + "Extended Properties=Excel 8.0;";
// Create Connection to Excel Workbook
//using (OleDbConnection = new OleDbConnection(excelConnectionString)) ;
OleDbConnection connection = new OleDbConnection(strConn);
OleDbCommand command = new OleDbCommand("Select * FROM [Data$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
//using (SqlDataReader dr = command.ExecuteReader())
DbDataReader dr = command.ExecuteReader();


//// SQL Server Connection String
//string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";
//string sqlConnectionString = "Data Source=.;Initial Catalog=exceldata;Integrated Security=True";
string sqlConnectionString = "server=system2;database=goelco;uid=sa";
// Bulk Copy to SQL Server
//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString);
bulkCopy.DestinationTableName = "tblprod";
bulkCopy.WriteToServer(dr);
dr.Close();
}

Crystal Report Problem

protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("sp_repairid", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.Add("@Repairid", SqlDbType.Int).Value = TextBox1.Text;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds, "tbrepair1");
//int tes = ds.Tables[0].Rows.Count;
ReportDocument report = new ReportDocument();
report.FileName = Server.MapPath("crystalreport2.rpt");
report.SetDataSource(ds);
//report.PrintOptions.PrinterName=
//report.PrintToPrinter(1,false,0,0);
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.DataBind();
cmd.Dispose();
}

Download File Code in vb.net

Private Sub DownloadFile(ByVal fname As String, ByVal forceDownload As Boolean)
Dim path As Path
Dim fullpath = IO.Path.GetFullPath(fname)
Dim name = IO.Path.GetFileName(fullpath)
Dim ext = IO.Path.GetExtension(fullpath)
Dim type As String = ""
If Not IsDBNull(ext) Then
ext = LCase(ext)
End If
Select Case ext
Case ".htm", ".html"
type = "text/HTML"
Case ".txt"
type = "text/plain"
Case ".doc", ".rtf"
type = "Application/msword"
Case ".csv", ".xls"
type = "Application/x-msexcel"
Case Else
type = "text/plain"
End Select
If (forceDownload) Then
Response.AppendHeader("content-disposition", _
"attachment; filename=" + name)
End If
If type <> "" Then
Response.ContentType = type
End If
Response.WriteFile(fullpath)
Response.End()
End Sub

Gridview with template field in vb.net...we can edit and update here

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI.Page
Imports shopping.classConnection
Partial Class userdetail
Inherits System.Web.UI.Page
Dim cmd As New SqlCommand()
Dim con As New shopping.classConnection()
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Page.IsPostBack = False Then
userdetail()
End If
End Sub
Private Function userdetail()
Dim adpuserdetail As New SqlDataAdapter
Dim dsuserdetail As New Data.DataSet
cmd.CommandText = "select * from tblUserDetail order by userid"
cmd.Connection = con.createConnection(Session("site"))
adpuserdetail.SelectCommand = cmd
adpuserdetail.Fill(dsuserdetail)
grduserdetail.DataSource = dsuserdetail
grduserdetail.DataBind()
End Function
Protected Sub grduserdetail_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles grduserdetail.RowEditing
grduserdetail.EditIndex = e.NewEditIndex
userdetail()
End Sub
Protected Sub grduserdetail_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles grduserdetail.RowUpdating
Dim varuserid As Int32 = CType(grduserdetail.Rows(e.RowIndex).FindControl("userid"), Label).Text
Dim struserTitle As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("usertitle"), TextBox).Text
Dim strfirstName As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("firstname"), TextBox).Text
Dim strsurname As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("surname"), TextBox).Text
Dim straddress1 As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("address1"), TextBox).Text
Dim straddress2 As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("address2"), TextBox).Text
Dim straddress3 As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("address3"), TextBox).Text
Dim strcity As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("city"), TextBox).Text
Dim strpostCode As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("postCode"), TextBox).Text
Dim strcountry As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("country"), TextBox).Text
Dim strphone1 As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("phone1"), TextBox).Text
Dim strphone2 As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("phone2"), TextBox).Text
Dim strusername As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("username"), TextBox).Text
Dim strpassword As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("password"), TextBox).Text
Dim strstatus As Char = CType(grduserdetail.Rows(e.RowIndex).FindControl("status"), TextBox).Text
Dim strdate As Date = CType(grduserdetail.Rows(e.RowIndex).FindControl("date1"), TextBox).Text
Dim strsaveas As String = CType(grduserdetail.Rows(e.RowIndex).FindControl("saveas"), TextBox).Text
Dim varprefrence As Int32 = CType(grduserdetail.Rows(e.RowIndex).FindControl("prefrence"), TextBox).Text
'Dim boolActive As Boolean = CType(grduserdetail.Rows(e.RowIndex).FindControl("check"), CheckBox).Checked
'Dim varactive As Int32
'If boolActive = True Then
' varactive = 1
'ElseIf boolActive = False Then
' varactive = 0
'End If
cmd.CommandText = "update tblUserDetail set userTitle='" & struserTitle & "', firstName ='" & strfirstName & "', surname ='" & strsurname & "', address1='" & straddress1 & "', address2='" & straddress2 & "', address3 ='" & straddress3 & "', city='" & strcity & "', postCode ='" & strpostCode & "',country='" & strcountry & "', phone1 ='" & strphone1 & "',phone2 ='" & strphone2 & "',username ='" & strusername & "',password='" & strpassword & "', status ='" & strstatus & "', date=" & strdate & ", saveas ='" & strsaveas & "',prefrence =" & varprefrence & " where userid =" & varuserid
cmd.Connection = con.createConnection(Session("site"))
cmd.ExecuteNonQuery()
grduserdetail.EditIndex = -1
userdetail()
End Sub
Protected Sub grduserdetail_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles grduserdetail.RowCancelingEdit
grduserdetail.EditIndex = -1
userdetail()
End Sub
End Class

ADMIN LOGIN page in VB.net

Purpose: Admin login into admin section
' Process: 1. Checking of username and paswword

Imports System.Data.SqlClient
Imports shopping.classConnection
Partial Class _Default
Inherits System.Web.UI.Page
Dim con As New shopping.classConnection
Dim cmd As New SqlCommand
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Page.IsPostBack = False Then
cmd.Connection = con.createConnection(1)
End If
txtName.Focus()
'txtName.Text = "admin"
'txtPassword.Text = "admin"
End Sub
' Process 1
Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click
If Page.IsValid Then


Dim strLogName As String
Dim strPassword As String
strLogName = txtName.Text
strLogName = Replace(strLogName, "'", "''")
strPassword = txtPassword.Text
strPassword = Replace(strPassword, "'", "''")
If strLogName <> "" And strPassword <> "" Then
Dim cmd As New SqlCommand
cmd.CommandText = "select LogName,Passwd,rights,site from tblAdmin where LogName='" & strLogName & "' and Passwd='" & strPassword & "'"
cmd.Connection = con.createConnection(1)
Dim drLogin As SqlDataReader = cmd.ExecuteReader
If drLogin.Read Then
Session("UserName") = drLogin("LogName")
Session("Rights") = drLogin("rights")
Session("Site") = drLogin("site")
drLogin.Close()
cmd.Dispose()
lblResult.Text = "LogIn Succeed"
Response.Redirect("adminFrames.aspx")
Else
drLogin.Close()
cmd.Dispose()
lblResult.Text = "Incorrect Username or Password"
txtName.Text = ""
txtPassword.Text = ""
End If
End If
End If
End Sub
End Class

Back UP of Database on System in vb.net

Imports System.Data.SqlClient
Imports shopping.classConnection
Partial Class bkup

Inherits System.Web.UI.Page
Dim objConnection As New shopping.classConnection()
Dim cmd As New SqlCommand()
Dim adp As New SqlDataAdapter() '("select * from tblCat", objConnection.con)
Dim ds As New Data.DataSet()
Dim dtreader As SqlDataReader
Dim strSql As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Put user code to initialize the page here
If Session("username") = "" Then
Session.Abandon()
Response.Redirect("error.aspx")
End If

End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim cmd As New SqlCommand
cmd.CommandText = "bkup"
cmd.Connection = objConnection.createConnection(Session("site"))
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.ExecuteNonQuery()
cmd.Dispose()
lblresult.Text = "Backup of Database is being taken sucessfully"
Catch ex As Exception
lblresult.Text = "Sorry there is sum problem in taking database backup try again"
End Try
End Sub
'Private Sub btnDownload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDownload.Click
' Dim mystreamwriter As System.IO.TextWriter
' mystreamwriter = Response.Output
' mystreamwriter.WriteLine("This is a test download text file")
' mystreamwriter.Write(Date.Now.ToLongDateString() & " " & Date.Now.ToLongTimeString())
' mystreamwriter.Close()
' Response.AddHeader("content-disposition", "attachmentfilename=download.txt")
'End Sub
End Class




ALTER PROCEDURE bkup
as
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name ='goelco'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Make Log ERROR FILE in vb.net

Dim FILENAME As String = Server.MapPath("style\style.css")
Dim srw As StreamWriter = New StreamWriter(FILENAME)
srw.WriteLine(".subCategories{")
srw.WriteLine("font-family:" & var152 & ";")
srw.WriteLine("font-size:" & var1 & "px;")
srw.WriteLine("border-bottom-width:" & var3 & "px;")
srw.WriteLine("border-bottom:" & var4 & ";")
srw.WriteLine("border-bottom-color:#" & var5 & ";")
srw.WriteLine("}")