Technical Corner

Thursday, January 20, 2011

upload excel file and upload to sql-server c# asp.net 4.0

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div> <asp:FileUpLoad id="FileUpLoad1" runat="server" /><asp:Button id="UploadBtn" Text="Upload File" OnClick="UploadBtn_Click" runat="server" Width="105px" CssClass="but" />

<br />
<br />
<br />
<br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>

</div>
</form>
</body>
</html>

code behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}

protected void UploadBtn_Click(object sender, EventArgs e)
{
if (FileUpLoad1.HasFile)
{
string filePath = FileUpLoad1.PostedFile.FileName;
string filenameStor = FileUpLoad1.FileName;
FileUpLoad1.SaveAs(@"c:\\" + "Book1.xls");
Label1.Text = "File Uploaded : " + FileUpLoad1.PostedFile.FileName;
datatosql();
}
else
{
Label1.Text = "No File Uploaded.";
}

}
void datatosql()
{
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Book1.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""";
using (OleDbConnection connection = new OleDbConnection(strConn))
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();

using (DbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnectionString))
{
bulkCopy.DestinationTableName = "testexceldata";
bulkCopy.WriteToServer(dr);
}
}
}
}

private string ConnectionString
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["smdbConnectionString"].ConnectionString;
return connectionString;
}
}
}

Display Excel data in Gridview asp.net c#

string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Book1.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""";

OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

DataSet myDataSet = new DataSet();

myCommand.Fill(myDataSet,

"ExcelInfo");

GridView1.DataSource = myDataSet.Tables[

"ExcelInfo"].DefaultView;

GridView1.DataBind();

The 'Microsoft.Jet. OLEDB.4.0' provider is not registered on the local machine.- Error 64 bit 4.0

You will have to run asp.net in 32 bit environment
Run following command
cscript c:\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0
c:\windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i

Word or PDF into Database sql-server c#

private int SaveToDB(string imgName, byte[] imgbin, string imgcontenttype)
{

SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["DSN111"]);
SqlCommand command = new SqlCommand( "INSERT INTO Image (img_name,img_data,img_contenttype) VALUES ( @img_name, @img_data,@img_contenttype )", connection );

SqlParameter param0 = new SqlParameter( "@img_name", SqlDbType.VarChar,50 );
param0.Value = imgName;
command.Parameters.Add( param0 );

SqlParameter param1 = new SqlParameter( "@img_data", SqlDbType.Image );
param1.Value = imgbin;
command.Parameters.Add( param1 );

SqlParameter param2 = new SqlParameter( "@img_contenttype", SqlDbType.VarChar,50 );
param2.Value = imgcontenttype;
command.Parameters.Add( param2 );

connection.Open();
int noRowsAffected = command.ExecuteNonQuery();
connection.Close();

return noRowsAffected;

}

connect to ftp from browser with port

ftp://user:password@host:port/path

Wednesday, January 19, 2011

Read & create tabs excel file C#

The technique that is most frequently used to transfer data to an Excel workbook is Automation. With Automation, you can call methods and properties that are specific to Excel tasks. Automation gives you the greatest flexibility for specifying the location of your data in the workbook, formatting the workbook, and making various settings at run-time.
http://support.microsoft.com/kb/306023

Monday, January 17, 2011

HyperLinkField which are not part of your Gridview DataSource

How to pass external values with GridView HyperLinkField which are not part of your Gridview DataSource member ?

DataNavigationUrlField use only those fields as parameter which are the part of GridView DataSource. Now the problem comes when you want to pass some other variables as parameter which are not part of the DataSource. As shown in below image we are passing EmpID and ParentId as argument and this two field are the data member of GridView DataSource.

http://dailydotnettips.com/2011/01/15/how-to-pass-external-values-with-gridview-hyperlinkfield-which-are-not-part-of-your-gridview-datasource-member/

To change the path for linked tables in access

To change the path for a set of linked tables in access database

Open the database that contains links to tables.
On the Tools menu, point to Database Utilities, and then click Linked Table Manager.
Select the Always prompt for new location check box.
Select the check box for the tables whose links you want to change, and then click OK.
In the Select New Location of <table name> dialog box, specify the new location, click Open, and then click OK.

