SQL SERVER Standard Date Formats

Standard Date Formats
Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY 1 HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2005 1:29PM 1
MM/DD/YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	11/23/98
MM/DD/YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	11/23/1998
YY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]	72.01.01
YYYY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]	1972.01.01
DD/MM/YY	British/French	SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	19/02/72
DD/MM/YYYY	British/French	SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]	19/02/1972
DD.MM.YY	German	SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]	25.12.05
DD.MM.YYYY	German	SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]	25.12.2005
DD-MM-YY	Italian	SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]	24-01-98
DD-MM-YYYY	Italian	SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]	24-01-1998
DD Mon YY 1	-	SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 06 1
DD Mon YYYY 1	-	SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2006 1
Mon DD, YY 1	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 98 1
Mon DD, YYYY 1	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 1998 1
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1	Default + milliseconds	SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2006 12:32:29:253PM 1
MM-DD-YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]	01-01-06
MM-DD-YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]	01-01-2006
YY/MM/DD	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]	98/11/23
YYYY/MM/DD	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]	1998/11/23
YYMMDD	ISO	SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]	980124
YYYYMMDD	ISO	SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]	19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1	Europe default + milliseconds	SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H)	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]	11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)	ODBC Canonical	SELECT CONVERT(VARCHAR(19), GETDATE(), 120)	1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)	ODBC Canonical (with milliseconds)	SELECT CONVERT(VARCHAR(23), GETDATE(), 121)	1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM	ISO8601	SELECT CONVERT(VARCHAR(23), GETDATE(), 126)	1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1	Kuwaiti	SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2006 12:39:32:429AM

Source: http://www.sql-server-helper.com/tips/date-formats.aspx

Make a list box search

You need a listbox with items, a textbox and a button.

protected void Button1_Click(object sender, EventArgs e)

    {

        ListBoxPolicies.SelectedIndex = -1;

        for(int i=0; i< ListBox1.Items.Count; i++)

        {

            if (ListBox1.Items[i].ToString().Contains(TextBox1.Text))

            {

                ListBox1.SelectedIndex = i;

            }

        }

    }

"SPLIT(x)" --> get the second part of the string in SQL SERVER

If you need something like Split('ABC - DE ') and would like to get
1) 'ABC'  --> The first part of the string
or
2) 'DE'    --->The second part of the string


2) I will start by the one I created:
Here is how to get the second part of the string:

CREATE FUNCTION [dbo].[GetSecondString] (@text VARCHAR(MAX), @character varchar(64))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @start int;
DECLARE @result VARCHAR(MAX)
SELECT @start =  CHARINDEX(@character,@text) --Get the starting character
---Substring(text, start right after the character, length of text (second string))
SELECT @result = LTRIM(RTRIM(substring(@text, @Start +1, LEN(@text)-@start)))
return @result
END


--------------------
Executing the function


SELECT dbo.GetSecondString ('abcdef1234- What a great result','-') AS myResult;


will produce -->"What a great result"

--------------------


1) To get the first part -obtained from the SQl Authority 

