URGENT.SQLServer,NULLS And Foreign Keys.

[Jaime Herrero]

Some days ago I sent a question and I cannot believe nobody has found this problem.
I post it again because any suggestion would be appreciated. Thanks!!!


The trouble is the following:

I have a SQLServer table. One of the fields corresponds with a foreign key value. But it may
be a null value.I mean, it may be a null value, but if it is not, it may correspond with one of the values of the foreign key
I have a DataCombo bound to this field. If I don´t edit the field, nothing happens when I update. A null value is correctly stored.
But if the user edits the value and decides he
wants to cancel edit (Backspace, or blanks " ")
when I update SQLSErver says "" or " " is not a
correct foreign key!!!
I tried, before updating the value (WillMove event), to set a NULL
value if the value is a blank. But sometimes i don´t have the control of every update - i have
Grids and other bound objects-
Otherwise, i cannot store a NULL value in a combobox.
Is there any way to do this, any option in Database design that doesn´t make me control it
using Database triggers?

Thanks in advance
JaIme

[Gary Grant]

The only thing I can think of is to set the ListIndex Property of your combobox to -1

[Jaime Herrero]

Mmmm...It would be easy but will never work.I'm working with datacombos and they don´t have the listindex property.
Thanks anyway,Cary

[Gary Grant]

Ok, someday I will have to learn to read ;-)

Try setting the DataChanged property to False in the Validate Event.

[kieran_rush]

TRY THIS CODE, IT WORKS FOR ME, THIS IS IF YOU USE A FK WITH NULL VALUE, YOU MUST BE USING SQL COMMANDS

FOR INSERT AND UPDATE COMMAND
REFERENCES
1.- txtname (TEXT BOX FOR CHAR)
'" & iif (Trim(txtname)="","NULL",txtname) & "'

WHEN YOU TRY TO OPEN A RECORDSET PROBABLY YOU WILL RECIEVE AN ERROR THAT SAYS THAT THE COLUMN xxxx CAN HAVE NULL VALUES SO IN VB WHEN YOU OPEN THE RECORDSET USE THIS COMMAND

IIf(IsNull(rs("CPHONENUMBER")), "", rs("CPHONENUMBER"))

TRY IT WILL HELP YOU, ANY PROBLEM ASK ME FOR MORE HELP

REGARDS

[kieran_rush]

BY THE WAY, I USE THIS IN VB USING SQL AS DATABASE, WITH MORE THAN 300 TABLES, USING IN ONE TABLE 32 FK'S
REGARDS