SQL Server Performance Forum – Threads Archive
Numbering
Hello, We want to have a table with several counters in it. These counters will be used for invoicing, ordering numbers, etc…. But what we want to know, how to lock this table so a user can get a numberincrease it without that the other user can read or change it. the application is written in VB 6. With regards Geeraert Ralph
BEGIN TRAN
SELECT … FROM table WITH (HOLDLOCK, UPDLOCK)
…other stuff like UPDATE
COMMIT TRAN
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Do you have to do this in SQL Server Stor Proc? Or can you call this from VB?
Thanks
Wouldn’t you do this with an ADO Command object and a Stored Procedure anyway? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Hello We haven’t used stored procedures before. And have no plans to use it in the future. So I think we’ll need to program in VB
In that case you would have to embed that probably like this:
Dim cSQL as String
cSQL = "BEGIN TRAN; SELECT … FROM … WITH (HOLDLOCK, UPDLOCK); Plug in your code to UPDATE here; COMMIT TRAN;"
‘Execute it
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Hello, So the followiong strSQL as string con.begintrans ‘execute read with hold lock option ‘execute update command con.committrans
Ralph, ADO is an excellent interface for accessing SQL Server data and functionality from VB. It is a very rich object model, and there are tons of materials available on building an interface using ADO.
]]>