Please read the fall artical before post.

as the link I post before My last post. as down to the page it said it is possible.

Here is quote form page to your question for access

However, if you're stuck with Access and need more security that this won't happen, you can use a Recordset object with an adOpenKeyset cursor.

Hi Matt,

I am not sure if he meant it like this, but there is a point in mtuppers's answer.

I can't prove it, but there is a chance that two users insert a record at the same time, so there is a very small chance they get each other's ID. Initially, you asked for someone that had experience with this, which I don't have, so I never answered.

Anyway, if there is a change, you should guard yourself against it. One way to do that is to use SQL server and it's Scope_Identity function. Another is to use the AddNew method of the ADO recordset.

But, back to mtuppers's answer. Let's say you save the session ID together with a record. This way, two users will end up creating two records like this:

Id SomeValue SessionId
23 SomeValue 832568465754
24 SomeOtherValue 9484324639cd

If you now query for TOP 1 ID FROM this table, AND use the Session ID as an additional WHERE clause, then you can be sure you never get the wrong ID. Session IDs aren't unique over time, but they are unique enough to fix this problem....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: Techno Rave Entrance by Blade (From the album: Blade Soundtrack) What's This?

If you want to real answer why don't you try your self and see what happend.

I mean asking 20 co-worker or friends to test the things for you at same time.

Make sure it is unique record by have random mix chr and num. and save it as array in session veriable and insert in to one field. as the page make sure the code able to reflash it self. therefore you will have way more insert statment been exc.

Oh, set each page reflash for 3 minutes. so 20 user that might have at less more then 600 insert records.

After the test the code, make sure the user either print out the array or save the html that print array page. Compare the array and the insert records.

Or testing on pws or IIS your self, write the code that will start the test at same time, Open more then 5 set of IE so session will be unique. then proccessed above suggestion.

Imar - thank you for your input

mtuppers
;;;If you want to real answer why don't you try your self and see what happend

You will notice in my first post:
"IMO: I believe it may be possible however I am yet to come accross someone who has actually seen it happen and I can not manage to make this occur (I have tryed)"

Thank you for your input also. I think we have wasted enough of Wrox's disk space on this post


Wind is your friend
Matt

Instead of using an INSERT use a SELECT ID, X FROM y WHERE ID = -1
Where -1 will always return MyRS.EOF

You can then use:

MyRS.AddNew
MyRS("X").Value = "Whatever"
MyRS.Update

If you use the correct cursor's for your DB provider (1,2,2) you can access the new id:

NewID = MyRS("ID")

Hope this helps

Courtenay Probert
http://www.probert.me.uk

;;;SELECT ID, X FROM y WHERE ID = -1
returns an empty record set (EOF), this is not my objective. My appologies if I dont understand your post and what it has to do with my question

A little confused

Wind is your friend
Matt

Sorry let me explain.

Your problem is that your performing 2 actions an insert then a select. As you have correctly identified it is not impossible for a second insert to happen b4 the 1st select returns the ID. What I'm suggesting is you create a recordset with a select statement and use the .AddNew method instead of using SQL INSERT. Whats great about this is it facilitated using the same page for both adding new records and editing existing records. I will try and demonstrate with some code.

<%
' Check to see if in edit mode
Dim MyID : MyID = -1
If Request.QueryString("ID") <> "" Then MyID = Request.QueryString("ID")

Dim strSQL : strSQL= "Select ID, field1, etc FROM tblName WHERE ID = "& MyID

Set rs_MyRS = Server.CreateObject("ADODB.Recordset")
rs_MyRS.ActiveConnection = connStr ' Set your connection string
rs_MyRS.CursorType = 1
rs_MyRS.CursorLocation = 2
rs_MyRS.LockType = 2
rs_MyRS.Source = strSQL
rs_MyRS.Open()

' If there was no querystring then the record set will be end of file so we create a new record
If rs_MyRS.EOF Then rs_MyRS.AddNew

' Then you set the new values
rs_MyRS("field1") = "SomeValue"

' You then update the database
rs_MyRS.Update
' Job Done

' To return the ID of the new record you can use the recordset
Dim MyNewID
MyNewID = rs_MyRS("ID")

' Be careful you will need the correct cursors for your DB provider for this to happen - the above should be OK

' Cleanup
rs_MyRS.Close
Set rs_MyRS = Nothing
%>
Using this method INSERTS are a little slower but you can retrieve the new ID in 1 action and you can merge your edit code into your add page (if needed)

I hope this a a little clearer


Courtenay Probert
http://www.probert.me.uk

cprobert

Thankyou for your time. I understand your process now. To be honest with you I always use the default forward cursor and control the recorset either in an array or moveNext, moveFirst etc.

The following code:

Set rs_MyRS = Server.CreateObject("ADODB.Recordset")
rs_MyRS.ActiveConnection = connStr ' Set your connection string
rs_MyRS.CursorType = 1
rs_MyRS.CursorLocation = 2
rs_MyRS.LockType = 2
rs_MyRS.Source = strSQL
rs_MyRS.Open()

I am aware of however have never used.

I have always used this type of coding:

        sql = "SELECT users.id,users.fName,users.lName,users.eMail,group s.shortName,users.lastLogon,users.lastActivated,us ers.activeUntil FROM users LEFT OUTER JOIN userGroups ON users.ID = userGroups.uId LEFT OUTER JOIN groups ON groups.ID = userGroups.gId WHERE (users.inactive=" & cint(state) & ") order by fName;"
     end if
     set getInfo = conn.execute(sql)
     dim lastID
     if not getInfo.Eof then
        lastID = 0
        do until getInfo.EoF
           if (cint(getInfo(0)) <> cint(lastID)) then
                   .....

I am unsure if as I said above "the default forward cursor" is correct. Please feel free to correct me. I have never run into a problem where my method can not be used. IMO better use of server resources, perhaps I am incorrect in saying this however logically I think it must be.

Thanking you for your time

Wind is your friend
Matt