• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1392
  • Last Modified:

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
0
schwientekd
Asked:
schwientekd
  • 4
  • 2
1 Solution
 
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
 
käµfm³d 👽Commented:
Do you have a "default value" set on the table?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
PagodNaUtakCommented:
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now