Inserting NULL values in to int Field in SQL

Inserting NULL values in to int Field in SQL

Post by Yong » Sat, 31 Aug 2002 01:11:18



Hi

I am trying to set a field to NULL, but am not having much joy. Using VB6
and ADO 2.6.

The adoRs.Open selects a row to update, using ForwardOnly cursor and
Optimistic locking.
How do I set an int (SQL Defined) field to NULL.

I need to use this type of synatx for the update not an adoCommand Object:-

adoRs("MyIntValue").Value = adodb.datatypeenum.adEmpty
adoRs.Update

The line of code above, works but sets the value to zero, so any queries
doing "SELECT * FROM Table1 WHERE MyIntValue IS NULL" will fail.

I have tried VBs .Value = Empty , but this generates the following error:-

Error 3219 - Operation is not allowed in this context

Thanks in advance!

 
 
 

Inserting NULL values in to int Field in SQL

Post by MS » Sat, 31 Aug 2002 01:28:44


Did you try:

adoRs("MyIntValue").Value = NULL
adoRs.Update

This should work as long as the field allows nulls.

...joe


Quote:> Hi

> I am trying to set a field to NULL, but am not having much joy. Using VB6
> and ADO 2.6.

> The adoRs.Open selects a row to update, using ForwardOnly cursor and
> Optimistic locking.
> How do I set an int (SQL Defined) field to NULL.

> I need to use this type of synatx for the update not an adoCommand
Object:-

> adoRs("MyIntValue").Value = adodb.datatypeenum.adEmpty
> adoRs.Update

> The line of code above, works but sets the value to zero, so any queries
> doing "SELECT * FROM Table1 WHERE MyIntValue IS NULL" will fail.

> I have tried VBs .Value = Empty , but this generates the following error:-

> Error 3219 - Operation is not allowed in this context

> Thanks in advance!


 
 
 

Inserting NULL values in to int Field in SQL

Post by Yong » Sat, 31 Aug 2002 01:47:23


Hi Joe

Yeah I tried that but it gave me

Quote:> > Error 3219 - Operation is not allowed in this context

And yes the SQL column is set to allow NULLs
Is there any sort of specific type of cursor , I need to use?


> Did you try:

> adoRs("MyIntValue").Value = NULL
> adoRs.Update

> This should work as long as the field allows nulls.

> ...joe



> > Hi

> > I am trying to set a field to NULL, but am not having much joy. Using
VB6
> > and ADO 2.6.

> > The adoRs.Open selects a row to update, using ForwardOnly cursor and
> > Optimistic locking.
> > How do I set an int (SQL Defined) field to NULL.

> > I need to use this type of synatx for the update not an adoCommand
> Object:-

> > adoRs("MyIntValue").Value = adodb.datatypeenum.adEmpty
> > adoRs.Update

> > The line of code above, works but sets the value to zero, so any queries
> > doing "SELECT * FROM Table1 WHERE MyIntValue IS NULL" will fail.

> > I have tried VBs .Value = Empty , but this generates the following
error:-

> > Error 3219 - Operation is not allowed in this context

> > Thanks in advance!

 
 
 

Inserting NULL values in to int Field in SQL

Post by Yong » Sat, 31 Aug 2002 02:06:55


Thanks Joe,

You are right setting = Null works, it was another line in my code which was
causing it to fail updating


> Hi Joe

> Yeah I tried that but it gave me

> > > Error 3219 - Operation is not allowed in this context

> And yes the SQL column is set to allow NULLs
> Is there any sort of specific type of cursor , I need to use?



> > Did you try:

> > adoRs("MyIntValue").Value = NULL
> > adoRs.Update

> > This should work as long as the field allows nulls.

> > ...joe



> > > Hi

> > > I am trying to set a field to NULL, but am not having much joy. Using
> VB6
> > > and ADO 2.6.

> > > The adoRs.Open selects a row to update, using ForwardOnly cursor and
> > > Optimistic locking.
> > > How do I set an int (SQL Defined) field to NULL.

> > > I need to use this type of synatx for the update not an adoCommand
> > Object:-

> > > adoRs("MyIntValue").Value = adodb.datatypeenum.adEmpty
> > > adoRs.Update

> > > The line of code above, works but sets the value to zero, so any
queries
> > > doing "SELECT * FROM Table1 WHERE MyIntValue IS NULL" will fail.

> > > I have tried VBs .Value = Empty , but this generates the following
> error:-

> > > Error 3219 - Operation is not allowed in this context

> > > Thanks in advance!

 
 
 

Inserting NULL values in to int Field in SQL

Post by Sukesh Hooga » Sat, 31 Aug 2002 02:05:52


Yong

Try the following

1)  adoRs("MyIntValue").Value = strConv(Null,vbUppercase)
     adoRs.Update

In VB, NULL gets converted propercase to Null, which databases may not
accept/recognise.

2) Dim vNull As Variant
    vNull=Null
    adoRs("MyIntValue") = vNull

3) "SELECT * FROM Table1 WHERE MyIntValue ISNULL"

Sukesh


Quote:> Hi

> I am trying to set a field to NULL, but am not having much joy. Using VB6
> and ADO 2.6.

> The adoRs.Open selects a row to update, using ForwardOnly cursor and
> Optimistic locking.
> How do I set an int (SQL Defined) field to NULL.

> I need to use this type of synatx for the update not an adoCommand
Object:-

> adoRs("MyIntValue").Value = adodb.datatypeenum.adEmpty
> adoRs.Update

> The line of code above, works but sets the value to zero, so any queries
> doing "SELECT * FROM Table1 WHERE MyIntValue IS NULL" will fail.

> I have tried VBs .Value = Empty , but this generates the following error:-

> Error 3219 - Operation is not allowed in this context

> Thanks in advance!

 
 
 

Inserting NULL values in to int Field in SQL

Post by Alejandro Mes » Sat, 31 Aug 2002 02:08:40


Try,

adoRs("MyIntValue").Value = vbNull

AMB


> Hi Joe

> Yeah I tried that but it gave me

> > > Error 3219 - Operation is not allowed in this context

> And yes the SQL column is set to allow NULLs
> Is there any sort of specific type of cursor , I need to use?



> > Did you try:

> > adoRs("MyIntValue").Value = NULL
> > adoRs.Update

> > This should work as long as the field allows nulls.

> > ...joe



> > > Hi

> > > I am trying to set a field to NULL, but am not having much joy. Using
> VB6
> > > and ADO 2.6.

> > > The adoRs.Open selects a row to update, using ForwardOnly cursor and
> > > Optimistic locking.
> > > How do I set an int (SQL Defined) field to NULL.

> > > I need to use this type of synatx for the update not an adoCommand
> > Object:-

> > > adoRs("MyIntValue").Value = adodb.datatypeenum.adEmpty
> > > adoRs.Update

> > > The line of code above, works but sets the value to zero, so any
queries
> > > doing "SELECT * FROM Table1 WHERE MyIntValue IS NULL" will fail.

> > > I have tried VBs .Value = Empty , but this generates the following
> error:-

> > > Error 3219 - Operation is not allowed in this context

> > > Thanks in advance!

 
 
 

Inserting NULL values in to int Field in SQL

Post by Adam D. Barrat » Sat, 31 Aug 2002 02:36:28


[...]

Quote:> I am trying to set a field to NULL, but am not having much joy. Using
> VB6 and ADO 2.6.
[...]
> adoRs("MyIntValue").Value = adodb.datatypeenum.adEmpty

That will never work. DataTypeEnum is exactly what it says - an
enumeration of data types supported by ADO. Setting the contents of the
field to a member of that enum will simply result in a numeric value
being inserted, as you've discovered.

If you're simply updating the record, I'd also strongly suggest dumping
the Recordset object and writing your own UPDATE SQL statement.

[...]

Quote:> I have tried VBs .Value = Empty , but this generates the following
> error:-

> Error 3219 - Operation is not allowed in this context

Empty is *very* different from NULL. If you want to set the field to
NULL, set it to NULL; hardly rocket science. ;-)

  adoRS.Fields("foo").Value = NULL

hth

Adam
--
Duty, n:
        What one expects from others.
                -- Oscar Wilde

 
 
 

Inserting NULL values in to int Field in SQL

Post by Adam D. Barrat » Sat, 31 Aug 2002 03:31:07


I believe I've pointed both of these out in the past, but since you're
still offering them as advice:

[...]

Quote:> 1)  adoRs("MyIntValue").Value = strConv(Null,vbUppercase)
>     adoRs.Update

  StrConv(Null,x)

will **always** be Null, regardless of the value of x. That is, it will
be the value Null, *not* a string containing the characters n, u, l and
l.

Try

  ?StrConv(Null,vbUpperCase), TypeName(StrConv(Null,vbUpperCase))

in the immediate window and see for yourself.

Quote:> In VB, NULL gets converted propercase to Null, which databases may not
> accept/recognise.

I have yet to come across a database that's case-sensitive in that way.
In fact, I would be astonised if such a database exists. If it does, the
programmer(s) should be shot, since the SQL standard (ISO/ANSI SQL99)
specifies that all tests for equivalence to language syntax elements
should be performed in a case-insensitive manner (specifically by
upper-casing the values before attempting the equivalence test).

NULL === null === NuLl in the same way as SELECT === select === sELeCt.

Adam
--
The trouble with eating Italian food is that five or six days later
you're hungry again.
                -- George Miller

 
 
 

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

Dear Guru's,

When I make an ADODBconnection to Excel files with Jet 3.51 and execute a
SQLquery statement to an ADODBrecordset I have the following issue:
If there are numeric values in general cellformat the
recordset.fields(i).value=null. If I make the cellformat=text then the
returned value's aren't NULL.
What is the problem? Should I change some recordset properties?
If yes: what, how?

2. Unexpected Error happened on 'Open Table'

3. Check for NULL field value without returning field value

4. Transaction Log filling up under strange conditions

5. Snitz forum trying to insert null values into autonumbered (IDENTITY) fields

6. PostgreSQL v6.1 Released

7. quote in WHERE clause (SQL7 / VB6)

8. inserting null values into not null column in Server 7.0

9. help! conversion of null int value to varchar()

10. null values in INT/FLOAT columns........

11. Inserting Null values using Bulk Insert

12. HOWTO: Declare table field having Default Value - NULL or NOT NULL