GetJSON Only Runs Once

Problem:
$getJSON() method only runs once

Solution:

If you are using jQuery, and multiple $.getJSON() methods are called in the web page, but can only get one of the getJSON method working, then you will need to add in this code to refresh the cache, before each of the getJSON method.

$.ajaxSetup({ cache: false });

PS: This problem usually occurs when running the web page from Internet Explorer web browser. The same problem should not be encountered when running it from Chrome.

Read More

Check if a Function Exists in Javascript

There are times when we want to call a JavaScript function, and in certain circumstances, we want to make sure that the function does exist. e.g. When a dialog window can be opened from more than one page. And, we want to refresh the component of the the parent page from the dialog window. Thus, we need to check if the function really exists.

This is a simple yet useful example to check if a function exists in JavaScript.

<input type="button" onclick="checkFunctionExist();" value="Check Function Existence" />

<script type="text/javascript" language="javascript">

 function testFunction() {

 }

 function checkFunctionExist() {
  if (window.testFunction) alert('testFunction exists');
  else alert('testFunction does not exist');

  if (window.nonExistantFunction) alert('nonExistantFunction exists');
  else alert('nonExistantFunction does not exist');
 }

</script>

To check whether a function exists on the parent page (the page which opened the dialog window), use:

if(window.opener.testFunction) alert('testFunction exists');
else alert('testFunction does not exist');

Read More

The Server Was Unable to Process The Request Due to an Internal Error

Error:

Message: The server was unable to process the request due to an internal error. For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs.

This error usually occurs when the application uses web service or similar technologies.

Solution:
The solution may vary, depending on the issue faced. One thing for sure, there is one way to make sure the exact technical error of the message.

Open the web.config or app.config file of the application, then search for serviceDebug tag, then change the includeExceptionDetailInFaults attribute to true.

<serviceDebug includeExceptionDetailInFaults="false" />

Afterwards, reproduce the error. The exact technical error message should be displayed.

Read More

Get The First and The Last Day of The Month

Just saw a question from someone on a mailing list I join asking for how to get the transactions occurring for the last three months (in other words, three months from current date).


First, you may want to do something simple.
To get the first day and the last day of the month, we can use:

SELECT 
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),
DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0) - 1

If you play around a little bit, you can also get the first day and the last day of the previous month:

SELECT 
DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0),
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) - 1

To get the transactions for the last three months, we just need to modify the number a little bit.

SELECT
DATEADD(month, DATEDIFF(month, 0, GETDATE())-2, 0),
DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0) - 1

Then, just select the transactions occurring in between these ranges.

SELECT * FROM TableName WHERE TransactDate BETWEEN
DATEADD(month, DATEDIFF(month, 0, GETDATE())-2, 0) AND
DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0) - 1

Read More

Select Several Rows into One Row in SQL

In SQL Server, there are times when we want to consolidate the content of several rows into one row.

Though it is not meant for sophisticated purposes, there is actually a simple and easy way to do this.
Suppose we have a Member table containing  MemberID and Name fields.

To select the names of the members in one row, simply use the query:

DECLARE @strResult VARCHAR(5000)
SELECT @strResult = COALESCE(@strResult + ', ', '') + Name
FROM Member

SELECT MembersName = @strResult

The result will be:

We can also add some conditions to the select statement.
e.g. To generate the members with name containing 'Himura', we can simply do:

DECLARE @strResult VARCHAR(5000)
SELECT @strResult = COALESCE(@strResult + ', ', '') + Name
FROM Member
WHERE Name LIKE '%himura%'

SELECT TheHimura = @strResult

And we'll get:

That's all folks :)

Read More

Formulas Not Calculating in Excel 2007

I just encountered a weird problem.
When I open an excel document, the formula does not seem to calculate. I have a cell containing formula. When I change the value of other cells which form the formula, the cell containing the formula does not change.

Find the solution. Somehow, the settings on my Excel changes.
I am using Office 2007. To enable the formula calculation, go to the Excel Menu, click Excel Options.

On the Formulas tab -> Calculation options, Choose Automatic. It was Manual before. If you choose Automatic, then the result of the formula will be refreshed everytime the document is loaded.

There is another workaround, if you don't want the formula to be recalculated everytime the file is opened. Press F9 to refresh the value of the cells with formula.

Read More

Access to Temp Directory is Denied

Error:
When deploying application and viewing the web page, the following error occurs:

Access to the temp directory is denied.

Identity 'IIS APPPOOL\WebAppPool' under which XmlSerializer is running does not have sufficient permission to access the temp directory. CodeDom will use the user account the process is using to do the compilation, so if the user doesnt have access to system temp directory, you will not be able to compile. Use Path.GetTempPath() API to find out the temp directory location.

Solution:
Go to IIS, change advanced settings of the application pool used for the website, then under Process Model Category, change the Identity to ApplicationPoolIdentity.

Read More

Cannot Display Web Page on IIS due to Configuration Error

Error:

HTTP Error 500.19 - Internal Server Error

The requested page cannot be accessed because the related configuration data for the page is invalid.

Config Error: There is a duplicate 'system.web.extensions/scripting/scriptResourceHandler' section defined
Config File:  Application\Web\web.config

The error on IIS 7.5 should look like this.

Solution:
Open web.config, and then comment out the "system.web.extensions" sectionGroup under configSections tag.
For example:

<!--<sectiongroup name="system.web.extensions">
............
............
...........
</sectiongroup>-->

Read More

Data Type Exception Error

Error:
This is only part of the errors, since it is quite long, I will just quote the core part.

System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.InvalidOperationException: DataReader.GetFieldType(5) returned null.

This happens when I deploy a web application developed under Visual Studio .Net 2010 using SQL Server 2008 R2 for the database. The query used happen to retrieve fields of type hierarcyid. It works on other machines, this is the first time I encountered such error.
After searching through the internet, I found a solution in a thread on MSDN forum.

Seems that not only for table with field of CLR data type: hierarchyid, fields of Spatial type: geometry and geography also could result in such error.

Solution:
Add the file C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll to the project (as a reference), or simply copy the DLL file to the bin folder in which the project is deployed.

Reason:
According to the post, it seems that the new SQL Server 2008 data types are not native .NET data types. On the machine where the application is deployed to, the DLL file was not available, so the DataAdapter can't initialize the data types correctly.

Read More

Object Name is Not Declared

Error BC30451: Object_name is not declared

An object, e.g. button, linkbutton, or lable is created on the .aspx page. The intellisense does detect the object name. However, when it is debugged, it gives the error Object_name is not declared, where Object_name is the name of the object.

Solution:
1. If you are web application project, try deleting the aspx.cs or aspx.vb (code) file, then right-click the .aspx file, and choose Convert to web application.

2. Check if there is another copy of the same files in the same folder. This happens to me quite some times. Before doing major changes, I usually make a copy of the files first. The files are usually automatically included in the solution we are developing. Since the class of the copies have the same names, they conflict each other. Move the copy from the folder or simply exclude the copies from the solution by right-clicking on the files, then choose "Exclude From Project". See if it solves the issue.

3. If neither solution 1 nor 2 solves the issue, try adding new files with different names, then copy the content of the old files to the new ones. Note that the names of the class can not be the same. Try compiling and see if it works. If it does, you can then delete or move the old files, then rename the new files according to the old ones.

4. If none of the alternatives above work, try finding from internet, then POST IT HERE! Thanks ^_^

Read More

Paging in SQL 2

Regarding my post about Paging in SQL, I have found another easier way to include a column as a running number of the records. Hence, we do not need to create a temporary table, and the code will be much simpler.

I will use the same tables for example. In case you find difficulty finding the post about Paging in SQL, I include them here.

Here is the Create table Query:

CREATE TABLE dbo.MsUser
(  UserID CHAR(20) PRIMARY KEY,
   Username VARCHAR(100),
   Address VARCHAR(50),
   DivisionID INT
)

And here is the query to insert sample data:

-- Insert Data into MsUser
INSERT INTO dbo.MsUser VALUES ( 'Himura', 'Miss Himura', 'Earth', 3 )
INSERT INTO dbo.MsUser VALUES ( 'Selvia', 'Selvia', 'Indonesia', 2 )
INSERT INTO dbo.MsUser VALUES ( 'Superman', 'Clark Kent', 'Earth', 2 )
INSERT INTO dbo.MsUser VALUES ( 'SelviaHimura', 'Selvia Himura', 'Earth', 4 )
INSERT INTO dbo.MsUser VALUES ( 'Luck', 'Steven Luck', 'Indonesia', 1 )
INSERT INTO dbo.MsUser VALUES ( 'SuperLuck', 'Super Luck', 'United States', 1 )
INSERT INTO dbo.MsUser VALUES ( 'Selvi', 'Selvi', 'Indonesia', 1 )
INSERT INTO dbo.MsUser VALUES ( 'Lucky', 'Lucky Luke', 'United States', 3 )
INSERT INTO dbo.MsUser VALUES ( 'Steven', 'Steven', 'Earth', 4 )

Here is the data inserted to the tables:

Here is the query to get the data using paging:

DECLARE @PageSize INT, @PageNo INT
SELECT a.Username FROM (
    SELECT Username, RowNumber = ROW_NUMBER() OVER (ORDER BY Username) 
    FROM MsUser
) a WHERE a.RowNumber BETWEEN (@PageNo - 1 )* @PageSize + 1 AND @PageNo * @PageSize

Note that the key is in this part of code:

ROW_NUMBER() OVER (ORDER BY Username)

If the PageSize is set to 5 item per page, then the top 5 users will be displayed on Page 1 and the next 5 users will be displayed on Page 2.

e.g. 1

PageSize = 5, PageNo = 1

DECLARE @PageSize INT, @PageNo INT
SELECT  @PageSize = 5, @PageNo = 1
SELECT a.Username FROM (
    SELECT Username, RowNumber = ROW_NUMBER() OVER (ORDER BY Username) 
    FROM MsUser
) a WHERE a.RowNumber BETWEEN (@PageNo - 1 )* @PageSize + 1 AND @PageNo * @PageSize

The data retrieved on Page 1 will be:

e.g. 2

PageSize = 5, PageNo = 2

DECLARE @PageSize INT, @PageNo INT
SELECT  @PageSize = 5, @PageNo = 2
SELECT a.Username FROM (
    SELECT Username, RowNumber = ROW_NUMBER() OVER (ORDER BY Username) 
    FROM MsUser
) a WHERE a.RowNumber BETWEEN (@PageNo - 1 )* @PageSize + 1 AND @PageNo * @PageSize

And the data retrieved on Page 2 will be:

Read More