Here is a typical stored procedure to prefetch IDs from an ID table. You can modify it to meet your needs:
CREATE PROCEDURE sp_newid
@tcKeyWord varchar(30)
AS
DECLARE @cId varchar(11),
@cNewId varchar(11),
@cIncProc varchar(60),
@nMaxLen int
BEGIN TRANSACTION
SELECT @cId = Value,
@cIncProc = IncrementProcedure,
@nMaxLen = MaxLength
FROM Id With (TabLockX,HOLDLOCK)
WHERE keyname = @tcKeyWord
IF (@@rowcount = 1)
BEGIN
SELECT @cId = RTRIM(@cId)
EXECUTE @cIncProc @cId, @nMaxLen, @cNewId OUTPUT
UPDATE Id
SET Value = @cNewId
WHERE keyname = @tcKeyWord
COMMIT TRANSACTION
END
ELSE
BEGIN
SELECT @cId = 0
ROLLBACK TRANSACTION
END
-- Return the id as a cursor
SELECT iid = CAST(@cId AS INT)
GO
You would just call this using the ExecuteScalar method of a CommandObject in VB .NET.
Dim iID as Integer
iID = CType(oCmd.ExecuteScalar(), Integer)
>Hi,
>
>Need help some help.
>
>I need to generate and retreive an unique Transaction_id (int field) for different offices; each office would start off with say 1000000001 for Office One and Office Two would start off with 2000000001; etc. The field would be in the Office Table, one row for each office.
>
>The SQL database is on a remote server and the offices are located in different locations and they use the same SQL database on the remote server. The shared SQL Office Table has a row for each office with a field for the last transaction Id which will be incremented for each office transactions.
>
>Not sure how to update a row in a table with an int field and add +1 to it and return it to VB .net. I understand that ADO.Net is disjoined. In VFP I would simply retrieve the record, lock it, and if locked then update the field with id+1 and I would have the number I needed.
>
>What would I need to do to accomplish this in VB .net project? I need detail because I'am not very versed in SQL and Vb.net. I learning more every day.
>
>Thanks!!!
>Roland
-----------------------------------------
Cathi Gero, CPA
Prenia Software & Consulting Services
Microsoft C# / .NET MVP
Mere Mortals for .NET MVP
cgero@prenia.comwww.prenia.comWeblog: blogs.prenia.com/cathi