Help with update using table adapter

[jpack23]

Hi,

I have a form with comboboxes, if the user doesnt select a value in the box I want a null to be sent to the database.

The insert works fine, i look at the record in the db and see the null.

The update doesnt work. gives me a n "Update statement conflicted with foreign key constaint....."

Public Sub Update()
TableAdapterCreate().Update(ID1, ID2, CType(cbox1.SelectedValue, Integer), CType(cbox2.SelectedValue, Integer), CType(cbox3.SelectedValue, Integer), CType(cbox4.SelectedValue, Integer), CType(cbox5.SelectedValue, Integer), CType(PostAssign.SOB.SelectedValue, Integer), CType(cbox6.SelectedValue, Integer), chkOther.Checked, Notes.Text)
End Sub

as I step through the selected value of the cbox 5 is shown as nothing (which it should be because the user didnt select a value for this combobox).

I assume this needs to be a null instead of nothing to get written to the database. the combox boxes need to match a primary key in another table so sending a 0 would also result in the forieign key error since the related table does not have a 0 ID field.

Thank you for your time and help

[Chris147]

Hi,

You need to look at your db table - the value you are omitting is set as a required field.

Change it to Required: No and enter a default value (0, "" etc), then add a new Update Query to your TableAdapter that omits the missing value (the default you just created will be used) and use it in your code:

Public Sub Update()
TableAdapterCreate().Update(ID1, ID2, CType(cbox1.SelectedValue, Integer), CType(cbox2.SelectedValue, Integer), CType(cbox3.SelectedValue, Integer), CType(cbox4.SelectedValue, Integer), CType(PostAssign.SOB.SelectedValue, Integer), CType(cbox6.SelectedValue, Integer), chkOther.Checked, Notes.Text)
End Sub

HTH,

Chris

[jpack23]

Hi,

You need to look at your db table - the value you are omitting is set as a required field.

Change it to Required: No and enter a default value (0, "" etc), then add a new Update Query to your TableAdapter that omits the missing value (the default you just created will be used) and use it in your code:

Public Sub Update()
TableAdapterCreate().Update(ID1, ID2, CType(cbox1.SelectedValue, Integer), CType(cbox2.SelectedValue, Integer), CType(cbox3.SelectedValue, Integer), CType(cbox4.SelectedValue, Integer), CType(PostAssign.SOB.SelectedValue, Integer), CType(cbox6.SelectedValue, Integer), chkOther.Checked, Notes.Text)
End Sub

HTH,

Chris

Chris,

The fields in the database are set to allow nulls.

when the user inserts a new record with no values selected in the comboboxes it does work...no error record is written to the database The database records shows Null in those fields

but....when a user updates that record, it fails, if the user selects a value in every combobox the update works but When the user leaves a combobox without a value if fails.

My guess is that on the insert a value of null is getting sent to the database and on the update nothing is getting sent and causing the error.

In the code I provided, is there some way to check if that selected value is nothing and if it is nothing , send a null to the database? Would that even work?

thanks again

[Chris147]

You really need to look at your DB/TableAdapter - the Update command is clearly expecting a value to be passed to it.

Right-Click on the TableAdapter and Add New Query - select Update and go from there.

[jmcilhinney]

Foreign keys, by definition, cannot be null. If you want to allow nulls then you must remove the foreign key constraint.