Insert NULL or "" into DB


I have a form and some of the fields are mandatory and some are not.  I control the mandatory validation with Javascript which works fine.

2 fields are not mandatory so I want to give the user to leave them blank, but when I do an update I get an Error.

Error Type:
Provider (0x80020005)
Type mismatch.
/mm/MM_stage2/departments/updatedepartmentData.asp, line 110

This is to do with the datatypes used in the DB for these to feilds.

field 1 = INT 4
field 2 = Decimal 8
I have 'Allow nulls' ticked as well

If I set these fields to 'varchar' then it works fine but I do not want these fields to be varchar for when I use them later.

I want these to be blank if the user does not enter any values in these but do not know how to do it!  

Any ideas?
Who is Participating?
aprestoConnect With a Mentor Commented:
Why dont you convert them before they are inserted into the table:

Whack an If statement right before the SQL

IF field1 = "" THEN
   Field1 = 0000
End If

The above is for your integer one

If field2 = "" THEN
  Field2 = ...

whatever your decimal default value is etc

give it a go
You should have posted the SQL you want to ewxecute in line 110... that yould help. I would suggest you try to input kind of that:

INSERT INTO ... (fields1,...) values ('',...)

Error because '' can not bi inserted into an INT field.

Replace the value '' by NULL:
INSERT INTO ... (fields1,...) values (NULL,...)

Before you perform the insert/update, if you found out that field1 and field2 is empty (assuming you JavaScript validation assigned "" to those blank fields) then you should leave out those two field when performing the insert/update (this is possible if you have set allow null / assigned a default value for these columns in the table design before hand)


Field1 = int 4
Field2 = decimal 8
Field3 = varchar 10
Field4 = varchar 20

If field1 & field 2is empty,then perform
INSERT INTO table (field3,field4)  VALUES (field3,field4)

The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Possible, too. But this way you have to take a look at avery value that COULD be NULL and generate another SQL String for each possible case.

In my opitnion it's easier to call:
SQL = Replace(SQL, "''", "NULL")
Hi Monster,

Try replacing the blank values at the time of JavaScript Validation to NULL and aloowing the same in DB.
Provide your SQL Query if possible.

It is indeed easier to user SQL = Replace(SQL, "''", "NULL") to replace every "" with NULL, however, there might be some columns that are not nullable and accept "" (for eg. varchar)

Then again, it's all depends on how the Table is designed.

However, in my opinion, it is best to set which column to null programmatically by yourself.
I agree with cheehoong.

Principally you should set the blank value to NULL if it is a nullable field.

@cheehoong: of corse you are right, but monster said that he did perform a client side form field validation before sending the form... so it's no more possible to fill a non nullable field.

I hope monster wil be able to follow the discussion and get his conclusion to find a solution :)

MPKR : In my humble opinion, I think, the form validation (or the part where insert/update is perform) is the cause of all these in the first place :)

Indeed, with the client side form validation, it will be impossible for to fill a non nullable field. However, if a field is optional, and is left blank by the user (due to the field is optional), the script will then either assign"" to the field or some other default value.

Hence, when you perform insert/update, and try to insert/update "" into the a numeric column (int,decimal,float, etc ...), you will get SQL error.

That's why, if you know what type of field you are inserting/updating, you should set the default value accordingly, and if the field is nullable, set the value to null.
All Courses

From novice to tech pro — start learning today.