By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,953 Members | 1,131 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,953 IT Pros & Developers. It's quick & easy.

SQL Server--when is identity field set?

P: n/a
Jan
Hi:

I'm working on my first SQL Server-backend application and am already
running into trouble. This is an application that has run successfully
with a Jet backend, and I'm starting out by trying to just use linked
SQL Server tables and not changing any of the front-end code.

The problem seems basic. In a table with an autonumber primary key
("TestUnitID") I'm in a module and adding a record. I'll also need to
add a record to a related table, so I need to have the primary key field
so the records will be linked. My normal procedure is as follows (air
code, but you get the idea):

rst is the recordset, strholdval is a variable
rst.addnew
rst!blah blah set the values of relevant fields
strholdval=rst!testunitID 'fails here in the new app
rst.update

With a Jet backend this works fine. But with the SQL Server backend, it
fails because testUnitID has no value. I tried just adding data to the
plain table and saw the same thing: an Access table creates a value for
the autonumber as soon as the record starts, but SQL Server seems only
to create it after the record is saved/updated. But after the
rst.update line, I'm no longer on the record and thus I can't find out
what the testUnitID is.

THere's a simple answer here, I'm sure, but I can't find it!

Thanks.

Jan
Aug 10 '06 #1
Share this Question
Share on Google+
13 Replies


P: n/a
On Thu, 10 Aug 2006 15:25:21 -0500, Jan <ja*@dontspamme.comwrote:

rst.addnew
rst!blah blah set the values of relevant fields
rst.update
rst.Move 0, rst.LastUpdated
strholdval=rst!testunitID 'fails here in the new app

-Tom.

>Hi:

I'm working on my first SQL Server-backend application and am already
running into trouble. This is an application that has run successfully
with a Jet backend, and I'm starting out by trying to just use linked
SQL Server tables and not changing any of the front-end code.

The problem seems basic. In a table with an autonumber primary key
("TestUnitID") I'm in a module and adding a record. I'll also need to
add a record to a related table, so I need to have the primary key field
so the records will be linked. My normal procedure is as follows (air
code, but you get the idea):

rst is the recordset, strholdval is a variable
rst.addnew
rst!blah blah set the values of relevant fields
strholdval=rst!testunitID 'fails here in the new app
rst.update

With a Jet backend this works fine. But with the SQL Server backend, it
fails because testUnitID has no value. I tried just adding data to the
plain table and saw the same thing: an Access table creates a value for
the autonumber as soon as the record starts, but SQL Server seems only
to create it after the record is saved/updated. But after the
rst.update line, I'm no longer on the record and thus I can't find out
what the testUnitID is.

THere's a simple answer here, I'm sure, but I can't find it!

Thanks.

Jan
Aug 11 '06 #2

P: n/a
Jan <ja*@dontspamme.comwrote in news:12dn5hjhsl8qbe8
@corp.supernews.com:
There's a simple answer here, I'm sure, but I can't find it!
This is pretty simple, IMO. Will you like it? I doubt it. Oh well, such
is life.

' a stored preocedure

ALTER PROCEDURE [dbo].[spInsertBulgariaAccount]
@CommonName varchar(50),
@FormalDescription varchar(255)=NULL,
@IncludeInSummary bit=NULL,
@Identity int OUTPUT
AS
DECLARE @TRIMCommonName varchar(50)
SET @TRIMCommonName=LTRIM(RTRIM(@CommonName))

IF
(
LEN(@TRIMCommonName) 0
AND @TRIMCommonName IS NOT NULL
AND NOT EXISTS (SELECT * FROM BulgariaAccounts WHERE
CommonName=@TRIMCommonName)
)

