Solved

Insert NULL or "" into DB

Posted on 2004-09-28
9
263 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connection String to remote Server not working 3 92
Connecting to multiple databases to create a Dashboard 5 48
Html value of radio 14 29
ASP exit 10 19
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 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 …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 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