@@IDENTITY is 0 ?

[A Ratcliffe]

I've hit a problem with INSERT and @@IDENTITY for Access that is driving me
up the wall. I'm using the OLEDB Jet4.0 in my connection string, so
@@IDENTITY is supported. I hope you might be able to help.

The basic situation...

Tables:

Company
-------------
cmp_id ReplicationID (autonumber) (PK)
cmp_name Text(50)
.....

QuizInfo
------------
quiz_id ReplicationID (autonumber) (PK)
quiz_title Text(100)
.....

CompanyQuizLink
--------------------------
cmp_id ReplicationID
quiz_id ReplicationID

Some of you might comment on my using ReplicationID's for the primaries, but
there is a reason.

I already have the Company Id of the user, from when they logged in. They
add a new quiz. I INSERT using SQL in a cnn.Execute...

INSERT INTO QuizInfo (quiz_title) VALUES ('This was the title the user
entered')

The INSERT works, I can check the DB and see it.

Immediately after the INSERT, following information in various web-sites, I
now execute the following:-

Set rsNewId = cnn.Execute("SELECT @@IDENTITY")
TheNewId = rsNewId(0)

Unfortunately, TheNewId contains 0 (zero).

I haven't had any other problems using the ReplicationID. I treat it as a
string, and it works out pretty well (looks just like a a GUID from GUIDGEN,
well it is a GUID after all).

Thanks in advance for any help you can provide. This is getting urgent. I've
been scouring the web, and this seems the right technique to do this.

Yours,

Ann-Marie Ratcliffe

[Bob Barrows]

A Ratcliffe wrote:

> I've hit a problem with INSERT and @@IDENTITY for Access that is
> driving me up the wall. I'm using the OLEDB Jet4.0 in my connection
> string, so @@IDENTITY is supported. I hope you might be able to help.
>
> The basic situation...
>
> Tables:
>
> Company
> -------------
> cmp_id ReplicationID (autonumber) (PK)
> cmp_name Text(50)
> ....
>
> QuizInfo
> ------------
> quiz_id ReplicationID (autonumber) (PK)
> quiz_title Text(100)
> ....
>
> CompanyQuizLink
> --------------------------
> cmp_id ReplicationID
> quiz_id ReplicationID
>
> Some of you might comment on my using ReplicationID's for the
> primaries, but there is a reason.
>
> I already have the Company Id of the user, from when they logged in.
> They add a new quiz. I INSERT using SQL in a cnn.Execute...
>
> INSERT INTO QuizInfo (quiz_title) VALUES ('This was the title the user
> entered')
>
> The INSERT works, I can check the DB and see it.
>
> Immediately after the INSERT, following information in various
> web-sites, I now execute the following:-
>
> Set rsNewId = cnn.Execute("SELECT @@IDENTITY")
> TheNewId = rsNewId(0)
>
> Unfortunately, TheNewId contains 0 (zero).
>
> I haven't had any other problems using the ReplicationID. I treat it
> as a string, and it works out pretty well (looks just like a a GUID
> from GUIDGEN, well it is a GUID after all).
>
> Thanks in advance for any help you can provide. This is getting
> urgent. I've been scouring the web, and this seems the right
> technique to do this.
>
> Yours,
>
> Ann-Marie Ratcliffe

I cannot reproduce your problem. Here is the code I used in my attempt. Are
you doing something different?

<%
Option Explicit
dim cn,lRecsInserted, sSQL, newID,rs
set cn=server.CreateObject("adodb.connection")

cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
server.MapPath("db7.mdb")

'tblBlogs has 3 fields: ID-autonumber,body-Text, Links-Text
sSQL = "INSERT INTO tblBlogs(body) " &_
"VALUES('test25')"
cn.Execute sSQL,lRecsInserted,129
'129=adCmdText(1) + adExecuteNoRecords(128)

if lRecsInserted > 0 then
Response.Write lRecs & " record was inserted<BR>"
set rs= cn.Execute("Select @@IDENTITY",,1)
'1=adCmdText(1)
newID = rs(0).value
Response.Write "The ID was " & newid & "<BR>"
rs.close
set rs=nothing
end if
cn.Close
set cn=nothing
%>


HTH,
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"

[A Ratcliffe]

I have the same problem running your code sample. Is your autonumber a
ReplicationID type?

Yours,

Ann-Marie Ratcliffe

P.S.

> Response.Write lRecs & " record was inserted<BR>"

should be

> Response.Write lRecsInserted & " record was inserted<BR>"

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OgQ2ekImDHA.976@tk2msftngp13.phx.gbl...

>
> I cannot reproduce your problem. Here is the code I used in my attempt.

Are

> you doing something different?
>
> <%
> Option Explicit
> dim cn,lRecsInserted, sSQL, newID,rs
> set cn=server.CreateObject("adodb.connection")
>
> cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
> server.MapPath("db7.mdb")
>
> 'tblBlogs has 3 fields: ID-autonumber,body-Text, Links-Text
> sSQL = "INSERT INTO tblBlogs(body) " &_
> "VALUES('test25')"
> cn.Execute sSQL,lRecsInserted,129
> '129=adCmdText(1) + adExecuteNoRecords(128)
>
> if lRecsInserted > 0 then
> Response.Write lRecs & " record was inserted<BR>"
> set rs= cn.Execute("Select @@IDENTITY",,1)
> '1=adCmdText(1)
> newID = rs(0).value
> Response.Write "The ID was " & newid & "<BR>"
> rs.close
> set rs=nothing
> end if
> cn.Close
> set cn=nothing
> %>
>
>
> HTH,
> 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"
>
>

[A Ratcliffe]

Searching more web-sites, it looks like the rs.AddNew...rs.Update route
might work, but I'd rather not if I can help it. I've used SQL executes for
everything else, would be a pain to have to drop into that archaic method
just for this.

I could 'fake' it temporarily of course. There is a datetime field, set to
Now() when the record is created, I could always do a SELECT on that, and
hopefully it would the record just inserted, but its a hack at best.

Yours,

Ann-Marie

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OgQ2ekImDHA.976@tk2msftngp13.phx.gbl...

> A Ratcliffe wrote:

> > I've hit a problem with INSERT and @@IDENTITY for Access that is
> > driving me up the wall. I'm using the OLEDB Jet4.0 in my connection
> > string, so @@IDENTITY is supported. I hope you might be able to help.
> >
> > The basic situation...
> >
> > Tables:
> >
> > Company
> > -------------
> > cmp_id ReplicationID (autonumber) (PK)
> > cmp_name Text(50)
> > ....
> >
> > QuizInfo
> > ------------
> > quiz_id ReplicationID (autonumber) (PK)
> > quiz_title Text(100)
> > ....
> >
> > CompanyQuizLink
> > --------------------------
> > cmp_id ReplicationID
> > quiz_id ReplicationID
> >
> > Some of you might comment on my using ReplicationID's for the
> > primaries, but there is a reason.
> >
> > I already have the Company Id of the user, from when they logged in.
> > They add a new quiz. I INSERT using SQL in a cnn.Execute...
> >
> > INSERT INTO QuizInfo (quiz_title) VALUES ('This was the title the user
> > entered')
> >
> > The INSERT works, I can check the DB and see it.
> >
> > Immediately after the INSERT, following information in various
> > web-sites, I now execute the following:-
> >
> > Set rsNewId = cnn.Execute("SELECT @@IDENTITY")
> > TheNewId = rsNewId(0)
> >
> > Unfortunately, TheNewId contains 0 (zero).
> >
> > I haven't had any other problems using the ReplicationID. I treat it
> > as a string, and it works out pretty well (looks just like a a GUID
> > from GUIDGEN, well it is a GUID after all).
> >
> > Thanks in advance for any help you can provide. This is getting
> > urgent. I've been scouring the web, and this seems the right
> > technique to do this.
> >
> > Yours,
> >
> > Ann-Marie Ratcliffe

>
> I cannot reproduce your problem. Here is the code I used in my attempt.

Are

> you doing something different?
>
> <%
> Option Explicit
> dim cn,lRecsInserted, sSQL, newID,rs
> set cn=server.CreateObject("adodb.connection")
>
> cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
> server.MapPath("db7.mdb")
>
> 'tblBlogs has 3 fields: ID-autonumber,body-Text, Links-Text
> sSQL = "INSERT INTO tblBlogs(body) " &_
> "VALUES('test25')"
> cn.Execute sSQL,lRecsInserted,129
> '129=adCmdText(1) + adExecuteNoRecords(128)
>
> if lRecsInserted > 0 then
> Response.Write lRecs & " record was inserted<BR>"
> set rs= cn.Execute("Select @@IDENTITY",,1)
> '1=adCmdText(1)
> newID = rs(0).value
> Response.Write "The ID was " & newid & "<BR>"
> rs.close
> set rs=nothing
> end if
> cn.Close
> set cn=nothing
> %>
>
>
> HTH,
> 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"
>
>

[Bob Barrows]

A Ratcliffe wrote:

> I have the same problem running your code sample. Is your autonumber a
> ReplicationID type?

Sorry, I missed that it was a ReplicationID. I do not think @@IDENTITY will
return a GUID. I suspect you are going to be stuck using a recordset to do
your inserts.

>
> Yours,
>
> Ann-Marie Ratcliffe
>
> P.S.

>> Response.Write lRecs & " record was inserted<BR>"

> should be

>> Response.Write lRecsInserted & " record was inserted<BR>"

>

Oops - I changed the name of the variable to make its purpose clearer. I
forgot to change it here.

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"

[Bob Barrows]

A Ratcliffe wrote:

> Searching more web-sites, it looks like the rs.AddNew...rs.Update
> route might work, but I'd rather not if I can help it. I've used SQL
> executes for everything else, would be a pain to have to drop into
> that archaic method just for this.

You might consider not using an autonumber field. You could use this code to
generate your own GUID which you can then insert into a Text field in your
database:
guid = left(createobject("scriptlet.typelib").guid,38)

>
> I could 'fake' it temporarily of course. There is a datetime field,
> set to Now() when the record is created, I could always do a SELECT
> on that, and hopefully it would the record just inserted, but its a
> hack at best.
>

Absolutely. It will not work in a multi-user environment. Oh, you may get
away with it for awhile, but there will come a day when two users insert
records simultaneously and ...

HTH,
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"

[A Ratcliffe]

I wish I'd discovered that little gem a long time ago. Time to change the
code to take advantage of it I think.

Thanks for all your help,

Yours,

Ann-Marie

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OJ1lDWJmDHA.1960@TK2MSFTNGP12.phx.gbl...

> A Ratcliffe wrote:

> > Searching more web-sites, it looks like the rs.AddNew...rs.Update
> > route might work, but I'd rather not if I can help it. I've used SQL
> > executes for everything else, would be a pain to have to drop into
> > that archaic method just for this.

>
> You might consider not using an autonumber field. You could use this code

to

> generate your own GUID which you can then insert into a Text field in your
> database:
> guid = left(createobject("scriptlet.typelib").guid,38)
>
>

> >
> > I could 'fake' it temporarily of course. There is a datetime field,
> > set to Now() when the record is created, I could always do a SELECT
> > on that, and hopefully it would the record just inserted, but its a
> > hack at best.
> >

> Absolutely. It will not work in a multi-user environment. Oh, you may get
> away with it for awhile, but there will come a day when two users insert
> records simultaneously and ...
>
> HTH,
> 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"
>
>