INSERT INTO BulgariaAccounts
(
CommonName,
FormalDescription,
IncludeInSummary
)
VALUES
(
@TRIMCommonName,
@FormalDescription,
@IncludeInSummary
)
SET @Identity=@@Identity
Return
' using the stored procedure to add a record
Dim c As ADODB.Connection
Dim m As ADODB.Command
Dim p As ADODB.Parameter
Set c = New ADODB.Connection
c.Open "PROVIDER=SQLOLEDB.1" _
& ";INITIAL CATALOG=MyDatabase" _
& ";DATA SOURCE=MyServer" _
& ";USER ID=MyId" _
& ";PASSWORD=MyPassword"
Set m = New ADODB.Command
With m
..ActiveConnection = c
..CommandType = adCmdStoredProc
..CommandText = "spInsertBulgariaAccount"
Set p = .CreateParameter("@CommonName", adBSTR, adParamInput, 30,
"Temporary1")
..Parameters.Append p
Set p = .CreateParameter("@FormalDescription", adBSTR, adParamInput, 255,
Null)
..Parameters.Append p
Set p = .CreateParameter("@IncludeInSummary", adTinyInt, adParamInput, ,
Null)
..Parameters.Append p
Set p = .CreateParameter("@Identity", adInteger, adParamOutput)
..Parameters.Append p
..Execute
Debug.Print "You entered a new Record with ID: " & .Parameters
("@Identity")
End With
End Sub

' getting the new identity number
'You entered a new Record with ID: 11

--
Lyle Fairfield
Aug 11 '06 #3

P: n/a
Jan
Thanks, Tom! I'm sure it will work, and in fact will go test it as soon
as I'm done writing this (and let you know if there are problems).

One question, though: Will that still work if many people are entering
data at the same time? Will the lastupdated still be mine if someone
else is doing it too? I never know how "micro" the microseconds are in
a case like this.

Jan

Tom van Stiphout wrote:
On Thu, 10 Aug 2006 15:25:21 -0500, Jan <ja*@dontspamme.comwrote:

rst.addnew rst!blah blah set the values of relevant fields rst.update
rst.Move 0, rst.LastUpdated strholdval=rst!testunitID 'fails here
in the new app

-Tom.
>Hi:

I'm working on my first SQL Server-backend application and am
already running into trouble. This is an application that has run
successfully with a Jet backend, and I'm starting out by trying to
just use linked SQL Server tables and not changing any of the
front-end code.

The problem seems basic. In a table with an autonumber primary key
("TestUnitID") I'm in a module and adding a record. I'll also
need to add a record to a related table, so I need to have the
primary key field so the records will be linked. My normal
procedure is as follows (air code, but you get the idea):

rst is the recordset, strholdval is a variable rst.addnew rst!blah
blah set the values of relevant fields strholdval=rst!testunitID
'fails here in the new app rst.update

With a Jet backend this works fine. But with the SQL Server
backend, it fails because testUnitID has no value. I tried just
adding data to the plain table and saw the same thing: an Access
table creates a value for the autonumber as soon as the record
starts, but SQL Server seems only to create it after the record is
saved/updated. But after the rst.update line, I'm no longer on the
record and thus I can't find out what the testUnitID is.

THere's a simple answer here, I'm sure, but I can't find it!

Thanks.

Jan

Aug 11 '06 #4

P: n/a
Jan
Hi, Tom:

I take it back; it didn't work! Failed on the rst.lastupdated, with the
error "operation is not supported for this type of object." And, in
fact, when I look up lastupdated, it seems to return the date the
recordset was last updated, which of course isn't what I want.

If I just do Rst.move 0 I'm back to "no current record", so clearly that
doesn't work alone.

What am I missing?

Jan

Tom van Stiphout wrote:
On Thu, 10 Aug 2006 15:25:21 -0500, Jan <ja*@dontspamme.comwrote:

rst.addnew
rst!blah blah set the values of relevant fields
rst.update
rst.Move 0, rst.LastUpdated
strholdval=rst!testunitID 'fails here in the new app

-Tom.
>>Hi:

I'm working on my first SQL Server-backend application and am already
running into trouble. This is an application that has run successfully
with a Jet backend, and I'm starting out by trying to just use linked
SQL Server tables and not changing any of the front-end code.

The problem seems basic. In a table with an autonumber primary key
("TestUnitID") I'm in a module and adding a record. I'll also need to
add a record to a related table, so I need to have the primary key field
so the records will be linked. My normal procedure is as follows (air
code, but you get the idea):

rst is the recordset, strholdval is a variable
rst.addnew
rst!blah blah set the values of relevant fields
strholdval=rst!testunitID 'fails here in the new app
rst.update

With a Jet backend this works fine. But with the SQL Server backend, it
fails because testUnitID has no value. I tried just adding data to the
plain table and saw the same thing: an Access table creates a value for
the autonumber as soon as the record starts, but SQL Server seems only
to create it after the record is saved/updated. But after the
rst.update line, I'm no longer on the record and thus I can't find out
what the testUnitID is.

