NULL dates are driving me nuts

[LinXG]

Well not really but......

I have an insert statement that looks mostly like this.....

mySQL = "INSERT INTO myTABLE (someDumbDate) VALUES (#" & aTextBox.text & "#)"

This works fine when aTextBox.text has a real date in it but is causing me unspeakable grief when aTextBox.text = "".

Is there an easy way around this?

Database: MS Access 2003
Language: Visual Basic (VS 2005)

[si_the_geek]

You can't use blank as a Null in an SQL statement, you need to explicitly specify it, eg:

... VALUES (Null)"

[Hack]

You can also do

If aTextBox.Text <> vbNullString Then
   mySQL = "INSERT INTO myTABLE (someDumbDate) VALUES (#" & aTextBox.text & "#)"
End If

[LinXG]

Hi Hack and Si;

I was expecting those answers so it looks like workaround time has arrived.

Thanks guys.

[GaryMazzone]

I normally do this if a date value can be null (like a closed date):

Dim strCDate As String
strCDate = "NULL"
if txtCDate.Text <> vbNullString Then
   If IsDate(txtCDate) Then'
      strCDate = "#" txtCDate & "#"
   End If
End If
Dim mySQL as String
mySQL = "INSERT INTO myTABLE (someDumbDate) VALUES (" & strCDate & ")"

[LinXG]

Thanks Gary;

Your suggestion is what I'll most likely do.

Thanks

[Megistal]

Isn't there a possibility to trap the error with a try and catch?

Also, for your information, your query is most probably vulnerable to SQL injection.

[GaryMazzone]

No the query is not vunrable to injection attack. You are testing for valid date before doing anything with the value if it is not a valid date then the NULL will be stored to the database. Every is yelling about the injection attacks if you validate data properly and check for single qoutes inside text strings that will not be an extremly likely attack. And yes I know that all the experts here say the only way to perform SQL inserts is to use parameterized queries only.

[Megistal]

Using a value directly from the textbox

mySQL = "INSERT INTO myTABLE (someDumbDate) VALUES (#" & aTextBox.text & "#)"

sound to me like a real issue.

[si_the_geek]

It would be if there weren't checks before it.. in this case (where the value must be a valid date, otherwise it is replaced by Null), it is perfectly safe.