How to support nullable integer (number) field in Access / VBA (1 Viewer)

[mdlueck]

I have run into a dilemma that integer (number) variables in VBA do not seem to support null, but as for a column type in a table, that is allowed to support null.

I recall while developing with the DB2 database, that the DB2 driver / API supported the concept of fields having null indicator variables. That is a second variable that may be checked to find out the null state of the field in question. The correct way to deal with nullable fields on DB2 was to use that second variable, do the null check BEFORE attempting to access the variable which is suppose to contain the value of said field.

Does Access / VBA have anything comparable in ADODB.Recordset objects?

As-is, Access / VBA turns the NULL that was in that field of the record into 0 upon issuing a SQL UPDATE as the Integer variable defaults to 0.

 

[mdlueck]

Answering my own question, according to:

VBA Traps: Working with Variables
Assigning Null
http://allenbrowne.com/ser-30.html

It would appear that I MUST use a Variant type variable. No mention of a "Null Indicator Variable", so I will head down the Variant path.

 

[gemma-the-husky]

it's more a question of what a variable is

an integer data type is just a simple memory location. a field (and a variant) is a more complex structure.

 

[mdlueck]

Nope, not so simple of a fix...

In the watches window I have opened to the ADODB.Paramemters object which sent the NULL to SQL Server. I have a value of Null, and the type is AdSmallInt which matches the database column type. The call to .Parameters.Refresh correctly sets SmallInt (SQL Server) type columns.

The update was successful.

In SQL Server Management Console, doing a SELECT, I now see 0 as that field's value, where NULL formerly was.

So, looks like more ADO strangeness with SQL Server.

Oh, and this is doing the update via Stored Procedure (SP)... I will check if there is a way to properly support NULL numeric values in SP's.

 

[vbaInet]

I have run into a dilemma that integer (number) variables in VBA do not seem to support null, but as for a column type in a table, that is allowed to support null.

I recall while developing with the DB2 database, that the DB2 driver / API supported the concept of fields having null indicator variables. That is a second variable that may be checked to find out the null state of the field in question. The correct way to deal with nullable fields on DB2 was to use that second variable, do the null check BEFORE attempting to access the variable which is suppose to contain the value of said field.

What are you talking about here, an Integer variable that can be set to Null or a field with a Number type that can be set to Null? These are two different things.

Any field (be it Integer or otherwise) can be set to Null. An Integer variable on other hand cannot be set to Null, not in any programming language I'm aware of. It is a primitive type.

If your Integer field is displaying 0 after you've set it to Null it's a design issue. You most likely have done one of two things:

1. Set the Default Value property of the field or control
2. Set the Format property to 0 for Null values

 

[mdlueck]

Ja, a bit of mind bending going on dealing with the differences of Integers in the database table and Integers in VBA in the Access context.

So, I have converted over to storing this field in a VBA String.

I have implemented the setting of the ADO.Parameters object as the following:

    If Me.ver = "-" Then
      .Parameters("@ver").Value = Null
    Else
      .Parameters("@ver").Value = Me.ver
    End If

Yes, I am using a "-" in the field to indicate NULL. Thus another reason to change the variable in VBA to a String.

When the UPDATE is performed, I end up with 0 in that field, replacing NULL. However I saw the code set the ADO.Parameters obj to Null.

I am thinking to execute the SP with SQL in the Management Console and see if that is able to retain the NULL correctly. If that is successful, consider a alternate design as follows:

<><><><>

As an alternate design, I am considering making optional DB fields have a default value in the SP. After making the call to:

.Parameters.Refresh

to auto create the necessary .Parameters objects, see about deleting the Parameters object if it needs to be null.

Not setting a Parameters object results in an error, thus that can not be a design alternative.

Any advice on being able to store NULL to an Integer non-required database field using an ADO.Parameters object?

 

[mdlueck]

Choosing to delete the Parameters object results in a bewildering error message based on the fact that these are named Parameters...

Date: 20111216 Time: 09:44:49 UserID: c_mlueck
AppErrorMsg: Class: clsObjPartsTbl, Function: Update()
Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Number: -2147217913
Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: int is incompatible with date
MessageText: Error not found.

The code is updated as follows:

    If Me.ver = "-" Then
      .Parameters.Delete ("@ver")
    Else
      .Parameters("@ver").Value = Me.ver
    End If

And I defaulted the @ver Parameter to NULL in the SP.

