Get data from database

[crabgrass]

Suppose you create a new record in a database with a command sequence like:
<code>
sql = "EXEC 'insert into users (name) values ([name])'"
oConn.Execute(sql)
</code>
and suppose the database automatically create the value for the keyfield of the new record. How would you extract the new keyfield value and assign it to a variable? I've been told it can be done with something like:
<code>
newkeyID = oConn.Execute("EXEC 'return users.keyID'")
</code>
but this doesn't work for me.
Thanks

 

[gmmastros]

What database are you using? Microsoft Access, Microsoft SQL Server, MySQL, Oracle, etc....

The answer to your question depends on the type of database.

-George

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

 

[nickdel]

crab, what kind of database? If it's SQL Server then your looking for

scope_identity()

.

where would we be without rhetorical questions...

 

[crabgrass]

It's Visual FoxPro using the VFPOLEDB provider:
<code>
dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")
ConnStr = "Provider=VFPOLEDB;Data source=" & session("fileloc") & "contactmanager.dbc"
with oConn
.Mode = adModeShareDenyNone
.Open ConnStr
.CursorLocation = adUseClient
.Execute ("set null off")
end with
</code>