Often you need to add an item to a table and return the id (autonumber) value of the new entry.
You can use SQL Server's identity column feature for the underlying structure:
data base unlimited
| Services | Products | Code |
Home
Up
SQL Problems
Correlation
Temp tables
Denormalisation
Semi-dynamic
UK Post Codes
SQL7 SP2
SQL Increment
SQL stored procedures that return an incremented number
CREATE TABLE [AutoResponses]
([id] [int] IDENTITY (1, 1) NOT NULL,
[Responding PF] [varchar] (50) NOT NULL,
[Sender email] [varchar] (100) NOT NULL,
[Sender name] [varchar] (100) NOT NULL,
[Subject] [varchar] (100) NULL,
[Entry date] [smalldatetime] NOT NULL)
ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_AutoResponses] ON [AutoResponses]([Responding
PF], [Sender name]) ON [PRIMARY]
GO
ALTER TABLE [AutoResponses] WITH NOCHECK
ADD CONSTRAINT [DF_AutoResponses_Entry date] DEFAULT (getdate()) FOR
[Entry date],
CONSTRAINT [PK_AutoResponses] PRIMARY KEY NONCLUSTERED ([id])
ON [PRIMARY]
GO
You could now use an ordinary insert statement and each new record would have a new [identitycol] property; you could then retrieve that value using the @@identity global variable created by SQL server:
INSERT INTO [AutoResponses]
([Responding PF]
, [Sender email]
, [Sender name]
, Subject)
VALUES
("a","b","c","d")
Notice the id field is incremented automatically and the [Entry Date] field is automatically set by the default getdate()
Select @@identity
There are two problems here:
- without a transaction around the statement, the @@identity you retrieve may not be the last record of the insert statement you have just executed (although you can get around this in SQL2000 with the SCOPE_IDENTITY or IDENT_CURRENT properties)
- You have had to go to SQL Server twice
This is where a stored procedure is needed that both sets the new data and retrieves the @@identity value.
CREATE Procedure NewResponse
@pf varchar(50)
,@email varchar(100)
,@name varchar(100)
,@subj varchar(100)
,@autonum int OUTPUT
AS
begin tran
insert AutoResponses
([Responding PF], [Sender email], [Sender name], Subject)
values
(@pf,@email, @name, @subj)
set @autonum = @@identity
commit tran
GO
Using VB code to insert the new data and retrieve the new id value can now be done in one move, using the Data Environment (DE) or the connection object:
Private Function GetTrackingNum() As Long
Dim resp As Long
DE.NewResponse m_RequestingPF, m_Senderemail, m_SenderName,
m_Subject, resp
GetTrackingNum = resp
End Function
By encapsulating this process into a stored procedure and a class function call, you can create a VB class for a DLL that accepts 4 parameters corresponding to the input parameters of the SQL Stored procedures and returns one value -- the new id.
Public Property Get TrackingNumber() As Long
TrackingNumber = GetTrackingNum()
End Property
Public Property Let Senderemail(ByVal vData As String)
m_Senderemail = vData
End Property
Public Property Let SenderName(ByVal vData As String)
m_SenderName = vData
End Property
Public Property Let RequestingPF(ByVal vData As String)
m_RequestingPF = vData
End Property
Public Property Let Subject(ByVal vData As String)
m_Subject = Trim(vData)
End Property
You can register this DLL on Exchange Server and then
have a script call it every time a new message arrives in a Public Folder.
Copyright DBU 2000.
Last updated
24 May 2004
Location Map
Updates