1

I am trying to pass a Null value for Data in SQL Server, but I keep getting a value of '1900-01-01'

strSQL = "INSERT INTO Table (Data_Col) VALUES ('Null')"

How do I get a Null value instead of this value '1900-01-01'?

Edit: This gives an error 

strSQL = "INSERT INTO Ex_Tbl (Data_1, Data_2, Date_1, Date_2, Data_3)
            VALUES (1,2,'2015-3-2',,30)"

But this does not 

strSQL = "INSERT INTO Ex_Tbl (Data_1, Data_2, Date_1, Date_2, Data_3)
           VALUES (1,2,'2015-3-2','',30)"

Edit No.2 Do I need to make the string look like this.

strSQL = "INSERT INTO Ex_Tbl (Data_1, Data_2, Date_1, Date_2, Data_3)
           VALUES (1,2,'2015-3-2',Null,30)"

If so, I am going to need code like this correct,

strSQL = "INSERT INTO Ex_Tbl (Data_1, Data_2, Date_1, Date_2, Data_3) 
           VALUES (1,2,'2015-3-2'," & "Null" & ",30)"
Improve this question
4
  • You're still not doing what the answer suggests. Put ,NULL, in where you currently have ,, Commented Dec 24, 2015 at 12:12
  • @Damien_The_Unbeliever I did that in the code, but I ended with a value like this, VALUES (1,2,'2015-3-2','',30). Check my edit in the OP. Is that what you mean? Commented Dec 24, 2015 at 12:31
  • You haven't mentioned a host language, which is obviously (from your examples) involved also - you're mangling strings together to try to achieve a result. You should be looking into whether parameters are supported which allows you to separate the query text (which you can then write once) from data that you want to supply. Frequently, once you switch to using parameters, it's far easier to deal with nullable data. (Not to mention that, unlike string mangling, you'll be safe from SQL injection) Commented Dec 24, 2015 at 17:09
  • @Damien_The_Unbeliever Really, I had no idea I was mangling the strings, I thought this was the way it needed to be done? As for language, it is done in VBA. And I am pretty new to all this (This is my first time using SQL Server), what is SQL injection. Thanks for the help thus far. Commented Dec 24, 2015 at 23:34

1 Answer 1

Reset to default
5

Try strSQL = "INSERT INTO Table (Data_Col) VALUES (NULL)"

Null in quotes counts a string = Invalid date format = changes to 1900-01-01.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Matt's right on target. This is a SQLFiddle to help OP try out this method: sqlfiddle.com/#!3/15ab4/1
When I try to pass the Null value without the quotes I get an error. And I do have allow nulls selected on the table. @zedfoxus, that seems like a great tool, how do you work it?
@JapanDave - "I get an error" - well, that error probably contains more information which may be useful to give to people who might be trying to help you.
Sorry I am not at my PC so I can't give you the error code. I can give you the string value to see if the SQL statement is correct though.
@Damien_The_Unbeliever Sorry I am not at my PC so I can't give you the error code. I can give you the string value to see if the SQL statement is correct though. This gives an error strSQL = "INSERT INTO Ex_Tbl (Data_1, Data_2, Date_1, Date_2, Data_3) VALUES (1,2,'2015-3-2',,30)" But this does not strSQL = "INSERT INTO Ex_Tbl (Data_1, Data_2, Date_1, Date_2, Data_3) VALUES (1,2,'2015-3-2','',30)"
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.