How to represent a null value within an Insert statement string

[Dean Slindee]

Using VS2005 and ADODB to populate tables in an Access 2003 .mdb database.
Is there a way to represent a null value in text for objMiddleName's value
when using a sql statement composed and used directly in text, like this:



'build insert values

Dim sql As String = String.Empty

Dim objLastName As Object = "Smith"

Dim objFirstName As Object = "John"

Dim objMiddleName As Object = ????????

Dim objBirthDate As Object = Today.Date





sql = "INSERT INTO tblClient " & _

"(`LastName`, `FirstName`, `MiddleName`, `BirthDate)) "
& _

"VALUES ('" & objLastName & "', '" & _

objFirstName & "', '" & _

objMiddleName & "', '" & _

objBirthDate & "')"



'insert

Try

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



Thanks,

Dean S

 

[Douglas J. Steele]

You're more likely to get a correct answer in a newsgroup related to .Net,
but I would think that you want String variables for objLastName,
objFirstName, objMiddleName and objBirthDate, not Objects.

To pass a value of Null in a SQL statement, you use the literal Null.

BTW, Dates must be delimited with # characters, and regardless of what your
Regional Settings may be, they must be in mm/dd/yyyy format (or an
unambiguous format such as yyyy-mm-dd or dd mmm yyyy)

Ultimately, your SQL statement should look like:

INSERT INTO tblClient (LastName, FirstName, MiddleName, BirthDate)
VALUES ('Smith', 'John', Null, #05/26/2007#)

Note that there are no quotes around the names of the fields (plus you had
too many closing parentheses). If there are spaces or special characters in
the field (or table) names, you enclose them in square brackets:

INSERT INTO [tblClient] ([LastName], [FirstName], [MiddleName], [BirthDate])

Your other option, of course, is simply to leave MiddleName out of the SQL:

INSERT INTO tblClient (LastName, FirstName, BirthDate)
VALUES ('Smith', 'John', #05/26/2007#)

Note that in either case, MiddleName must be not be defined as Required.