Export Data From MS Access to Excel File

I passed data from MS Access to Excel recently, so I would like to share the techniques and code that I used. Firstly, a reference is needed to be made to Microsoft Excel. To add a reference, go to Tools -> References while working in any code module. As shown from the list, I am using Excel 11 (Office 2003). Look for the Microsoft Excel Object Library that is available on your computer and click the check box.


Here is my code from Access:

Public Function Export() As String
Dim oApp As Excel.Application
Dim oWB As Excel.Workbook
Dim i As Integer
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String

'Create an instance of Excel and add a new blank workbook
sSQL = "SELECT * FROM [table_name]"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

Set oApp = New Excel.Application
oApp.Visible = False
Set oWB = oApp.Workbooks.Add

'Add the field names as column headers (optional)
For i = 0 To rst.Fields.Count - 1
oWB.Sheets(1).Cells(1, i + 1).Value = rst.Fields(i).Name
Next

oWB.Sheets(1).Range("1:1").Font.Bold = True
oWB.Sheets(1).Cells(2, 1).CopyFromRecordset rst

'Clean up ADO Objects
rst.Close
Set rst = Nothing

'Create a folder if not exist
Dim strFilePath As String
Dim strFolder As String
strFolder = "C:\NewFolder"
strFilePath = strFolder & "\Rpt_" & Format(Now(), "yyyymmdd_HHmmss") & ".xls"

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(strFolder) Then
'Create the file
FileSystem.MkDir (strFolder)
End If

'Clean up Excel Objects
oWB.Close SaveChanges:=True, Filename:=strFilePath
Set oWB = Nothing
oApp.Quit
Set oApp = Nothing

'Open the file after export to excel
Shell "EXCEL.EXE """ & strFilePath & "", vbNormalFocus
End Function

HAPPY PROGRAMMING!

Import Data from Excel File (.cont)

As I has stated in my previous post regarding to Export Data Form an Excel file, there is one drawback lying behind them. The Sheet1 from the query actually is the name of the spreadsheet in your excel file. What if there is no Sheet1 inside the excel file? What if you don’t even know the sheet name?

Yes. I found this error when I tried to upload an excel file with different sheet name. And here is the solution for that.

To get the sheet name in your excel file, firstly, Microsoft DAO 3.5 Library is needed. Go to Project -> Reference -> Add Reference, select Microsoft DOA 3.5 Library from the list and add to your project. Here is the example code of how to get the first sheet in the excel file.

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 strSheet As String

dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("C:\test.xls", False, True, "Excel 8.0;")
strSheet = 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 [" & _
strSheet & "]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

Hope this will help you work it out.

CAPITAL LETTERS ONLY in Combo Box

For some reasons, you may need to let users key in ONLY CAPITAL LETTER in your text box or combo box. In the Key Down event, you can capture the key that user has entered and from there, convert the letter to capital letter and return to the text box. Instead of Key Down, you can also use Key Press event. The following codes will make it. Try it out yourself.

Private Sub TextBox1_KeyPress (KeyAscii As Integer)
Char = Chr(KeyAscii)
KeyAscii = Asc(UCase(Char))
End Sub

MAINTAINING SCROLL POSITION IN A WEB USER INTERFACE IN ASP .NET

Some people have problems in maintaining scroll position for a web page after a post back is performed. I had posted an article in PHP example, Maintaining Scroll Position in a Web User Interface in PHP last time.

There is a very simple solution to apply this in ASP .NET. You can just turn on the “Smart Navigation” in the Properties of ASP .NET. But sometimes it can mess up with other JavaScript. Alternatively, you can just add a Java Script to keep the scroll position.

Similar with PHP example, firstly, you need two hidden fields in your form to store the position of X and Y every time a post back is performed. Other than that, you also need two Java Script methods. One is save the position before the page is refreshed; one is set the position X and Y when it is refreshed. Here is the example:

<HTML>
<HEAD>
<TITLE>Maintain Scroll Position</TITLE>

<script>
function SavePos() {
document.Form1.posX.value = document.body.scrollLeft;
document.Form1.posY.value = document.body.scrollTop;
}
function SetPosition() {
var x, y;
x = document.Form1.posX;
y = document.Form1.posY;
if (x != 'undefined' && y != 'undefined')
{
window.scroll (x.value, y.value);
// either use scroll, scrollBy or scrollTo
}
}
</script>

</HEAD>
<BODY onload="SetPosition()">

<form id="Form1" method="post" runat="server"
onsubmit="SavePos();">
<input name="posX" id="posX" type="hidden"
value="0" runat="server" />
<input name="posY" id="posY" type="hidden"
value="0" runat="server" />
<p> A very long page……</p>
…
<P> A very long page……</P>

</form>

</BODY>
</HTML>

MAINTAINING SCROLL POSITION IN A WEB USER INTERFACE IN PHP

Anyone who is creating a very long Web Form that entails a lot of post backs may encounter to the user interface problem that occurs in such scenario that loss of scroll position on each post back (namely, if you scroll down to the middle of a long page, and after the browser refreshed, the scroll position is lost).

For this case, using Java Script for keeping the scroll position is the best way to go. Java Script can help us to know the current position of the browser.

Firstly, you need two hidden fields in your form to store the position of X and Y every time a post back is performed. Other than that, you also need two Java Script methods. One is save the position before the page is refreshed; one is set the position X and Y when it is refreshed. Here is the example:

<HTML>
<HEAD>
<TITLE>Maintain Scroll Position</TITLE>

<script>
function SavePos() {
document.Form1.posX.value = document.body.scrollLeft;
document.Form1.posY.value = document.body.scrollTop;
}
function SetPosition() {
var x, y;
x = document.Form1.posX;
y = document.Form1.posY;
if (x != 'undefined' && y != 'undefined')
{
window.scroll (x.value, y.value);
// either use scroll, scrollBy or scrollTo
}
}
</script>

</HEAD>
<BODY onload="SetPosition()">

<form name="Form1" id="Form1" method="post"
onsubmit="SavePos()" action="index.php">
<input name="posX" id="posX" type="hidden"
value="<?php echo $_REQUEST['posX'] ? >" />
<input name="posY" id="posY" type="hidden"
value="<?php echo $_REQUEST['posY'] ? >" />
<p>A very long page……</p>
…
<P> A very long page……</P>
</form>

</BODY>
</HTML>

Good Luck!

Dealing with apostrophes in SQL strings

In many applications, the developer has side-stepped the potential use of the apostrophe in some of the text fields. So when adding values to a table, be aware that problems may be caused by embedded apostrophes in a string.

Consider the SQL Insert statement below.

INSERT INTO table_name (table_field_name) VALUES (‘O’reill’, ‘92314567’)

Notice that there is an apostrophe in the text “O’reill”. In SQL, the apostrophe is an illegal character. It is interpreted as a string delimiter or the end of the string. So when it encounters to the text and detects another apostrophe, an error will occur.

A very simple solution to solve the problem: to allow apostrophes to be inserted into a database, simply double-up all occurrences of the apostrophes. So the final SQL will look like this:

INSERT INTO table_name (table_field_name) VALUES (‘O’’reill’, ‘92314567’)

In VB.NET, in order to convert the single enclosed apostrophe to two apostrophes, you can use Replace statement to accomplish the replacement.

"'" & Replace (txtName.text, "'", "''") & "'"

Good Luck!

Embedded Java Script in ASP .NET

Many developers like to use the flexibility of writing Java Script in ASP .NET to achieve the functionality they need or notify users of some server-side behavior. For example, if there was some server-side error and the data entered weren’t correctly saved, or the data entered by users are not in the correct format. In this case, we might want to pop up alert message box to inform the users.

Basically there are two ways to write a java script code in ASP .NET. Either call the java script in the html of the page by creating an onload event, or we can use an additional methods provided in ASP .NET to handle this in the code behind and register a client script block with the ASP .NET web page.

There are two methods available to register a script in .NET environment. There are RegisterStartUpScript and RegisterClientScriptBlock.


RegisterStartUpScript
This method takes two string inputs:

RegisterStartUpScript (string key, string script)
// Key – unique key that identifies a script block
// Script – content of the script that will be sent to the client

Typically what will happen is when the user clicks to submit his/her data, we will do some processing and validation checking, and then alert user of some unexpected behavior. Thus, we will call the RegisterStartUpScript() method in the Click event handler of the button.

Here is a simple example for that.

The code first generates an error message by checking field values (in this case if a txtName TextBox control was empty) and builds the message appropriately. Next, we will build the client-side JavaScript script block. Note that before I register the script, I added a conditional if statement, IsStartupScriptRegistered(string key). This is very essential calling this method to avoid unnecessary assembling of the client-side script.

IsStartUpScriptRegistered(string key)
// key – the string key of the start up script to search for. This method will check
// whether the script is already registered or not.

RegisterClientScriptBlock
This method takes two string inputs:

RegisterClientScriptBlock (string key, string script)
// Key – unique key that identifies a script block
// Script – content of the script that will be sent to the client

This is very similar with the RegisterStartupScript() method. You only need to write the Java Script string and pass it as the second parameter in the method with a string key. Before I register the script, I added a conditional if statement, IsClientScriptBlockRegistered(string key). This is very essential calling this method to avoid unnecessary assembling of the client-side script.

IsClientScriptBlockRegistered (string key)
// key – the string key of the start up script to search for. This method will check
// whether the script is already registered or not.

Difference between RegisterStartUpScript and RegisterClientScriptBlock
As you can see from the above, two of the methods are very similar. The main difference between them is RegisterStartUpScript will embed the script BEFORE the CLOSING TAG of the page object’s < form runat=”server” > element, while RegisterClientBlockScript will embed the script AFTER the OPENING TAG of the page object’s < form runat = “server” > element.

Happy Programming!

DELETE FROM and TRUNCATE table functionality

There are two main commands used for deleting all the data from a table: TRUNCATE and DELETE FROM. Two of these achieve the same result; however there are significant differences between the two. There are advantages, limitations and consequences that you should consider to decide which one to use.

About TRUNCATE
 TRUNCATE command is faster because it removes all records in a table by deallocating the data pages used by the table, thus reduces the resource overhead of logging in the log and the number of acquired locks as well.

 It does not generate any UNDO information, does not fire DELETE triggers and does not record any information in the snapshot log.

 The only record of the truncation is the page deallocation, so the removed data cannot be restored.

 WHERE condition cannot be applied in TRUNCATE command.

 Auto commit and cannot roll back.

 It reset the IDENTITY value back to the SEED and the deallocated pages can be re-used immediately. If you want to retain the identity counter, use DELETE FROM command instead.

 This means if you have a table with an identity column and you have 20 rows with a seed value of 1, you last record will have the identity’s value as 20. After the table is truncated, the identity column will have the value of 1 when a new record is inserted. While for DELETE command, when a new record is inserted, the identity column will have a value of 21.

 This command cannot be used on tables that are referenced by foreign keys, or involved in replication or log shipping.

 If a TRUNCATE TABLE is issued against a table that are referenced by foreign key, an error is returned: Cannot truncate table ‘xxx’.


About DELETE FROM
 DELETE FROM command logging all rows in the transaction log, thus it consumes more database resources and locks.

 WHERE clause can be applied to narrow down the rows that need to be deleted.

 Not an auto commit and the removed data can be rolled back.

 To reset the IDENTITY value back to the seed, use DBCC CHECKIDENT command once you are done with the DELETE FROM command.

 When a table requires all records to be deleted and TRUNCATE command cannot be used due to against the rules, the following statement can be used to achieve the same result as TRUNCATE command:

DELETE FROM [table_name]
DBCC CHECKIDENT(“table_name”, RESEED, “reseed_value”)

Hope this article provides some clarify to you on this issue.

Access Error: XXX DB can't append all the records in the append query

Currently I am working on a small project using MS Access Database. Since I am not so expert with the MS Access, it really brings me a lot of problems and spent me a lot of time too.

I tried to insert a new record into my database, but it kept came up with the message below

XX DB can't append all the records in the append query.

XX DB set 0 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 1 record(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.

I tried to search online for the solution, but i can't find one. i tried to use .Execute method hope that it may give me a more meaningful error message. Finally i figured out what is the cause.

This error may happen if the field is Required, but no value is supplied.

It also can happen if the field's Allow Zero Length property is set to No, but you attempt to add a zero-length string instead of a Null. And there is a Validation Rule for the table (in the Properties box in the table design), as well as for the fields.

Hope this can help you.

Request.ServerVariables (“LOGON_USER”) Error

Active Server Pages exposes an object called ServerVariables, which is part of the Request object. This ServerVariables object allows the programmer to see many environment variables. You can use the ServerVariables along with Internet Information Services (IIS)’s directory security options to determine who is currently accessing your site. You can also grab the visitor's IP address using the ServerVariables object.

In some .NET projects, when you try to access the Request.ServerVariables (“LOGON_USER”) variable in ASP .NET, it will return an empty string. This problem will occur due to the related authentication variables in the Server Variables list are not populated if you are using Anonymous Access Security to access the page. In the section of the Web.Config file, if you give the Anonymous user access, this problem will occur as well.

So how this problem can be solved?

There are two types of authentication mode which are Forms mode and Windows mode. To populate the LOGON_USER variable when you use any of the authentication mode other than none, you can deny access for Anonymous user in the section of the Web.Config file.

For authentication mode = “Forms”, use the following syntax in the Web.Config file.
< authorization >
< deny users = "?" > < !-- This denies access to the Anonymous user -- >
< allow users ="*" > < !-- This allows access to all users -- >
< /authorization >

If you are using Windows Authentication, go to Start > Run > Type inetmgr, this will bring IIS’s dialog box.

Expand Default Web Site and right click the Web Project folder, and then click Properties.

In the Properties dialog box, click the Directory Security tab, under the Anonymous access and authentication control, click Edit.

In the Authentication Methods dialog box, un-checked the Anonymous Access check box. Click OK for both dialog boxes. Close the IIS and does the testing again whether can get the value of LOGON_USER.

Hope this can help you work it out. Good luck.

A Simple Solution for Export ASP .NET Data Grid to Excel

This sample demonstrates an easy way to export DataGrid into an Excel file. Most of the web controls have a RenderControl method which will write an html text stream. All have to do is declare the response object, call RenderControl method and output the “rendering”. Quite simple!

Here is the code for the Export method:

Public Shared Sub ExportToExcel(ByVal dg As DataGrid, ByVal lblTitle As Label)
Try
If dg.Items.Count > 0 Then
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" & lblTitle.Text & ".xls")

'Remove the charset from the Content-Type header
HttpContext.Current.Response.Charset = ""

Dim lbl As New System.Web.UI.WebControls.Label
lbl.Text = "<>"

'Turn off the view state
lbl.EnableViewState = False
dg.EnableViewState = False
Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)

'Get the HTML for the control
lblTitle.EnableViewState = False
lblTitle.RenderControl(hw)
lbl.RenderControl(hw)
lbl.RenderControl(hw)
dg.RenderControl(hw)

'Write the HTML back to the browser
HttpContext.Current.Response.Write(tw.ToString())

'End the response
HttpContext.Current.Response.End()
End If
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Sub

This is really a simple solution. Hope it works out for you.

Task Manager Had Been Disabled By Your Administrator

Yesterday, my friend called me for help because her computer has been infected by some kinds of spyware and caused an error – Task Manager being disabled.

If this happens normally you will need to edit the Windows Registry to fix it. Listed below you will find some ways to re-enable Task Manager.

Method 1 – Using Group Policy Editor in Windows XP Professional
1. Start > Run > gpedit.msc > OK.
2. Under User Configuration, expands the Administrative Templates.
3. Under Administrative Templates, expands System, then click on Ctrl+Alt+Delete Options.
4. Select Remove Task Manager from the list in the right-hand. Right click on it and select Properties. If this setting is enabled and users try to start Task Manager, a message appears explaining that a policy prevents the action. So by disabling the policy, you are enabling the Task Manager.
5. Click Setting. Select Disabled option and apply OK.


Method 2 – Change the Task Manager option through the Run time
1. Click Start, Run, and type the following command in the command prompt.

REG add HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\System /v DisableTaskMgr /t REG_DWORD /d 0 /f

2. Click Enter.


Method 3 – Change Task Manager using a Registry REG file
1. Open a new Notepad file.
2. Copy and paste the information below into the Notepad and save it to your desktop as taskmanager.reg

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\System]
"DisableTaskMgr"=dword:00000000

3. Double click on the file to enter the information into the Windows Registry.


Method 4 – Delete the restriction in the registry manually
1. Click Start, Run, and type REGEDIT.
2. Press Enter.
3. Navigate to the following branch.

HKEY_CURRENT_USER \ Software \ Microsoft \ Windows \ CurrentVersion \ Policies\ System

4. From the list in the right-hand pane, find and delete the value named Disable TaskMgr.
5. Close the Registry Editor.


Method 5 – Download and Run FixTaskManager program
1. Click on this links and download the program FixTaskManager to your desktop.
2. Double click on it and run it.

How to Import Data from Excel File

This article provides a very simple example of how to query an Excel spreadsheet from an ASP.NET page using VB .NET. Check it out!

Sub Page_Load(sender As Object, e As EventArgs)
Dim ds As New DataSet()

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test.xls;" & _
"Extended Properties=""Excel 8.0;"""

Dim da As New OledbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
da.TableMappings.Add("Table", "Excel")
da.Fill(ds)

DataGrid1.DataSource = ds.Tables(0).DefaultView
DataGrid1.DataBind()
End Sub

Good Luck!