store nullable value into database

Question

3.00/5 (2 votes)

See more:

I created application, in which I want to store combobox value items into database.

Problem is if value member is null then pass 0, -1, etc in database. But this field has a foreign key. So gives error.

so, how can I store null value into integer field?

Thx.

Posted 14-Sep-12 1:09am

Member 9120792

Updated 14-Sep-12 1:12am

Malli_S

v3

Add a Solution

Comments

Malli_S 14-Sep-12 7:14am

You can have an entry for 0 or -1 in the table from where the column refers the foreign key.

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Answer 1 · 2012-09-14T01:20:00

Solution 2

Use NULL in the column definition[^] that makes up the foreign key constraint[^]. That way you can express that there are no records connected to the current record via this foreign key.

Modification:

TabAlleman[^] wrote on ASP.NET Forums[^]:
In the stored proc, give the nullable parameters a default value of NULL,

and in your C# code, if no value is being passed to those parameters, simply don't attach them to your command.

Please read the whole thread as there is a lot of useful information there.
End Modification

Second Modification

MSDN[^] wrote:

Specifying the Order of Parameters

If you supply parameters in the form @parameter = value, you can supply them in any order. You can also omit parameters for which defaults have been supplied. If you supply one parameter in the form @parameter = value, you must supply all subsequent parameters this way. If you do not supply parameters in the form @parameter = value, you must supply them in the order given in the CREATE PROCEDURE statement.

When executing a stored procedure, the server rejects any parameters that were not included with the parameter list during procedure creation. Any parameter passed by reference (explicitly passing the parameter name) is not accepted if the parameter name does not match.

This explains how to pass the parameters by specifying them by name and in any order you like. Missing parameters that have a default value of NULL will thus be set to NULL.
End Second Modification

Regards,

— Manfred

Posted 14-Sep-12 1:20am

Manfred Rudolf Bihy

Updated 14-Sep-12 2:09am

v3

Comments

[no name] 14-Sep-12 7:41am

if manually insert null value then its work, but in Windows form side not works.

Manfred Rudolf Bihy 14-Sep-12 7:51am

If you have the definition set up as recommended and manual insertion via INSERT statement works. Then you should be able to do this from code also. Can you post the relevant bits from your code and your stored procedure? I think that would really help us clear this up for you. :)

[no name] 14-Sep-12 7:58am

ya, if i dont pass parameter then by default save as Null,

but dont kw how to do with parameter?

Manfred Rudolf Bihy 14-Sep-12 8:06am

Make the default value of the stored procedures parameter that is used to pass in the int NULL. Then when you call the stored procedure don't add that parameter to your statement. Then it will be set to null as the default. When you have changed your SP please test it first from SQL Management Studio or whatever you're using to make sure the mechanism works as expected.

Manfred Rudolf Bihy 14-Sep-12 8:10am

I've added some content to my solution that will help you in passing the parameters in a way that will NULL them if desired.

[no name] 14-Sep-12 8:09am

i already set null by default,

but i use parameter multipurpose so cant remove....

Manfred Rudolf Bihy 14-Sep-12 8:12am

Inside the table definition or inside the stored procedure parameter definition?
What do you mean you can't remove? Explain in great detail.
Multipurpose parameters are never a good idea, but please explain first what you mean by this.

[no name] 14-Sep-12 8:14am

inside SP.
if i remove Parameter then what to do when i want to insert !!!

Manfred Rudolf Bihy 14-Sep-12 8:20am

You understood this wrong. You don't remove the parameter from the SP definition. You make the default value of that parameter NULL. Then when calling the SP from your code and you detect that there was no value selected you don't pass the parameter to the stored procedure. (Parameters.Add etc. etc.)

[no name] 14-Sep-12 8:24am

i solved my Problem,

thx for you Help.

Mahesh Puppala · Answer 2 · 2013-03-20T21:22:00

Solution 4

how to pass default value to date parametere in storedprocedure - Sqlserver 2008?
http://stackoverflow.com/questions/15540932/how-to-pass-default-value-to-date-parametere-in-storedprocedure-sqlserver-2008[^]

Posted 20-Mar-13 21:22pm

Mahesh Puppala

Updated 20-Mar-13 21:23pm

v2

Mahesh Puppala · Answer 3 · 2013-03-20T21:23:00

Solution 5

http://stackoverflow.com/questions/15540932/how-to-pass-default-value-to-date-parametere-in-storedprocedure-sqlserver-2008]

Posted 20-Mar-13 21:23pm

Mahesh Puppala

Aarti Meswania · Answer 4 · 2012-09-14T01:24:00

Solution 3

tbl_Main               tbl_Sub
---------------------------------
Col1     -------->     Col1
pk                     fk
Not Allow null         Allow Null

now,when combobox's selected value is 0 or -1 pass NULL value for tbl_sub's Col1(Foreign key)

Happy Coding!
:)

Posted 14-Sep-12 1:24am

Aarti Meswania

Updated 14-Sep-12 4:35am

v2

Comments

[no name] 14-Sep-12 7:37am

i do this way but,
i use Sp to insert, in form side when pass array of arguments then its take 0 so, reference field not contain 0 and give error.

Aarti Meswania 14-Sep-12 7:41am

have u set foreign-key allow null = true?
and do not insert '0' insert NULL

