Using Identity Field in Forms

[PhillD]

I have setup a simple table in SQL 2005 with 2 fields, EmployeeID and EmployeeName. EmployeeID is an identity field.

A created a new form and setup a data source added the 2 fields to the form by clicking and dragging from the data source. Visual studio setup the TableAdapter, DataSet and BindingSource.

I am able to create and save records from the form to the database.

When I click the new button, the EmployeeID column shows a negative number. When I save the record, the EmployeeID is changed. I understand why negative numbers are used and that I can change them to positive but I do not want any number to appear in the EmployeeID column until the record is saved. So I turned off the Identity setting in the dataset and it leaves the EmployeeID box blank. However, when I save, I get an Error that EmployeeID cannot be blank.

If I remove the EmployeeID box from the form, I can successfully save but obviously, I cannot show the ID number. Can someone please help me solve this? I need for the record to be saved with the contents of the EmployeeID being left blank on the form. Hope that makes sense.

Thanks

 

[PhillD]

Can anyone help me? Please.

 

[PhillD]

I think I have figured out part of this mystery.

I found the insert statement in the table adapter. EmployeeID is not even listed, so it's nothing to do with that. However, in the DataSet, I did find something interesting for the EmployeeID column. There is a setting called NullValue and it is set to "Throw exception". Could this be it?

I checked in another project and all of the columns are set to this value? I tried to change it to "Nothing" but I get an error "property value is not valid".

So I'm lost.

 

[jmcilhinney]

First up, you don't change how your data works to suit the UI. You need to change how the UI works to suit the data. If you have a grid column bound to that column of the DataTable then the cells are going to show the values, plain and simple. The table fields MUST contain a value, so if you don't want the grid to show them then you need to change how the grid works.

I haven't tried this but you MIGHT be able to handle the CellFormatting event of the grid and set e.Value to Nothing or an empty string or DBNull.Value if it's an Integer less than 0. That may change the value displayed by the cell without changing the underlying data source. Then again, it may not and you may have to create your own custom column and cell classes.

 

[PhillD]

I am getting the error on the .EndEdit portion of my code so it has nothing to do with the database. If I remove the EmployeeID box from the form, the data will save successfully. So this has to be an issue with the internal workings of the dataset, tableadapter or binding source. Is there a way I can look at the code which is performing the validation?

The real problem here is that the EmployeeID box has inherited the properties from the database and absolutely insists on keeping it that way. If I have an Identity field in my database, then the control on the form wants to be an Identity field also. Seems like a weakness here! Even if I was to hand code the dataset and use a data adapter, I'm still going to get the same problem when I bind the EmployeeID text box to the EmployeeID field in the binding source/Dataset.

What I really don't understand is how no one on this forum seems to use Record ID's in their code. If they do, everyone is keeping quiet. Surely you are not issuing ID's such as "-1" to your users and then letting it change to another ID?

 

[jmcilhinney]

Most people don't ever show the user the ID generated by the database, and that's the way it should be.

The problem is the fact that a PK field is always going to be non-nullable. When you call EndEdit the row is committed to the DataTable, which is when the temporary ID is generated. You could simply edit the properties of that column of the DataTable to make it nullable and not an identity column, but then you couldn't make it the PK either. If you don't enter a value for more than one row then you've got duplicate values, so the PK constraint would baulk. If you set AutoIncrement to False and don't enter a value then you're violating the non-nullable constraint.

Maybe you need to add an extra column to your DataTable. There's nothing to stop you from adding extra columns that don't correspond to database columns. You could set the Expression of the column to result in NULL if the ID is less than zero and equal to the ID otherwise. You can then hide your ID column and show the calculated column.

 

[PhillD]

But when your in SQL, or editing the table directly through visual studio, you don't have to eneter a value in the PK field, SQL populates it for you. So why would VB force you to supply a value?

If I link an MS Access database to the SQL Table, it allows me to save a record without specifying a value for the PK field. Isn't this a Bug?

 

[PhillD]

jmcilhinney, are you saying that I can't achieve the AutoID the way I want to? I need to create an additonal column for the purposes of storing the EmployeeID? If so, I will have to ID columns in my table. 1 for the primary key, and another for the EmployeeID. That seems like bad design.

 

[PhillD]

Solved ??

I'm sure someone is going to tell me that this is a bad idea BUT I found the answer.

I turned off "Enforce Constraints" option in the dataset. Seen as I have to write validation code anyway, is this really that bad of an idea?

What will I loose out on?