reference lookup table .... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

reference lookup table ....

Author

Topic

macupryk
Starting Member

9 Posts

Posted - 2004-01-18 : 13:20:18

Is it possible in asp.net to create a web form to display say for instance the lookup tables as dropdownlist by just references through the creation of the table? That is Say I want to display in a form the other fields in a textbox (UserName, Password, HeadLine, FirstName, LastName, SecretWord, City) and the others lookup tables as dropdownlist is this possible? If so how could I go about do this.

create table SignupUsers

(

UserID int identity primary key ,

UserName varchar(50) not null,

PasswordHash varchar(50) not null,

Salt varchar(10) not null,

HeadLine varchar(50) null,

TitleID varchar(10) not null references lookup_User_Title (TitleID),

FirstName varchar(50) not null,

LastName varchar(50) not null,

Email varchar(75) not null,

SecretWordTypeID varchar(50) not null references lookup_User_SecretWordType (SecretWordTypeID),

SecretWord varchar(50) not null,

Age integer not null ,

DOB datetime not null ,

Zodiac varchar(50) not null,

GenderID integer null references Lookup_User_Gender (GenderID),

City varchar(50),

StateProvinceID integer null references Lookup_User_StateProvince (StateProvinceID),

CountryID integer not null references Lookup_User_Country (CountryID),

DateCreated datetime not null constraint [DF_tbl_SignupUsers_DateCreated] default (getdate()),

LastLogin datetime not null constraint [DF_tbl_SignupUsers_LastLogin] default (getdate()) ,

LogCount int not null constraint [DF_tbl_SignupUsers_LogCount] default (1)

UNIQUE (UserName)

)

GO

-- signup.aspx Create Tables for dropdownlist

----------------------------------------------- 1

CREATE TABLE Lookup_User_Title (

[TitleID] varchar(10) primary key,

[TitleText] varchar (10) NOT NULL

)

GO

----------------------------------------------- 2

CREATE TABLE Lookup_User_Gender (

[GenderID] [int] IDENTITY (1, 1) NOT NULL ,

[GenderText] varchar (50) NOT NULL

)

GO

----------------------------------------------- 3

CREATE TABLE Lookup_User_SecretWordType (

[SecretWordTypeID] varchar(50) primary key,

[SecretWordTypeText] varchar (50) NOT NULL

)

GO

----------------------------------------------- 4

CREATE TABLE Lookup_User_StateProvince (

[StateProvinceID] [int] IDENTITY (1, 1) NOT NULL ,

[StateProvinceText] varchar (50) NOT NULL

)

GO

----------------------------------------------- 5

CREATE TABLE Lookup_User_Country (

[CountryID] [int] IDENTITY (1, 1) NOT NULL ,

[CountryText] varchar (50) NOT NULL

)

GO

Given the insert tables below:

-- S I G N U P === P A G E 1

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

-- signup.aspx First Page Lookup Tables which are dropdownlist

----------------------------------------------- 1

SET IDENTITY_INSERT Lookup_User_Title ON

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('Mr.','Mr.')

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('Ms.','Ms.')

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('Miss.','Miss.')

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('Mrs.','Mrs.')

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('Dr.','Dr.')

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('M.','M.')

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('Mme.','Mme.')

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('Mlle.','Mlle.')

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('Prof.','Prof.')

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('Rev.','Rev.')

GO

INSERT INTO Lookup_User_Title(TitleID,TitleText) VALUES('Other','Other')

GO

SET IDENTITY_INSERT Lookup_User_Title OFF

GO

----------------------------------------------- 2

SET IDENTITY_INSERT Lookup_User_Gender ON

GO

INSERT Lookup_User_Gender(GenderText) VALUES('Male')

INSERT Lookup_User_Gender(GenderText) VALUES('Female')

GO

SET IDENTITY_INSERT Lookup_User_Gender OFF

GO

----------------------------------------------- 3

SET IDENTITY_INSERT Lookup_User_SecretWordType ON

GO

INSERT INTO Lookup_User_SecretWordType(SecretWordTypeID,SecretWordTypeText) VALUES('What street did I grow up on?','What street did I grow up on?')

GO

INSERT INTO Lookup_User_SecretWordType(SecretWordTypeID,SecretWordTypeText) VALUES('What is the name of the high school I attended?','What is the name of the high school I attended?')

