0

I am using SQL Server Express 2016 and Excel VBA to generate unique lot numbers for a bunch of different excel documents. Currently, I am creating a new row,then a script in SQL Server increments the lot number. Then I run a select statement to grab the the field from the row that was just created. So far I have not had any issues, however, I am concerned that if the excel file is fired by different users at the same time, the select query for one user may grab the row that another user created. Is this a concern? If so how do I avoid it?

statement = "INSERT INTO LotInfo(RefNum,DateCreated,UserName)VALUES('" &  RefNum 
 & "','" & DateCreated & "','" & user & "')"

conn.Execute statement

Set lot = conn.Execute("SELECT top 1 Lot FROM LotInfo Order By ID desc;")
Dalton
  • 23
  • 3
  • Isn't `RefNum` unique here? Anyway, you can return `@@IDENTITY` or probably better, `SCOPE_IDENTITY()` – DavidG Jan 17 '17 at 16:50
  • No, unfortunately RefNum is not unique as users can make different lots of the same RefNum at the same time. I am not familiar with @@IDENTITY or SCOPE)_IDENTITY(), what would be the correct way to use it in this example? – Dalton Jan 17 '17 at 16:57
  • Plenty of resources out there for you, but for example you need to do `SELECT SCOPE_IDENTITY()`. See here: http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide – DavidG Jan 17 '17 at 16:58
  • Thanks David, so the second query would be "Select Lot FROM LotInfo where ID = SCOPE_IDENTITY()"? – Dalton Jan 17 '17 at 17:17

1 Answers1

1

I don't believe that Scope identity will work. The statements are entirely separate. first you do the insert. That ends. Then you send the select. While I'm not 100% familiar with VBA, I'm not sure that the second select will know anything about the result of the first invoice. I would suggest you create a stored procedure which you can call from VBA. The procedure would perform the insert and then return the lot number.

Adam Jacobson
  • 564
  • 4
  • 9
  • Adam, that is what i thought as well. However, I think the Scope identity is indeed working. I ran the code for each of the excel documents (making sure to put in a break before the select query ran). When I stepped through WITHOUT using the Scope Identity both of the documents received the most recent entry. However, when I performed the same test using the scope identity, each document received the correct lot number. However, you may be right, the stored procedure may be the way to go. – Dalton Jan 17 '17 at 19:57
  • If you've tested, great. Over the years, strange interactions between VBA and SQL server have pushed me to work with stored procedures. Easier to test the logic in SSMS and then just have a simple call in VBA. (Also, SSMS gives much better error messages than VBA) – Adam Jacobson Jan 17 '17 at 20:54
  • Thanks Adam! I am going to use a stored procedure. – Dalton Jan 18 '17 at 15:08