auto assign a number

[dvannoy]

I have a project that connects to a sql server via ADO.

I want the DB to auto assign a work order # to the person that enters the work order.

This is currently in Access but I am converting it to SQL server.
Below is my access code to make this happen. it works fine.
can someone help me convert this code so that it will work with VB & Sql server.

If DCount("wrkordno", "wrkord&quot = 0 Then
Me![txtWrkOrdNo] = 100
Else
Me![txtWrkOrdNo] = DMax("wrkordno", "wrkord&quot + 1
End If

Thanks alot DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[DavidJA]

If I'm understanding you correctly, all you want is the database to automaticly generate a number when a row is added to a table.

This is commenly called an Identity column in SQL server, (an autonumber in Access).

To create an identity column in SQL server, open up the enterprise manager, navigate to your database, "Design Table" and add a column. There is a property on the column that you can select called it an identity column.

Identity Seed and Increment control where the number starts and how much it goes up by.


When you are adding the row using ADO, you can find out the value of the last added row by simply refering to it after the update; EG:

rs.addnew
rs.fields("a&quot = "sdfdffddsf"
rs.update
msgbox(rs.fields("myIDColumn&quot)

 

[dvannoy]

OK, I was thinking about using that. But I wasnt sure if it acted liek the auto number field in access. The auto number field in access would sometimes skip numbers. I dont want that.

If i use this feature how can I display it right when the form opens? The user needs to see the number before they enter data.

Thanks dvannoy@onyxes.com

 

[DavidJA]

In my experience, the identity field of SQL is a lot more stable then the autonumber field.

But, as for the value of the identity field being shown; I'm not sure that this is possable using VB's bound controls.

SQL will not actually generate the autonumber until there is an INSERT. You could GUESS the next autonumber by doing a SELECT lngAutoNumber = max(identity_column_name); but you really need to consider what happens if this application is being used my more then one person at a time.

With the user needing to be able to see the autonumber before entering data, is this _REALLY_ needed? In the past I've quite often been able to BS management into dropping these requirments by arguing the logic on the situation; afterall, how is the system going to know what autonumber has been assigned if it does not know if the data will actually be entered, and how would it know in a multi-user situation.

In my opinion, a much better way is to MsgBox the user once the data has been entered (msgbox("Your order has been saved and assigned an order number of " & rs.fields("myidcolumn&quot)

BUT, if you really need to functionality of Access (remember that access does not show the autonumber until a field has been changed); when the form opens have the database INSERT the row, and return the value of the identity column, then when the user clicks on save, have the database UPDATE the row with the rest of the fields (using the identity column).

BUT, what happens if the user does not choose the continue using the form, or their computer becomes disconnected 1/2 way through; then you will have all of these placeholders records with no real data.

 

[gigsvoo]

What I normally does is I will get the latest number using the following statement then increase the number by one...this will always confirmed that no phantom case or sharing violation happened.

"SELECT Top 1 FROM ..... ORDER BY....DESC" Thanks

Neo

http://communities.msn.com.my/VisualBasicwithNeo