THere's a simple answer here, I'm sure, but I can't find it!

Thanks.

Jan

Aug 11 '06 #5

P: n/a
"Jan" <ja*@dontspamme.comwrote in message
<12*************@corp.supernews.com>:
Hi, Tom:

I take it back; it didn't work! Failed on the rst.lastupdated, with
the error "operation is not supported for this type of object." And,
in fact, when I look up lastupdated, it seems to return the date the
recordset was last updated, which of course isn't what I want.

If I just do Rst.move 0 I'm back to "no current record", so clearly
that doesn't work alone.

What am I missing?
If you're using DAO, try the .LastModified property.

--
Roy-Vidar
Aug 11 '06 #6

P: n/a
Jan
That did it! Thanks a million.

I'm sure I'll be back with the next error; got to get this working by
next Friday....

Jan

RoyVidar wrote:
"Jan" <ja*@dontspamme.comwrote in message
<12*************@corp.supernews.com>:
>Hi, Tom:

I take it back; it didn't work! Failed on the rst.lastupdated, with
the error "operation is not supported for this type of object." And,
in fact, when I look up lastupdated, it seems to return the date the
recordset was last updated, which of course isn't what I want.

If I just do Rst.move 0 I'm back to "no current record", so clearly
that doesn't work alone.

What am I missing?


If you're using DAO, try the .LastModified property.
Aug 11 '06 #7

P: n/a
Jan
Followup question (your suggestion worked; see other post)

Question still remains, though; will this work when several people are
working at once? Will the lastmodified reliably be mine or could it be
the next person's?

Jan

RoyVidar wrote:
"Jan" <ja*@dontspamme.comwrote in message
<12*************@corp.supernews.com>:
>Hi, Tom:

I take it back; it didn't work! Failed on the rst.lastupdated,
with the error "operation is not supported for this type of
object." And, in fact, when I look up lastupdated, it seems to
return the date the recordset was last updated, which of course
isn't what I want.

If I just do Rst.move 0 I'm back to "no current record", so clearly
that doesn't work alone.

What am I missing?


If you're using DAO, try the .LastModified property.
Aug 11 '06 #8

P: n/a
Jan
Thanks, Lyle.

As you suspected, I'm going for "few changes" first, but I'm saving your
suggestion for whenever I bite the bullet and do it the "complete" (I
was tempted to say "right") way.

Jan

Lyle Fairfield wrote:
Jan <ja*@dontspamme.comwrote in news:12dn5hjhsl8qbe8
@corp.supernews.com:

>There's a simple answer here, I'm sure, but I can't find it!


This is pretty simple, IMO. Will you like it? I doubt it. Oh well,
such is life.

' a stored preocedure

ALTER PROCEDURE [dbo].[spInsertBulgariaAccount] @CommonName
varchar(50), @FormalDescription varchar(255)=NULL, @IncludeInSummary
bit=NULL, @Identity int OUTPUT AS DECLARE @TRIMCommonName varchar(50)
SET @TRIMCommonName=LTRIM(RTRIM(@CommonName))

IF ( LEN(@TRIMCommonName) 0 AND @TRIMCommonName IS NOT NULL AND NOT
EXISTS (SELECT * FROM BulgariaAccounts WHERE
CommonName=@TRIMCommonName) )

INSERT INTO BulgariaAccounts ( CommonName, FormalDescription,
IncludeInSummary ) VALUES ( @TRIMCommonName, @FormalDescription,
@IncludeInSummary ) SET @Identity=@@Identity Return
' using the stored procedure to add a record Dim c As
ADODB.Connection Dim m As ADODB.Command Dim p As ADODB.Parameter Set
c = New ADODB.Connection c.Open "PROVIDER=SQLOLEDB.1" _ & ";INITIAL
CATALOG=MyDatabase" _ & ";DATA SOURCE=MyServer" _ & ";USER ID=MyId" _
& ";PASSWORD=MyPassword" Set m = New ADODB.Command With m
.ActiveConnection = c .CommandType = adCmdStoredProc .CommandText =
"spInsertBulgariaAccount" Set p = .CreateParameter("@CommonName",
adBSTR, adParamInput, 30, "Temporary1") .Parameters.Append p Set p =
.CreateParameter("@FormalDescription", adBSTR, adParamInput, 255,
Null) .Parameters.Append p Set p =
.CreateParameter("@IncludeInSummary", adTinyInt, adParamInput, ,
Null) .Parameters.Append p Set p = .CreateParameter("@Identity",
adInteger, adParamOutput) .Parameters.Append p .Execute Debug.Print
"You entered a new Record with ID: " & .Parameters ("@Identity") End
With End Sub

