| Author |
Topic |
|
kamii47
Constraint Violating Yak Guru
353 Posts |
 Posted - 2008-03-17 : 12:54:26
|
| How to get the next number to be inserted in ms access queryMy column is autonumberI don't want's to use max as User might have delete some recordI need to show the number + some text in one of my labelKamran ShahidSr. Software Engineer(MCSD.Net)www.netprosys.com |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
kamii47
Constraint Violating Yak Guru
353 Posts |
Posted - 2008-03-18 : 14:39:11
|
 Any work around or any tip how to deal with these type of issueKamran ShahidSr. Software Engineer(MCSD.Net)www.netprosys.com |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-18 : 15:08:33
|
| Why do you need to show the number before the row is created? What value is there in that? By definition, you cannot do that because what if two people have that same form open, both are told that they would get ID #200 next because you have no way of knowing that two people are "thinking about" adding a row, and then they both create the record? - Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
kamii47
Constraint Violating Yak Guru
353 Posts |
Posted - 2008-03-20 : 08:04:08
|
| u are absolutely correctBut in my scenario there is just a single admin who can upload fileI am converting an classic asp application to asp.net[There they were using max() but i have find this potential problem so i am trying to avoid it]What i am doing is naming a file as primaryKey + some textAlsoDoes any body have any idea about IDENT_CURRENT equivalent in MS access[@@identity and @@scope_identity] have no use in my scenarioKamran ShahidSr. Software Engineer(MCSD.Net)www.netprosys.com |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-20 : 08:36:00
|
| I don't see any issue here.1) user uploads file2) Add a new row to your table 3) Grab the identity value of the new row4) save the file on your server named with the identity valueYou have not yet provided any specific reason why you need to know the identity ahead of time. In this scenario, you certainly don't need it.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
kamii47
Constraint Violating Yak Guru
353 Posts |
Posted - 2008-03-20 : 08:48:39
|
| Yep but the working is different thereAdd page comes where there is some fields including fileiploaduser clcik a button a popup comeswhere have to save file physically {at that time not in db}Same popup is used for add and update .....what i need is SELECT IDENT_CURRENT('myTableName') + IDENT_INCR('myTableName') equivalent in ms access if u or any body else can provide it then it will be very helpfulThanksKamran ShahidSr. Software Engineer(MCSD.Net)www.netprosys.com |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-20 : 09:30:19
|
| Again, you just repeated what I wrote and gave absolutely no reason that the ID needs to be known ahead of time. Without specifics, I still cannot understand your logic, it is all way too vague.If you have a form field called "FileID" that is being posted along with the file itself, and you are using that "fileID" field to determine what to name it when you save it and what you expect the next identity to be in the database, then YOU DO NOT NEED THIS FIELD AT ALL. Take it off your form completely. And simply have your code add a row to the database, grab the @@identity, and that's all the info you need to now save the file.You have been working pretty hard now for a few days on "fixing" this issue, when all you need to do is handle things like this the standard, easiest, quickest, and safest way. The long and short of it is this: YOU DO NOT KNOW WHAT THE NEXT IDENTITY WILL BE UNTIL YOU CREATE THE ROW. End of story. You can accept this fact and write easy, simple, short and clean code that works with this reality, or you can keep trying to write workarounds that will be complicated and buggy. Up to you.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-03-20 : 09:49:45
|
| I will say this, though: if you really think you need to know the value ahead of time, don't use an identity at all. Just use a regular INT primary key column, and use MAX()+1 and insert the ID you want explicitly. I see no reason to use an autogenerated identity at all in that case since you are not using any of the key features an autonumber provides... in fact, the autonumber does much more harm than good and makes things more complicated than they need to be.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
cdzero
Starting Member
1 Post |
Posted - 2009-08-04 : 09:00:25
|
To check and see what the next identity will be:DBCC CHECKIDENT (’tablename’, NORESEED)To reset the next identity recordDBCC CHECKIDENT ('tablename', RESEED, 25)(This last statement will cause the next identity to be 26) |
|
|
|
|
How to get the next autonumber to be inserted