Solved

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

Posted on 2004-08-12
7
215 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
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…

632 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