How do I allow NULL value on a required field in disconnected recordset 
Author Message
 How do I allow NULL value on a required field in disconnected recordset

I am using  ADO 2.5 against a SQL 7.0 database.
I have a disconnected ADO recordset.  One of the fields is GUID that is
defined as not null in the Sql 7.0 database.

When I disconnect the recordset I want to be able to set the field to null,
I will trap the invalid data later when I attempt to update.

I want the user to be able to temporarily set the field to null and only be
required to enter a valid entry when they
try to save.

The problem is:

rsMaster("MyField").Value = Null
I get an error.  Remember I am disconnected at this point and no update of
the
underlying data was attempted.  I do want to enforce the rule in the
database just not in the disconnected recordset.

Is there a way to keep my database rule but use my disconnected recordset to
store null value?

Some recordset property perhaps?

Any help would be appreciated.
--
Kalman Skulski
Western Software Solutions



Sat, 29 Mar 2003 03:00:00 GMT  
 How do I allow NULL value on a required field in disconnected recordset
Hi Kalman,

Whether a Field allows NULL value is reflected in the Attributes property
of the Field object in ADO. If Attributes property of the Field object
contains adFldIsNullable, it can allow NULL value. However, the Attributes
property of Field object is read-only, so we cannot change this field to
accept NULL value if the corresponding field from the database doesn't
allow NULL value, even though the Recordset is disconnected from the
Connection.

Since the corresponding field in the SQL database doesn't allow NULL value,
ultimately you will store some non-NULL value to the database. So I think
you can capture the error when setting NULL value to this field and set it
to the non-NULL value that you will store to the SQL database in the error
handler.

Best Regards,
Elan



Sun, 30 Mar 2003 03:00:00 GMT  
 How do I allow NULL value on a required field in disconnected recordset

Hi :)

NULL is used to indicate invalid data for variants. VB provides vbNullString
and vbNullChar to do what you are trying to do. Use them :)

Hope that helps :)

--
==================
K.G.Khanna

Your one stop solution for Microsoft technologies




Sun, 30 Mar 2003 03:00:00 GMT  
 How do I allow NULL value on a required field in disconnected recordset

The field I want to update is GUID reference (uniqueidentifer) to a record
in another table.
The field does not allow nulls and  there is a foreign key restraint as
well.

What I want is to only enforce this rule when an update takes place.  not on
the field assignment.
I use disconnected recordsets and persist them elsewhere until the user
wants to commit his or her work.
I want to have a more relaxed set of rules for work in progress, but strict
when updating the database.

i.e.  I want to allow
rs.Fields("GUID").Value = Null

instead have  throw an error. I would not normally allow it to get to this
point.

rs.updateBatch
I know why I am getting the problem is there an ADO way around it?  I can
manage the storage of the value myself, but
it would be more convenient if ADO could be made to allow this.



Quote:
> Hi :)

> NULL is used to indicate invalid data for variants. VB provides
vbNullString
> and vbNullChar to do what you are trying to do. Use them :)

> Hope that helps :)

> --
> ==================
> K.G.Khanna

> Your one stop solution for Microsoft technologies





Sun, 30 Mar 2003 03:00:00 GMT  
 How do I allow NULL value on a required field in disconnected recordset

Hi Kalman,

Since the attribute that indicate if the Field is nullable in the recordset
can not be changed, I think you can store some other specail value to the
field rather than Null to workaround it. For example,
rs.Fields("GUID").Value = Empty. Then when updating to the database, detect
this specail value and change it to a value that is not Null.

Regards,
Elan



Tue, 01 Apr 2003 03:00:00 GMT  
 How do I allow NULL value on a required field in disconnected recordset

this is code from a great book titled: "Designing for Scalability with MS
Windows DNA"  The authors argue that identity columns are not always the
best idea for long-life code (and perhaps in your situation).  To provide
for your own identity column the following code retrieves the next ID and
allows the developer to insert it where necessary (i.e. update!)  Hope this
helps.  Also, the authors mention that there is a better example from
Microsoft in the MTS documentation - Island Hopper News sample - TakeANumber
component.  The MS example makes use of SPM in MTS/COM+.

------------BEGIN CODE ---------------------
Public Function GetNewNumber(strTable As String) As Integer
   Dim rs As Recordset, strSQL As String

   strSQL = "SELECT Tablename, LastIdIssued " & _
      "FROM IdTable " & _
      "WHERE TableName = '" & strTable & "'"
   Set rs = CreateObject("ADODB.Recordset")
   rs.LockType = adLockPessimistic
   rs.CursorLocation = adUseServer
   rs.CursorType = adOpenKeyset
   rs.Source = strSQL
   rs.ActiveConnection = strConn
   rs.Open
   rs!LastIdIssued = rs!LastIdIssued + 1
   rs.Update

   GetNewNumber = rs!LastIdIssued
   rs.Close

End Function
----------- END CODE --------------


Quote:
> I am using  ADO 2.5 against a SQL 7.0 database.
> I have a disconnected ADO recordset.  One of the fields is GUID that is
> defined as not null in the Sql 7.0 database.

> When I disconnect the recordset I want to be able to set the field to
null,
> I will trap the invalid data later when I attempt to update.

> I want the user to be able to temporarily set the field to null and only
be
> required to enter a valid entry when they
> try to save.

> The problem is:

> rsMaster("MyField").Value = Null
> I get an error.  Remember I am disconnected at this point and no update of
> the
> underlying data was attempted.  I do want to enforce the rule in the
> database just not in the disconnected recordset.

> Is there a way to keep my database rule but use my disconnected recordset
to
> store null value?

> Some recordset property perhaps?

> Any help would be appreciated.
> --
> Kalman Skulski
> Western Software Solutions



Thu, 24 Apr 2003 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

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

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

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

4. Allowing a date field to be null value

5. Re.allowing a date field to take null value

6. Assigning Null value to field of ADO Recordset

7. Allow A Null Value For A Date Picker

8. How to allow Null Value entry in VB Forms

9. ADO Update the Database with the modify done on a disconnected recordset in background mode

10. Creating adodb.Recordset that allows NULLs.

11. doesn't recognize Null value from a field value

12. Creating adodb.Recordset that allows NULLs.

 

 
Powered by phpBB® Forum Software