GO

INSERT INTO Lookup_User_SecretWordType(SecretWordTypeID,SecretWordTypeText) VALUES('What is my favorite type of music?','What is my favorite type of music?')

GO

INSERT INTO Lookup_User_SecretWordType(SecretWordTypeID,SecretWordTypeText) VALUES('What is my pet''s name?','What is my pet''s name?')

GO

SET IDENTITY_INSERT Lookup_User_SecretWordType OFF

GO

----------------------------------------------- 4

SET IDENTITY_INSERT Lookup_User_StateProvince ON

GO

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Not Applicable')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Alabama')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Alaska')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Army Post Office')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Arizona')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Arkansas')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('California')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('District of Columbia')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Delaware')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Florida')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Fleet Post Office')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Georgia')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Guam')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Hawaii')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Idaho')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Illinois')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Indiana')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Iowa')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Kansas')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Kentucky')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Louisiana')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Maine')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Maryland')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Massachusetts')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Michigan')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Minnesota')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Mississippi')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Missouri')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Montana')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Nebraska')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Nevada')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('New Hampshire')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('New Jersey')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('New Mexico')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('New York')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('North Carolina')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('North Dakota')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Ohio')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Oklahoma')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Oregon')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Pennsylvania')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Puerto Rico')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Rhode Island')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('South Carolina')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('South Dakota')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Tennessee')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Texas')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Utah')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Vermont')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('West Virginia')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Wisconsin')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Wyoming')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('British Columbia')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Manitoba')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('New Brunswick')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Newfoundland')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Northwest Territories')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Nova Scotia')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Nunavut')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Ontario')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Prince Edward Island')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Quebec')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Saskatchewan')

INSERT Lookup_User_StateProvince(StateProvinceText) VALUES('Yukon')

GO

SET IDENTITY_INSERT Lookup_User_StateProvince OFF

GO

----------------------------------------------- 5

SET IDENTITY_INSERT Lookup_User_Country ON

GO

INSERT Lookup_User_Country(CountryText) VALUES('Not Applicable')

INSERT Lookup_User_Country(CountryText) VALUES('Canada')

INSERT Lookup_User_Country(CountryText) VALUES('Afghanistan')

INSERT Lookup_User_Country(CountryText) VALUES('Albania')

INSERT Lookup_User_Country(CountryText) VALUES('Algeria')

INSERT Lookup_User_Country(CountryText) VALUES('American Samoa')

INSERT Lookup_User_Country(CountryText) VALUES('Andorra')

INSERT Lookup_User_Country(CountryText) VALUES('Angola')

INSERT Lookup_User_Country(CountryText) VALUES('Angola')

INSERT Lookup_User_Country(CountryText) VALUES('Anguilla')

INSERT Lookup_User_Country(CountryText) VALUES('Antarctica')

INSERT Lookup_User_Country(CountryText) VALUES('Antigua and Barbuda')

INSERT Lookup_User_Country(CountryText) VALUES('Argentina')

INSERT Lookup_User_Country(CountryText) VALUES('Armenia')

INSERT Lookup_User_Country(CountryText) VALUES('Aruba')

INSERT Lookup_User_Country(CountryText) VALUES('Australia')

INSERT Lookup_User_Country(CountryText) VALUES('Austria')

INSERT Lookup_User_Country(CountryText) VALUES('Azerbaijan')

INSERT Lookup_User_Country(CountryText) VALUES('Bahrain')

INSERT Lookup_User_Country(CountryText) VALUES('Bangladesh')

INSERT Lookup_User_Country(CountryText) VALUES('Barbados')

INSERT Lookup_User_Country(CountryText) VALUES('Belarus')

INSERT Lookup_User_Country(CountryText) VALUES('Belgium')

INSERT Lookup_User_Country(CountryText) VALUES('Belize')

INSERT Lookup_User_Country(CountryText) VALUES('Benin')

INSERT Lookup_User_Country(CountryText) VALUES('Bermuda')

INSERT Lookup_User_Country(CountryText) VALUES('Bhutan')

INSERT Lookup_User_Country(CountryText) VALUES('Bolivia')

INSERT Lookup_User_Country(CountryText) VALUES('Bosnia and Herzegovina')

INSERT Lookup_User_Country(CountryText) VALUES('Botswana')

