SQL Server - 何时设置标识字段？ [英] SQL Server--when is identity field set?

问题描述

嗨：


我正在开发我的第一个SQL Server后端应用程序，而且我已经遇到了麻烦。这是一个使用Jet后端成功运行的应用程序

，我开始尝试使用链接的

SQL Server表而不是更改任何前端代码。


问题似乎很基本。在一个包含自动编号主键的表格中，我在一个模块中并添加了一条记录，其中包含一个自动编号主键

（TestUnitID）。我还需要

将记录添加到相关表中，因此我需要拥有主键字段

所以记录将被链接。我的正常程序如下（空气

代码，但你明白了）：


rst是记录集，strholdval是变量

rst.addnew

rst！blah blah设置相关字段的值

strholdval = rst！testunitID''在新应用程序中失败

rst.update


使用Jet后端这很好用。但是使用SQL Server后端，因为testUnitID没有值，所以
会失败。我尝试将数据添加到

普通表并看到同样的事情：一旦Access记录开始，Access表就会为自动编号创建一个值，但是SQL Server保存/更新记录后，创建它似乎只需要
。但是在

rst.update行之后，我不再记录在案了，因此我找不到

testUnitID是什么。


这里有一个简单的答案，我很有信心，但我找不到了！


谢谢。


Jan

解决方案

2006年8月10日星期四15:25:21 -0500，Jan< ja * @ dontspamme.comwrote：


rst.addnew

rst！blah blah设置相关字段的值

rst.update

rst.Move 0，rst.LastUpdated

strholdval = rst！testunitID''在新应用程序中失败


-Tom。

>嗨：

我正在研究我的第一个SQL Server后端应用程序，我已经
问题似乎很基本。在具有自动编号主键的表格中（TestUnitID）我在模块中并添加记录。我还需要将记录添加到相关表中，因此我需要拥有主键字段
以便记录链接。我的正常程序如下（空气代码，但你明白了）：

首先是记录集，strholdval是变量
rst.addnew
rst ！blah blah设置相关字段的值
strholdval = rst！testunitID''在新应用程序中失败
rst.update

使用Jet后端这很好用。但是使用SQL Server后端，它会失败，因为testUnitID没有值。我尝试将数据添加到
普通表并看到同样的事情：一旦记录开始，Access表就会为自动编号创建一个值，但SQL Server似乎只能创建记录保存/更新后。但是在第一次更新之后，我不再记录在案了，因此我无法找到testUnitID是什么。

这是''这是一个简单的答案，我很有信心，但我找不到了！

谢谢。

Jan

Jan< ja *@dontspamme.com在新闻中写道：12dn5hjhsl8qbe8

@ corp.supernews.com：

这里有一个简单的答案，我很有信心，但我找不到它！

这很简单，IMO。你喜欢吗？我对此表示怀疑。哦，好吧，这样的

就是生活。


''存储的预处理


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


如果

（

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 = @@身份

返回

' '使用存储过程添加记录

Dim c As ADODB.Connection

Dim m As ADODB.Command

Dim p As ADODB。参数

设置c =新ADODB.Connection

c.Open" PROVIDER = SQLOLEDB.1" _

& " ;; INITIAL CATALOG = MyDatabase" _

& " ;; DATA SOURCE = MyServer" _

& " ;; USER ID = MyId" _

& " ;; PASSWORD = MyPassword"

设置m =新ADODB.Command

用m

..ActiveConnection = c

..CommandType = adCmdStoredProc

..CommandText =" spInsertBulgariaAccount"

设置p = .CreateParameter（" @ CommonName"，adBSTR，adParamInput，30，

" Temporary1"）

..Parameters.Append p

设置p = .CreateParameter（" @ FormalDescription"，adBSTR，adParamInput ，255，

Null）

..Parameters.Append p

设置p = .CreateParameter（" @ IncludeInSummary"，adTinyInt，adParamInput ,,,

Null）

..Parameters.Append p

设置p = .CreateParameter（" @ Identity"，adInteger，adParamOutput）

..Parameters.Append p

..Execute

Debug.Print"您输入了一个带ID的新记录：" &安培; .Parameters

（@ Identity）

结束

结束子


' '获得新的身份证号码

''您输入了一张ID为新的记录：11


-

Lyle Fairfield

谢谢，汤姆！我相信它会起作用，事实上它会很快进行测试

因为我写完这个（如果有问题，请告诉你）。


但有一个问题：如果很多人同时输入

数据，这仍然有效吗？如果有人

其他人也这样做，那么上次更新仍然是我的吗？我从来不知道微是怎么回事微秒是这样的。
a这样的情况。


Jan


Tom van Stiphout写道：

On Thu，2006年8月10日15:25:21 -0500，Jan< ja * @ dontspamme.comwrote：


rst。 addnew rst！blah blah设置相关字段的值rst.update

rst.Move 0，rst.LastUpdated strholdval = rst！testunitID''在这里失败

in new app


-Tom。

>嗨：

我正在努力我的第一个SQL Server后端应用程序并且已经遇到了麻烦。这是一个使用Jet后端成功运行的应用程序，我开始试图只使用链接的SQL Server表而不是更改任何前端代码。

问题似乎很基本。在具有自动编号主键的表格中（TestUnitID）我在模块中并添加记录。我还需要将记录添加到相关表中，因此我需要具有
主键字段，以便将记录链接起来。我的正常程序如下（航空代码，但你明白了）：

首先是记录集，strholdval是变量rst.addnew rst！blah
blah set相关字段的值strholdval = rst！testunitID
''在新的应用程序中失败rst.update

使用Jet后端这很好用。但是使用SQL Server
后端，它失败了，因为testUnitID没有值。我尝试将数据添加到普通表并看到同样的事情：一旦记录启动，Access
表就会为自动编号创建一个值，但SQL Server似乎只是创建保存/更新记录后。但是在rst.update之后，我不再在
记录上了，因此我无法找到testUnitID是什么。

这里只是一个简单的答案，我很有信心，但我找不到了！

谢谢。

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

解决方案

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

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

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

这篇关于SQL Server - 何时设置标识字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！