Sunday, September 30, 2012

Importing Data into SQL

If you need to import data from a file, this can be achieved by using SQL Bulk Insert command. Recently I did use this method to import some 500 000 data from few comma separated value (CSV) files and thought to share it with you. What you need to remember is, if you are importing data from more than one file the data should have the same format through out the files.

This is a sample set of data I imported into my table from the file named File1.csv.

Login,Name,Date,Result,Pass

U0001,Roman Silva,1/10/2010 17:23,100,TRUE

U0002,Anthony Don,28/09/2010 10:01,70,TRUE

U0003,Saman Perera,16/09/2010 11:31,90,TRUE

U0004,Silvia Raz,26/09/2010 22:11,40,FALSE

U0005,Rebecca Maine,18/09/2010 11:30,100,TRUE

I used the following script to create a temporary table for my imported data.

  1. SET ANSI_NULLS ON
  2. GO
  3.  
  4. SET QUOTED_IDENTIFIER ON
  5. GO
  6.  
  7. CREATE TABLE [dbo].[TABLENAME_ImportedData](
  8.     [LoginId] [NVARCHAR](50) NOT NULL,
  9.     [Name] [NVARCHAR](200) NULL,
  10.     [Date] [DATETIME] NULL,
  11.     [Result] [INT] NULL,
  12.     [Pass] [NVARCHAR](8) NULL
  13. ) ON [PRIMARY]
  14.  
  15. GO

To fetch data from the file I used the following script.

  1. BULK INSERT [TABLENAME_ImportedData]
  2. FROM 'D:\DataFiles\File1.csv'
  3. WITH (
  4.          FIELDTERMINATOR =',', -- Since my columns are seperated using commas (,).
  5.          ROWTERMINATOR ='\n',  -- Since each data row is in its own line.
  6.          FIRSTROW = 2          -- Since my first row is having column names. Please note FIRSTROW is not recommended to skip the column names.
  7.       )

While running the script I faced an issue with the date since my server was set to US English as the default language. In US English the dates should be in MDY format. So I had 3 choices, either to change the date formats on my data files, change the server default language to another language which has its date format as DMY or change the date format of the server. I used the easy way to change the date format of the server by running  the following command.

  1. SET DATEFORMAT dmy

If you like to change SQL server default language and need help please read my article on that.

Saturday, September 29, 2012

Changing SQL Server Default Language

If you needed to change the default language of a SQL Server login you can do so by using sp_defaultlanguage command.

First determine the current default language by using the @@language variable as follows.

  1. SELECT @@language

Then choose the language you want to change to from the available languages. You can find the available languages by the following command.

  1. EXEC sys.sp_helplanguage

After selecting the language wanted to change, run the following command to change the default language, I am changing it to British using the following command.

  1. DECLARE @Login NVARCHAR(30)
  2. SET @Login = SYSTEM_USER
  3. EXEC sp_defaultlanguage @Login, 'British'

Run the @@language to see whether the default language is changed as you wanted. Do not forget to use a new query window to see the changes.

Sunday, September 23, 2012

Change Quick Access Default Application Icons on Samsung Galaxy S2

If you ever wanted to change the quick access default application icons on Samsung Galaxy S2 you can do this by going to applications screen. For example as seen in the following image we have Phone, Contacts, Messaging and Applications as the default set of icons.

Screenshot_2012-09-22-22-59-08

To change this first go to the applications page by touching Applications.

Screenshot_2012-09-23-12-05-02Screenshot_2012-09-22-23-59-25Screenshot_2012-09-22-23-59-08

Then touch the menu button on the phone to activate the menu and select Edit. This will take you to edit screen view. Now it is simple as dragging the application icon you want on your quick access to the icon you need to be replaced.

When you are satisfied, touch on the back button to save the changes. If you need to ignore the changes press the middle button.

As seen in the following images I have replaced the Contacts with Play Movies. Also if you want you keep only 3 or less icons in quick access you can achieve this by deleting unwanted icons using the edit view.

Screenshot_2012-09-23-12-37-16Screenshot_2012-09-22-23-58-36Screenshot_2012-09-22-22-56-39

Friday, August 24, 2012

Beetaloo Reservoir – Beetaloo South Australia

If you like to enjoy nature like me another place you can visit in South Australia is the Beetaloo Reservoir in Beetaloo which is managed by SA Water. It is about 220 kms from Adelaide. It is a reservoir built in the year of 1885 with a capacity of 3150 mega liters of water and having a catchment area of 4856 hectares. Its wall is 33.53 meters high and the maximum water level could reach 23.88 meters.

After parking your car you need to walk for about 10 minutes to visit the lookout area. Walk is not difficult, but if it rains some areas might be slippery. You do not need a 4WD to reach Beetaloo Reservoir. Its gate is open from 9:00 AM to 4:00 PM each weekday and on Saturday, Sunday and public holidays open till 5:00 PM.