' getting the new identity number 'You entered a new Record with ID:
11
Aug 11 '06 #9

P: n/a
Most of my JET code is like:

dim rstRec as DAO.RecordSet
dim lngNewID as long
set rstRec = currentdb.OpenRecordSet("yourtable")

rstRec.AddNew
rstRec!City = "Edmonton"
lngNewID = rstRec!ID
rstRec.Update
rstRec.Close
set RstRec = Nothing

At this point, lngNewID is set to the last id created.

When using sql server, you have to force the update, so, all my code (which
works both for JET, and sql server) is now:

rstRec.AddNew
rstRec!City = "Edmonton"
rstRec.Update
rstRec.BookMark = rstRec.LastUpdated
lngNewID = rstRec!ID
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Aug 11 '06 #10

P: n/a
Jan
Hi, Albert:

Thanks for the response. This approach seems to work for me, too, but I
still wonder about the multi-user environment (I'm testing here on my
standalone); will lastupdated for sure be MY last updated, and not
someone else's?

Jan

Albert D. Kallal wrote:
Most of my JET code is like:

dim rstRec as DAO.RecordSet dim lngNewID as long set
rstRec = currentdb.OpenRecordSet("yourtable")

rstRec.AddNew rstRec!City = "Edmonton" lngNewID = rstRec!ID
rstRec.Update rstRec.Close set RstRec = Nothing

At this point, lngNewID is set to the last id created.

When using sql server, you have to force the update, so, all my code
(which works both for JET, and sql server) is now:

rstRec.AddNew rstRec!City = "Edmonton" rstRec.Update rstRec.BookMark
= rstRec.LastUpdated lngNewID = rstRec!ID

Aug 12 '06 #11

P: n/a
"Jan" <ja*@dontspamme.comwrote in message
news:12*************@corp.supernews.com...
Hi, Albert:

Thanks for the response. This approach seems to work for me, too, but I
still wonder about the multi-user environment (I'm testing here on my
standalone); will lastupdated for sure be MY last updated, and not someone
else's?
The code is running on YOUR machine...so, the last updated book mark is
simply that..a book mark to the last record you updated.

This issue actually has NO relation to the last identify field in sql
server. We simply are moving the record pointer to the last record we
updated, and we are using a book mark to do this. This book mark is created
on your local computer....

If you execute a "addnew", you get a new record into your reocrdset.....all
other users WILL NOT EVEN see that record in their reocrdset unless they
requery, or re-load their recordsets....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Aug 12 '06 #12

P: n/a
Albert D. Kallal wrote:
"Jan" <ja*@dontspamme.comwrote in message
news:12*************@corp.supernews.com...
Hi, Albert:

Thanks for the response. This approach seems to work for me, too, but I
still wonder about the multi-user environment (I'm testing here on my
standalone); will lastupdated for sure be MY last updated, and not someone
else's?

The code is running on YOUR machine...so, the last updated book mark is
simply that..a book mark to the last record you updated.

This issue actually has NO relation to the last identify field in sql
server. We simply are moving the record pointer to the last record we
updated, and we are using a book mark to do this. This book mark is created
on your local computer....

If you execute a "addnew", you get a new record into your reocrdset.....all
other users WILL NOT EVEN see that record in their reocrdset unless they
requery, or re-load their recordsets....
IMO, it's very bad form to open a recordset to update a record, or
records. Just the bother of closing the recordset and setting it to
nothing is sufficient for thinking so, but there may also be concerns
of speed, use of resources and, of course, danger of something going
wrong when that Recordset is open.
Executing an SQL DDL statements will almost always be the better way.

Aug 12 '06 #13

P: n/a

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
Executing an SQL DDL statements will almost always be the better way.
Ah...yes, I would have not only agree, but addition, we are talking about
sql server here!!...so, what you suggest is
even more SO.....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Aug 13 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.