Solved

Insert NULL or blank value in smalldatetime field SQL 2005

Posted on 2011-03-09
7
1,200 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now