CREATE FUNCTION [dbo].[GetFirst] (@StringVar VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @rtStringVar VARCHAR(MAX)
SELECT @rtStringVar = CASE CHARINDEX('-', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX('-',LTRIM(@StringVar), 1) - 1)
END
RETURN @rtStringVar
END


---------------------
Executing the function

-Usage of FunctionSELECT dbo.GetFirstWord ('') AS FirstWordofSentence;SELECT dbo.GetFirstWord (' Space Before') AS FirstWordofSentence;SELECT dbo.GetFirstWord ('Long Statement') AS FirstWordofSentence;SELECT dbo.GetFirstWord ('SingleWord') AS FirstWordofSentence;

Insert into without duplicates

INSERT INTO  TableDuplicates
(empId)
SELECT  empId
FROM TableEmployees
WHERE empId NOT IN (select empId from TableDuplicates)

Get duplicates records in SQL

What are we cooking today? A query to count duplicates

SELECT ColumnX, COUNT(*) as  TotalCount
FROM TableY
GROUP BY  ColumnX 
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

List of Countries

RECIPE SOLUTION

(1) List of Countries in plain html

<select id="toCountry" style="margin:3px;" >

<option selected="selected" value="">Select Destination</option>

<option value="AF">Afghanistan</option>

<option value="AX">Aland islands</option>

<option value="AL">Albania</option>

<option value="DZ">Algeria</option>

<option value="USAS">American Samoa</option>

<option value="AD">Andorra</option>

<option value="AO">Angola</option>

<option value="AI">Anguilla</option>

<option value="AQ">Antarctica</option>

<option value="AG">Antigua/Barbuda</option>

<option value="AR">Argentina</option>

<option value="AM">Armenia</option>

<option value="AW">Aruba</option>

<option value="AC">Ascension</option>

<option value="AU">Australia</option>

<option value="AT">Austria</option>

<option value="AZ">Azerbaijan</option>

<option value="PT_ALIAS1">Azores</option>

<option value="BS">Bahamas</option>

<option value="BH">Bahrain</option>

<option value="ES_ALIAS1">Balearic Islands</option>

<option value="BD">Bangladesh</option>

<option value="BB">Barbados</option>

<option value="BY">Belarus</option>

<option value="BE">Belgium</option>

<option value="BZ">Belize</option>

<option value="BJ">Benin</option>

<option value="BM">Bermuda</option>

<option value="BQ_ALIAS3">BES Islands</option>

<option value="BT">Bhutan</option>

<option value="BO">Bolivia</option>

<option value="BQ_ALIAS4">Bonaire</option>

<option value="BA">Bosnia-Herz</option>

<option value="BW">Botswana</option>

<option value="BV">Bouvet Island</option>

<option value="VG">Br. Virgin Isl</option>

<option value="BR">Brazil</option>

<option value="IO">Br Ind Ocean Tr</option>

<option value="BN">Brunei</option>

<option value="BG">Bulgaria</option>

<option value="BF">Burkina Faso</option>

<option value="MM_ALIAS1">Burma</option>

<option value="BI">Burundi</option>

<option value="KH">Cambodia</option>

<option value="CM">Cameroon</option>

<option value="CM">Canada</option>

<option value="ES_ALIAS2">Canary Islands</option>

<option value="CV">Cape Verde</option>

<option value="KY">Cayman Islands</option>

<option value="CF">Central African Republic</option>

<option value="TD">Chad</option>

<option value="GB_ALIAS1">Channel Islands</option>

<option value="CL">Chile</option>

<option value="CN">China</option>

<option value="CX">Christmas Island</option>

<option value="CC">Cocos Islands</option>

<option value="CO">Colombia</option>

<option value="KM">Comoros</option>

<option value="CG">Congo</option>

<option value="CD">Congo Democratic Republic</option>

<option value="CK">Cook Islands</option>

<option value="FR_ALIAS1">Corsica</option>

<option value="CR">Costa Rica</option>

<option value="CI">Côte d'Ivoire</option>

<option value="HR">Croatia</option>

<option value="CU">Cuba</option>

<option value="CW">Curaçao</option>

<option value="CY">Cyprus</option>

<option value="CZ">Czech Republic</option>

<option value="DK">Denmark</option>

<option value="DJ">Djibouti</option>

<option value="DM">Dominica</option>

<option value="DO">Dominican Republic</option>

<option value="BQ">Dutch Caribbean</option>

<option value="TL_ALIAS1">East Timor</option>

<option value="EC">Ecuador</option>

<option value="EG">Egypt</option>

<option value="SV">El Salvador</option>

<option value="GB_ALIAS2">England</option>

<option value="GQ">Equatorial Guinea</option>

<option value="ER">Eritrea</option>

<option value="EE">Estonia</option>

<option value="ET">Ethiopia</option>

<option value="FK">Falkland Islands</option>

<option value="FO">Faroe Islands</option>

<option value="FJ">Fiji</option>

<option value="FI">Finland</option>

<option value="FR">France</option>

<option value="GF">French Guiana</option>

<option value="PF">French Polynesia</option>

<option value="TF">Fr S.Territory</option>

<option value="GA">Gabon</option>

<option value="GM">Gambia</option>

<option value="IL_ALIAS1">Gaza</option>

<option value="GE">Georgia</option>

<option value="GS">Georgia/Sandwich</option>

<option value="DE">Germany</option>

<option value="GH">Ghana</option>

<option value="GI">Gibraltar</option>

<option value="GB">Great Britain</option>

<option value="GR">Greece</option>

<option value="GL">Greenland</option>

<option value="GD">Grenada</option>

<option value="GP">Guadeloupe</option>

<option value="USGU">Guam</option>

<option value="GT">Guatemala</option>

<option value="GG">Guernsey</option>

<option value="GN">Guinea</option>

<option value="GW">Guinea-Bissau</option>

<option value="GY">Guyana</option>

<option value="HT">Haiti</option>

<option value="HM">Heard/McDonald</option>

<option value="NL_ALIAS1">Holland</option>

<option value="VA">Holy See</option>

<option value="HN">Honduras</option>

<option value="HK">Hong Kong</option>

<option value="HU">Hungary</option>

<option value="IS">Iceland</option>

<option value="IN">India</option>

<option value="ID">Indonesia</option>

<option value="IR">Iran</option>

<option value="IQ">Iraq</option>

<option value="IE">Ireland</option>

<option value="IM">Isle of Man</option>

<option value="IL">Israel</option>

<option value="IT">Italy</option>

<option value="JM">Jamaica</option>

<option value="JP">Japan</option>

<option value="JE">Jersey</option>

<option value="JO">Jordan</option>

<option value="KZ">Kazakhstan</option>

<option value="CC_ALIAS1">Keeling Islands</option>

<option value="KE">Kenya</option>

<option value="KI">Kiribati</option>

<option value="KR">Korea</option>

<option value="KP">Korea Dem Rep</option>

<option value="XZ_ALIAS1">Kosova</option>

<option value="XZ">Kosovo</option>

<option value="KW">Kuwait</option>

<option value="KG">Kyrgyzstan</option>

<option value="LA">Laos</option>

<option value="LV">Latvia</option>

<option value="LB">Lebanon</option>

<option value="LS">Lesotho</option>

<option value="LR">Liberia</option>

<option value="LY">Libya</option>

<option value="LI">Liechtenstein</option>

<option value="LT">Lithuania</option>

<option value="LU">Luxembourg</option>

<option value="MO">Macau</option>

<option value="MK">Macedonia</option>

<option value="MG">Madagascar</option>

<option value="PT_ALIAS2">Madiera</option>

<option value="MW">Malawi</option>

<option value="MY">Malaysia</option>

<option value="MV">Maldives</option>

<option value="ML">Mali</option>

<option value="MT">Malta</option>

<option value="FK_ALIAS1">Malvinas</option>

<option value="USMH">Marshall Islnds</option>

<option value="MQ">Martinique</option>

<option value="MR">Mauritania</option>

<option value="MU">Mauritius</option>

<option value="YT">Mayotte</option>

<option value="MX">Mexico</option>

<option value="USFM">Micronesia</option>

<option value="MD">Moldova</option>

<option value="MC">Monaco</option>

<option value="MN">Mongolia</option>

<option value="ME">Montenegro</option>

<option value="MS">Montserrat</option>

<option value="MA">Morocco</option>

<option value="MZ">Mozambique</option>

<option value="MM">Myanmar</option>

<option value="USMP">N.Mariana Island</option>

<option value="NA">Namibia</option>

<option value="NR">Nauru</option>

<option value="NP">Nepal</option>

<option value="NL">Netherlands</option>

<option value="NC">New Caledonia</option>

<option value="NZ">New Zealand</option>

<option value="NI">Nicaragua</option>

<option value="NE">Niger</option>

<option value="NG">Nigeria</option>

<option value="NU">Niue</option>

<option value="NF">Norfolk Island</option>

<option value="GB_ALIAS3">Northern Ireland</option>

<option value="KP_ALIAS1">North Korea</option>

<option value="NO">Norway</option>

<option value="OM">Oman</option>

<option value="PK">Pakistan</option>

<option value="USPW">Palau</option>

<option value="PS">Palestine</option>

<option value="PA">Panama</option>

<option value="PG">Papua N Guinea</option>

<option value="PY">Paraguay</option>

<option value="PE">Peru</option>

<option value="PH">Philippines</option>

<option value="PN">Pitcairn</option>

<option value="PL">Poland</option>

<option value="PT">Portugal</option>

<option value="ST_ALIAS1">Principe</option>

<option value="USPR">Puerto Rico</option>

<option value="QA">Qatar</option>

<option value="RE">Reunion</option>

<option value="RO">Romania</option>

<option value="RU">Russia</option>

<option value="RW">Rwanda</option>

<option value="ST">S.Tome/Principe</option>

<option value="BQ_ALIAS1">Saba</option>

<option value="SH">Saint Helena</option>

<option value="LC">Saint Lucia</option>

<option value="MF">Saint Martin</option>

<option value="WS">Samoa</option>

<option value="SM">San Marino</option>

<option value="SA">Saudi Arabia</option>

<option value="GB_ALIAS4">Scotland</option>

<option value="SN">Senegal</option>

<option value="RS">Serbia</option>

<option value="SC">Seychelles</option>

<option value="IT_ALIAS1">Sicily</option>

<option value="SL">Sierra Leone</option>

<option value="SG">Singapore</option>

<option value="BQ_ALIAS2">Sint Eustatius</option>

<option value="SX">Sint Maarten</option>

<option value="SK">Slovakia</option>

<option value="SI">Slovenia</option>

<option value="SB">Solomon Islands</option>

<option value="SO">Somalia</option>

<option value="ZA">South Africa</option>

<option value="KR_ALIAS1">South Korea</option>

<option value="SS">South Sudan</option>

<option value="ES">Spain</option>

<option value="ES_ALIAS3">Spanish North African Terr</option>

<option value="LK">Sri Lanka</option>

<option value="BL">St Barthelemy</option>

<option value="VI_ALIAS1">St Croix</option>

<option value="KN">St Kitts/Nevis</option>

<option value="PM">St Pierre &amp; Miq</option>

<option value="VI_ALIAS2">St Thomas</option>

<option value="VC">St Vincent/Gren</option>

<option value="SD">Sudan</option>

<option value="SR">Suriname</option>

<option value="SJ">Svalbard</option>

<option value="SZ">Swaziland</option>

<option value="SE">Sweden</option>

<option value="CH">Switzerland</option>

<option value="SY">Syria</option>

<option value="TW">Taiwan</option>

<option value="TJ">Tajikstan</option>

<option value="TZ">Tanzania</option>

<option value="TH">Thailand</option>

<option value="CN_ALIAS1">Tibet</option>

<option value="TL">Timor-Leste</option>

<option value="TG">Togo</option>

<option value="TK">Tokelau</option>

<option value="TO">Tonga</option>

<option value="VG_ALIAS1">Tortola</option>

<option value="TT">Trinidad/Tobago</option>

<option value="TA">Tristan da Cunh</option>

<option value="TN">Tunisia</option>

<option value="TR">Turkey</option>

<option value="TM">Turkmenistan</option>

<option value="TC">Turks &amp; Caicos</option>

<option value="TV">Tuvalu</option>

<option value="UG">Uganda</option>

<option value="UA">Ukraine</option>

<option value="AE">United Arab Emr</option>

<option value="GB_ALIAS5">United Kingdom</option>
 <option value="USA">United States</option>

<option value="UY">Uruguay</option>

<option value="USUM">US Minor Islnds</option>

<option value="USVI">US Virgin Isl</option>

<option value="UZ">Uzbekistan</option>

<option value="VU">Vanuatu</option>

<option value="VA_ALIAS1">Vatican</option>

<option value="VE">Venezuela</option>

<option value="VN">Vietnam</option>

<option value="GB_ALIAS6">Wales</option>

<option value="WF">Wallis &amp; Futuna</option>

<option value="EH">Western Sahara</option>

<option value="WS_ALIAS1">Western Samoa</option>

<option value="YE">Yemen</option>

<option value="CD_ALIAS1">Zaire</option>

<option value="ZM">Zambia</option>

<option value="ZW">Zimbabwe</option>

</select>

(2) List of countries in vb.net

 ddlCountry.Items.Add("Afghanistan")

        ddlCountry.Items.Add("Aland islands")

        ddlCountry.Items.Add("Albania")

        ddlCountry.Items.Add("Algeria")

        ddlCountry.Items.Add("American Samoa")

        ddlCountry.Items.Add("Andorra")

        ddlCountry.Items.Add("Angola")

        ddlCountry.Items.Add("Anguilla")

        ddlCountry.Items.Add("Antarctica")

        ddlCountry.Items.Add("Antigua/Barbuda")

        ddlCountry.Items.Add("Argentina")

        ddlCountry.Items.Add("Armenia")

        ddlCountry.Items.Add("Aruba")

        ddlCountry.Items.Add("Ascension")

        ddlCountry.Items.Add("Australia")

        ddlCountry.Items.Add("Austria")

        ddlCountry.Items.Add("Azerbaijan")

        ddlCountry.Items.Add("Azores")

        ddlCountry.Items.Add("Bahamas")

        ddlCountry.Items.Add("Bahrain")

        ddlCountry.Items.Add("Balearic Islands")

        ddlCountry.Items.Add("Bangladesh")

        ddlCountry.Items.Add("Barbados")

        ddlCountry.Items.Add("Belarus")

        ddlCountry.Items.Add("Belgium")

        ddlCountry.Items.Add("Belize")

        ddlCountry.Items.Add("Benin")

        ddlCountry.Items.Add("Bermuda")

        ddlCountry.Items.Add("BES Islands")

        ddlCountry.Items.Add("Bhutan")

        ddlCountry.Items.Add("Bolivia")

        ddlCountry.Items.Add("Bonaire")

        ddlCountry.Items.Add("Bosnia-Herz")

        ddlCountry.Items.Add("Botswana")

        ddlCountry.Items.Add("Bouvet Island")

        ddlCountry.Items.Add("Br. Virgin Isl")

        ddlCountry.Items.Add("Brazil")

        ddlCountry.Items.Add("Br Ind Ocean Tr")

        ddlCountry.Items.Add("Brunei")

        ddlCountry.Items.Add("Bulgaria")

        ddlCountry.Items.Add("Burkina Faso")

        ddlCountry.Items.Add("Burma")

        ddlCountry.Items.Add("Burundi")

        ddlCountry.Items.Add("Cambodia")

        ddlCountry.Items.Add("Cameroon")

        ddlCountry.Items.Add("Canada")

        ddlCountry.Items.Add("Canary Islands")

        ddlCountry.Items.Add("Cape Verde")

        ddlCountry.Items.Add("Cayman Islands")

        ddlCountry.Items.Add("Central African Republic")

        ddlCountry.Items.Add("Chad")

        ddlCountry.Items.Add("Channel Islands")

        ddlCountry.Items.Add("Chile")

        ddlCountry.Items.Add("China")

        ddlCountry.Items.Add("Christmas Island")

        ddlCountry.Items.Add("Cocos Islands")

        ddlCountry.Items.Add("Colombia")

        ddlCountry.Items.Add("Comoros")

        ddlCountry.Items.Add("Congo")

        ddlCountry.Items.Add("Congo Democratic Republic")

        ddlCountry.Items.Add("Cook Islands")

        ddlCountry.Items.Add("Corsica")

        ddlCountry.Items.Add("Costa Rica")

        ddlCountry.Items.Add("Côte d'Ivoire")

        ddlCountry.Items.Add("Croatia")

        ddlCountry.Items.Add("Cuba")

        ddlCountry.Items.Add("Curaçao")

        ddlCountry.Items.Add("Cyprus")

        ddlCountry.Items.Add("Czech Republic")

        ddlCountry.Items.Add("Denmark")

        ddlCountry.Items.Add("Djibouti")

        ddlCountry.Items.Add("Dominica")

        ddlCountry.Items.Add("Dominican Republic")

        ddlCountry.Items.Add("Dutch Caribbean")

        ddlCountry.Items.Add("East Timor")

        ddlCountry.Items.Add("Ecuador")

        ddlCountry.Items.Add("Egypt")

        ddlCountry.Items.Add("El Salvador")

        ddlCountry.Items.Add("England")

        ddlCountry.Items.Add("Equatorial Guinea")

        ddlCountry.Items.Add("Eritrea")

        ddlCountry.Items.Add("Estonia")

        ddlCountry.Items.Add("Ethiopia")

        ddlCountry.Items.Add("Falkland Islands")

        ddlCountry.Items.Add("Faroe Islands")

        ddlCountry.Items.Add("Fiji")

        ddlCountry.Items.Add("Finland")

        ddlCountry.Items.Add("France")

        ddlCountry.Items.Add("French Guiana")

        ddlCountry.Items.Add("French Polynesia")

        ddlCountry.Items.Add("Fr S.Territory")

        ddlCountry.Items.Add("Gabon")

        ddlCountry.Items.Add("Gambia")

        ddlCountry.Items.Add("Gaza")

        ddlCountry.Items.Add("Georgia")

        ddlCountry.Items.Add("Georgia/Sandwich")

        ddlCountry.Items.Add("Germany")

        ddlCountry.Items.Add("Ghana")

        ddlCountry.Items.Add("Gibraltar")

        ddlCountry.Items.Add("Great Britain")

        ddlCountry.Items.Add("Greece")

        ddlCountry.Items.Add("Greenland")

        ddlCountry.Items.Add("Grenada")

        ddlCountry.Items.Add("Guadeloupe")

        ddlCountry.Items.Add("Guam")

        ddlCountry.Items.Add("Guatemala")

        ddlCountry.Items.Add("Guernsey")

        ddlCountry.Items.Add("Guinea")

        ddlCountry.Items.Add("Guinea-Bissau")

        ddlCountry.Items.Add("Guyana")

        ddlCountry.Items.Add("Haiti")

        ddlCountry.Items.Add("Heard/McDonald")

        ddlCountry.Items.Add("Holland")

        ddlCountry.Items.Add("Holy See")

        ddlCountry.Items.Add("Honduras")

        ddlCountry.Items.Add("Hong Kong")

        ddlCountry.Items.Add("Hungary")

        ddlCountry.Items.Add("Iceland")

        ddlCountry.Items.Add("India")

        ddlCountry.Items.Add("Indonesia")

        ddlCountry.Items.Add("Iran")

        ddlCountry.Items.Add("Iraq")

        ddlCountry.Items.Add("Ireland")

        ddlCountry.Items.Add("Isle of Man")

        ddlCountry.Items.Add("Israel")

        ddlCountry.Items.Add("Italy")

        ddlCountry.Items.Add("Jamaica")

        ddlCountry.Items.Add("Japan")

        ddlCountry.Items.Add("Jersey")

        ddlCountry.Items.Add("Jordan")

        ddlCountry.Items.Add("Kazakhstan")

        ddlCountry.Items.Add("Keeling Islands")

        ddlCountry.Items.Add("Kenya")

        ddlCountry.Items.Add("Kiribati")

        ddlCountry.Items.Add("Korea")

        ddlCountry.Items.Add("Korea Dem Rep")

        ddlCountry.Items.Add("Kosova")

        ddlCountry.Items.Add("Kosovo")

        ddlCountry.Items.Add("Kuwait")

        ddlCountry.Items.Add("Kyrgyzstan")

        ddlCountry.Items.Add("Laos")

        ddlCountry.Items.Add("Latvia")

        ddlCountry.Items.Add("Lebanon")

        ddlCountry.Items.Add("Lesotho")

        ddlCountry.Items.Add("Liberia")

        ddlCountry.Items.Add("Libya")

        ddlCountry.Items.Add("Liechtenstein")

        ddlCountry.Items.Add("Lithuania")

        ddlCountry.Items.Add("Luxembourg")

        ddlCountry.Items.Add("Macau")

        ddlCountry.Items.Add("Macedonia")

        ddlCountry.Items.Add("Madagascar")

        ddlCountry.Items.Add("Madiera")

        ddlCountry.Items.Add("Malawi")

        ddlCountry.Items.Add("Malaysia")

        ddlCountry.Items.Add("Maldives")

        ddlCountry.Items.Add("Mali")

        ddlCountry.Items.Add("Malta")

        ddlCountry.Items.Add("Malvinas")

        ddlCountry.Items.Add("Marshall Islnds")

        ddlCountry.Items.Add("Martinique")

        ddlCountry.Items.Add("Mauritania")

        ddlCountry.Items.Add("Mauritius")

        ddlCountry.Items.Add("Mayotte")

        ddlCountry.Items.Add("Mexico")

        ddlCountry.Items.Add("Micronesia")

        ddlCountry.Items.Add("Moldova")

        ddlCountry.Items.Add("Monaco")

        ddlCountry.Items.Add("Mongolia")

        ddlCountry.Items.Add("Montenegro")

        ddlCountry.Items.Add("Montserrat")

        ddlCountry.Items.Add("Morocco")

        ddlCountry.Items.Add("Mozambique")

        ddlCountry.Items.Add("Myanmar")

        ddlCountry.Items.Add("N.Mariana Island")

        ddlCountry.Items.Add("Namibia")

        ddlCountry.Items.Add("Nauru")

        ddlCountry.Items.Add("Nepal")

        ddlCountry.Items.Add("Netherlands")

        ddlCountry.Items.Add("New Caledonia")

        ddlCountry.Items.Add("New Zealand")

        ddlCountry.Items.Add("Nicaragua")

        ddlCountry.Items.Add("Niger")

        ddlCountry.Items.Add("Nigeria")

        ddlCountry.Items.Add("Niue")

        ddlCountry.Items.Add("Norfolk Island")

        ddlCountry.Items.Add("Northern Ireland")

        ddlCountry.Items.Add("North Korea")

        ddlCountry.Items.Add("Norway")

        ddlCountry.Items.Add("Oman")

        ddlCountry.Items.Add("Pakistan")

        ddlCountry.Items.Add("Palau")

        ddlCountry.Items.Add("Palestine")

        ddlCountry.Items.Add("Panama")

        ddlCountry.Items.Add("Papua N Guinea")

        ddlCountry.Items.Add("Paraguay")

        ddlCountry.Items.Add("Peru")

        ddlCountry.Items.Add("Philippines")

        ddlCountry.Items.Add("Pitcairn")

        ddlCountry.Items.Add("Poland")

        ddlCountry.Items.Add("Portugal")

        ddlCountry.Items.Add("Principe")

        ddlCountry.Items.Add("Puerto Rico")

        ddlCountry.Items.Add("Qatar")

        ddlCountry.Items.Add("Reunion")

        ddlCountry.Items.Add("Romania")

        ddlCountry.Items.Add("Russia")

        ddlCountry.Items.Add("Rwanda")

        ddlCountry.Items.Add("S.Tome/Principe")

        ddlCountry.Items.Add("Saba")

        ddlCountry.Items.Add("Saint Helena")

        ddlCountry.Items.Add("Saint Lucia")

        ddlCountry.Items.Add("Saint Martin")

        ddlCountry.Items.Add("Samoa")

        ddlCountry.Items.Add("San Marino")

        ddlCountry.Items.Add("Saudi Arabia")

        ddlCountry.Items.Add("Scotland")

        ddlCountry.Items.Add("Senegal")

        ddlCountry.Items.Add("Serbia")

        ddlCountry.Items.Add("Seychelles")

        ddlCountry.Items.Add("Sicily")

        ddlCountry.Items.Add("Sierra Leone")

        ddlCountry.Items.Add("Singapore")

        ddlCountry.Items.Add("Sint Eustatius")

        ddlCountry.Items.Add("Sint Maarten")

        ddlCountry.Items.Add("Slovakia")

        ddlCountry.Items.Add("Slovenia")

        ddlCountry.Items.Add("Solomon Islands")

        ddlCountry.Items.Add("Somalia")

        ddlCountry.Items.Add("South Africa")

        ddlCountry.Items.Add("South Korea")

        ddlCountry.Items.Add("South Sudan")

        ddlCountry.Items.Add("Spain")

        ddlCountry.Items.Add("Spanish North African Terr")

        ddlCountry.Items.Add("Sri Lanka")

        ddlCountry.Items.Add("St Barthelemy")

        ddlCountry.Items.Add("St Croix")

        ddlCountry.Items.Add("St Kitts/Nevis")

        ddlCountry.Items.Add("St Pierre &amp; Miq")

        ddlCountry.Items.Add("St Thomas")

        ddlCountry.Items.Add("St Vincent/Gren")

        ddlCountry.Items.Add("Sudan")

        ddlCountry.Items.Add("Suriname")

        ddlCountry.Items.Add("Svalbard")

        ddlCountry.Items.Add("Swaziland")

        ddlCountry.Items.Add("Sweden")

        ddlCountry.Items.Add("Switzerland")

        ddlCountry.Items.Add("Syria")

        ddlCountry.Items.Add("Taiwan")

        ddlCountry.Items.Add("Tajikstan")

        ddlCountry.Items.Add("Tanzania")

        ddlCountry.Items.Add("Thailand")

        ddlCountry.Items.Add("Tibet")

        ddlCountry.Items.Add("Timor-Leste")

        ddlCountry.Items.Add("Togo")

        ddlCountry.Items.Add("Tokelau")

        ddlCountry.Items.Add("Tonga")

        ddlCountry.Items.Add("Tortola")

        ddlCountry.Items.Add("Trinidad/Tobago")

        ddlCountry.Items.Add("Tristan da Cunh")

        ddlCountry.Items.Add("Tunisia")

        ddlCountry.Items.Add("Turkey")

        ddlCountry.Items.Add("Turkmenistan")

        ddlCountry.Items.Add("Turks &amp; Caicos")

        ddlCountry.Items.Add("Tuvalu")

        ddlCountry.Items.Add("Uganda")

        ddlCountry.Items.Add("Ukraine")

        ddlCountry.Items.Add("United Arab Emr")

        ddlCountry.Items.Add("United Kingdom")
        ddlCountry.Items.Add("United States")

        ddlCountry.Items.Add("Uruguay")

        ddlCountry.Items.Add("US Minor Islnds")

        ddlCountry.Items.Add("US Virgin Isl")

        ddlCountry.Items.Add("Uzbekistan")

        ddlCountry.Items.Add("Vanuatu")

        ddlCountry.Items.Add("Vatican")

        ddlCountry.Items.Add("Venezuela")

        ddlCountry.Items.Add("Vietnam")

        ddlCountry.Items.Add("Wales")

        ddlCountry.Items.Add("Wallis &amp; Futuna")

        ddlCountry.Items.Add("Western Sahara")

        ddlCountry.Items.Add("Western Samoa")

        ddlCountry.Items.Add("Yemen")

        ddlCountry.Items.Add("Zaire")

        ddlCountry.Items.Add("Zambia")

        ddlCountry.Items.Add("Zimbabwe")

(3) LIST OF COUNTRIES using ListItem

   Public Sub FillCountries()

        Dim listOfCountries As String() = {

            "Afghanistan",

        "Aland islands",

        "Albania",

        "Algeria",

        "American Samoa",

        "Andorra",

        "Angola",

        "Anguilla",

        "Antarctica",

        "Antigua/Barbuda",

        "Argentina",

        "Armenia",

        "Aruba",

        "Ascension",

        "Australia",

        "Austria",

        "Azerbaijan",

        "Azores",

        "Bahamas",

        "Bahrain",

        "Balearic Islands",

        "Bangladesh",

        "Barbados",

        "Belarus",

        "Belgium",

        "Belize",

        "Benin",

        "Bermuda",

        "BES Islands",

        "Bhutan",

        "Bolivia",

        "Bonaire",

        "Bosnia-Herz",

        "Botswana",

        "Bouvet Island",

        "Br. Virgin Isl",

        "Brazil",

        "Br Ind Ocean Tr",

        "Brunei",

        "Bulgaria",

        "Burkina Faso",

        "Burma",

        "Burundi",

        "Cambodia",

        "Cameroon",

        "Canada",

        "Canary Islands",

        "Cape Verde",

        "Cayman Islands",

        "Central African Republic",

        "Chad",

        "Channel Islands",

        "Chile",

        "China",

        "Christmas Island",

        "Cocos Islands",

        "Colombia",

        "Comoros",

        "Congo",

        "Congo Democratic Republic",

        "Cook Islands",

        "Corsica",

        "Costa Rica",

        "Côte d'Ivoire",

        "Croatia",

        "Cuba",

        "Curaçao",

        "Cyprus",

        "Czech Republic",

        "Denmark",

        "Djibouti",

        "Dominica",

        "Dominican Republic",

        "Dutch Caribbean",

        "East Timor",

        "Ecuador",

        "Egypt",

        "El Salvador",

        "England",

        "Equatorial Guinea",

        "Eritrea",

        "Estonia",

        "Ethiopia",

        "Falkland Islands",

        "Faroe Islands",

        "Fiji",

        "Finland",

        "France",

        "French Guiana",

        "French Polynesia",

        "Fr S.Territory",

        "Gabon",

        "Gambia",

        "Gaza",

        "Georgia",

        "Georgia/Sandwich",

        "Germany",

        "Ghana",

        "Gibraltar",

        "Great Britain",

        "Greece",

        "Greenland",

        "Grenada",

        "Guadeloupe",

        "Guam",

        "Guatemala",

        "Guernsey",

        "Guinea",

        "Guinea-Bissau",

        "Guyana",

        "Haiti",

        "Heard/McDonald",

        "Holland",

        "Holy See",

        "Honduras",

        "Hong Kong",

        "Hungary",

        "Iceland",

        "India",

        "Indonesia",

        "Iran",

        "Iraq",

        "Ireland",

        "Isle of Man",

        "Israel",

        "Italy",

        "Jamaica",

        "Japan",

        "Jersey",

        "Jordan",

        "Kazakhstan",

        "Keeling Islands",

        "Kenya",

        "Kiribati",

        "Korea",

        "Korea Dem Rep",

        "Kosova",

        "Kosovo",

        "Kuwait",

        "Kyrgyzstan",

        "Laos",

        "Latvia",

        "Lebanon",

        "Lesotho",

        "Liberia",

        "Libya",

        "Liechtenstein",

        "Lithuania",

        "Luxembourg",

        "Macau",

        "Macedonia",

        "Madagascar",

        "Madiera",

        "Malawi",

        "Malaysia",

        "Maldives",

        "Mali",

        "Malta",

        "Malvinas",

        "Marshall Islnds",

        "Martinique",

        "Mauritania",

        "Mauritius",

        "Mayotte",

        "Mexico",

        "Micronesia",

        "Moldova",

        "Monaco",

        "Mongolia",

        "Montenegro",

        "Montserrat",

        "Morocco",

        "Mozambique",

        "Myanmar",

        "N.Mariana Island",

        "Namibia",

        "Nauru",

        "Nepal",

        "Netherlands",

        "New Caledonia",

        "New Zealand",

        "Nicaragua",

        "Niger",

        "Nigeria",

        "Niue",

        "Norfolk Island",

        "Northern Ireland",

        "North Korea",

        "Norway",

        "Oman",

        "Pakistan",

        "Palau",

        "Palestine",

        "Panama",

        "Papua N Guinea",

        "Paraguay",

        "Peru",

        "Philippines",

        "Pitcairn",

        "Poland",

        "Portugal",

        "Principe",

        "Puerto Rico",

        "Qatar",

        "Reunion",

        "Romania",

        "Russia",

        "Rwanda",

        "S.Tome/Principe",

        "Saba",

        "Saint Helena",

        "Saint Lucia",

        "Saint Martin",

        "Samoa",

        "San Marino",

        "Saudi Arabia",

        "Scotland",

        "Senegal",

        "Serbia",

        "Seychelles",

        "Sicily",

        "Sierra Leone",

        "Singapore",

        "Sint Eustatius",

        "Sint Maarten",

        "Slovakia",

        "Slovenia",

        "Solomon Islands",

        "Somalia",

        "South Africa",

        "South Korea",

        "South Sudan",

        "Spain",

        "Spanish North African Terr",

        "Sri Lanka",

        "St Barthelemy",

        "St Croix",

        "St Kitts/Nevis",

        "St Pierre &amp; Miq",

        "St Thomas",

        "St Vincent/Gren",

        "Sudan",

        "Suriname",

        "Svalbard",

        "Swaziland",

        "Sweden",

        "Switzerland",

        "Syria",

        "Taiwan",

        "Tajikstan",

        "Tanzania",

        "Thailand",

        "Tibet",

        "Timor-Leste",

        "Togo",

        "Tokelau",

        "Tonga",

        "Tortola",

        "Trinidad/Tobago",

        "Tristan da Cunh",

        "Tunisia",

        "Turkey",

        "Turkmenistan",

        "Turks &amp; Caicos",

        "Tuvalu",

        "Uganda",

        "Ukraine",

        "United Arab Emr",

        "United Kingdom",
        "United States",

        "Uruguay",

        "US Minor Islnds",

        "US Virgin Isl",

        "Uzbekistan",

        "Vanuatu",

        "Vatican",

        "Venezuela",

        "Vietnam",

        "Wales",

        "Wallis &amp; Futuna",

        "Western Sahara",

        "Western Samoa",

        "Yemen",

        "Zaire",

        "Zambia",

        "Zimbabwe"}

        For Each s As String In listOfCountries

            Dim li As New ListItem

            li.Value = s

            li.Text = s

            If s.Equals("Canada") Then

                li.Selected = True

            End If

            ddlCountry.Items.Add(li)

            ddlEmployerCountry.Items.Add(li)

        Next

    End Sub