Any alternate suggestions to try?

 

[vbaInet]

Can you tell me what kind of SQL statement you're running? A SELECT query or an UPDATE query? And I'm guessing that @ver is pointing to a variable that should be set to Null (depending on the criteria above)?

What is the datatype of @ver?

 

[mdlueck]

Oh, and I did try executing the SP from the SQL Server Management Console and that is able to store NULL values via the SP successfully.

So it is just a problem via Access / VBA / ADO objects.

 

[mdlueck]

Can you tell me what kind of SQL statement you're running? A SELECT query or an UPDATE query?

UPDATE

And I'm guessing that @ver is pointing to a variable that should be set to Null (depending on the criteria above)?

What is the datatype of @ver?

In the database it is an integer column. I am using a String now in VBA so that I can have a "NULL indicator character" of "-".

 

[vbaInet]

In the database it is an integer column. I am using a String now in VBA so that I can have a "NULL indicator character" of "-".

Ok. So why don't you just use the Format property to display "-" when the value is Null?

 

[mdlueck]

Ok. So why don't you just use the Format property to display "-" when the value is Null?

This is not a display problem, it is an UPDATE problem.

I seem to be able to successfully SELECT a NULL value from an Integer column. The following LOC is used to read the ver column:

Me.ver = Nz(adoRS!ver, "-")

Where I see NULL in SQL Server Management Console issuing a SELECT, I see "-" in the Access Form. So I believe that LOC must be working as expected.

However I am not able to UPDATE (save back to the record) a NULL value.

It is a problem with the DB storage, not the VBA forms.

 

[mdlueck]

hhhmmm... seems like it is an issue with the ADO.Parameters object. I found out the "correct" way to request the database to use the DEFAULT value for a SP Parameter...

    If Me.ver = "-" Then
     [B] .Parameters("@ver").Value = Default[/B]
    Else
      .Parameters("@ver").Value = Me.ver
    End If

However after that LOC executes, I see a 0 as that Parameters's value staring me in the face in the Watches window, and sure enough the UPDATE executes and I see 0 in the database.

 

[vbaInet]

@ver needs to be set to a data type that accepts Null. String will not.

 

[mdlueck]

@ver needs to be set to a data type that accepts Null. String will not.

In that code I keep fiddling with is where the VBA variables mesh up with the ADO.Parameters. Specifically if the VBA sees the "null indicator value" in the string then I am attempting to assign NULL or DEFAULT to the Parameters object.

If I place Null in the Parameters object, I see Null in the watches window as the value, however once the update executes I see 0 in the table.

Trying then to default that column / SP parameter is even worse... when I assign Default as the value, immediately I see 0 as the Parameters object value.

Works just fine every which way driving the SP with T-SQL in the Management Studio.

 

[mdlueck]

What is the Access / ADO equiv of "DBNull.Value" in ADO.Net? I see refefences that if you set the Parameters object to a value of "DBNull.Value" that tells ADO to specify SQL NULL which is what I am trying to achieve.

 

[vbaInet]

Can I see your stored proc.

 

[mdlueck]

What is the Access / ADO equiv of "DBNull.Value" in ADO.Net? I see refefences that if you set the Parameters object to a value of "DBNull.Value" that tells ADO to specify SQL NULL which is what I am trying to achieve.

This apearantly is the way to do "DBNull.Value" in Access / VBA:

    If Me.ver = "-" Then
      .Parameters("@ver").Value = [B]Empty[/B]
    Else
      .Parameters("@ver").Value = Me.ver
    End If

I thought I had tried that and it blew up upon execution. At this point I find that working reliably. (shrug)

Now onto handling SELECTing back out NULL values. (Might perhaps result in another thread!)

 

[mdlueck]

Oh! I just found out that it is the solution above in Access / VBA IN ADDITION TO specifying a default value (of null) in the SP:

  @ver AS smallint = NULL

I went on to correcting another column and the above fix resulted in an error message executing the SP as follows:

Date: 20111216 Time: 16:15:43 UserID: c_mlueck
AppErrorMsg: Class: clsObjPartsTbl, Function: Update()
Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Number: -2147217900
Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function 'clsObjPartsTbl_Update' expects parameter '@quoteid', which was not supplied.
MessageText: Error not found.

So I guess more correctly stated is this is the solution to be able to have the SP use the default value specified for the SP Parameter... NOT the same as successfully passing NULL as the Parameter Value.