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

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 [at] [at] 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"

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 [at] [at] 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.'

I then created a new recordset and sql statement, and added the select =
[at] [at] 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 [at] [at] 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]" <reb01501 [at] NOyahoo.SPAMcom> wrote in message =
news:%23oIdsebHGHA.532 [at] 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
>
> I would opt for the "SELECT [at] [at] 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=3D23
>
> Here are some more posts to read:
>
> =
http://www.google.com/groups?hl=3Den&lr=3D&ie=3DUTF-8&oe=3DU TF-8&selm=3De=
6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> =
http://groups.google.com/groups?hl=3Den&lr=3D&ie=3DUTF-8&c2c off=3D1&selm=3D=
eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
> Using Command object to parameterize CommandText:
> =
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/ms=
g/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"
>
>
------=_NextPart_000_0009_01C61DC4.CEC28D50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Thanks for your help Bob.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I'm having a bit of trouble =
implementing your
suggestion however, and get a return of 0 which is the incorrect
value.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>MY Insert statement is as follows, the =
values are
taken from a recordset</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>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');</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Now I assumed that the select =
[at] [at] 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.' </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I then created a new recordset and sql =
statement,
and added the select [at] [at] statement as a second SQL string, so the whole =
thing now
looks like this...</FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>%></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>conn =3D
"PROVIDER=3DMicrosoft.Jet.OLEDB.4.0;DATA
SOURCE=3Dc:\inetpub\wwwroot\database\lancer.mdb"<BR>Set rs =3D
Server.CreateObject("ADODB.Recordset")</FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>%></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><%</FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>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 & "');"</FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT> </DIV>
<DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>SQL4 =3D "SELECT =
[at] [at] identity as
call_num;"</FONT></DIV></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>rs.open SQL, conn, 2, =
1</FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>rs3.open SQL1, conn, 2, =

1</FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>%></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>This returns the value of call_num to =
be
0.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I apologies for being stupid in =
advance, and would
really appreciate your insight on this one.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks again</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Paul</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>"Bob Barrows [MVP]" <</FONT><A
href=3D"mailto:reb01501 [at] NOyahoo.SPAMcom"><FONT face=3DArial
size=3D2>reb01501 [at] NOyahoo.SPAMcom</FONT></A><FONT face=3DArial =
size=3D2>> wrote in
message </FONT><A =
href=3D"news:%23oIdsebHGHA.532 [at] TK2MSFTNGP15.phx.gbl"><FONT
face=3DArial =
size=3D2>news:%23oIdsebHGHA.532 [at] TK2MSFTNGP15.phx.gbl</FONT></A><FONT
face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> =
Paul Malbon
wrote:<BR>>> Hi all,<BR>>><BR>>> Newbie post here so =
apologies
in advance....<BR>>><BR>>> I have a 'Call log' form that =
enters
details of a job into an access<BR>>> database (works a treat) and =
then
redirects to a page that will<BR>>> enable you to print those =
details onto
a call sheet. In the database,<BR>>> the first field is an ID =
field which
is an auto number used as a Call<BR>>> number for the call sheet. =
My
question is, Is there a way of<BR>>> retrieving the call number =
for the
item just added to the DB before<BR>>> redirecting the page so =
that I can
add the call number to the call<BR>>> sheet?<BR>> This article =
has
several methods.<BR>> </FONT><A
href=3D"http://www.aspfaq.com/show.asp?id=3D2174"><FONT face=3DArial
size=3D2>http://www.aspfaq.com/show.asp?id=3D2174</FONT></A><BR><FONT =
face=3DArial
size=3D2>> <BR>> I would opt for the "SELECT [at] [at] IDENTITY" =
option.<BR>>
<BR>> If you are using a recordset to add the data, then you should =
consider
using <BR>> DML (data modification language - INSERT, UPDATE and =
DELETE
statments) <BR>> instead. Recordsets, while offering benefits (such =
as
handling concurrency <BR>> issues) when used for data modifications =
in
desktop applications, have <BR>> little to no value in a server-based =

environment such as ASP. In fact, <BR>> especially with an Access =
backend,
they can kill your application's <BR>> scalability.<BR>> <BR>> =
If you
are using dynamic sql, you should become aware of the security issues =
<BR>>
involved in its use:<BR>> </FONT><A
href=3D"http://mvp.unixwiz.net/techtips/sql-injection.html"><FONT =
face=3DArial
size=3D2>http://mvp.unixwiz.net/techtips/sql-injection.html</FONT></A><BR=
><FONT
face=3DArial size=3D2>> </FONT><A
href=3D"http://www.sqlsecurity.com/DesktopDefault.aspx?tabid =3D23"><FONT =
face=3DArial
size=3D2>http://www.sqlsecurity.com/DesktopDefault.aspx?tabi d=3D23</FONT>=
</A><BR><FONT
face=3DArial size=3D2>> <BR>> Here are some more posts to =
read:<BR>>
<BR>> </FONT><A
href=3D"http://www.google.com/groups?hl=3Den&lr=3D&i e=3DUTF-8&=
;oe=3DUTF-8&selm=3De6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.g bl"><FONT
face=3DArial
size=3D2>http://www.google.com/groups?hl=3Den&lr=3D& ie=3DUTF-8&am=
p;oe=3DUTF-8&selm=3De6lLVvOcDHA.1204%40TK2MSFTNGP12.phx. gbl</FONT></A=
><BR><FONT
face=3DArial size=3D2>> <BR>> </FONT><A
href=3D"http://groups.google.com/groups?hl=3Den&lr=3D&am p;ie=3DUTF-8&=
amp;c2coff=3D1&selm=3DeHYxOyvaDHA.4020%40tk2msftngp13.ph x.gbl"><FONT =

face=3DArial
size=3D2>http://groups.google.com/groups?hl=3Den&lr=3D&a mp;ie=3DUTF-8=
&c2coff=3D1&selm=3DeHYxOyvaDHA.4020%40tk2msftngp13.p hx.gbl</FONT>=
</A><BR><FONT
face=3DArial size=3D2>> <BR>> Using Command object to parameterize =

CommandText:<BR>> </FONT><A
href=3D"http://groups-beta.google.com/group/microsoft.public .inetserver.a=
sp.db/msg/72e36562fee7804e"><FONT
face=3DArial
size=3D2>http://groups-beta.google.com/group/microsoft.publi c.inetserver.=
asp.db/msg/72e36562fee7804e</FONT></A><BR><FONT
face=3DArial size=3D2>> <BR>> Bob Barrows<BR>> -- <BR>> =
Microsoft MVP -
ASP/ASP.NET<BR>> Please reply to the newsgroup. This email account is =
my spam
trap so I<BR>> don't check it very often. If you must reply off-line, =
then
remove the<BR>> "NO SPAM" <BR>> <BR>></FONT></BODY></HTML>

------=_NextPart_000_0009_01C61DC4.CEC28D50--

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 [at] [at] 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 [at] [at] 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 [at] [at] 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 [at] [at] 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.

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 [at] [at] 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.
>
>

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.

BINGO!!!

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

Paul
"Bob Barrows [MVP]" <reb01501 [at] NOyahoo.SPAMcom> wrote in message
news:eK7Q9HeHGHA.2036 [at] 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.
>
>