Syntax to write a null value inside a sql Insert string?

[Dean Slindee]

Is there a way to represent the null value as a string (destination: varchar
column) the way that the following string represents a null value for a
date?

Public Const cNullDate As Date = #12:00:00 AM#



Writing to an Access 2003, (conversion) table with VS2005 in VB.NET.

Within the table is a field named VSubType1 with a validation rule of: "In
('P','O') Or Is Null"



The declare/move/insert statements look like this (shortened example):



Dim intRecNbr As Integer = 0

Dim VSubType1 As Object (defined as Text 1 column in Access)

Dim V1_Mom_Id As Object (Long Integer)

Dim V1_Dad_Id As Object (Long Integer)



intRecNbr += 1

VSubType1 = System.DBNull.Value

V1_Mom_Id = 0

V1_Dad_Id = 0



'insert new row

sql = "INSERT INTO Convert_Case " & _

"(`RecNbr`, `VSubType1`, `V1_Mom_Id`, `V1_Dad_Id`) " & _

"VALUES (" & intRecNbr & ", '" & _

VSubType1 & "', " & _

V1_Mom_Id & ", " & _

V1_Dad_Id & ")"



'insert

Try

Dim lngRecordsAffected As Long = 0

cnnJet.Execute(sql, lngRecordsAffected, adCmdText +
adExecuteNoRecords)



Is there any way to code the "sql =" statement to pass a null value for
VSubType1? Like, maybe setting VSubType1 = Ctrl+0 (as in SQL Server)
instead of System.DBNull.Value? Or, perhaps System.DBNull.Value is not the
right value for null within Access db?



Thanks in advance,

Dean S

 

[Graham Mandeno]

Hi Dean

A text field can have two sorts of "null" value.

If the Required property is not set, then its value can be Null. This
corresponds to a VarType of 1 in VBA. In SQL you use the keyword Null:
Update ... Set MyField = Null
Select ... where MyField is Null
Insert into MyTable (MyField) values (Null)
... etc

If the AllowZeroLength property is set, then its value can be "" (an empty
string). This corresponds to the constant vbNullString in VBA. In SQL you
use two adjacent single or double quotes ("" or ''):
Update ... Set MyField = ""
Select ... where MyField = ''
Insert into MyTable (MyField) values ("")
... etc

Note that the rule is similar for dates. If the Required property is not
set, then the value of a date field can be Null. This is different from a
value of zero, which corresponds to #30-Dec-1899 12:00:00 AM#. I would
always use a Null in preference to a zero date.