Insert NULL or blank value in smalldatetime field SQL 2005

I'm writing some code in VB.NET which inserts some values into a table.  I'm getting data from one table and inserting it into another table.  There is a date field which I want to insert a NULL or blank value if it is NULL in the source table.  As is it will insert the date 1/1/1900.  Here is my code, which I have just summarized.

For Each dr as DataRow.....

Dim myDate = dr.item(myDT.myDateColumn)

If IsDBNull(myDate) Then
myDate = ""
End If

<define connection variables....>

INSERT INTO dbo.TableName (MyDateField)  VALUES (myDate)

Next
schwientekdAsked:
Who is Participating?
 
PagodNaUtakConnect With a Mentor Commented:
The reason why you achieve this result because in your original query you use something like this:

Dim InsertStatement as string = "INSERT INTO dbo.TableName (MyDateField)  VALUES ('" & myDate & "')"

if you supply null or an empty string in the variable the query becomes something like this:


INSERT INTO dbo.TableName (MyDateField)  VALUES ('')

If you run the statement in sqlserver the server will substitute the value 1/1/1900.

So, to solve your problem, I recommend you to use sqlparamater instead. here is the link on how to use SQLParameter.

http://vbnetsample.blogspot.com/2007/10/using-sqlparameter-class.html

0
 
käµfm³d 👽Commented:
Don't set it to empty string; rather set it to DBNull.Value. I think you would be safe in removing your "if" logic, since if it is DBNull, then that is what you want to insert.
0
 
schwientekdAuthor Commented:
I tried it both ways using the if statement and removing it.  Both result in inserting a 1/1/1900 date.  The field I'm inserting into is a date of birth field so I either need a value or nothing at all to be inserted.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
käµfm³d 👽Commented:
Do you have a "default value" set on the table?
0
 
schwientekdAuthor Commented:
No, there is no default value for that field.
0
 
käµfm³d 👽Commented:
To confirm, what you tried was something similar to this?
For Each dr as DataRow.....

Dim myDate = dr.item(myDT.myDateColumn)

<define connection variables....>

INSERT INTO dbo.TableName (MyDateField)  VALUES (myDate)

Next

Open in new window

0
 
käµfm³d 👽Commented:
P.S.

What is the type defined as in the source table that this column comes from?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.