INSERT Lookup_User_Country(CountryText) VALUES('Bouvet Island')

INSERT Lookup_User_Country(CountryText) VALUES('Brazil')

INSERT Lookup_User_Country(CountryText) VALUES('British Indian Ocean Territory')

INSERT Lookup_User_Country(CountryText) VALUES('Brunei Darussalam')

INSERT Lookup_User_Country(CountryText) VALUES('Burkina Faso')

INSERT Lookup_User_Country(CountryText) VALUES('Burundi')

INSERT Lookup_User_Country(CountryText) VALUES('Cambodia')

INSERT Lookup_User_Country(CountryText) VALUES('Cameroon')

INSERT Lookup_User_Country(CountryText) VALUES('Cape Verde')

INSERT Lookup_User_Country(CountryText) VALUES('Cayman Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Central African Republic')

INSERT Lookup_User_Country(CountryText) VALUES('Chad')

INSERT Lookup_User_Country(CountryText) VALUES('China')

INSERT Lookup_User_Country(CountryText) VALUES('Christmas Island')

INSERT Lookup_User_Country(CountryText) VALUES('Cocos (Keeling Islands)')

INSERT Lookup_User_Country(CountryText) VALUES('Colombia')

INSERT Lookup_User_Country(CountryText) VALUES('Comoros')

INSERT Lookup_User_Country(CountryText) VALUES('Congo')

INSERT Lookup_User_Country(CountryText) VALUES('Cook Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Costa Rica')

INSERT Lookup_User_Country(CountryText) VALUES('Cote D''Ivoire (Ivory Coast)')

INSERT Lookup_User_Country(CountryText) VALUES('Croatia (Hrvatska)')

INSERT Lookup_User_Country(CountryText) VALUES('Cuba')

INSERT Lookup_User_Country(CountryText) VALUES('Cyprus')

INSERT Lookup_User_Country(CountryText) VALUES('Czech Republic')

INSERT Lookup_User_Country(CountryText) VALUES('Denmark')

INSERT Lookup_User_Country(CountryText) VALUES('Djibouti')

INSERT Lookup_User_Country(CountryText) VALUES('Dominican Republic')

INSERT Lookup_User_Country(CountryText) VALUES('Dominica')

INSERT Lookup_User_Country(CountryText) VALUES('East Timor')

INSERT Lookup_User_Country(CountryText) VALUES('Ecuador')

INSERT Lookup_User_Country(CountryText) VALUES('Egypt')

INSERT Lookup_User_Country(CountryText) VALUES('El Salvador')

INSERT Lookup_User_Country(CountryText) VALUES('Equatorial Guinea')

INSERT Lookup_User_Country(CountryText) VALUES('Eritrea')

INSERT Lookup_User_Country(CountryText) VALUES('Estonia')

INSERT Lookup_User_Country(CountryText) VALUES('Ethiopia')

INSERT Lookup_User_Country(CountryText) VALUES('Falkland Islands (Malvinas)')

INSERT Lookup_User_Country(CountryText) VALUES('Faroe Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Fiji')

INSERT Lookup_User_Country(CountryText) VALUES('Finland')

INSERT Lookup_User_Country(CountryText) VALUES('France, Metropolitan')

INSERT Lookup_User_Country(CountryText) VALUES('France')

INSERT Lookup_User_Country(CountryText) VALUES('French Guiana')

INSERT Lookup_User_Country(CountryText) VALUES('French Polynesia')

INSERT Lookup_User_Country(CountryText) VALUES('French Southern Territories')

INSERT Lookup_User_Country(CountryText) VALUES('Gabon')

INSERT Lookup_User_Country(CountryText) VALUES('Gambia')

INSERT Lookup_User_Country(CountryText) VALUES('Georgia')

INSERT Lookup_User_Country(CountryText) VALUES('Germany')

INSERT Lookup_User_Country(CountryText) VALUES('Ghana')

INSERT Lookup_User_Country(CountryText) VALUES('Gibraltar')

INSERT Lookup_User_Country(CountryText) VALUES('Greece')

INSERT Lookup_User_Country(CountryText) VALUES('Greenland')

INSERT Lookup_User_Country(CountryText) VALUES('Grenada')

INSERT Lookup_User_Country(CountryText) VALUES('Guadeloupe')

INSERT Lookup_User_Country(CountryText) VALUES('Guam')

