Solved

Insert NULL or blank value in smalldatetime field SQL 2005

Posted on 2011-03-09
7
1,232 Views
Last Modified: 2012-05-11
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
Comment
Question by:schwientekd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35086958
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
 

Author Comment

by:schwientekd
ID: 35087039
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35087333
Do you have a "default value" set on the table?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:schwientekd
ID: 35087357
No, there is no default value for that field.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35088879
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35088891
P.S.

What is the type defined as in the source table that this column comes from?
0
 
LVL 8

Accepted Solution

by:
PagodNaUtak earned 500 total points
ID: 35090350
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question