Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Learn how Concerto can help you.
Solved
Insert NULL or blank value in smalldatetime field SQL 2005
Posted on 2011-03-09
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
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
[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
Active today
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.
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.
Active today
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
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
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
Featured Post
The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
So every once in a while at work I am asked to export data from one table and insert it into another on a different server. I hate doing this. There's so many different tables and data types. Some column data needs quoted and some doesn't. What …
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.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
- Document Management
- Printers and Scanners
- Document Imaging
- Software-Other
- Images and Photos
- Adobe Acrobat, Photos / Graphics Software, Security, *scanning, *PDF
Suggested Courses
Course of the Month12 days, 4 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.