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...

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 ->...

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 ...

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...

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...

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...

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...

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....

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 belowXX 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...

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,...

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)TryIf dg.Items.Count > 0 ThenHttpContext.Current.Response.Clear()HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"HttpContext.Current.Response.AddHeader("content-disposition",...

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 Professional1. Start > Run > gpedit.msc > OK.2. Under User Configuration, expands the Administrative Templates.3. Under Administrative Templates, expands System, then click...

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).DefaultViewDataGrid1.DataBind()End SubGood...