newbie select last item entered to a DB question

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

Re: newbie select last item entered to a DB question

Paul Malbon wrote:
> 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?
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&sel m=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.inetser ver.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"

Re: newbie select last item entered to a DB question

This is a multi-part message in MIME format.

------=_NextPart_000_0009_01C61DC4.CEC28D50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

"Bob Barrows [MVP]" wrote in message =
news:%23oIdsebHGHA.532@TK2MSFTNGP15.phx.gbl...
> Paul Malbon wrote:
>> 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?
> This article has several methods.
> http://www.aspfaq.com/show.asp?id=3D2174
>=20
> I would opt for the "SELECT @@IDENTITY" option.
>=20
> If you are using a recordset to add the data, then you should consider =
using=20
> DML (data modification language - INSERT, UPDATE and DELETE statments) =

> instead. Recordsets, while offering benefits (such as handling =
concurrency=20
> 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=20
> scalability.
>=20
> If you are using dynamic sql, you should become aware of the security =
issues=20
> involved in its use:
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=3D23
>=20
> Here are some more posts to read:
>=20
> =
http://www.google.com/groups?hl=3Den&lr=3D&ie=3DUTF-8&oe=3DU TF-8&selm=3De=
6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>=20
> =
http://groups.google.com/groups?hl=3Den&lr=3D&ie=3DUTF-8&c2c off=3D1&selm=3D=
eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>=20
> Using Command object to parameterize CommandText:
> =
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/ms=
g/72e36562fee7804e
>=20
> Bob Barrows
> --=20
> 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"=20
>=20
>
------=_NextPart_000_0009_01C61DC4.CEC28D50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: newbie select last item entered to a DB question

Paul Malbon wrote:
> 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,

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

> 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...)

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

> when
> the sql statement runs the error displays '(0x80040E14) Characters
> found after end of SQL statement.'

>
> I then created a new recordset
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.

> and sql statement, and added the
> select @@ statement as a second SQL string, so the whole thing now
> looks like this...
> %>
> conn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> SOURCE=c:\inetpub\wwwroot\database\lancer.mdb"
> Set rs = Server.CreateObject("ADODB.Recordset")
> %>
>
> <%
> SQL = "Insert into Tab_call_log (company_id, Company, FName, SurName,

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

> 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 = "SELECT @@identity as call_num;"

No need for the column alias. See below:
>
> rs.open SQL, conn, 2, 1

:-)
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.

Re: newbie select last item entered to a DB question

mmmmmmm.... I now get "(0x800A01A8) Object required: 'conn'"

Conn is declared and set to
conn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=c:\inetpub\wwwroot\database\lancer.mdb"

which is the connection I use for all of my data manipulation. What I have I
done now? Any ideas?

Sorry and thanks again

Paul

>
> 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.
>
>

Re: newbie select last item entered to a DB question

Paul Malbon wrote:
> mmmmmmm.... I now get "(0x800A01A8) Object required: 'conn'"
>
> Conn is declared and set to
> conn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> SOURCE=c:\inetpub\wwwroot\database\lancer.mdb"

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.

Re: newbie select last item entered to a DB question

BINGO!!!

Got it, and it works!! Thanks very much Bob, sorry for being such a drain on
you!!

Paul
"Bob Barrows [MVP]" wrote in message
news:eK7Q9HeHGHA.2036@TK2MSFTNGP14.phx.gbl...
> Paul Malbon wrote:
>> mmmmmmm.... I now get "(0x800A01A8) Object required: 'conn'"
>>
>> Conn is declared and set to
>> conn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
>> SOURCE=c:\inetpub\wwwroot\database\lancer.mdb"
>
> 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.
>
>