Easy way to find PK that was for record just appended?

[Ovatvvon]

Hello,
I am making a site where employees can signup on the intranet for a work order account. They enter their id, pwd, name, and work area, along with as many phone numbers (fax, cell, pager, etc) as they like....to do that, I made a seperate table for phone info linked by their pk in their member table and a foreign key field in the phone table.

problem:

There isn't enough time or space to write out the entire problem, however, upon checking to see if the id they want is already takin, if it's not it will enter it in the db, and then next see what the pk is where it was entered so they can continue entering the info...I'd rather not do it this way, I'd rather submit it to the db all at once (at least the main info)....but I need the pk for the phone info...

question:
Is there a way to check for the pk at the same time as appending the information to the table rather than having to go back again and look it up? (PK = autonumber, and multiple people could be submitting info at same time).

Thanks for your help in advance... -Ovatvvon :-Q

 

[ToddWW]

Nope. But go ahead and write the problem out. I'm not sure I understand why you wouldn't have enough space ?? Here's what it would look like.

dim objRst
dim Conn
dim strSQL
dim strProvider
dim id
dim pk

id = Request.Form("id")

strProvider = Your conn string or DSN

set objRst = Server.CreateObject("ADODB.Recordset")
objRst.CursorLocation = 3
objRst.CursorType = 3
strSQL = "SELECT FROM tablename WHERE id = '" & id & "'"
objRst.Open strSQL, strProvider
IF NOT objRst.EOF
  objRst.Close
  set objRst = Nothing
  Response.Redirect "Back To Page With Duplicate Error"
END IF

set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strProvider
strSQL = "INSERT INTO tablename (id,pwd,name) VALUES ('" & id & "', '" & Request.Form("pwd") & "', '" & Request.Form("name") & "')"
Conn.Execute strSQL
Conn.Close
set Conn = Nothing

set objRst = Server.CreateObject("ADODB.Recordset")
objRst.CursorLocation = 3
objRst.CursorType = 3
strSQL = "SELECT FROM tablename WHERE id = '" & id & "'"
objRst.Open strSQL, strProvider
pk = objRst("pk")
objRst.Close
set objRst = Nothing

Response.Redirect "nextpage.asp?pk=" & pk

Seems like alot, but at least you have two unique fields in the user table (pk & id or username) so you won't battle with multiple people doing this at once. Notice that the last SQL pulls the record based on the ID that was just entered and not the MAX pk.

ToddWW

 

[Ovatvvon]

It's not the fact of enough room on the page for the code....I meant I didn't want to write for 10 hours explaining in detail our situation because nobody else cares about that...I just wanted to shortly state the problem and ask for a solution.
......

I think I might have found another work-around though...let me find out first.
-Ovatvvon :-Q

 

[ToddWW]

Hmm.. Sounds good. I hope I didn't come across the wrong way. Let me know if I can help in any other way.

ToddWW