adding/saving data to my table

[johncassell]

Hi There,

I need some help verifying data before saving it to my database table please..

I have a 'Customers' table and have dragged all the fields onto a windows form so it has come with the standard BindingNavigator bar across the top (With save, add, delete, next etc..)

CustomerCode is the primary key.

If for example I load customercode '1's data, and I change the value '1' to '2' then try to save, it comes up with 'value already exists in database' error.

If I put a check in to say 'if that value already exists then thrown a msgbox' then that would work in the above scenario but what If i loaded '1's data and then made changes to other fields, it would check to see if '1' is already in the table column and throw the msgbox even though I don't want it to in that case.

Hope that makes sense and someone can help with a way around this.

Thanks

John

 

[Arg81]

When you call your tableAdapter's update command, it will either insert, update or delete.

Because your CustomerCode is your primary key, it will error on this if you try changing. Although why would you want to change this? Any data that relates to this would become "parentless" (an orphan) or relate to the wrong parent.

I normally do 1 of 2 things.
a) If I have to show the ID, then bind it to a label so the user can't change the value
b) Don't show the ID at all.

Your row will still update even if the ID isn't visible on the form, as it's present in the dataTable.

And in answer to your question about other fields - that will not error unless any of them are Primary Key fields.

Example

CustomerCode (Primary Key)
Address1
Address2
Address3

If you change values of CustomerCode and Address1, Address2 and call tableAdapter.Update(blah blah blah), you will get an error if you change CustomerCode to a value already existing in your DB because that value is the Primary Key (i.e. Unique)

If you change values of Address1, Address2, Address3 and call tableAdapter.Update(blah blah blah), it will commit these changes.

Again, the main point here is why are you allowing for your Primary Key to be edited by a user? Thats not good database practice tbh.

 

[cjard]

CustomerCode is the primary key.

If for example I load customercode '1's data, and I change the value '1' to '2' then

Thats what happens when you change a primary key. Dont change a primary key. Your bank dont let you change your account number for this very reason.

 

[johncassell]

Hi Arg,

I have a combobox bound to that primary key so people can easily select the customercode. I wouldn't want to change the Primary key but I know that some user will do it accidentally and say 'This systems crap!" when it crashes.

I think what I might do is use a separate combobox (not bound) but changes populate the real bound box, for the user to select the custcode and if they try to edit a value in the fake box it will only allow it the boundbox is empty (i.e it is a new record).

That probably doesn't make any sense but I think I understand it!!

Once again, thanks a lot for your help

John

 

[cjard]

b) Don't show the ID at all.

Your row will still update even if the ID isn't visible on the form, as it's present in the dataTable.

THis is a great point; usually PK IDs are a meaningless string of numbers and letters intended for the computer ONLY to be able to ID and edit that row. The user has NO NEED to see this info.

If your PK is formed from the data, and it is on show you should ensure it is data that doesnt need editing. A person's age would never be a good candidate for all or part of a primary key because it will change, within a year.

 

[Arg81]

I have a combobox bound to that primary key so people can easily select the customercode. I wouldn't want to change the Primary key but I know that some user will do it accidentally and say 'This systems crap!" when it crashes.

Change it to a DropDownList instead of DropDown and they can't edit the value

 

[cjard]

Hi Arg,

I have a combobox bound to that primary key so people can easily select the customercode.

You have a relationship problem or a design problem.

If you must put the PK on show in your combo, DO NOT BIND the combo. A bound combo will edit values.

For example, you have an order system and you have only 10 suppliers. You let the user pick the supplier to edit from a dropdown combo. THAT COMBO IS NOT FOR EDITING. it is for navigation only. DO NOT DATABIND .SelectedValue or .Text or .SelectedItem or anything. Just set the .DataSource and .DisplayMember


If you are binding the combo for use in a lookup scenario, do not bind it to the same table that it gets its datasource from.

COntinuing our example, you make a new order for PAPERCLIPS, and you choose the supplier it is to come from:

combo.DataSource = tblSuppliers
combo.DisplayMember = "SUPPLIER_NAME"
combo.ValueMember = "SUPPLIER_ID_PK"
combo databinding .SelectedValue bound to tblOrders table, FROM_SUPPLIER_ID foreign key column

I wouldn't want to change the Primary key but I know that some user will do it accidentally and say 'This systems crap!" when it crashes.

You must not bind a control up in such a way that it is capable of editing the value in the underlying table. Observe this and the user will not be able to cause that scenario

I think what I might do is use a separate combobox (not bound) but changes populate the real bound box, for the user to select the custcode and if they try to edit a value in the fake box it will only allow it the boundbox is empty (i.e it is a new record).

If you are looking to allow the user to insert a pk value but never to edit it, then remove the part of the update statement that edits the PK. leave the insert capable of inserting a PK value. Additionally, you can set .Enabled = false on the combo when the .CurrentChanged of the bindingsource fires and the .Current row has a RowState of anything other than Added. Additionally, you can have the underlying datarow reject an attempt to change a column (ColumnCHanging event) if the row is not Added (new) RowState

That probably doesn't make any sense

You'd be right, but I suspect it's a bit of an iron-and-wood solution to a technological-age issue

 

[davy_g]

You can also do the following:

Table customers:
ID
Customer-id
Name
etc...

You use the ID as PK and use the customer-id to show on the screen. Your user has nothing to do with the internal ID.
You can load it in your dataset but you don't drag it on your form (or you set it to visible = false).
In this way the customer-id can still be changed but your user will never touch the internal ID.

This works great for us.