By Edhy Rijo - 3/19/2008
Hi All, I have several form which will show a list with a sequential order number like 1,2,3,4, etc. (See picture below). I want this field LineOrder to be autogenerated with the next unused value when a new record is created, so if the last one used is 5, then when clicking New, the value will be 6. 
I added the following code in the PaymentMethodBO.vb: Private Sub PaymentMethod_SetDefaultValues() ' --- Calculate the next LineOrder number to be added as a default value for Me.LineOrder field. Me.MoveLast() ' This will move the record pointer to the last record, in this case the new one. Me.MovePrevious() ' Go back to the previous record which should be the last one. Dim nextLineOrder As Integer = 0 nextLineOrder = Me.LineOrder + 1 ' Increment 1 to get the next default value. If nextLineOrder = 0 Then nextLineOrder = 1 End If Me.MoveLast() ' Go back to the new record ' Set the default Value here Me.LineOrder = nextLineOrderEnd Sub So far this is working fine, but I would like to make sure this is the proper way to get this done when working with data in the BO. I guess I may have another BO with a SELECT MAX() statement to get the last value used and then increment this value. So I would appreciate any recommendation in this regard. Thanks!
|
By Greg McGuffey - 3/19/2008
This will work in a single user scenario, but if you have multiple users, especially if they are all adding new records, you'll get dups pretty quick. The BO is disconnected from the db, so if another user adds a new record after the user has filled the BO, they won't know about the newly added record from the other user. There are a few of solutions for this:
- If possible, just use an AutoIncrement field. The down side is that it is hard to have no holes (in some cases holes are a problem if the field is audited) and it will by a system number, not one that necessarily makes any sense in the context of the business domain.
- Query the db for the last LineOrder number and increment it. I've used this when it is very unlikely that two people are going to be adding records at the same time (in fact, it is a bad idea in these scenarios). Pretty much what you are doing, but if someone else adds new records while the user is in their BO, they'll see the changes.
- Use a table to hand out the LineOrder numbers. I.e. you query this table to get the next line order number. This is more complex because you have to deal with canceled adds, etc.
The long and sort of it is to use the DB to handle this, since the disconnected BO won't know about other users changes in a timely enough fashion (usually). I'm sure there are other ways to do this too, but thought I'd offer some ideas.
|
By Edhy Rijo - 3/19/2008
Hi Greg,This kind of data will be usually in use by a single user, since it is just the Line order for the records that will be shown as a lookup in a combo, so these forms will not be use by multiple users. That's why I used the data in the BO to get the last one used, even though I think a better approach is to use the database with a view or SP to get the MAX() + 1. Thanks for the explanation.
|
By Edhy Rijo - 3/19/2008
Hi Greg,I ended up creating a function "GetNextLineOrder" in the BO which will query the table for the next available number. Please see code below. Only thing I could not figure out is how to pass the FieldName as a string instead of hard coding it in the Service_SetDefaultValues in order to make the call more generic since I will be using this code in several BO's. Again, thanks for all your advice and pointing me in the right direction. ''' <summary>''' Sets the default values for a new row''' </summary>''' <remarks></remarks>Private Sub Service_SetDefaultValues()' --- Calculate the next LineOrder number to be added as a default value for Me.LineOrder field. Me.LineOrder = Me.GetNextLineOrder("LineOrder", Me.TableName.ToString())End Sub ''' <summary>''' This function will generate a query to get the next available Line Order number.''' </summary>''' <param name="LineOrderFieldName"></param>''' <param name="TableName"></param>''' <returns></returns>''' <remarks></remarks>Private Function GetNextLineOrder(ByVal LineOrderFieldName As String, ByVal TableName As String) As Integer Dim sqlStringCommand As String sqlStringCommand = "SELECT MAX(" & LineOrderFieldName.ToString & ") + 1 FROM " & TableName.ToString Return CType(Me.ExecuteScalar(sqlStringCommand), Integer)End Function P.S. I will add some TRY...ENDTRY error validation in case something happend in two months 
|
By Greg McGuffey - 3/19/2008
I might make a couple of suggestions:
- You don't need to use the ToString() on LineOrderFieldName or TableName. They are already a strings 
- Another way to get the same result as using the MAX function, is to use sorting and the top directive. I'm not sure which is faster, assuming that the LineOrderFieldName is indexed, but thought I'd throw out another idea. I.e. you sql would be something like:
"SELECT Top 1 (" & LineOrderFieldName & ") + 1 FROM " & TableName & " Order By " & LineOrderFieldName & " DESC"
- Use String.Format. It's a good habit to get into and it deals with strings more efficiently, as I recall. So now you might do the SQL String like:
String.Format("SELECT Top 1 [{0}] + 1 FROM {1} Order By [{0}] DESC", LineOrderFieldName, Me.TableName)
- When the BO is created there is an enum created that provides access to all the field names. It will be within the BO and have a name like MyBO.MyBOFieldNames. You could then just use the ToString() on the field (you'd replace MyBO with the actual name of your BO). I'm not sure this helps, but it does provide a strongly typed access to field names, so if you change the name, code won't compile and you can find the problem quicker:
String.Format("SELECT Top 1 [{0}] + 1 FROM {1} Order By [{0}] DESC", MyBO.MyBoFieldNames.LineOrderFieldName.ToString(), Me.TableName)
- This will fail if there are no LineOrder records found. I.e. the Select will return NULL as there is no data yet, and NULL + 1 is still NULL. So, you should either check the return for NULL (DbNull.Value) in you BO method or add to the SQL to handle it.
Good luck!
|
By Edhy Rijo - 3/19/2008
Greg McGuffey (03/19/2008)
- When the BO is created there is an enum created that provides access to all the field names. It will be within the BO and have a name like MyBO.MyBOFieldNames. You could then just use the ToString() on the field (you'd replace MyBO with the actual name of your BO). I'm not sure this helps, but it does provide a strongly typed access to field names, so if you change the name, code won't compile and you can find the problem quicker:
String.Format("SELECT Top 1 [{0}] + 1 FROM {1} Order By [{0}] DESC", MyBO.MyBoFieldNames.LineOrderFieldName.ToString(), Me.TableName) Hi Greg, You definitely get me going  I made some modifications and checked the return value to foolproof the function. I tested with a blank table and it worked perfectly. I stick with the MAX() for simplicity, anyway those tables will not have many records anyway and they are properly indexed. Here the new version: Private Function GetNextLineOrder(ByVal LineOrderFieldName As String, ByVal TableName As String) As Integer Dim sqlStringCommand As String sqlStringCommand = String.Format("SELECT MAX( {0} ) + 1 FROM {1}", LineOrderFieldName, TableName) ' --- Test the returned result for DBNull value and if so return 1. Dim loValue As Object loValue = Me.ExecuteScalar(sqlStringCommand) If loValue Is DBNull.Value Then Return 1 Else Return CType(loValue, System.Int16) End IfEnd Function Of course I cheated a bit with the loValue code above which I copied from the BO designer file.  For the Field Enums I only found a property Me.FieldEnums and frankly I could not found a way to use it to return the field name, but in this case it may no be necessary since the field name is the same in all tables "LineOrder", but it will be good to know how to get the field name from the field object Me.LineOrder. Thanks again!
|
By Greg McGuffey - 3/19/2008
Looking good Edhy!
The FieldNames is an enum that is defined within the designer file of the BO. If you look in the Object Browser, find you BO, the next object in the list will be the BO. If the BO is named MyBO, then right after it will be an enum named MyBOFieldNames. If the BO is named YourBO, then right after it will be an enum named YourBOFieldNames.
I don't believe you can get the field name from the strongly typed property itself (i.e. you can't get the field name from Me.LineOrder).
Glad you're getting it going!
|
By Trent L. Taylor - 3/20/2008
Looks good...as long as you don't plan on two people hitting this table at the same time, then you could have an issue. If you do not want to allow an auto-incrementing field to do this for you, then I would recommend creating a good ol' fashioned NextId table that is incremented with every query, this way if you have two, ten, or a hundred people hit this at the same time you will not have an overlapping of values. Otherwise, your code really does look good. The only thing I might mention would be the code you posted initially. You were using MoveLast and the MovePrior. You can do that, but you could do this in one line instead of two or three:MyBo.MoveAbsolute(MyBo.Count-2) But keep up the good work...it looks good!
|
By Edhy Rijo - 3/20/2008
Hi Trent, For now these tables will be used initially in the application to setup some lookup values, so I am pretty sure only the Admin will have access to those forms, but I will enhance it to use an old fashioned NextID routine since there are a couple of those I need for Cash Receipt and Invoice. I don't know if with SQL2005 you can have as many AutoIncrement fields as you want, but if not then a NextID routine will be needed to handle those numbers. Thanks for the tip on MyBo.MoveAbsolute I'll keep it in mind since there are some data manipulations I need to do in this project but have not get there yet, so be ready for my questions 
|
By Trent L. Taylor - 3/20/2008
I don't know if with SQL2005 you can have as many AutoIncrement fields as you want You can have as many as you need and this is the recommended way to do things and is what we do. However, if you need the old style of NextId, then you can do that too
|
By Doron Farber - 10/28/2008
Hi Trent,Regarding an Invoice ID which is a unique number and how would you really create that in the ms sql 2005? You are allowed just one counter per table. In the last training we had a discussion about a class that will be created in some point. But maybe there is a direct way to implement that in the sql server level. Regards, Doron
|
By Trent L. Taylor - 10/28/2008
If you want SQL Server to generate it, just create the column as auto-incrementing and provide the seed just as you would for an auto-incrementing PK. In this case, you would just not make it a PK field.
|
By Doron Farber - 10/29/2008
Hi Trent,But I have already PK field that is an Auto Increment, and you cannot have 2 fields like that in the same table. I tried that and did not work I think another solution like a generic class that you use in your medical application could be very helpfull these days. When I used the VFP framework like the ProMatrix one, that was a given. That will be nice to see something like that in the next update... More features more sales... Thanks, Doron
|
By Trent L. Taylor - 10/29/2008
Sure you can You can have as many identity columns as you would like. It doesn't need to be a PK, just an identity column that is auto-incrementing.
|
By Doron Farber - 10/29/2008
Ok Trent,
But when I change the Identity to Yes in the TransactionNo field then the PK field is lossing its Identity and it turns to No or visa versa.
So that does not work for me unless you can be more specific and maybe through me a screen shot. I need to keep the Identity of the PK all the time.
Thanks,
Doron
|
By Trent L. Taylor - 10/30/2008
Here is a primary key column with an identity (auto-increment) defined:
Then here is a column with just the identity )aut-incrementing) column defined: 
These are both in the same table and will both automatically generate a new value when a new row is created using the identity column (auto-incrementing) settings. I have also attached these images for a better view.
|
By Doron Farber - 10/31/2008
Hi Trent,Thanks for your reply. Yes I see that you can have the second Auto Increment using the DDT package which I also tried to do and see below image. Right now I am NOT using yet the DDT package and I tried to create the second auto increment in the actual ms sql 2005 table. As far as I know it can not be accomplished unless you use a Stored Procedure. I assune the DDT also created a Stored Procedure for this purpose. Thanks,
Doron
|
By Trent L. Taylor - 11/1/2008
No. Actually you can do the very same thing through SSMS (SQL Server Management Studio) or if you create your tables using a script. This is just standard SQL Server functionality. You are welcome to post your error or take a screen shot of how you are setting up the database. I just used the DDT for the screen shots, but it creates this as part of the standard SQL Server table when deployed. This is not done via a sproc.In SQL Server, this is called an identity column. You can technically setup as many of these as you need. If you modify a table through SSMS, you can go down to the Identity section, and specify an auto-increment seed, etc.
|
By Doron Farber - 11/3/2008
Hi Trent,
Thanks for your reply and please see this small video of what I tried to do regarding the transactionNo field. That field will be an Invoice Number like 1 , 2, 35 etc... and must be unique the same way as a PK field.http://www.dfarber.com/Counter.rar (it is 1.7 mb)
and Video worth a thousands words. Regards, Doron
|
By Trent L. Taylor - 11/3/2008
Here would be my suggestion. Instead of fighting all of that, just create a sproc and either create an INSERT trigger or set the default value to pull from where ever you need to populate that transation unique ID. You could have unique ID table that has the name of the table and the next value that you pull from or if you just want to execute another query from the same table and get the MAX value of the current column and increment it by one, you could do that as well. Another option would be to do this at the BO level in the SetDefaultValues event. Just call a scalar method that does the same thing that the trigger I was talking about would do. You have a number of options here.
|
By Greg McGuffey - 11/3/2008
Well, I would have sworn Trent was right on this one. But I just attempted to do this via scripts and it reported that only one identity column is allowed per table. I'd use one of Edhy's suggestions.
|
By Greg McGuffey - 11/3/2008
Er...I mean one of Trent's other suggestions...can't see straight this morning apparently..
|
By Trent L. Taylor - 11/3/2008
Yeah, I noticed the same thing... *scratching head* I don't have a lot of time to dedicate to this at the moment, but I know that it is possible, so I will have to add this to the "to-look-at" list.
|
|