Solved

Trying a db insert but fails due to nothing being passed in one of my form fields...

Posted on 2004-08-12
7
213 Views
Last Modified: 2013-12-24
Hi,

I am trying to do a simple database insert, however, I seem to have a problem because the column in my table is set to

stateProvinceCode nchar(3)  and does not accept nulls

I have a drop down box on my page like so

<select name="stateProvince" id="stateProvince">
          <option value="">Select One</option>
          <option value="CA">California</option
 </select>

I want to be able to submit my form without having to choose a State/Province.  If I choose nothing it passes "" which seems to cause a problem, because if I choose a state, I do not get any error.

If my column is set to not accept nulls, and has a datatype of nchar(3) how can I get around this?

The stateProvinceCode column is a FK from another table, a table called StateProvince

Should be simple to fix, but I can't seem to get it.

Any help appreciated.

-WS

0
Comment
Question by:Westside2004
[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
  • 2
  • 2
  • 2
  • +1
7 Comments
 

Expert Comment

by:zakaz
ID: 11781894
I would use a <cfparam> tag on the receiving page with default of Null. This at least fills the table

<cfparam name="stateProvince" default="null">
0
 
LVL 11

Accepted Solution

by:
hart earned 125 total points
ID: 11781959
FK can also be null....
why don't you just change the table not null to allow nulls....

else....

or in StateProvince create a value with 0 as a value and pass that in the db...
then you won't have to change anything...

what i mean is <option value="0">Select One</option>

and db insert 0...
also you will have a record with 0 in stateprovince table...

Regards
Hart

0
 
LVL 11

Expert Comment

by:hart
ID: 11782030
zakaz : you won't be able to put "NULL" value into the db as the field is a FK to stateprovince...

Regards
Hart
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 

Expert Comment

by:zakaz
ID: 11782558
True Hart anything,

i would do as you said change the db myself or stick in "None Chosen" as please choose value in select
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 11783344
hart has the the solution, but i would use XX inplace of 0, then you are consistent in the format

<select name="stateProvince" id="stateProvince">
          <option value="XX">Select One</option>
          <option value="CA">California</option
             ....
 </select>
0
 
LVL 1

Author Comment

by:Westside2004
ID: 11785340
Hi,

Yes, I just added a value in the StateProvince table with XX

And it worked..

Thanks

West
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 11785465
partly my solution....

and nothing for me :-{

;-)

glad you got it working, in your provisnce state table add

XX Not Listed

as the code and long text
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
What You Need to Know when Searching for a Webhost Provider
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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