Sunday, January 16, 2011

WPF Two-way Databinding in ASP.NET

Bringing WPF like declarative data binding to ASP.NET Web Forms to enable declarative two-way data binding to any object whilst opening up MVVM UI development.

http://www.codeproject.com/KB/aspnet/WPFDataBindingInASPX.aspx

Friday, January 14, 2011

Using Razor Pages with WebForms Master Pages

There's been some recent chatter about mixing ASP.NET MVC with WebForms, which is something I'm all too familiar with. This setup works just fine, but what if you wanted to use the new Razor view engine within ASP.NET MVC? A bit of backstory.
On a secondary project that I work on within Microsoft, we were given the ability to rewrite the entire UI of the application in ASP.NET MVC. At that time, the first beta of MVC3 was rolling out and we decided to take our first plunge and use the Razor view engine. Unfortunately, the common UI layout that is shared across multiple properties that we had to use was WebForms based. Our attempts to convince the developers of the common UI components to support more of a modularized infrastructure failed for any foreseeable future, so we were left with the conundrum of figuring out an intermediary solution.

http://www.eworldui.net/blog/post/2011/01/07/Using-Razor-Pages-with-WebForms-Master-Pages.aspx

Building an HTML5 App with ASP.NET

The application takes advantage of HTML5 local storage to store all of the reference entries on the local browser. IE 8, Chrome 8, Firefox 3.6, and Safari 5 all support local storage.

When you open the application for the first time, all of the reference entries are transferred to the browser. The data is stored persistently. Even if you shutdown your computer and return to the application many days later, the data does not need to be transferred again.

http://stephenwalther.com/blog/archive/2011/01/11/building-an-html5-app-with-asp-net.aspx

Thursday, January 13, 2011

sql-server 2008 express how to save Import and Export Wizard

In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.

Wednesday, January 12, 2011

The settings saved on this computer do not match the requirements of the wireless network.

in vista you go to network and sharing center then click manage networks. delete the one your using coz the settings are different. you maybe changed your router settings . when your done deleteing it, find it in the wireless list, type in the password and you wil be able to connect now.

Monday, January 10, 2011

Cross tab query in sql-server sample

declare

@drdate as varchar(15)

declare

@drweek as varchar(15)

declare

@fdate1 as varchar(15)

declare

@fdate2 as varchar(15)

declare

@fdate3 as varchar(15)

declare

@fdate4 as varchar(15)

declare

@fdate5 as varchar(15)

declare

@fdate6 as varchar(15)

declare

@fdate7 as varchar(15)

declare

@fdate8 as varchar(15)

declare

@fdate9 as varchar(15)

declare

@SQL varchar (max)

set

@drdate='1/3/2010'

select

@drweek=drweek from dbo.DateRef where drdate=@drdate

set

@fdate1=convert(varchar(10), cast(@drweek as datetime),101)

set

@fdate2=convert(varchar(10),dateadd(day,1,@fdate1),101)

set

@fdate3=convert(varchar(10),dateadd(day,1,@fdate2),101)

set

@fdate4=convert(varchar(10),dateadd(day,1,@fdate3),101)

set

@fdate5=convert(varchar(10),dateadd(day,1,@fdate4),101)

set

@fdate6=convert(varchar(10),dateadd(day,1,@fdate5),101)

set

@fdate7=convert(varchar(10),dateadd(day,1,@fdate6),101)

set

@fdate8=convert(varchar(10),dateadd(day,1,@fdate7),101)

set

@fdate9=convert(varchar(10),dateadd(day,1,@fdate8),101)

@fdate9

@fdate1

select

distinct m.FICLBNO,m.FIUPC,m.prscitemno,m.prsamsitemno,m.OwnerName,m.HubName,

isnull

(m1.FIQTY,0) Q1 ,isnull(m1.FICOST,0) C1,isnull(m1.FISALES,0) T1,

isnull

(m2.FIQTY,0) Q2,isnull(m2.FICOST,0) C2,isnull(m2.FISALES,0)T2,

isnull

