May 26, 2011 at 5:49 am
Hello Dear Friends,
How can I increase the value 'Engg0' to N'Engg1', N'Engg2' and so on when next records are inserted in a database. This field is Primary Key and have varchar data type in a table?
May 26, 2011 at 6:08 am
you write logic to append the same with name. Thats the best way.
However, wonder why did you keep your primary key on this instead you can have only the integer part in different column with int/bigint data type and make that primary by keeping the concatenated one for your display purpose.
May 26, 2011 at 6:15 am
You are right ...
if i remove a primary key from this field, but my question is is there any script which can increase the value of this filed by 1 when a record is inserted and this field acting as reference number? any idea ?
Waiting your reply
May 26, 2011 at 6:33 am
If you are okei to have a incremental integer value, please go ahead with identity set on the column.
May 26, 2011 at 6:51 am
LEFT(RefNo,PATINDEX('%[0-9]%',RefNo)-1)+
CAST(CAST(SUBSTRING(RefNo,PATINDEX('%[0-9]%',RefNo),255) as int)+1 as varchar)
Far away is close at hand in the images of elsewhere.
Anon.
May 26, 2011 at 7:58 am
Hello ,
I have create table Test table with primary key id test_Id and auto increment when inserting a record in a table, then i add another field with name of RefNo with varchar (50), and last field is name with varchar(50).
So Now on RefNo field, I have set Default Value or Binding value as ('LEFT(RefNo,PATINDEX(''%[0-9]%'',RefNo)-1)+CAST(CAST(SUBSTRING(RefNo,PATINDEX(''%[0-9]%'',RefNo),255) as int)+1 as varchar)')
but when i insert name in a table primary key field value increased not RefNo value like Engg001, Engg002 and so on...
Any Idea...
Waiting your mail
May 26, 2011 at 8:44 am
Gopal Singh (5/26/2011)
Hello ,I have create table Test table with primary key id test_Id and auto increment when inserting a record in a table, then i add another field with name of RefNo with varchar (50), and last field is name with varchar(50).
So Now on RefNo field, I have set Default Value or Binding value as ('LEFT(RefNo,PATINDEX(''%[0-9]%'',RefNo)-1)+CAST(CAST(SUBSTRING(RefNo,PATINDEX(''%[0-9]%'',RefNo),255) as int)+1 as varchar)')
but when i insert name in a table primary key field value increased not RefNo value like Engg001, Engg002 and so on...
Any Idea...
Waiting your mail
Where would you get 'Engg' from in that case
The code I posted would extract and increment from an existing RefNo value
ie SELECT MAX(RefNo) FROM [sometable]
Far away is close at hand in the images of elsewhere.
Anon.
May 26, 2011 at 8:54 am
Hello ,
I have create a table in SQL Server 2008 and create a filed name eng_ref as varchar and set the default value as Engg.
The structure of the file is as ....
CREATE TABLE [dbo].[Test](
[test_id] [int] IDENTITY(1,1) NOT NULL,
[eng_ref] [varchar](50) NOT NULL CONSTRAINT [DF_Test_RefNo] DEFAULT ('Engg'),
[Name] [varchar](50) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[test_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I want how to increase the Engg01 to Engg02 when records are inserted in a table from ASP.NET
May 26, 2011 at 9:07 am
Use a trigger like this
CREATE TRIGGER trg_test ON [Test] FOR INSERT AS
UPDATE t
SET t.eng_ref=t.eng_ref+RIGHT('0'+CAST(t.test_id as varchar),2)
FROM [Test] t
JOIN inserted i ON i.test_id=t.test_id
Far away is close at hand in the images of elsewhere.
Anon.
May 26, 2011 at 9:08 am
btw this will only allow 99 occurrances
Far away is close at hand in the images of elsewhere.
Anon.
May 26, 2011 at 9:18 am
First thanks for giving your valuable time. I am near the solution.
The data was increased value properly. but when I enter name in a table. It show a message transaction was committed successfully. but not sewing the record because not able to retrieve the data shown as read only once i right click on inserted row and select Execute SQL , data was shown in proper format.
Any idea how to fix it....
May 26, 2011 at 9:21 am
based on what David showed, i'd just make the column calculated based on the identity instead of using a trigger:
I don't know if you want preceedig zeros, or if Engg4592 is fine for you , as an example id.
CREATE TABLE [dbo].[TEST] (
[TEST_ID] INT IDENTITY(1,1) NOT NULL,
[ENG_REF] AS ('Engg'+RIGHT('0000000000' + CONVERT([varchar],[test_id],0),(10))),
[NAME] VARCHAR(50) NULL PERSISTED,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED (test_id),
CONSTRAINT [UQ_eng_ref] UNIQUE NONCLUSTERED (eng_ref))
Lowell
May 26, 2011 at 9:24 am
but i need this value can gone upto 4-5 million times, so can take datatype varchar(50) to varchar(max), then might it will allow me to enter increased the value of the field as record are inserted to the table.
Auctually I am going to develop job portal Engineers society and out target user more then 4 million belongs to engineers society from civil, mechanical, aeronotical, IT etc...
May 26, 2011 at 9:35 am
Gopal Singh (5/26/2011)
but i need this value can gone upto 4-5 million times, so can take datatype varchar(50) to varchar(max), then might it will allow me to enter increased the value of the field as record are inserted to the table.Auctually I am going to develop job portal Engineers society and out target user more then 4 million belongs to engineers society from civil, mechanical, aeronotical, IT etc...
then your datatype needs to jump to a bigint instead of int,(int is limited to ~2 billion).
so now, if there are going to be that many records, what value does appending a string to the identity have? how does that help, other than make it "obvious" it's important to Engg~ people? wouldn't the idnetity and a seperate category make more sense? so you can hav e"Eng" people as well as "App" and "DBA" people?
Lowell
May 26, 2011 at 9:36 am
Hello,
I have made changes in trigger and now it will allow to increase the number more then hundrade even up to more then million.
Thanks for giving your suggestion... Have a great night !!!!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply