How to define our own format for our field in database programming

bhanu_smile507 · #1 (**permalink**) 02-22-11, 01:23

HI, I am trying to do one thing, bt I don't have an idea, where to start..

I am programming a webapplication using asp.net, C# using sqlserver2008.

I had a form, which had so many fields in it. I need to write a stored procedure for it.

So, actually, I had to define a format for a database field like for example "010101" N "001", this is the actual format. Here the first 6 digits is a code of a user selection done by user. Actually, the user selects a name in the webform, we had defined some digit format for that name and that is the 6 digit format. so the six digit may vary with the user selection.

So, firstly, I should get the user selection and then append a letter "N" to it, and then also append last three digits..this thing is more crucial.

the last three digits should be auto incremented, but the auto increment is dependent on the first six digits ..

like i will explain with an example,

like if the user selects a country "Australia", then the first six digits will be some value like 050607, then we need to append letter "N" to it and then we need to check for the next three digits, like if the user is entering the info for the first time to the country "Australia", then the last 3 digits should be 001 and should be incremented by one for each insertion to that country.

And if the user selects some other country India, and the code would be something like, 030601, append N to it and then we need to check for the last 3 digits recent incremented value for the country named India, for example, the user has already inserted some data, and the last 3 digits is something like 006, then it should be increased to 007

Finally, I want to summarize like this

Firstly, the user has to get the six digit value from the webform, then append letter "N" to it and then need to check for sixdigit value, whether the user is inserting data for that particular country for the first time or else increment by one to the recent increment value of the last 3 digits..

In this way, the last 3 digits in independent of the different country values..

Hope you got me...
Plz help me a lot... this is complicative for me...

Regards,
J.BhanuChandra

Kaiowas · #2 (**permalink**) 02-22-11, 08:13

My first reaction would be to split the lot in two columns, one for the user indication and the other for the country increments. For the merge of both including the "N", I suppose a view or another column with a insert-trigger could do the trick.

I would separate them because I can't find a proper column name, and for me, that's a sign of a possible design flaw.

Anyway, this might be of help:

Code:

create table table1 (account varchar(6), insertz int, merged varchar(10))
go
create trigger trInsert on table1 instead of insert
as
declare @account varchar(6)
,       @insertz int
,       @merged varchar(10)

select @account = account from inserted
select @insertz = isnull(max(insertz),0)+1 from table1 t where t.account = @account
select @merged = @account + 'N' + right('000' + convert(varchar, @insertz), 3)

insert into table1 values (@account, @insertz, @merged)
go

insert into table1 (account) values ('010101')

select * from table1

go
drop trigger trInsert
go
drop table table1
go

bhanu_smile507 · #3 (**permalink**) 02-23-11, 00:16

Quote:

Originally Posted by Kaiowas

My first reaction would be to split the lot in two columns, one for the user indication and the other for the country increments. For the merge of both including the "N", I suppose a view or another column with a insert-trigger could do the trick.

I would separate them because I can't find a proper column name, and for me, that's a sign of a possible design flaw.

Anyway, this might be of help:

Code:

create table table1 (account varchar(6), insertz int, merged varchar(10))
go
create trigger trInsert on table1 instead of insert
as
declare @account varchar(6)
,       @insertz int
,       @merged varchar(10)

select @account = account from inserted
select @insertz = isnull(max(insertz),0)+1 from table1 t where t.account = @account
select @merged = @account + 'N' + right('000' + convert(varchar, @insertz), 3)

insert into table1 values (@account, @insertz, @merged)
go

insert into table1 (account) values ('010101')

select * from table1

go
drop trigger trInsert
go
drop table table1
go

Hey, Thanks a lot for helping me a lot, I learned a lot from your reply...but I need some clarifications in some of the things... like...

1) you have used this statement, may I know why u have used this" insert into table1 (account) values ('010101') ".

2) Actually, I am designing a webform in asp.net, so, in that form , after the submit button gets fired, these all manipulations has to be done and data has to be stored in database, so, if u had any knowledge reg with this technology, can you just tell me, how and where I should execute this query, should I insert this query inside a stored procedure or need suggestions with this!!!

3) And you are dropping the trigger and table, may I know y you are doing this at the end??

4) And also help me, how do we get the country code(the six digit) from the webform to this query??
Regards,
J.BhanuChandra

Teddy · #4 (**permalink**) 02-23-11, 09:39

Get Started with ASP.NET and ASP.NET MVC: The Official Microsoft ASP.NET Site

They have some good videos focusing on using parameterized sql from a webform. Highly recommend watching those.

Internet Services

Web Development

Digital Marketing

Consumer Tech