Null insertion in Microsoft SQL

Hey All,

I am using Microsoft SQL Server

I am stuck up with this prob for some time. I have a data entry form. If the user submits the form without entering any data, the record should be inserted with null entries for the respective fields.

But when i execute the INSERT SQL query with these null values, it gives me an error.

This is vat I am doing:

<%
.....
address = request.form("address")
if request.form("Age")= "" then
ageValue=""
.....
.....
%>
I use these values in SQL query

This is the SQL statement when the above is executed
" INSERT INTO tblStatistics (Age,address) VALUES (, aaa)"

Because of no value for age, the query is not gettin executed. I have also tried
<%
if request.form("Age")= "" then
ageValue=null
....
%>

I dont want a 0, I want a nul entry (<null>).
Could someone throw some light on how to set the age field to null?

Try ageValue=null instead of ageValue=""

you an an error because you did not have a value for Age in your Insert statement.

I have tried that too, please find that in my first posting.

Also, incase my scenario is not clear:

I am not directly inserting the values. I am storing the value of age and address in the variables "ageValue" and "address". This I give as an input to the Values part of the query statement. This is what i do:

sSQL1 = "INSERT INTO tblStatistics (Age,address) Values (" &ageValue& "," &address& ")"

Since ageValue is not able to hold the null value, I get the query statement mentioned in my first posting

Well try breaking up your SQL statement to account for this then...it may be a bit more tedious, but it will produce your end result.

You have:

sSQL1 = "INSERT INTO tblStatistics (Age,address) Values (" &ageValue& "," &address& ")"


What you should do is:

sSQL1 = "INSERT INTO tblStatistics (Age,address) Values ("

if ageValue = "" OR isnull(ageValue) then
sSQL1 = sSQL1 & "NULL, "
else
sSQL1 = sSQL1 & ageValue & ", "
end if

sSQL1 = sSQL1 & "'" & address & "' ) "

I am adding the single quotes around the address because I will assume it is a string instead of a number. If it is a number, take them out.

Try that and see how it works out