(m3.FIQTY,0) Q3,isnull(m3.FICOST,0) C3,isnull(m3.FISALES,0)T3,

isnull

(m4.FIQTY,0) Q4,isnull(m4.FICOST,0) C4,isnull(m4.FISALES,0)T4,

isnull

(m5.FIQTY,0) Q5,isnull(m5.FICOST,0) C5,isnull(m5.FISALES,0)T5,

isnull

(m6.FIQTY,0) Q6,isnull(m6.FICOST,0) C6,isnull(m6.FISALES,0)T6,

isnull

(m7.FIQTY,0) Q7,isnull(m7.FICOST,0) C7,isnull(m7.FISALES,0)T7,

isnull

(m8.FIQTY,0) Q8,isnull(m8.FICOST,0) C8,isnull(m8.FISALES,0)T8,

isnull

(m9.FIQTY,0) Q9,isnull(m9.FICOST,0) C9,isnull(m9.FISALES,0)T9

from

smrsalesrep_m m

left

outer join smrsalesrep m1 on m.FICLBNO=m1.FICLBNO and m.FIUPC=m1.FIUPC and m.prscitemno=m1.prscitemno

and

m.prsamsitemno=m1.prsamsitemno and m.OwnerName=m1.OwnerName and m.HubName=m1.HubName and m1.FISALDAT=@fdate1

left

outer join smrsalesrep m2 on m.FICLBNO=m2.FICLBNO and m.FIUPC=m2.FIUPC and m.prscitemno=m2.prscitemno

and

m.prsamsitemno=m2.prsamsitemno and m.OwnerName=m2.OwnerName and m.HubName=m2.HubName and m2.FISALDAT=@fdate2

left

outer join smrsalesrep m3 on m.FICLBNO=m3.FICLBNO and m.FIUPC=m3.FIUPC and m.prscitemno=m3.prscitemno

and

m.prsamsitemno=m3.prsamsitemno and m.OwnerName=m3.OwnerName and m.HubName=m3.HubName and m3.FISALDAT=@fdate3

left

outer join smrsalesrep m4 on m.FICLBNO=m4.FICLBNO and m.FIUPC=m4.FIUPC and m.prscitemno=m4.prscitemno

and

m.prsamsitemno=m4.prsamsitemno and m.OwnerName=m4.OwnerName and m.HubName=m4.HubName and m4.FISALDAT=@fdate4

left

outer join smrsalesrep m5 on m.FICLBNO=m5.FICLBNO and m.FIUPC=m5.FIUPC and m.prscitemno=m5.prscitemno

and

m.prsamsitemno=m5.prsamsitemno and m.OwnerName=m5.OwnerName and m.HubName=m5.HubName and m5.FISALDAT=@fdate5

left

outer join smrsalesrep m6 on m.FICLBNO=m6.FICLBNO and m.FIUPC=m6.FIUPC and m.prscitemno=m6.prscitemno

and

m.prsamsitemno=m6.prsamsitemno and m.OwnerName=m6.OwnerName and m.HubName=m6.HubName and m6.FISALDAT=@fdate6

left

outer join smrsalesrep m7 on m.FICLBNO=m7.FICLBNO and m.FIUPC=m7.FIUPC and m.prscitemno=m7.prscitemno

and

m.prsamsitemno=m7.prsamsitemno and m.OwnerName=m7.OwnerName and m.HubName=m7.HubName and m7.FISALDAT=@fdate7

left

outer join smrsalesrep m8 on m.FICLBNO=m8.FICLBNO and m.FIUPC=m8.FIUPC and m.prscitemno=m8.prscitemno

and

m.prsamsitemno=m8.prsamsitemno and m.OwnerName=m8.OwnerName and m.HubName=m8.HubName and m8.FISALDAT=@fdate8

left

outer join smrsalesrep m9 on m.FICLBNO=m9.FICLBNO and m.FIUPC=m9.FIUPC and m.prscitemno=m9.prscitemno

and

m.prsamsitemno=m9.prsamsitemno and m.OwnerName=m9.OwnerName and m.HubName=m9.HubName and m9.FISALDAT=@fdate9