Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert NULL or "" into DB

Posted on 2004-09-28
9
Medium Priority
?
283 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 160 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

715 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