Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
Course Of The Month
4 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
by Mark Wills
Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server... So if you are the accidental DBA, or, simp…
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month4 days, 17 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.