INSERT Lookup_User_Country(CountryText) VALUES('Guatemala')

INSERT Lookup_User_Country(CountryText) VALUES('Guinea-Bissau')

INSERT Lookup_User_Country(CountryText) VALUES('Guinea')

INSERT Lookup_User_Country(CountryText) VALUES('Guyana')

INSERT Lookup_User_Country(CountryText) VALUES('Haiti')

INSERT Lookup_User_Country(CountryText) VALUES('Heard and McDonald Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Honduras')

INSERT Lookup_User_Country(CountryText) VALUES('Hong Kong')

INSERT Lookup_User_Country(CountryText) VALUES('Hungary')

INSERT Lookup_User_Country(CountryText) VALUES('Iceland')

INSERT Lookup_User_Country(CountryText) VALUES('India')

INSERT Lookup_User_Country(CountryText) VALUES('Indonesia')

INSERT Lookup_User_Country(CountryText) VALUES('Iran')

INSERT Lookup_User_Country(CountryText) VALUES('Iraq')

INSERT Lookup_User_Country(CountryText) VALUES('Ireland')

INSERT Lookup_User_Country(CountryText) VALUES('Israel')

INSERT Lookup_User_Country(CountryText) VALUES('Italy')

INSERT Lookup_User_Country(CountryText) VALUES('Jamaica')

INSERT Lookup_User_Country(CountryText) VALUES('Japan')

INSERT Lookup_User_Country(CountryText) VALUES('Jordan')

INSERT Lookup_User_Country(CountryText) VALUES('Kazakhstan')

INSERT Lookup_User_Country(CountryText) VALUES('Kenya')

INSERT Lookup_User_Country(CountryText) VALUES('Kiribati')

INSERT Lookup_User_Country(CountryText) VALUES('Korea (North)')

INSERT Lookup_User_Country(CountryText) VALUES('Korea (South)')

INSERT Lookup_User_Country(CountryText) VALUES('Kuwait')

INSERT Lookup_User_Country(CountryText) VALUES('Kyrgyzstan')

INSERT Lookup_User_Country(CountryText) VALUES('Laos')

INSERT Lookup_User_Country(CountryText) VALUES('Latvia')

INSERT Lookup_User_Country(CountryText) VALUES('Lebanon')

INSERT Lookup_User_Country(CountryText) VALUES('Lesotho')

INSERT Lookup_User_Country(CountryText) VALUES('Liberia')

INSERT Lookup_User_Country(CountryText) VALUES('Libya')

INSERT Lookup_User_Country(CountryText) VALUES('Liechtenstein')

INSERT Lookup_User_Country(CountryText) VALUES('Lithuania')

INSERT Lookup_User_Country(CountryText) VALUES('Luxembourg')

INSERT Lookup_User_Country(CountryText) VALUES('Macau')

INSERT Lookup_User_Country(CountryText) VALUES('Macedonia')

INSERT Lookup_User_Country(CountryText) VALUES('Madagascar')

INSERT Lookup_User_Country(CountryText) VALUES('Malawi')

INSERT Lookup_User_Country(CountryText) VALUES('Malaysia')

INSERT Lookup_User_Country(CountryText) VALUES('Maldives')

INSERT Lookup_User_Country(CountryText) VALUES('Mali')

INSERT Lookup_User_Country(CountryText) VALUES('Malta')

INSERT Lookup_User_Country(CountryText) VALUES('Marshall Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Martinique')

INSERT Lookup_User_Country(CountryText) VALUES('Mauritania')

INSERT Lookup_User_Country(CountryText) VALUES('Mauritius')

INSERT Lookup_User_Country(CountryText) VALUES('Mayotte')

INSERT Lookup_User_Country(CountryText) VALUES('Mexico')

INSERT Lookup_User_Country(CountryText) VALUES('Micronesia')

INSERT Lookup_User_Country(CountryText) VALUES('Moldova')

INSERT Lookup_User_Country(CountryText) VALUES('Monaco')

INSERT Lookup_User_Country(CountryText) VALUES('Mongolia')

INSERT Lookup_User_Country(CountryText) VALUES('Montserrat')

INSERT Lookup_User_Country(CountryText) VALUES('Morocco')

INSERT Lookup_User_Country(CountryText) VALUES('Mozambique')

