The Old Joel on Software Forum: Part 3 (of 5)

Null-ing combo boxes in VB6

I'm working on my first VB6 database application after years of developing in Access, and this one is a real jaw-dropper:

Using a form with data-aware combo boxes (in my case, I'm using ComponentOne's combo boxes, but the effect is the same with a DataCombo from MSDATLST.OCX) and an ADO Recordset (based on an Access 2000 MDB) as the data source, it's impossible to Null-ify the field a combo box is bound to without using some rather silly code.

I can open the form, retrieve a record, clear the contents of the combo box, save and close -- but when I reopen the same record, the value I cleared is still there.

I can't set it to Null in code either, because then I get an error message stating that "Multiple-step operation generated errors".

The only workaround I've found is to call the recordset's .Update method, then run a SQL statement like "UPDATE table SET field=Null WHERE primarykey=123".

Needless to say, this feels a bit kludgey. Any alternate solutions?

Sam Livingston-Gray
Thursday, September 25, 2003

In your phrase 'save and close' what do you think is happening during save? Is it an updateable cursor? I guess it must be if you can call an UPDATE method on it (though that might go redo the query all over again).

Have you gone and looked at the data directly in the database to see whether it has been changed?

My guess is that it hasn't, and that you're working with a plain old cursor or view.

Simon Lucy
Thursday, September 25, 2003

Funny, no matter how much information you post when you first ask a question here, someone always asks for something else. ;>

Anyway, Simon, it's a connected ADO recordset with client-side dynamic cursor. Problem #1 is that apparently no Combo box is smart enough to set its value to Null when you clear it (except, of course, for the combo boxes in Access). Problem #2 is that the simplest workaround (setting the value to Null directly before updating) errors out on the line that calls .UpdateBatch, so the data is not being written to the underlying DB.

Sam Livingston-Gray
Thursday, September 25, 2003

Sam,

<quote>
Anyway, Simon, it's a connected ADO recordset with client-side dynamic cursor.
</quote>

There is no such thing. If you have an ADO recordset with a client cursor then the cursor type is always Static (regardless of what you set it). This is not well documented, but you can check it for yourself.

<quote>
Problem #1 is that apparently no Combo box is smart enough to set its value to Null when you clear it (except, of course, for the combo boxes in Access).
</quote>

Its not a 'smartness' issue. Its that standard VB textboxes return strings. And VB strings cannot contain Null (by definition).

<quote>
Problem #2 is that the simplest workaround (setting the value to Null directly before updating) errors out on the line that calls .UpdateBatch, so the data is not being written to the underlying DB.
</quote>

To be honest, this is most likely programmer error (ie you have made a mistake). I would suggest posting your code to a VB list (say http://peach.ease.lsoft.com/scripts/wa.exe?A0=visbas-l ) and we can give you a hand.

Seeya

Matthew
Thursday, September 25, 2003

"If you have an ADO recordset with a client cursor then the cursor type is always Static (regardless of what you set it). This is not well documented, but you can check it for yourself."

Charming!

Programmer error is definitely a possibility; I haven't worked with ADO much before. I'll head over to the list you mention.

Sam Livingston-Gray
Friday, September 26, 2003

Sam,

>> I can't set it to Null in code either, because then I get an error message stating that "Multiple-step operation generated errors". <<

This error is normally caused by the underlying database column not accepting Nulls, or at least not accepting whatever type of data you're trying to push into it. Have you checked the constraints on the underlying database column?

As Matt said earlier, a VB string can't be Null in a database sense, although you can set it to vbNull (which is definitely *not* the same as a database Null).

HTH,

Mark
----
Author of "Comprehensive VB .NET Debugging"
http://www.apress.com/book/bookDisplay.html?bID=128

Mark Pearce
Friday, September 26, 2003

For the record, I'm well aware that a String can't contain a Null. (How newbie do you think I am? (= ) I just find it amusing that combo boxes in Access, when you clear them, know to set their underlying value to Null rather than an empty string -- and, in fact, some of the 3rd-party text box controls I'm using have an option to do that as well! (=

As for the constraints on the underlying database column (and I did mention that I'm using Access 2000 with an MDB, right?), the column is a long integer field, which is definitely nullable through the Access table interface (and via a SQL statement). It's only complaining when I set the field to Null (not vbNull, which evaluates to 1 thanks to type coercion) with the statement:

rs.Fields(sFieldName) = Null

Interestingly, calling UpdateRecordset before I do this, then setting fields to Null, then calling UpdateRecordset *again* works fine. Hm.

Sam Livingston-Gray
Friday, September 26, 2003

Sam,

As mentioned earlier, I really think you would be wiser to post this question on a VB list. You are probably missing something really obvious.

<pedant mode on>
You stated:
not vbNull, which evaluates to 1 thanks to type coercion

That is not really type coercion. It is just because vbNull is a member of an enum (and members of an enum always are numeric). Type coercion would be something like assigned a string to a numeric (or vice versa).
<pedant mode off>

Seeya

Matthew
Sunday, September 28, 2003