I have this condition as part of my codes. The idea is to save a Null value if strXXX is blank.

strXXX = Request.QueryString("XXX")
If len(strXXX) <= 1 Then strXXX = Null

(or should it be If strXXX = "" Then strXXX = Null?)

strSQL = "INSERT INTO tblXXX (ColumnX) VALUES ('"& strXXX &"')"

The problem is.. instead of Null, blank was inserted into ColumnX. If strXXX is a date, instead of Null, 01-01-1900 was inserted. I know it has something to to with those inverted commas in '"& strXXX &"' but I have no idea how to fix it. Any help? Thanks heaps.

First question.

Whay would you want to enter a NULL into a field in a database?  NULL means 'undefined' or 'not known'.

What is wrong with an empty string field? Most of the time, NULLS are better avoided and alternatives preferred.

There may be legit reasons for this cause, although I can't think of any at the moment.

Perhaps if you tell us the details of what you are trying to accomplish, we might be able to provide a better solution.

Cheers,
Andrew

I'm capturing some information from users using a form. Some fields are not compulsory and may be left blank, so I thought instead of leaving them empty, it's better to save them as Nulls in the database.

As you can see, I'm rather new to this database thing, so I'm still experimenting on which one is better and which one is not. And thanks to you, I'm learning more than I expect.

I don't have any issue in saving empty string fields in my table, but how do you avoid the date "01-01-1900" from being inserted when the field is empty?

Ok. 

In your database, make sure 'Allow Nulls' option is selected. (Might be something else depending on the database - which database are you using).

If you are adding new records and you want a field to contain <NULL>, then you can simply leave it out of your INSERT statement.

If you want to UPDATE an existing record so as to record <NULL> in a field then we'll have to work out something else..

What database are you using again?

Cheers,
Andrew

I'm running SQL2000.

And I have already set the required columns to Allow Nulls.

Thing is, I can't leave those fields out of my INSERT statement since I don't know whether they'd be blanks in the first place. It all depends on the user input. If they don't enter anything, then it's Null.

So.. what's the correct syntax for my INSERT statement above?

ah ok.

Its just NULL without the quotes. So you'll need to test to see if the user entered anything before constructing your SQL statement, to avoid adding the quotes.  Assume that your form value has been transferred to the variable: theValue.

If len([TheValue]) > 0 then
   strField2 = "'" & theValue & "'"
Else
   strField2 = "NULL"
End If

strSQL = "INSERT INTO myTable (Field1, Field2, Field3)"
strSQL = strSQL & " VALUES ('" & Value1 & "',"
strSQL = strSQL & strField2
strSQL = strSQL & ",'" & Value3 & "')"
conn.execute (strSQL)

So basically, the quotes are only added to the variable strField2 if a value has been entered into that form field.

Note that you could also just leave that field out using a similar method..

Hope this helps,
Andrew

place this code before conn.execute(strSQL)

response.write strSQL
response.end

and tell us what is sent to the screen in both cases.

Well.. whaddaya know.. after quite a few trials and errors, I managed to get the whole thing working. And Andrew, there's nothing wrong with your code. My mistakes, somewhere.

That datetime error message was caused by my date format. The input I got from the form was in dd-mm-yyyy format. Somehow SQL2000 converted that to mm-dd-yyyy, which caused dates like 25-05-2002 to be invalid.

Anyway.. I've fixed that -- I changed the date format to dd-mmm-yyyy -- and everything works just fine. So far. Still a lot more to go.

Thanks heaps, Andrew. You're great.

Few....

<Hands you a beer>