VB6 Binding NULL value to Textbox with ADODB.Recordset

I'm having to dig into an old VB6 app and I don't have a lot of experience with this language.

Right now I'v开发者_JAVA技巧e got a form where the controls are bound to a ADODB.Recordset. So the "DataField" property of the TextBox is set to "MyIntColumnName".

When the textbox is left empty, I would expect a null to be put into the database, but instead I am getting zeros and a foreign key violation. Is there any way to specify the binding so that empty == NULL?

You might want to check the column in the database: It sounds like it's set to Not Null and a default of 0, which is why, when you don't enter anything, a zero gets submitted.

I don't think changing the binding so that empty==Null will help since you would still get a foreign key violation--your table has a foreign key that depends on a valid value entered.

You're gonna have to either change your table (remove the default and the foreign key)--which may not be the best solution--or change your form field--perhaps to a combo or list value that correlates to the value from the foreign key table.

Here's a workaround:

Private Sub txtDataField_Change
Dim cDataField As String
Dim rsTmp as ADODB.Recordset

    If txtDataField.Text = "" And Not txtDataField.DataSource Is Nothing Then
        cDataField = txtDataField.DataField
        txtDataField.DataField = ""
        set rsTmp = txtDataField.DataSource
        rsTmp.Fields(cDataField).Value = Null
        set rsTmp = Nothing
        txtDataField.DataField = cDataField
    End If

End Sub

You could also try:

YourADODBRecordset.Fields("MyIntColumnname").value = Null
txtTextBox.Datachanged = False

Then the recordset will not attempt to update the value from the bound field.