INSERT Lookup_User_Country(CountryText) VALUES('Myanmar')

INSERT Lookup_User_Country(CountryText) VALUES('Namibia')

INSERT Lookup_User_Country(CountryText) VALUES('Nauru')

INSERT Lookup_User_Country(CountryText) VALUES('Nepal')

INSERT Lookup_User_Country(CountryText) VALUES('Netherlands Antilles')

INSERT Lookup_User_Country(CountryText) VALUES('Netherlands')

INSERT Lookup_User_Country(CountryText) VALUES('New Caledonia')

INSERT Lookup_User_Country(CountryText) VALUES('New Zealand')

INSERT Lookup_User_Country(CountryText) VALUES('Nicaragua')

INSERT Lookup_User_Country(CountryText) VALUES('Nigeria')

INSERT Lookup_User_Country(CountryText) VALUES('Niger')

INSERT Lookup_User_Country(CountryText) VALUES('Niue')

INSERT Lookup_User_Country(CountryText) VALUES('Norfolk Island')

INSERT Lookup_User_Country(CountryText) VALUES('Northern Mariana Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Norway')

INSERT Lookup_User_Country(CountryText) VALUES('Oman')

INSERT Lookup_User_Country(CountryText) VALUES('Pakistan')

INSERT Lookup_User_Country(CountryText) VALUES('Palau')

INSERT Lookup_User_Country(CountryText) VALUES('Panama')

INSERT Lookup_User_Country(CountryText) VALUES('Papua New Guinea')

INSERT Lookup_User_Country(CountryText) VALUES('Paraguay')

INSERT Lookup_User_Country(CountryText) VALUES('Peru')

INSERT Lookup_User_Country(CountryText) VALUES('Philippines')

INSERT Lookup_User_Country(CountryText) VALUES('Pitcairn')

INSERT Lookup_User_Country(CountryText) VALUES('Poland')

INSERT Lookup_User_Country(CountryText) VALUES('Portugal')

INSERT Lookup_User_Country(CountryText) VALUES('Puerto Rico')

INSERT Lookup_User_Country(CountryText) VALUES('Qatar')

INSERT Lookup_User_Country(CountryText) VALUES('Reunion')

INSERT Lookup_User_Country(CountryText) VALUES('Russian Federation')

INSERT Lookup_User_Country(CountryText) VALUES('Rwanda')

INSERT Lookup_User_Country(CountryText) VALUES('S. Georgia and S. Sandwich Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Saint Kitts and Nevis')

INSERT Lookup_User_Country(CountryText) VALUES('Saint Lucia')

INSERT Lookup_User_Country(CountryText) VALUES('Saint Vincent and The Grenadines')

INSERT Lookup_User_Country(CountryText) VALUES('Samoa')

INSERT Lookup_User_Country(CountryText) VALUES('San Marino')

INSERT Lookup_User_Country(CountryText) VALUES('Sao Tome and Principe')

INSERT Lookup_User_Country(CountryText) VALUES('Saudi Arabia')

INSERT Lookup_User_Country(CountryText) VALUES('Senegal')

INSERT Lookup_User_Country(CountryText) VALUES('Seychelles')

INSERT Lookup_User_Country(CountryText) VALUES('Sierra Leone')

INSERT Lookup_User_Country(CountryText) VALUES('Singapore')

INSERT Lookup_User_Country(CountryText) VALUES('Slovak Republic')

INSERT Lookup_User_Country(CountryText) VALUES('Slovenia')

INSERT Lookup_User_Country(CountryText) VALUES('Solomon Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Somalia')

INSERT Lookup_User_Country(CountryText) VALUES('South Africa')

INSERT Lookup_User_Country(CountryText) VALUES('Spain')

INSERT Lookup_User_Country(CountryText) VALUES('Sri Lanka')

INSERT Lookup_User_Country(CountryText) VALUES('St. Helena')

INSERT Lookup_User_Country(CountryText) VALUES('St. Pierre and Miquelon')

INSERT Lookup_User_Country(CountryText) VALUES('Sudan')

INSERT Lookup_User_Country(CountryText) VALUES('Suriname')

INSERT Lookup_User_Country(CountryText) VALUES('Svalbard and Jan Mayen Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Swaziland')

INSERT Lookup_User_Country(CountryText) VALUES('Sweden')

INSERT Lookup_User_Country(CountryText) VALUES('Switzerland')

INSERT Lookup_User_Country(CountryText) VALUES('Syria')

INSERT Lookup_User_Country(CountryText) VALUES('Taiwan')

INSERT Lookup_User_Country(CountryText) VALUES('Tajikistan')

INSERT Lookup_User_Country(CountryText) VALUES('Tanzania')

INSERT Lookup_User_Country(CountryText) VALUES('Thailand')

INSERT Lookup_User_Country(CountryText) VALUES('Togo')

INSERT Lookup_User_Country(CountryText) VALUES('Tokelau')

INSERT Lookup_User_Country(CountryText) VALUES('Tonga')

INSERT Lookup_User_Country(CountryText) VALUES('Trinidad and Tobago')

INSERT Lookup_User_Country(CountryText) VALUES('Tunisia')

INSERT Lookup_User_Country(CountryText) VALUES('Turkey')

INSERT Lookup_User_Country(CountryText) VALUES('Turkmenistan')

INSERT Lookup_User_Country(CountryText) VALUES('Turks and Caicos Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Tuvalu')

INSERT Lookup_User_Country(CountryText) VALUES('US Minor Outlying Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Uganda')

INSERT Lookup_User_Country(CountryText) VALUES('Ukraine')

INSERT Lookup_User_Country(CountryText) VALUES('United Arab Emirates')

INSERT Lookup_User_Country(CountryText) VALUES('United Kingdom')

INSERT Lookup_User_Country(CountryText) VALUES('United States of America')

INSERT Lookup_User_Country(CountryText) VALUES('Uruguay')

INSERT Lookup_User_Country(CountryText) VALUES('Uzbekistan')

INSERT Lookup_User_Country(CountryText) VALUES('Vanuatu')

INSERT Lookup_User_Country(CountryText) VALUES('Vatican City State')

INSERT Lookup_User_Country(CountryText) VALUES('Venezuela')

INSERT Lookup_User_Country(CountryText) VALUES('Viet Nam')

INSERT Lookup_User_Country(CountryText) VALUES('Virgin Islands (British)')

INSERT Lookup_User_Country(CountryText) VALUES('Virgin Islands (US)')

INSERT Lookup_User_Country(CountryText) VALUES('Wallis and Futuna Islands')

INSERT Lookup_User_Country(CountryText) VALUES('Western Sahara')

INSERT Lookup_User_Country(CountryText) VALUES('Yemen')

INSERT Lookup_User_Country(CountryText) VALUES('Yugoslavia')

INSERT Lookup_User_Country(CountryText) VALUES('Zaire')

INSERT Lookup_User_Country(CountryText) VALUES('Zambia')

INSERT Lookup_User_Country(CountryText) VALUES('Zimbabwe')

GO

SET IDENTITY_INSERT Lookup_User_Country OFF

GO

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-18 : 17:07:50

I'm not sure whether you're saying you just want to be able to generate a drop-downlist from table values - or are you saying you want a drop down list with multiple columns?

Could you re-phrase your request?

here's how to build the options for a drop-down list

	rst.Open SQLTEXT, cnDB
	
	strSelectList = ""
	
	do while not rst.EOF
		strSelectList = strSelectList & "<option value=""" & Encode(rst.Fields("Value")) & """>" & Encode(rst.Fields("Display")) & Chr(13)	
		rst.MoveNext
	loop

and then in your html

			<select name="list">
			<% = strSelectList %>
			</select>

hope I'm not teaching you to suck eggs, let me know if you wanted to know something else.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-18 : 18:09:08

There's no way within the table creation syntax in SQL Server to indicate this is a lookup field and have it auto-generate a select list. There is a feature like that in Access, but that's because Access is a combination of database and presentation. Of course, you should define Foreign Key relationships to your lookup tables.

What I did back when I was doing that ancient for of web development known as ASP (before .NET) I created a class that would build select boxes for me. All I had to do was pass it the SQL SELECT statement and set a few options such as whether it should be a single or multiple-select, any first-value display such as "Select a car..." and I could also pass it the default selected value which was typically the value already in the record if we are editing or re-displaying an existing or newly-created record. Pretty damn handy once the class was created, and fairly easy to build. (Sorry, I'm not willing to share the source code for this, but you should be able to build one for yourself without too much trouble.)

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources