Next record ID = 1 greater than last record

[ckeezer]

After musch help from vis781, I was able to resolve one of my many problems...

Next comes one that I do not even know how to begin.

When I press my "Add New Record" menu item I want the customerID to be 1 number higher than the last record in the recordset. I have a feeling this is going to involve another one of those wonderful Select statements... but I am not sure.

here is the code for my current add button:

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] frmCommercialCustomers_mnuAdd_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] frmCommercialCustomers_mnuAdd.Click
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Clear out the current edits
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].BindingContext(dsOrdersByCustomer, "Customers").EndCurrentEdit()
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].BindingContext(dsOrdersByCustomer, "Customers").AddNew()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] eEndEdit [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Exception
System.Windows.Forms.MessageBox.Show(eEndEdit.Message)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].dsOrdersByCustomer_PositionChanged()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

 

[JohnH]

The database column have to be defined as AutoIncrement

 

[ckeezer]

Figured it out... sometimes I amaze myself with stupidity!!!!!!!!

 

[jmcilhinney]

Databases have this functionality built in already. In Access it's called an Autonumber and in SQL Server it's an identity column. The idea is that you don't create the value yourself. You just insert the record containing all the other field values and the database will generate the required ID value. if you then need that value you get it using a query. You can either include a second SQL statement in the same command to requery the new row or you can execute a separate query and use the @@IDENTITY variable to get the last generated ID value.

If you're using a DataTable then you can set a DataColumn to be an identity column too. The DataTable will then automatically generate a key value based on the data it contains, so you can use the value temporarily. Once you post the data to the database you can update the temporary value with the actual value.

 

[cjard]

Mr McIlhinney, does the behaviour of an identity column extend to being able to maintain relationships? If so, how is this m,anaged?

Suppose I have Customers and OrderItems, each order item can only belong to one customer, so I have the CustomerID in the OrderItem table. I have a relation defined outlining CUstomer as the parent and orderitem as the child. The relation enforces integrity too. On a form I have a button to add a new customer, and another to add an orderitem(s) to a customer. The controls on the form are bound correctly such that the textboxes for the customer aer bound to the datatable and the grid of orderitems is bound to the datarelation.

Which columns would I set to be identities? How would these identities be managed when I click the various add buttons?

I ask, because my current solution to the problem is to set the .DefaultValue of the related columns immediately before i perform an add

 

[jmcilhinney]

It is quite usual to use autoincremented fields as foreign keys. I'd say it is the most common relationship. You set up the corresponding realtionship between your local datatables. When you create records locally ADO.NET creates autoincremented values based on the local data. When you insert parent records into the database the actual values are created and the local data is refreshed before the child data is inserted, so the relationships are maintained.

 

[cjard]

So in setting up the datarelations, i'm giving VB a way to intelligently manage the actual values that maintain the relationships?

One possible complication is that all database donkey work is done via stored procedure, with the primary key fields being marked as input output. my query is exactly what kind of value will be created when I add a new record to the datatable via bindingsource.add ?

I ask because the stored procedure handles both insert and update. if the primary key value of CustomerID is passed as a negative number, then a new ID (the next in sequence) is calculated. If the number is positive, then no new ID is calculated and the relevant record is updated instead

how would i instruct my procedure to determine the difference between a record to be added and a record to be updated? What is the pattern of CustomerID if it is set to be an Identity field and a new record is added?

 

[ckeezer]

Understanding that the database will allow me to use an AutoNumber field, I need to assign my CustomerID field a value of "1000-". So the first record would be customerID 1000-1, vice 1, if I use the autonumber field.

When I click on my btnAdd I need it to increment this number to "1000-2". How can I do that. Below is the code for my btn.add:

[SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'Clear out the current edits
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].BindingContext(objdsCustomers, "Customers").EndCurrentEdit()
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].BindingContext(objdsCustomers, "Customers").AddNew()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] eEndEdit [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Exception
System.Windows.Forms.MessageBox.Show(eEndEdit.Message)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].objdsCustomers_PositionChanged()[/SIZE]
[SIZE=2]

[/SIZE]

This was generated by using the dataform wizard.

 

[cjard]

Erm.. can you have 2 columns, compounded to be a primary key.. The first column contains 1000, the second is an autoincrememnting one..