Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.
COURSE of the MONTH
13 days, 6 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
Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s quite interesting for me as I worked with Excel using vb.net for some time.
Here are some topics which I know want to share with others whom this might help.
First of all if you are working with Excel then you need to Download the Following
…
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.
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month13 days, 6 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.