September 10, 2008 at 2:04 pm
I have a column that I want to change to an autonumber field, and choose which number to start with. My current column looks like this:
NMR_No (PK, varchar(30), not null)
How do I go about this?
September 10, 2008 at 3:55 pm
Identity or ROWID may be helpful..
VG
September 10, 2008 at 4:51 pm
Note that you'll have to change the column to a numeric datatype. You don't say if the current column has data in it that you want to preserve. If it does, I'd add an identity column with a different name. See "Identity (property) (Transact-SQL)" in BOL.
Greg
September 10, 2008 at 5:59 pm
Autonumber is an Access term, what you may mean in SQL is termed an Identity column - which contains a number unique within that table and incremented each time a new row is added to the table. If this is what you want and as mentioned above
Note that you'll have to change the column to a numeric datatype. You don't say if the current column has data in it that you want to preserve. If it does, I'd add an identity column with a different name.
Having said that open SSMS select the database, expand the list of tables. Right click on the desired table and in the drop down menu select "Design". The table will open in the design view, select the field you want, change its type to INT or BIGINT. Scroll down a ways and in the "Column Properties" Tab expand the "Identity Specification", in the data that is then displayed select "is identity" change value to "Yes", the select "Increment" - in the right column type in the value you want.
To select the starting value select "Seed", input the value you want to start with.
Close up the window and click yes when it asks if you want to save the changes.
September 11, 2008 at 4:04 am
Hi,
you have to first change the datatype of that column and then set the seed value as per your requirement.
ALTER TABLE 'table_name' ALTER COLUMN NMR_No identity (seed,increment)
Where seed is the initial value your column would take and the increment would be the (no of rows+1).
eg: NMR_No identity (1,1) - it goes on like this (1,2,3,4,5...)
NMR_No identity (5,2) - (5,7,9,11,etc)
Please let em know if this helps.
-Rajini
September 12, 2008 at 12:25 pm
The script worked great. However, I have run into another problem with this. The data that goes into this table is entered into an Access form. When a user sets up a new record the form shows (NEW) in the NMR_No control, which of course is what is displayed on the table. Is there a way to get it to create the next suqential number on the table so that it is visible on my Access form when a new record is created?
September 15, 2008 at 4:56 am
can you provide SAMPLE data and error occurred ???
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 15, 2008 at 7:37 am
bpowers (9/12/2008)
The script worked great. However, I have run into another problem with this. The data that goes into this table is entered into an Access form. When a user sets up a new record the form shows (NEW) in the NMR_No control, which of course is what is displayed on the table. Is there a way to get it to create the next suqential number on the table so that it is visible on my Access form when a new record is created?
On the form_current event, check to see if the record is new (it's the form's properties, but I don't remember on the top of my head), then simply insert this line of code :
me.dirty = false
That will save the record in the DB, and display the right ID number right away. How that won't work if you have columns SET as NOT NULL and no defaults or no value are supplied.
September 15, 2008 at 7:56 am
This particular column is a unique number set to "not null". I tried the code anyway, but no luck.
September 15, 2008 at 8:03 am
You just told your mechanic that you have a problem with your car, but you didn't bring the car, nor what the problem actually his.
Please post the code you tried and the error message you got, then I'll have a clue of what I have to tell you.
September 15, 2008 at 8:24 am
Sorry. There is no error, it is just not doing what I would like it to do. Here are a couple of screen shots so that you can see what I am trying to do.
September 15, 2008 at 8:40 am
1 - Why does it matter in your case that the number be displayed before or after the record is inserted?
2 - Why not just use access working and built in system to handle that (hit create new record, enter info, let access enter the data and it'll display the record id then).
3 - I have never had a form where the users needed to see that id number... NEVER ever had to. The point being, why do your users need to see that number?
September 15, 2008 at 10:30 am
The information recorded in this form is ultimately printed off on a crystal report. There is a link on the form to our crystal reprots server, which they will then enter the NMR_No into the parameter to print the report. Also,there will be several groups who will come back into the form and sign off, or disposition the records. In either case the NMR_No has to be visible/searchable for reference. Make any sense?
September 15, 2008 at 10:59 am
Yes, I still don't see why it makes a difference as to when the id is displayed / created. The id is useless without the rest of the data (as far as my experience goes). The id will show once the data has been accepted on the server.
Why is that not enough?
September 15, 2008 at 11:12 am
Ok, I see what you are saying and I tested. I entered in some data and hit my save button. It didn't pull the NMR_No into the form, even though it was saved to my table. However, when I chose another record using my Go To command it showed the new number. I'll have to work out the VB code somehow.
Thanks
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply