Solved

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

Posted on 2014-04-25
9
2,413 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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 142

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 142

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 142

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

810 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