inetserver asp db: [quoted text, click to view]
This article has several methods.
http://www.aspfaq.com/show.asp?id=2174

I would opt for the "SELECT @@IDENTITY" option.

If you are using a recordset to add the data, then you should consider using
DML (data modification language - INSERT, UPDATE and DELETE statments)
instead. Recordsets, while offering benefits (such as handling concurrency
issues) when used for data modifications in desktop applications, have
little to no value in a server-based environment such as ASP. In fact,
especially with an Access backend, they can kill your application's
scalability.

If you are using dynamic sql, you should become aware of the security issues
involved in its use:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

Here are some more posts to read:

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

Using Command object to parameterize CommandText:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[quoted text, click to view]

Is company_id the autonumber field? if so, it should NOT be included in this
statement.

[quoted text, click to view]

No. Jet does not support batch execution. You can only execute one query at
a time.
I'm pretty sure this was covered in the KB article cited in the aspfaq
article ...
http://support.microsoft.com/default.aspx/kb/232144

Oh, i just looked at Aaron's article and I see where you got this idea. I'm
going to have to contact him about correcting this code snippet

[quoted text, click to view]
New recordset? you should not have used one in the first place. Never create
a recordset to run a query that does not return records.

[quoted text, click to view]

OK, I'm going to assume that company_id is not the autonumber field.

[quoted text, click to view]

No need for the column alias. See below:
[quoted text, click to view]

:-)
Bad. An insert statement returns no records. There is no need for a
recordset.

Simply do:
conn.execute SQL,,129 '129 = adCmdText + adExecuteNoRecords

followed by
set rs=conn.execute("select @@identity",,1) '1=adCmdText
newnumber= rs(0)
rs.close: set rs=nothing

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Hi all,

Newbie post here so apologies in advance....

I have a 'Call log' form that enters details of a job into an access
database (works a treat) and then redirects to a page that will enable you
to print those details onto a call sheet. In the database, the first field
is an ID field which is an auto number used as a Call number for the call
sheet. My question is, Is there a way of retrieving the call number for the
item just added to the DB before redirecting the page so that I can add the
call number to the call sheet?

Thanks in advance
Paul

[quoted text, click to view]

conn should be set like this:

set conn=createobject("adodb.connection")
conn.open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"DATA SOURCE=c:\inetpub\wwwroot\database\lancer.mdb"

Always create an explicit connection object. Never open recordsets using a
string. This practice can lead to memory leaks that may cause web server
failure.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Thanks for your help Bob.

I'm having a bit of trouble implementing your suggestion however, and =
get a return of 0 which is the incorrect value.

MY Insert statement is as follows, the values are taken from a recordset
Insert into Tab_call_log (company_id, Company, FName, SurName, =
contact_number, date_logged, logged_by, cstatus, call_details, =
call_severity) Values('PM051205001', 'AJT Smart', 'Adam', 'Taylor', =
'0129933 446789', '20/01/2006 13:04:03', 'Employee', '0', 'fgdg', =
'Severity');

Now I assumed that the select @@identity statement should go on the same =
insert line (i may be well off the mark here, I am new...) when the sql =
statement runs the error displays '(0x80040E14) Characters found after =
end of SQL statement.'=20

I then created a new recordset and sql statement, and added the select =
@@ statement as a second SQL string, so the whole thing now looks like =
this...
%>
conn =3D "PROVIDER=3DMicrosoft.Jet.OLEDB.4.0;DATA =
SOURCE=3Dc:\inetpub\wwwroot\database\lancer.mdb"
Set rs =3D Server.CreateObject("ADODB.Recordset")
%>

<%
SQL =3D "Insert into Tab_call_log (company_id, Company, FName, SurName, =
contact_number, date_logged, logged_by, cstatus, call_details, =
call_severity)" & " Values('" & cust_id & "', '" & Company & "', '" & =
FName & "', '" & surName & "', '" & telephone & "', '" & date_logged & =
"', '" & logged_by & "', '" & cstatus & "', '" & call_dets & "', '" & =
call_severity & "');"

SQL4 =3D "SELECT @@identity as call_num;"

rs.open SQL, conn, 2, 1
rs3.open SQL1, conn, 2, 1
%>

This returns the value of call_num to be 0.

I apologies for being stupid in advance, and would really appreciate =
your insight on this one.

Thanks again

Paul

[quoted text, click to view]
http://www.google.com/groups?hl=3Den&lr=3D&ie=3DUTF-8&oe=3DUTF-8&selm=3De=
6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
[quoted text, click to view]
http://groups.google.com/groups?hl=3Den&lr=3D&ie=3DUTF-8&c2coff=3D1&selm=3D=
eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
[quoted text, click to view]
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/ms=
g/72e36562fee7804e
[quoted text, click to view]