Solved

Insert NULL or blank value in smalldatetime field SQL 2005

Posted on 2011-03-09
7
1,185 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 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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
Comment Utility
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 74

Expert Comment

by:käµfm³d 👽
Comment Utility
Do you have a "default value" set on the table?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:schwientekd
Comment Utility
No, there is no default value for that field.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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 74

Expert Comment

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

11 Experts available now in Live!

Get 1:1 Help Now