How I Update Id In Sql By Asp.Net

Question

1.00/5 (1 vote)

See more:

SQL

Hi


I create a table in asp.net  I want to store Id into database by default  id is a primary key Column in database and I also create  order  by procedure  what I do on every insertion Id automatically changed For Eg I insert data  this time id is 1 and  2nd time id 2   the user cant see her id . and I dont want to use AutoIncrement In Sql

Posted 17-Nov-14 1:59am

Arora1992

Add a Solution

4 solutions

Solution 3

If you don't use IDENTITY in SQL (an autoincrement is called and "identity field" in SSMS) then there are a lot of possible problems you have to overcome.
The first is that you should never tell a user an ID before it is assigned - because the major advantage of SQL is that it works in a multiuser environment, and if you have two users being created at the same time, they can't both have "the next available number": one will get "n" and the other must have "n+1". Which means that you can't tell a user an Id number in advance of actually creating the record!
Yes, you could create a "temporary" record which will assign an ID, and tell them that - so one will then get "n" and the next "n+1" and it all works fine - except if the first one decides not to go ahead and pulls out, or disconnects - you end up with gaps in your numbers, which I assume you are also trying to avoid.

The second is that you need to store the "next free number" somewhere, and provide a stored procedure that allocates it and moves it on so that two users can't execute it at the same time. This can get quite complex, particularly if your system needs to be scalable - spreading this across multiple SQL servers can get quite nasty!

One way round is not to use integers - use a GUID instead and allocate that from your ASP.NET code - but users don't like them as a userID because they are long and complicated to remember.

In the end, this is going to depend on why you want to tell they user an ID value at all - most don't need to know at all - just use the email or a username for log in and all should be fine, then use the id value in your code from then on.

Posted 17-Nov-14 2:13am

OriginalGriff

Comments

Arora1992 17-Nov-14 8:18am

I use Id value in code thats ryt But I want to add +1 in ID on every new insertion Using How i Do that I already create a insert and orderby procedure

OriginalGriff 17-Nov-14 8:31am

Seriously, you don't want to do it - it's much, much better to use an Identity field and let SQL sort it out - you will just give yourself all the problems you have now, but with an added set of complications.

Why don't you want to use Identity?

Arora1992 17-Nov-14 10:35am

no

OriginalGriff 17-Nov-14 10:50am

"no" what?

Arora1992 17-Nov-14 10:52am

I create Order By procedure and Insert Procedure

I retrieve last Id from database using Order by procedure and Insert +1 of this I need proper query of this

OriginalGriff 17-Nov-14 11:04am

Don't do it!
Seriously - it will cause more problems than using Identity will as soon as you move to a multiuser environment - like a website for example.

Solution 1

check below link
http://angelolaris.blogspot.ae/2013/05/sql-autoincrement-without-using.html[^]

http://stackoverflow.com/questions/5069548/how-can-i-auto-increment-a-column-without-using-identity[^]

http://stackoverflow.com/questions/6084572/sql-server-how-to-set-auto-increment-after-creating-a-table-without-data-loss[^]

Posted 17-Nov-14 2:05am

Deepu S Nair

Comments

Arora1992 17-Nov-14 8:15am

I dont Want to Use auto Increment i already create a insert Procedure and Orderby procedure So Please tell me query

Solution 2

try this..

SQL

declare @Id bigint

Set @Id= select cast(isnull(max(Id),0) as bigint)+1 from table

insert into table(ID) values(Id)

Posted 17-Nov-14 2:06am

/\jmot

Comments

Arora1992 17-Nov-14 8:15am

I dont want to do anything in database

Thanks7872 17-Nov-14 9:07am

You don't want to use identity,dont want to use anything on DB,then you should briefly explain WHY?

/\jmot 17-Nov-14 8:28am

so, what do you want??

/\jmot 17-Nov-14 8:47am

i think you should use trigger.
i guess this is what you really want?? am i correct??

Arora1992 17-Nov-14 10:35am

no

Arora1992 17-Nov-14 10:37am

I create Order By procedure and Insert Procedure

I retrieve last Id from database using Order by procedure and Insert +1 of this I need proper query of this

/\jmot 17-Nov-14 11:11am

Set @Id= select cast(isnull(max(Id),0) as bigint)+1 from table

from this query you can get the last id+1
didn't you try it?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Muhammad Taqi Hassan Bukhari · Accepted Answer · 2014-11-17T18:39:00

Solution 4

Simply before inserting fetch the id of last row in the table and increment it by 1 and then store it with new record.
You Can use Store Procedures for this to solve your problem.

Posted 17-Nov-14 18:39pm

Muhammad Taqi Hassan Bukhari