Insert record and then pull out that records ID

[GWINTO]

I need to find out the ID for a record that has just been entered into a database, if I close the 'insert' record set, and then open a 'select' based on the data just entered it throws an error.

Is there a way to extract the ID without closing the 'insert' record set?

The more you know, the more you realise there is to know....
CCNA MCP

 

[onpnt]

extract the ID without closing the 'insert' record set

use the @@identity on a nested select
eg:
SET NOCOUNT ON
INSERT INTO table (column) VALUES (vals);
SELECT @@IDENTITY AS NewID
SET NOCOUNT OFF


note I believe access does not support multi SQL structures. 2K might as I know 2K only supports @@identity. (rediculous but...)

if you are using early versions of ms then do seperate staetments with the executes

_____________________________________________________________________

_{Hakuna matata!!}

 

[GWINTO]

Thanks for your reply

I've not seen this before, if the ID has the name industryID, does NewID need to be replaced with IndustryID, how do you populate a variable with this value?

Do I just use industryid=rs(industryid) after the sql statement has run?


The more you know, the more you realise there is to know....
CCNA MCP

 

[onpnt]

it doesn't matter what the primary or identity field is named unless you want to name the value returned as that column name.

when you execute the statement above NewID is given the identity fields new value. so if you have industryid as a autonumber and theres a field NAME, ADDR, PHONE in the table, you execute this
Dim SQL
SQL = "SET NOCOUNT ON " & _
"INSERT INTO table (NAME, ADDR, PHONE) " & _
"VALUES ('bob','11111-11','222.222.2222); " & _
"SELECT @@IDENTITY AS NewID " & _
"SET NOCOUNT OFF"

Set MyRecordSet = ConnectionObject.Execute(SQL)

now all you need to do is
MyRecordSet("NewID&quot to get teh last ID that was created from the insert. The actual column name of the identitfier plays no role




_____________________________________________________________________

_{Hakuna matata!!}

 

[GWINTO]

This is throwing the following error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)<br>[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

The database is Access2k
The SQL statement is as follows:

sqlstmt = &quot;SET NOCOUNT ON INSERT INTO industrySABD(industry,createdby,datein)&quot;
sqlstmt = sqlstmt & &quot; VALUES ('&quot; & industry & QU & createdby & QU & datein& &quot;'); SELECT @@IDENTITY AS NewID SET NOCOUNT OFF&quot;

The more you know, the more you realise there is to know....
CCNA MCP

 

[onpnt]

again, i'm not sure if Access 2K supports this type of SQL programming but for what you have

1) there are no , seperators in the values
2) there are more values then columns

eg:
(industry,createdby,dateinmissing column)&quot;
sqlstmt = sqlstmt & &quot; VALUES ('&quot; & industry & &quot;','&quot; & QU & &quot;','&quot; & createdby & &quot;','&quot; & QU & &quot;','&quot; & datein & &quot;');

_____________________________________________________________________

_{Hakuna matata!!}

 

[GWINTO]

Should have said QU is a variable filled with &quot;','&quot;

The more you know, the more you realise there is to know....
CCNA MCP

 

[onpnt]

read here then
thread333-710916
to find otehr methods

_____________________________________________________________________

_{Hakuna matata!!}

 

[GWINTO]

Counting the number of records won't work, if a record is deleted, autonumber doesn't reuse its value so you caould have an ID of 23 and only 15 records.

I've also tried

conn.open connection
sql=Insert stmt
conn.execute(sql)

set rs=Server.CreateObject(&quot;adodb.Recordset&quot
connectme=&quot;DSN=connection&quot;
sql=SELECT statement where 'content' = 'content inserted'
rs.open sqlstmt, connectme

And this returns an error that it can't find the object when it is clearly entered into the database.

Is there a time out period before an entered record can be extracted?

The more you know, the more you realise there is to know....
CCNA MCP

 

[onpnt]

you can use the thread to get the last ID if you hit it with that statement after teh insert. you should be locking the database when inserting anyhow or the db will crash when you hit it at teh same time with multiple users.

first thing, searching the net for @@identity should give you hundreds of hits on performing this task with all databases but here's what I mentioned you can do previously seeing as 2K must not support the multi SQL Statement

obviously seperating the statements is the next thing to hit the DB with
you have some issues with the code above unless you're just pasting parts and not pasting the parts that are needed
the main downfallsa re
conn.open connection <--bad idea to use restricted words, try connectionString

conn.execute(sql)
connectme=&quot;DSN=connection&quot; <--this is out of place and not referenced anywhere in your posted code as the connection to use

You need to use the same connection for these executes.
you should declare all your objects before coding anything dealing with the actions upon them also.

now to seperate the statements beign the next step as I can't debug your statements to see if tehre are syntax errors in them so I'll assume access doesn't support this (as it doesn't support much) unless someone ahs input to that.

so,
Dim sql,connectme
Dim identityRS
Dim cnn, identSQL

set cnn = Server.CreateObject(&quot;ADODB.Connection&quot
Set identityRS = Server.CreateObject(&quot;adodb.Recordset&quot
connectme=&quot;DSN=connection&quot;

cnn.Open(connectme)

sql = &quot;insert into somewhere (columns) values (some values)
identSQL = &quot;SELECT @@IDENTITY AS NewID&quot;

cnn.Execute(sql)
identityRS.Open(identSQL)

response.write identityRS(&quot;NewID&quot



I know only 2K supports @@identity so that information would help in further helping you

_____________________________________________________________________

_{Hakuna matata!!}

 

[onpnt]

ignore the otehr thread. I thought they discussed max() tehre

_____________________________________________________________________

_{Hakuna matata!!}

 

[Veep]

This will work for MSAccess:
I'm assuming that you opened your connection already. Also don't use a DSN (obsolete). Use OLEDB Provider.
myConnString=&quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\

http://wwwroot\YourDB.mdb;

Persist Security Info=False&quot;
Then:
conn.open myConnString
<%
sqlstmt = &quot;INSERT INTO industrySABD(industry,createdby,datein)&quot;
sqlstmt = sqlstmt & &quot; VALUES ('&quot; & industry & QU & createdby & QU & datein& &quot;')&quot;
conn.execute(sqlstmt)
set rsNewID=conn.execute(&quot;SELECT @@IDENTITY&quot
NewID=rs(0)
set rsNewID = nothing
conn.close
set conn=nothing
response.Write(&quot;The NewID = &quot; & NewID)
%>

 

[GWINTO]

DSNs - obsolete? Our ISP uses ODBC connections for databases, if we had a DSN with the name of webdata, how would this work in the context of OLEDB provider?

The more you know, the more you realise there is to know....
CCNA MCP