Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2011
    Posts
    2

    Lightbulb Unanswered: How to define our own format for our field in database programming

    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

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    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

  3. #3
    Join Date
    Feb 2011
    Posts
    2

    Lightbulb

    Quote Originally Posted by Kaiowas View Post
    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
    Last edited by bhanu_smile507; 02-23-11 at 01:26.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •