Inserting Null Values Through INSERT INTO Statement

I've got this problem, that I can't get around. I'm sending a null value through an SQL "INSERT INTO" statement using ADO, but the database does not accept the value in either date or numeric data type field. Even it's allow null is set to true. I've tested this problem with SQL Server and Microsoft Access. is there any solution for this problem, I need to do it with Insert into statement because I'm using Data Environment. (The field is optional, some
time I have to send values and some time not. So it is necessary to mention the name of field in the query, and obviously I can't make two queries for the one optional parameter because what if I have 2 or more optional parameters???)
This problem is also with command object.
I dont want to use ..

Rs.AddNew
Rs.fields("m_ID") = null
Rs.Update




I've tried everything, and it seems impossible through vb. You may accept it as a challenge.

Remember.. INSERT INTO STATEMENT .. NULL VALUES.

have you tried passing it as a literal, i.e

Rs.fields("m_ID") = 'NULL'

Andrew

What about doing this..

If there are multiple fields in your Database, then insert only those that are needed ( example the primary key )

rs.addnew
rs.Fields("PrimaryKey") = "Key"
rs.update

Null will be in the values that aren't needed.

Nic

Thanx Andrew, but as I already said, I don't want to use
the following

Rs.AddNew
Rs.fields("Fieldname") = 'Value'
Rs.Update



Because I'm using "INSERT INTO STATEMENT".

Sorry, misunderstood that.

You could actually use that in a insert statement

strSql = "INSERT INTO TABLE (<fields>)"
strSQL = strSQl & " VALUES (<value_1>, 'NULL', <value_3>,..,<value_n>)
rs.Sql = strSql
rs.Requery

Andrew

INSERT INTO SomeTable (SomeField, SomeOtherField)
VALUES ('SomeValue',null)



will insert Null value in second field

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook