SQL/ASP Primary key question

Bakedgoods

Bakedgoods

Bakedgoods

Capodecina
Permabanned
Joined
31 Dec 2003
Posts
5,172
Location
Barrow-In-Furness
Is there anyway of putting a prefix on an primary key field? I'll try explain with an example.

tblCodes
CodeID [PK] - Integer
CodeName
CodeDesc
CodeType

When a new code is created the ID is simply the next value as you would expect. To help with identifying the codes in my actual application, I would like the ID to be based on the CodeType.

For example: There are four types of code (red, green, blue, orange), if when creating a new code the user selects the type red, the CodeID will be "RED\1". If another is made using the type red, it will become "RED\2". The same applied the the others, a green code will have a prefix of "GREEN\" which increments.

Really not sure how to go about doing this, maybe a seperate table for CodeTypes is needed? I'm a novice programmer and i'm also new to SQL to please to be gentle!

Thanks for any help.
 
I think I know what you're saying, and if I am, then what you want to do can be done.

Simple make CodeType a primary key too and this will STOP the table from having two entries of, say, Green2.

*edit* if you're using SQL2005, then you could create a new computed column which would do this for you.
*edit2* actually, no it wouldn't I don't think.
 
Your on the right lines. Ideally CodeTypes should be in another table, and you can create a "compound" key from multiple keys if you so desire. In this case you can use the id + foreign key from the new table CodeTypes.

Or if your not that bothered about using the compound key and more interested in a display version.

You could just use the following for example (in sql server)
SELECT CodeType + '\' + CAST(codeId AS VARCHAR(10)) AS code FROM tblCodes
 
There is and only needs to be 4 code types, if that's any help.

To give a bit more information on the reasoning for wanting the prefix on the CodeID.

Scenario
A user is inputting the amount of time he has spent on a code. There is a drop down value which he must select the CodeID from. At the moment there is no distinguishing between the CodeTypes, so he will just see 1, 2, 3 ,4 ,5.

If I can do what i'm wanting, the user will see GREEN\1, GREEN\2, RED\1, RED\2 and so on. Making it a fair bit more user friendly.

Any ideas?
 
Oh the sql for that and a compound primary key would be something like this.

CREATE TABLE tblCodes (
CodeID numeric(18, 0) NOT NULL ,
CodeName varchar(10),
CodeDesc varchar(50).
CodeTypeId numeric(18, 0) NOT NULL ,
) ON PRIMARY
GO

ALTER TABLE [dbo].[tblCodes ] ADD
CONSTRAINT [PK_tblCodes ] PRIMARY KEY CLUSTERED
(
CodeID ,
CodeTypeId
) ON [PRIMARY]
GO
 
I'm not really sure what's going on there. I have only a basic understanding of SQL.

I'm using SQL Server Management Studio to deal with my DB.
 
I'm not totally sure on using the studio, but I think you are on the table designer. You can select two fields. Right click and select Primary key. I think that will work.

if you then want to see that primary key to look like "RED\1". You would then need to form the SQL to concatenate this, either by using a view, or in a stored procedure. Note this would be for viewing purposes only.
 
I don't really see why you are saying I need to have UserID in there at all.

I'm still not sure how to do this :(

It's only really needed for presentation reasons. So the actual value doesn't need to be stored as GREEN\1.

For example, I want the drop down menu to show GREEN\1 or RED\12 simply to make it easier for the user to tell what time of code his is picking, rather then all the codes looking the same but just with different numbers (1, 2, 3, 4, 5, 6).

Any ideas?

Although I suppose once it's in the drop down menu as Green\1 and the user selects it, it will then be stored as Green\1 in the user data table. Meaning I can't enforce any restraints between the two tables.

:\
 
Last edited:
I've done something similar. But you would need to do it in a BLL before it gets inserted into the database.

To do it you simply use some VB code to create a variable with GREEN/1 for example.

Then use the variable as the value for the id field in the database.
 
KevinCB said:
I've done something similar. But you would need to do it in a BLL before it gets inserted into the database.

To do it you simply use some VB code to create a variable with GREEN/1 for example.

Then use the variable as the value for the id field in the database.
Click to expand...

I still haven't really read much into BLLs and I didn't properly understand them at the time so it looks like it's time for a revisit :[

Do you know how to stop a time getting attached to a date field? I've selected smalldatetime, I only want the date like 20/10/07 but it always looks like "20/10/06 00:00:00" :(
 
Last edited:
Wardie said:
Do you know how to stop a time getting attached to a date field? I've selected smalldatetime, I only want the date like 20/10/07 but it always looks like "20/10/06 00:00:00" :(
Click to expand...

What I do is set the date field in the DetailsView/GridView as a template field.
Select the textbox in the insert template view, and change the databinding.
In the databinding window there should be a dropdown to choose the formatting of the field, select the one without the time, it should be '{0:d}'.
 
It's blanked out the format bit and I have to use Custom Binding?

I've had this quite a few times before and i'm never sure why.
 
You must log in or register to reply here.
Back
Top Bottom