Solved

Insert NULL or "" into DB

Posted on 2004-09-28
9
256 Views
Last Modified: 2008-02-26
Hi,

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?
0
Comment
Question by:Pigdogmonster
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 4

Expert Comment

by:MPKR
ID: 12168117
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,...)


MP
0
 

Expert Comment

by:cheehoong
ID: 12168447
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)

Eg.

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)

0
 
LVL 4

Expert Comment

by:MPKR
ID: 12168482
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")
0
 
LVL 23

Accepted Solution

by:
apresto earned 40 total points
ID: 12168504
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Expert Comment

by:apurvkansal
ID: 12168510
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.

Cheers,
AK
0
 

Expert Comment

by:cheehoong
ID: 12168575
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.
0
 

Expert Comment

by:apurvkansal
ID: 12169436
I agree with cheehoong.

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

Cheers,
AK
0
 
LVL 4

Expert Comment

by:MPKR
ID: 12169559
@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 :)

MP
0
 

Expert Comment

by:cheehoong
ID: 12175941
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now