Set A SQL Server Field to A DropDown?

Question

How can I set a SQL Server field to be a dropdown?  For example, how can I set a field called DayOfWeek to become a dropdown and only give the options Monday, Tuesday, Wednesday, Thursday, Friday

Also, if this will require me to drop the table, and re-create, is there a way for me to set my ID field to start counting again at 1 instead of picking up at the next highest number from the previous table?

Answer 1

What does SQL server filed means?

Are you trying to map the values returned from DB to dropdown control?

---

Thanks
Sarat

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

hmm, where do you like to do this? And what exactly do you want to do? There is no such thing like a "dropdown" in this context.

CREATE TABLE DayOfWeekTest1
(
  ID INT IDENTITY(1,1) NOT NULL 
    PRIMARY KEY,
  Payload NVARCHAR(255) NOT NULL,
  [DayOfWeek] NVARCHAR(255) NOT NULL
    CHECK ([DayOfWeek] IN ('Monday' , 'Tuesday', 'Wendsday', 'Thursday', 'Friday', 'Saturday', 'Sunday'))
);

CREATE TABLE DayOfWeekTest2
(
  ID INT IDENTITY(1,1) NOT NULL 
    PRIMARY KEY,
  Payload NVARCHAR(255) NOT NULL,
  [DayOfWeek] INT NOT NULL
    CHECK ([DayOfWeek] BETWEEN 1 AND 7)
);

See also SET DATEFIRST. As week days are really invariant, you don't need an look-up table (DKNF).

Answer 3

You can store this as comma separated values. Say for example, if you want to store the daysofweek in a table. you can store it as

daysofweek

monday,tuesday,wednesday

monday,tuesday

monday,friday

Below is sample code..

create table testtable
(
id int identity(1,1),
daysofweek varchar(100)
)

insert into testtable select 'monday,tuesday,wednesday'
insert into testtable select 'monday,tuesday'
insert into testtable select 'monday,friday'

select * from testtable

drop table testtable 

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

Answer 4

What do you mean by "dropdown?"  That is a user interface term and how you do that depends on whether you are editing the records using an Asp.Net application, Access, Java, Windows application, or Reporting Services.  In SQL Server Management Studio you can't use a dropdown in the way you describe.

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 5

What do you mean by "dropdown?"  That is a user interface term and how you do that depends on whether you are editing the records using an Asp.Net application, Access, Java, Windows application, or Reporting Services.  In SQL Server Management Studio you can't use a dropdown in the way you describe.

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

My apalogies.  As most of you can tell I am a Access Migrant to learning SQL Server.  I have the SQL table linked into Access as most users @ my company still use access.  The issue I am running into is that the users are mis-spelling the day of the week which in turn is causing data to not be displayed.  So I was thinking if I could hard code acceptable values, this would eradicate the issue.

This SQL table is having data pulled from it through both access as well as a windows application (C#)

Answer 6

What does SQL server filed means?

Are you trying to map the values returned from DB to dropdown control?

---

Thanks
Sarat

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

It is a SQL Table linked into access.  I was wanting (if possible) to give a SQL Field a list of acceptable answers and if what is input by the user does not match one of the 'acceptable' answer the entry will not be accepted.  Now I may be going about this the completely wrong way!

Answer 7

Using Stefan's solution, you would need to build the dropdown that is appropriate to the C# application.  I could figure out how to do it, but I would use the same means available to you.  Or if I were lazy, I would post this in the appropriate .Net newsgroup.

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 8

hmm, where do you like to do this? And what exactly do you want to do? There is no such thing like a "dropdown" in this context.

CREATE TABLE DayOfWeekTest1
(
  ID INT IDENTITY(1,1) NOT NULL 
    PRIMARY KEY,
  Payload NVARCHAR(255) NOT NULL,
  [DayOfWeek] NVARCHAR(255) NOT NULL
    CHECK ([DayOfWeek] IN ('Monday' , 'Tuesday', 'Wendsday', 'Thursday', 'Friday', 'Saturday', 'Sunday'))
);

CREATE TABLE DayOfWeekTest2
(
  ID INT IDENTITY(1,1) NOT NULL 
    PRIMARY KEY,
  Payload NVARCHAR(255) NOT NULL,
  [DayOfWeek] INT NOT NULL
    CHECK ([DayOfWeek] BETWEEN 1 AND 7)
);

See also SET DATEFIRST. As week days are really invariant, you don't need an look-up table (DKNF).

If I am following you, will the CHECK feature verify that the data in the field [DayOfWeek] is in 

CHECK ([DayOfWeek] IN ('Monday' , 'Tuesday', 'Wendsday', 'Thursday', 'Friday', 'Saturday', 'Sunday'))

Answer 9

Using Stefan's solution, you would need to build the dropdown that is appropriate to the C# application.  I could figure out how to do it, but I would use the same means available to you.  Or if I were lazy, I would post this in the appropriate .Net newsgroup.

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Beautiful!  I was unaware of that SQL feature, as I said I am migrating over to SQL Server from Access.  

Thanks to all for the assistance and prompt responses.

Answer 10

Test it..

Answer 11

Indigo,

You cannot "set a field to be a dropdown" in SQL Server. You can limit the acceptable values of this field in SQL Server. Then, in your UI language (C#, VB, ...) you can define a drop-down list which can be filled with values from this field.

To limit the values of your field, use CONSTRAINT CHECK. It is simpler to store the number of the weekday than its name. See here http://msdn.microsoft.com/en-us/library/ms188258(SQL.105).aspx

You can also do this in SQL Server Management Studio.

You can add a CONSTRAINT or modify it without dropping the table.

If you want your ID to start from a new number, use this :

DBCC CHECKIDENT(yourTableName, RESEED, newStartingNumber)

Answer 12

Can you do this for more than one column in a table?

Answer 13

Sure, you can define a check constraint on any column of a table.

The only problem is often: When to use a constraint and when a look-up table  (DKNF).

1. The values must be invariant. Thus the should never change during the life-time.

2. The values should represent a code.

For the DayOfWeek problem:

1. The days of a week are invariant, there will surly no change in the length of a week in near future :)

2. In the first example I'm using the day names, in the second numbers. Names can be codes. In this example using the day names is not that good, cause it limits the table for use in an English speaking environment. So using the numbers and doing the translation in the front-end is the better solution.

An example for names as codes: consider you are storing information from a web service which signals a state as ENABLED and DISABLED. Here you can definitly use the texutal representation.

You can also use a weaker concept, I called it enhanced check constraint:

USE tempdb;
GO

CREATE TABLE Languages
    (
      LanguageCode VARCHAR(2) NOT NULL , --Like de or en.
      CONSTRAINT PK_Languages PRIMARY KEY ( LanguageCode )
    );

CREATE TABLE Customers
    (
      CustomerID INT NOT NULL ,
      LetterLanguageCode VARCHAR(2) NOT NULL ,
      CONSTRAINT PK_Customers PRIMARY KEY ( CustomerID ) ,
      CONSTRAINT CK_Customers_LetterLanguageCode FOREIGN KEY ( LetterLanguageCode ) REFERENCES Languages ( LanguageCode )
    );
GO

So you can simpler administrate larger lists of basically invariant codes.

p.s. I use the term invariant by meaning that the values are not changing in the problem domain, thus during normal operations.