Auto ID

[anon47]

IN access when you start typing a value in say the Name Field the system generates an auto id while you’re in the record. In SQL however it makes you wait until the editing is done.

Is there any way to change the way SQL handles this so that you know what the auto id is before you finish the record? I need this so to provide it to the user on signup.

 

[philhege]

Have your application insert the record before accepting input. Then you'll know the ID beforehand.



Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[Crowley16]

or you can use: select max(id) + 1

--------------------
Procrastinate Now!

 

[ESquared]

or you can use: select max(id) + 1

And what happens if you and another person both get the same value at the same time? This doesn't work.

anon47,
Perhaps you need two tables with a 1-to-0-or-1 relationship.

In the "left" table you include all columns that are required before an insert can be made. In the "right" table you put everything else, with a clustered foreign key to the id column of the other table.

Now you can insert to the left table, get an ID, then wait for user input. When you're done, you can insert using the known ID to the right table. The right table can then have NOT NULL and CHECK constraints and things that you otherwise couldn't have with the single-table approach.

If that sounds like a lot of work, then maybe you could figure out how to avoid needing the user id until after record creation, as was already suggested by Phil.

A final option is not so much a left and a right table but to manage the incrementing number value yourself (I don't like this but it works):

CREATE TABLE NextUserID (UserID int)
INSERT NextUserID VALUES (1)
GO
CREATE PROC GetNextUserID @NextUserID int OUTPUT
AS
UPDATE NextUserID
SET @NextUserID = UserID, UserID = UserID + 1
GO
DECLARE @UID int
EXEC GetNextUserID @UID OUTPUT
SELECT [Your new userid is] = @UID

Since the work is done in a single update statement, there is no risk of multiple people getting the same value. This is less efficient than an identity column (by far) and does block while the update is being performed.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[anon47]

Thanks guys.

philhege how would I do that? I don't really want to select max(id) + 1 because if you have two simultaneous signups this could create a problem.

 

[SQLSister]

Usually when I need to return the id I do an insert in a stored proc where I have declared a variable say @id for illustration purposes
then I set the value of the @id field and return it as the result of the proc

declare @id int
insert table1 (field1)
values('test')
Set @id = scope_identity()
select @id

If you do it this way and later need to run data into a related table you have the id available for that as well. It is very very important to use scope_identity() and not @@identity though as @@identity can give the worng value of there are triggers onthe table and can really destroy your data integrity.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

or you can use: select max(id) + 1

Of course not, by the time you are finishing entering all the data 5 other ids might have been created

In SQL however it makes you wait until the editing is done.

In SQL where? step out of the World of Wizards and into the world of scripting
Why do you need to know the value, store scope_identity() in a variable and use that in other tables

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[anon47]

Ok I am confused, so here is the code I am using:

sql = "SELECT * FROM [Clients]"
rs.open sql, conn, 3, 3

session("myvar1")=rs("ClientID")

rs.Update
rs.Close

How can I get it to return the auto ID?

Thanks again all!

 

[anon47]

Ok guys, here is my work around for this:
After adding the new record on signup I close the conn string then run the following code that reopens the table and (since no two email address are the same I bound it by the email address used at signup which is required field) gets the id. If anyone sees a problem with this method please let me know I am creating this system and data integrity is very important.


sql = "SELECT * FROM [Clients] WHERE [Clients].='" &session("myvar11")&"' "
rs.open sql, conn

session("myvar16")=rs("ClientID")

rs.Close

 

[gmmastros]

>> If anyone sees a problem with this method

What if someone uses the following email address?

[tt][blue]blah'; Drop Table Clients;[/blue][/tt]

Your query would end up being....

sql = "SELECT * FROM [Clients] WHERE [Clients].='blah'; [!]Drop Table Clients[/!];' "

This little gem is called [google]SQL Injection[/google] I suggest you do a little research on this before you go too much further with your development.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[philhege]

Regarding injection, read this, and you'll be a convert.

 

[SQLSister]

And I wouldn't count on email addresses being unique either. We deal with doctors offices and often multiple doctors use one practice email address.

"NOTHING is more important in a database than integrity." ESquared

 

[anon47]

LOL Philhege now that’s funny!

Far as the uniqueness of the email. Only at signup is the email used to find the user ID and our type of service business there should only be one business with that email signing up.

The blah'; Drop Table Clients; gives an error hints not letting the user signup.


Even our password reminder is setup so the client has to type the id and it sends the info to the address on file assoc. with that id so I totally get what you guys are saying.

If you could help me with the above code I would be grateful. I just can't get my brain around how to make any of the solutions work?

 

[gmmastros]

>> The blah'; Drop Table Clients; gives an error hints not letting the user signup.

What is the error message? Table not found?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anon47]

I am out of it now but it gave a can't use quotations in string error or something like that.

I am trying to figer how to use the max(id) method but no dice at this point

 

[SQLSister]

See the first problem is that you are working using recordset connections. This is not the best way to work with SQL Server (or any other database except possibly Access).

In SQl server you should do your data access through stored procedures as much as possible. Typically in a form in access you have the user fill in the values for the fields, you set those values to variables (at this point you could perform checks onthe value to make sure it is in accordance with your business rules , these same checks should also be on the tables inthe db for data integrity reasons), then you use those variables as input values to the stored procedure which performs the update or insert and returns to you the value of the id field in the case of an insert. The query you call is the execution of the stored proc.

Stored procs can perform multiple steps so you can also insert related records at the same time and if any of the inserts fail, you can have the whole thing roll back.

I would suggest though that since your problem is how you use Access not how SQL Server works, you will probaly find better answers in one of the Access forums.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

Did you skip entirely over reading my post which has two solutions to your question?

 

[anon47]

NO I looked at it just working on trying this method.

I have it like this but its not displaying the results, or is this not the way to use it?

sql = "SELECT*,('SELECT max(id)+1') AS maxid FROM [Clients]"
rs.open sql, conn, 3, 3

session("myvar16")=rs("maxid")

rs.Close

 

[ESquared]

Do not use Max(id) + 1!!!!

We were trying to give you hints but failing that I'll just tell you not to do it.

If you were going to do it, which you most definitely SHOULD NOT BECAUSE IT WILL NOT WORK it would look something like this. Your SQL has extraneous single-quote marks and a missing from clause:

SELECT *, (SELECT max(id) + 1 FROM Clients) AS maxid FROM [Clients]

 

[anon47]

Agree with you so here is what I have:

sql = "SELECT *, (Select SCOPE_IDENTITY() From Clients) AS ClientID FROM [Clients]"
rs.open sql, conn, 3, 3

session("myvar16")=rs("ClientID")

rs.Update
rs.Close



Here is the error I am getting:


Microsoft SQL Native Client error '80004005'

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

/signup/processing_nm.asp, line 17