Solved

Insert NULL or "" into DB

Posted on 2004-09-28
9
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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