Creating adodb.Recordset that allows NULLs. 
Author Message
 Creating adodb.Recordset that allows NULLs.

Help!

I am trying to create a recordset for local use using the following code.
One of the fields must be Nullable, but it does not appear to be working.
Does anyone have any ideas?

      Dim rs As New ADODB.Recordset

  With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Fields.Append "Test", adVarChar, 24
    .Fields("test").Attributes = adFldUpdatable Or adFldIsNullable Or
adFldMayBeNull
    .Open
    .AddNew
    ![Test] = Null
  End With

When this runs, I get

An attempt was made to update a column to NULL which is not nullable.

Interestingly enough, after the Recordset is opened, the Attributes property
for the Test field resets to 4 (adFldUpdatable), and strips off the
adFldIsNullable and adFldMayBeNull bits.  I think if those bits were to be
preserved through the Open, I would be Ok.  Ideas?

I can make it work if I change the CursorType to adOpenDynamic, but it
reverts to adOpenStatic after the Recordset opens, but the Attributes
property is now 100 (as it was set in the .Fields("test").Attributes = ).
Arrgh!

Brandon



Fri, 15 Mar 2002 03:00:00 GMT  
 Creating adodb.Recordset that allows NULLs.
Did you try ![test] = "" ?
Null is converted to "" and "" to null (for text fields)

Brandon a crit dans le message

Quote:
>Help!

>I am trying to create a recordset for local use using the following code.
>One of the fields must be Nullable, but it does not appear to be working.
>Does anyone have any ideas?

>      Dim rs As New ADODB.Recordset

>  With rs
>    .CursorLocation = adUseClient
>    .CursorType = adOpenStatic
>    .LockType = adLockOptimistic
>    .Fields.Append "Test", adVarChar, 24
>    .Fields("test").Attributes = adFldUpdatable Or adFldIsNullable Or
>adFldMayBeNull
>    .Open
>    .AddNew
>    ![Test] = Null
>  End With

>When this runs, I get

>An attempt was made to update a column to NULL which is not nullable.

>Interestingly enough, after the Recordset is opened, the Attributes
property
>for the Test field resets to 4 (adFldUpdatable), and strips off the
>adFldIsNullable and adFldMayBeNull bits.  I think if those bits were to be
>preserved through the Open, I would be Ok.  Ideas?

>I can make it work if I change the CursorType to adOpenDynamic, but it
>reverts to adOpenStatic after the Recordset opens, but the Attributes
>property is now 100 (as it was set in the .Fields("test").Attributes = ).
>Arrgh!

>Brandon



Fri, 15 Mar 2002 03:00:00 GMT  
 Creating adodb.Recordset that allows NULLs.
I certainly hope you're wrong, as a null string ("") and Null are NOT
the same thing!

A null string means there is no value. Null means the value is not
known.

If you don't have a middle name, then "" would be a correct thing to
store for it. However, if I didn't know whether or not you had a middle
name, I would want to store Null. Using "" (which says you don't have
one) would not be correct!

Quote:

> Did you try ![test] = "" ?
> Null is converted to "" and "" to null (for text fields)

> Brandon a crit dans le message

> >Help!

> >I am trying to create a recordset for local use using the following code.
> >One of the fields must be Nullable, but it does not appear to be working.
> >Does anyone have any ideas?

> >      Dim rs As New ADODB.Recordset

> >  With rs
> >    .CursorLocation = adUseClient
> >    .CursorType = adOpenStatic
> >    .LockType = adLockOptimistic
> >    .Fields.Append "Test", adVarChar, 24
> >    .Fields("test").Attributes = adFldUpdatable Or adFldIsNullable Or
> >adFldMayBeNull
> >    .Open
> >    .AddNew
> >    ![Test] = Null
> >  End With

> >When this runs, I get

> >An attempt was made to update a column to NULL which is not nullable.

> >Interestingly enough, after the Recordset is opened, the Attributes
> property
> >for the Test field resets to 4 (adFldUpdatable), and strips off the
> >adFldIsNullable and adFldMayBeNull bits.  I think if those bits were to be
> >preserved through the Open, I would be Ok.  Ideas?

> >I can make it work if I change the CursorType to adOpenDynamic, but it
> >reverts to adOpenStatic after the Recordset opens, but the Attributes
> >property is now 100 (as it was set in the .Fields("test").Attributes = ).
> >Arrgh!

> >Brandon

--

Beer, Wine and Database Programming.  What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://webhome.idirect.com/~djsteele/



Fri, 15 Mar 2002 03:00:00 GMT  
 Creating adodb.Recordset that allows NULLs.
Brandon, try setting the attributes in the parameter for the Append method:

 Dim rs As New ADODB.Recordset

  With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Fields.Append "Test", adVarChar, 24, adFldUpdatable Or adFldIsNullable
Or adFldMayBeNull
    .Open
    .AddNew
    !Test = Null
  End With

If you check the Attributes property before the Open it will be 0 but after
the Open it should be 100.

HTH


Quote:
> Help!

> I am trying to create a recordset for local use using the following code.
> One of the fields must be Nullable, but it does not appear to be working.
> Does anyone have any ideas?

>       Dim rs As New ADODB.Recordset

>   With rs
>     .CursorLocation = adUseClient
>     .CursorType = adOpenStatic
>     .LockType = adLockOptimistic
>     .Fields.Append "Test", adVarChar, 24
>     .Fields("test").Attributes = adFldUpdatable Or adFldIsNullable Or
> adFldMayBeNull
>     .Open
>     .AddNew
>     ![Test] = Null
>   End With

> When this runs, I get

> An attempt was made to update a column to NULL which is not nullable.

> Interestingly enough, after the Recordset is opened, the Attributes
property
> for the Test field resets to 4 (adFldUpdatable), and strips off the
> adFldIsNullable and adFldMayBeNull bits.  I think if those bits were to be
> preserved through the Open, I would be Ok.  Ideas?

> I can make it work if I change the CursorType to adOpenDynamic, but it
> reverts to adOpenStatic after the Recordset opens, but the Attributes
> property is now 100 (as it was set in the .Fields("test").Attributes = ).
> Arrgh!

> Brandon



Mon, 18 Mar 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Creating adodb.Recordset that allows NULLs.

2. Creating adodb.record from Adodb.recordset

3. VB6 ADODB.Recordset.Field = NULL Error

4. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

5. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

6. VB6 ADODB.Recordset.Field = NULL Error

7. How do I allow NULL value on a required field in disconnected recordset

8. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

9. ADODB.Connection and ADODB.Recordset question

10. ADODB.Connection.Execute VS ADODB.Recordset.Update

11. ADODB.connection & ADODB.Recordset

12. How to set DbGrid datasource to a recordset created w/ADODB.Command

 

 
Powered by phpBB® Forum Software