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:

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!

Edhy Rijo

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

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.

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

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.

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

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.

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

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.

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.

Er...I mean one of Trent's other suggestions...can't see straight this morning apparently..

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.