Solved

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

Posted on 2004-08-12
7
209 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 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

14 Experts available now in Live!

Get 1:1 Help Now