your combobox's values, like this?
0 --select--
1 value-1
2 value-2
...

[no name] 14-Sep-12 7:47am

and 0 is not allow to insert because of reference!!!

Aarti Meswania 14-Sep-12 7:48am

what value 0 contains?

[no name] 14-Sep-12 7:45am

i dont know how to insert null??

how can store null into integer Parameter which is pass to the Store Procedure!!!

Aarti Meswania 14-Sep-12 7:47am

DBNull.value

[no name] 14-Sep-12 7:47am

DBNull.value meance?

[no name] 14-Sep-12 7:49am

here CityId is integer.
i set properties from another form.
objEmployee.CityId

if not set then automatically consider CityId = 0,

Aarti Meswania 14-Sep-12 7:51am

and 0 means nothing not set or null right?
means -1 & 0 are same thing, am I right?

[no name] 14-Sep-12 7:53am

objEmployee.City = Convert.ToInt32(DBNull.Value);

its give error.....i dont want to save anything in database if Null.

Aarti Meswania 14-Sep-12 7:58am

no no you can not convert null to integer
it's ok, do not change in your coding part fine go to database

you have sp right?
in that you have
insert statement
modify it
like below

insert into tbl(CityId,...) values(case when @CityId<=0 then Null else @CityId end,...)

Aarti Meswania 14-Sep-12 8:01am

and this for information only,
from vb.net pass null value to sp's parameter
cmd.Parameters.AddWithValue("@CityId", DBNull.Value)

[no name] 14-Sep-12 8:02am

@City<=0 then null else @City,

not work...incorrect syntax near <

Aarti Meswania 14-Sep-12 8:04am

it is
case when @CityId<=0 then Null else @CityId end

paste your insert query here

[no name] 14-Sep-12 8:05am

not work...incorrect syntax near less-than sign

[no name] 14-Sep-12 8:06am

INSERT INTO Employee VALUES(@EmployeeID,@EmployeeName,@Emp_Title,@Address_1,@Address_2,@Area,@CityID<=0 then null else @CityID,@Postcode,@JoiningDate,@BirthDate,@LeavingDate,@DepartmentID,@DesignationID,@MobileNo,@Office_PhoneNo,@Residence_PhoneNo,@Email_ID,@Active);

Aarti Meswania 14-Sep-12 8:13am

use below query...

INSERT INTO Employee
VALUES(@EmployeeID,@EmployeeName,@Emp_Title,@Address_1,
@Address_2,@Area,

case when @CityID<=0 then null else @CityID end,

@Postcode,@JoiningDate,@BirthDate,@LeavingDate,@DepartmentID,@DesignationID,
@MobileNo,@Office_PhoneNo,@Residence_PhoneNo,@Email_ID,@Active);

[no name] 14-Sep-12 8:20am

it's Work, i m so Happy, For Your great Help. and Quick Solution.

u r so Helpful, thanks a lot.

Aarti Meswania 14-Sep-12 8:21am

welcome :)

[no name] 14-Sep-12 8:22am

i can Add you in any My Personal contact PLZ....:)

Aarti Meswania 14-Sep-12 10:32am

what is reason for down-voting
it is too much
problem is solved
and somebody is down-voting
why they not writing reason when down-vote?

[no name] 15-Sep-12 1:26am

ya u r right,
dont know why, my Question is also Down-vote! WTF!

[no name] 15-Sep-12 1:29am

hey!!

what to do in Update Query??
Update Employee Set EmployeeName=@EmployeeName,Emp_Title=@Emp_Title,Address_1=@Address_1,Address_2=@Address_2,Area=@Area,case when @CityID<=0 then null else @CityID end, Postcode=@Postcode,JoiningDate=@JoiningDate,BirthDate=@BirthDate,LeavingDate=@LeavingDate,DepartmentID=@DepartmentID,DesignationID=@DesignationID,MobileNo=@MobileNo,Office_PhoneNo=@Office_PhoneNo,Residence_PhoneNo=@Residence_PhoneNo,Email_ID=@Email_ID,Active=@Active WHERE EmployeeID = @EmployeeID

*Karthik Harve* · Answer 5 · 2012-09-14T01:15:00

Solution 1

Hi,

If you want to insert NULL in to the database table column, remove all the contraints like Primary Key, Foriegn Key and Composite Key constraints associated with that column of a table. Basically, if you have constaints then the value is referring to value in some other table, since the server could not map NULL value to other table, it is throwing the error. So, remove the constraints of that column and then you can insert NULL value to that.

hope it works.

Posted 14-Sep-12 1:15am

Karthik Harve

Comments

Manfred Rudolf Bihy 14-Sep-12 7:27am

Sorry, that is so wrong on so many levels, I had to give you a one vote.
Removing primary keys and foreing key constraints, just to have a foreign key point at no connected records. WTF!
You don't go and break referential integrity and the assurance of having a unique primary key to achieve what OP wants. All you have to do is set the column that makes up the foreign key definition as nullable using the NULL keyword. This has been in place since DBs exist as there has always been a need to be able to model a relationsip 1 : 0 .. N.

Cheers!

fjdiewornncalwe 14-Sep-12 9:30am

Agreed. "that is so wrong on so many levels"

[no name] 14-Sep-12 7:39am

agree 3762091