Map


View Larger Map

Saturday, August 18, 2012

Handler was not added through the Sys.UI.DomEvent.addHandler method.

imageimage

Sometime back I started getting this error in all the forms in which AJAX tools were used. The thing worried me most is that there were no changes made to the project source code. Later a friend of mine found that it is happening because of the wrong AjaxControlToolkit.

As I feel this dll swap happens when a control is dragged from the Visual Studio toolbox because the toolbox is referenced to a newer version of the toolkit than the project was using.

If you are also getting this error simply delete all the AJAX related files and folders in your projects’ bin directory. Once you are done there should not be any folders such as ar, cs, de, etc. Also remove the AjaxControlToolkit.dll. Then copy the version of the toolkit dll your project was using earlier to the Bin directory. Now clean and build your project to see the error disappear.

If you are still getting the error after correctly doing all this you might be getting the error due to another reason causing the same error. Since there are many reasons for this same error you better check the internet for other reasons to find out the exact reason causing the error for you.

Thursday, August 16, 2012

Correcting AJAX Calendar Extender Popup Calendar Position

Recently I encountered a positioning error in AJAX calendar extender. When I use the calendar extender inside of other container controls the popup calendar started appearing few inches above the button. You will be able to get an idea of the problem by the following image.

image_thumb

Since I couldn’t get it fixed by changing the properties I thought to find a solution for this.

One way to correct this is by applying a CSS style sheet to change the calendar positioning manually. I found this method while searching the web. This way since you need to enter the location of the calendar you need to try several times to get the correct positioning. If you are using this method simply place the following CSS style in your page and apply the style as shown. Remember you need to change the value to suit your form.

  1. <style type="text/css">
  2.     .fromDtPos
  3.     {
  4.         left: 245px !important;
  5.     }
  6. </style>

Apply the style to your calendar extender.

  1. <cc2:calendarextender id="calExpiry" runat="server" targetcontrolid="txtExpiry"
  2.     format="dd MMM yyyy" popupbuttonid="imgExpiry" enabled="True" cssclass="ajax__calendar fromDtPos">
  3. </cc2:calendarextender>

My preferred way to do this is by using the JavaScript that I wrote below. Since you do not need to enter the position manually this will be easier. Also this code will work irrespective of the number of parent containers it is having above of the control.

Insert the below JavaScript into your page.

  1. <script type="text/javascript" language="javascript">
  2.     function showCalendar(sender, args) {
  3.         var processingControl = $get(sender._button.id); // Getting the control which triggered the calendar.
  4.         sender._popupDiv.parentElement.style.top = processingControl.offsetTop + processingControl.height + 'px';
  5.         sender._popupDiv.parentElement.style.left = processingControl.offsetLeft + 'px';
  6.  
  7.         var positionTop = processingControl.height + processingControl.offsetTop;
  8.         var positionLeft = processingControl.offsetLeft;
  9.         var processingParent;
  10.         var continueLoop = false;
  11.  
  12.         do {
  13.             // If the control has parents continue loop.
  14.             if (processingControl.offsetParent != null) {
  15.                 processingParent = processingControl.offsetParent;
  16.                 positionTop += processingParent.offsetTop;
  17.                 positionLeft += processingParent.offsetLeft;
  18.                 processingControl = processingParent;
  19.                 continueLoop = true;
  20.             }
  21.             else {
  22.                 continueLoop = false;
  23.             }
  24.         } while (continueLoop);
  25.  
  26.         sender._popupDiv.parentElement.style.top = positionTop + 'px';
  27.         sender._popupDiv.parentElement.style.left = positionLeft + 'px';
  28.     }
  29. </script>

Then call the function showCalendar on onClientShown event of the calendar extender as seen below.

  1. <cc2:calendarextender id="calExpiry" runat="server" targetcontrolid="txtExpiry"
  2.     format="dd MMM yyyy" popupbuttonid="imgExpiry" enabled="True" onclientshown="showCalendar">
  3. </cc2:calendarextender>

Both of the above methods will correct the appearance of the popup calendar of the AJAX Calendar Extender as seen below.

image_thumb1

Saturday, August 11, 2012

Aldinga Beach – South Australia

If you are a beach person then Aldinga beach is a place not to miss. One speciality in this South Australian beach is that you can take your vehicle into the beach and have a drive. Since the sand is hard on this beach you do not need a 4WD, a normal 2WD car will do the job, but do not get over excited since then there is a chance you can get into trouble.

Without trying to explain using words I will upload some photos I have taken since a picture is worth thousand words.

While we were there, we experienced an extraordinary scene provided by mother nature. Suddenly on top of wet sand we started seeing sand flying with wind for few minutes. Just enjoy it by watching the following video.

Aldinga Beach – Sandy Wind

Map


View Larger Map