Import Data from Excel File using VB .NET

The following is the sample code how to query an Excel spreadsheet from an ASP.NET page using VB .NET:

Dim strConn As String Dim da As OleDbDataAdapter Dim ds As New DataSet strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _          "Data Source=C:\test.xls;Extended Properties=""Excel 8.0;""" da = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn) da.TableMappings.Add("Table", "Excel") da.Fill(ds) dataGrid1.DataSource = ds.Tables(0).DefaultView dataGrid1.DataBind() da.Dispose()

Note:
   -  The code above is just select data from Sheet1(Worksheet) only.

If you want to select data from first sheet in the Excel workbook, need to get the name of the first sheet first.
To get the name of the first sheet in the Excel workbook, refer code below:

Dim dao_dbE As dao.DBEngine Dim dao_DB As DAO.Database Dim strFirstSheetName As String dao_dbE = New dao.DBEngine dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;") strFirstSheetName = dao_DB.TableDefs(0).Name da0_DB.Close()

Note:
   -  Using code above Microsoft DAO 3.5 Library is needed.
   -  From the Project menu, click References, click Add Reference...
      and then select the Microsoft DAO 3.5 Library to add it.

So, complete code should like below:

Dim strConn As String Dim da As OleDbDataAdapter Dim ds As New DataSet Dim dao_dbE As dao.DBEngine Dim dao_DB As DAO.Database Dim strFirstSheetName As String dao_dbE = New dao.DBEngine dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;") strFirstSheetName = dao_DB.TableDefs(0).Name strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _          "Data Source=C:\test.xls;Extended Properties=""Excel 8.0;""" da = New OleDbDataAdapter("SELECT * FROM [" & _           strFirstSheetName & "]", strConn) da.TableMappings.Add("Table", "Excel") da.Fill(ds) dataGrid1.DataSource = ds.Tables(0).DefaultView dataGrid1.DataBind() da.Dispose() da0_DB.Close()

Good Luck!!!

User.Identity.Name Returns Empty String in ASP .NET

If you try to get currently logged on user name with User.Identity.Namein ASP.NET, but the return value is empty string.
The following is syntax accesses this variable in C# .NET:

string strUserName = User.Identity.Name;

This problem occurs because if you use Anonymous Access security to access the .aspx page. This problem can also occurs if you give the Anonymous user access in the <authorization> section of the Web.config file.

Solution:
Check the authentication mode in the Web.config file.

If the authentication mode is Forms-based authentication:

<authentication mode="Forms" />

To solve this problem just deny access to the Anonymous user in the Web.config file, user the following syntax:

<authorization>    <deny users = "?" /> <!-- This denies access to the Anonymous user -->    <allow users = "*" /> <!-- This allows access to all users --> </authorization>

If the authentication mode is Windows-based authentication:

<authentication mode="Windows" />

To solve this problem, use the following steps:

1. Go to Internet Services Manager, right-click the .aspx file or the Web Project folder, and then click Properties. 2. If you clicked Properties for the Web Project folder, click the Directory Security tab. If you clicked Properties for the .aspx file, click the File Security tab. 3. Under Anonymous Access and authentication control, click Edit. 4. In the Authentication methods dialog boc, clear the Anonymous Access check box, and then select either theBasic, the Digist or the Integrated (NT Challenge/Response) check box. 6. Click OK to close both dialog boxes.