NULL dates are driving me nuts
Well not really but......
I have an insert statement that looks mostly like this.....
Code:
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)
Re: NULL dates are driving me nuts
You can't use blank as a Null in an SQL statement, you need to explicitly specify it, eg:
Re: NULL dates are driving me nuts
You can also do
Code:
If aTextBox.Text <> vbNullString Then
mySQL = "INSERT INTO myTABLE (someDumbDate) VALUES (#" & aTextBox.text & "#)"
End If
Re: NULL dates are driving me nuts
Hi Hack and Si;
I was expecting those answers so it looks like workaround time has arrived.:eek:
Thanks guys.
Re: NULL dates are driving me nuts
I normally do this if a date value can be null (like a closed date):
Code:
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 & ")"
Re: NULL dates are driving me nuts
Thanks Gary;
Your suggestion is what I'll most likely do.
Thanks
Re: NULL dates are driving me nuts
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.
Re: NULL dates are driving me nuts
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.
Re: NULL dates are driving me nuts
Using a value directly from the textbox
mySQL = "INSERT INTO myTABLE (someDumbDate) VALUES (#" & aTextBox.text & "#)"
sound to me like a real issue.
Re: NULL dates are driving me nuts
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.