Solved

how to pass sql parameter as null value in to integer data type variable

Posted on 2014-04-25
9
2,581 Views
Last Modified: 2014-04-29
how to pass sql parameter as null value in to integer data type variable


po =txt_PONo_int.Text == "" ? 0 : Convert.ToInt32(txt_PONo_int.Text);



how zero should be replaced , i want save it as null instead of  zero
0
Comment
Question by:codeoxygen
[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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40022301
Dbnull.value
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40022354
It depends what po is. If it's a Nullable<int> then you set it to null. The translation to DbNull should come when you are assigning the value to a parameter on a command object.
0
 

Author Comment

by:codeoxygen
ID: 40022416
o.po  o is a entity class object PO is variable of that class , whose value is int


in entity class po is declared as


public int po {get;set;}

can help me how to declare it if i want to pass null value to DB sql server
0
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40022453
int cannot accept NULL or DBNull.Value, as far as I know, so you will have to put some different code later, where po is used in the db query.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40022466
Is po defined as allowing null in your database table? It may just be that it hasn't filtered through to your entity model.

Edit the model and look at the settings for the po column. If "Nullable" is set to "(None)" then change it to true. But it needs to be nullable in the underlying table too.
0
 

Author Comment

by:codeoxygen
ID: 40023053
example for declaring as nullable
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40023107
int? po
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40023483
If you're building your entity model from a database then you won't specify they type directly; it will be picked up from the underlying columns data type.

But yes, it would appear as int?.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 40024170
Hi codeoxygen,

I think you can just give the SQL Stored Procedure Parameter a default value of NULL, by doing so the input param becomes optional; then don't pass that param when you execute the stored procedure from your code. Sorta the same effect as setting the ConvertEmptyStringToNull property in the SQL Parameter definition of a SqlDataSource object.
CREATE PROCEDURE dbo.my_proc
    @po int = NULL,  -- NULL default value
    @mo int = 2,    -- Default value of 2
    @ko int = 3      -- Default value of 3
AS 
    SET NOCOUNT ON;
    SELECT @po, @mo, @ko;
GO

-- exec dbo.my_proc    -- No parameters supplied
-- Returns:
-- NULL  2  3

Open in new window

Respectfully yours,
Alan
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

691 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