472,780 Members | 1,937 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,780 software developers and data experts.

C# How to check if null value exists in database table (using stored procedure)?

Folk!

How to programattically check if null value exists in database table (using stored procedure)?

I know it's possble in the Query Analyzer (see last SQL query batch statements)?

But how can I pass null value as parameter to the database stored procedure programattically using C#?

Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value in the following code snippet:

SQL Queries:
Expand|Select|Wrap|Line Numbers
  1. USE [master]
  2. GO
  3.  
  4. IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase') 
  5. DROP DATABASE [ExampleDatabase]; 
  6. GO
  7.  
  8. CREATE DATABASE [ExampleDatabase]; 
  9. GO
  10.  
  11. USE [ExampleDatabase]; 
  12. GO
  13.  
  14. IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable') 
  15. DROP TABLE dbo.ExampleTable; 
  16. GO
  17.  
  18. CREATE TABLE 
  19. dbo.ExampleTable
  20. (
  21. ID INT IDENTITY(1,1) NOT NULL, 
  22. UserID INT NULL, 
  23. Name NVARCHAR(50) NULL, 
  24. DateOfBirth DATETIME, 
  25. IsActive BIT, 
  26. Phone NVARCHAR(50) NULL,
  27. Fax NVARCHAR(50) NULL,
  28. CONSTRAINT PK_ID PRIMARY KEY(ID), 
  29. CONSTRAINT UNIQUE_Phone UNIQUE(Phone), 
  30. CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES ExampleTable(ID),
  31. CONSTRAINT FK_Fax FOREIGN KEY(Fax) REFERENCES ExampleTable(Phone)
  32. );
  33. GO
  34.  
  35. INSERT INTO dbo.ExampleTable
  36. (
  37. UserID, 
  38. Name, 
  39. DateOfBirth, 
  40. IsActive, 
  41. Phone,
  42. Fax
  43. )
  44. VALUES
  45. (
  46. 1,
  47. 'Bill',
  48. '12-31-2000',
  49. 'False',
  50. '12345678',
  51. '12345678'
  52. );
  53. GO
  54.  
  55. INSERT INTO dbo.ExampleTable
  56. (
  57. UserID, 
  58. Name, 
  59. DateOfBirth, 
  60. IsActive
  61. )
  62. VALUES
  63. (
  64. 2,
  65. 'Larry',
  66. '12-31-2005',
  67. 'True'
  68. );
  69. GO
  70.  
  71. SELECT * FROM ExampleTable;
  72. GO
  73.  
  74.  
  75. IF EXISTS(SELECT NAME FROM SYS.PROCEDURES WHERE NAME = N'CheckForeignKeyFax') 
  76. DROP PROCEDURE dbo.CheckForeignKeyFax;
  77. GO
  78.  
  79. CREATE PROCEDURE dbo.CheckForeignKeyFax
  80. (
  81. @Fax NVARCHAR(50)
  82. )
  83. AS
  84. DECLARE @ResultFax INT
  85. IF EXISTS
  86. (
  87. SELECT
  88. NULL
  89. FROM
  90. dbo.ExampleTable WITH (UPDLOCK) 
  91. WHERE
  92. ISNULL(Phone, 'NULL') = ISNULL(@Fax, 'NULL')     
  93. --(Phone IS NULL AND @Fax IS NULL) OR (@Fax = Phone) 
  94. BEGIN SELECT @ResultFax = 0 END
  95. ELSE BEGIN SELECT @ResultFax = -1 END
  96. RETURN @ResultFax
  97. GO
  98.  
  99. DECLARE @ReturnValue INT
  100. EXEC @ReturnValue = CheckForeignKeyFax @Fax = '12345678'
  101. SELECT ReturnValue=@ReturnValue;
  102. GO
  103.  
  104. DECLARE @ReturnValue INT
  105. EXEC @ReturnValue = CheckForeignKeyFax @Fax = NULL
  106. SELECT ReturnValue=@ReturnValue;
  107. GO
  108.  
C# Code:
Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.Drawing;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Windows.Forms;
  9.  
  10. using System.Data.SqlClient;
  11.  
  12. namespace WindowsFormsApplication1
  13. {
  14.     public partial class Form1 : Form
  15.     {
  16.         public Form1()
  17.         {
  18.             InitializeComponent();
  19.         }
  20.  
  21.         private string ConnectionString
  22.         {
  23.             get
  24.             {
  25.                 return @"Persist Security Info=False;Data Source=.\SQLEXPRESS;User ID=sa;Password=asdfglkjh;Initial Catalog=master;";                               
  26.             }
  27.         }
  28.  
  29.         private int CheckFax(string fax)
  30.         {
  31.             int Result = -1;
  32.  
  33.             try
  34.             {
  35.                 using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
  36.                 {
  37.                     using (SqlCommand CommandSql = new SqlCommand("ExampleDatabase.dbo.CheckForeignKeyFax"))
  38.                     {
  39.                         CommandSql.CommandType = CommandType.StoredProcedure;
  40.                         CommandSql.Parameters.Add(new SqlParameter("@Fax", fax));
  41.  
  42.                         SqlParameter ParameterSql = new SqlParameter("@ReturnValue", DbType.Int32);
  43.                         ParameterSql.Direction = ParameterDirection.ReturnValue;
  44.  
  45.                         CommandSql.Parameters.Add(ParameterSql);
  46.  
  47.                         ConnectionSql.Open();
  48.                         CommandSql.Connection = ConnectionSql;
  49.                         CommandSql.ExecuteScalar();
  50.                         Result = Int32.Parse(CommandSql.Parameters["@ReturnValue"].Value.ToString());
  51.                         ConnectionSql.Close();
  52.                     }
  53.                 }
  54.             }
  55.             catch (Exception ex)
  56.             {
  57.                 MessageBox.Show(ex.ToString());
  58.             }
  59.             return Result;
  60.         }
  61.  
  62.         private void button1_Click(object sender, EventArgs e)
  63.         {
  64.             MessageBox.Show(CheckFax(textBox1.Text).ToString());
  65.         }
  66.     }
  67. }
  68.  

Please help!
Nov 3 '09 #1

✓ answered by Plater

Check out the DBNull.Value object for passing in a null (or checking against a null value in a DataSet)

In SQL you can use the "is null" to check if a field is null

2 14546
Plater
7,872 Expert 4TB
Check out the DBNull.Value object for passing in a null (or checking against a null value in a DataSet)

In SQL you can use the "is null" to check if a field is null
Nov 3 '09 #2
Thanks for the reply!

@Plater
Nov 4 '09 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Matt | last post by:
I could use some help dealing with null blobs. I'm returning a transaction from an Image BLOB field in SQL Server 2000 using C#. If the transaction exists the value is returned with out trouble,...
8
by: craigkenisston | last post by:
I have a generic function that receives a couple of datetime values to work with. They can or cannot have a value, therefore I wanted to use null. This function will call a database stored...
1
by: John Hoge | last post by:
Is it possible to pass a null value to a stored procedure in .net? I have a search Sproc that can take one of two numbers to search on, but not both. I use the code below to pass a null value to...
6
by: David Lozzi | last post by:
Here is the proc: CREATE PROCEDURE . @CID as int, @Netname as nvarchar(25), @Return as int OUTPUT AS IF EXISTS (SELECT DISTINCT netname FROM computers WHERE CompanyID = @CID AND...
8
by: JIM.H. | last post by:
Hello, I am calling a stored procedure to update my table. If one of the date on the screen left empty, I need to send date as null. Since MyDate=”” gives error in asp.net, how should I do...
9
by: Carl Fenley | last post by:
I am successfully adding stored procedures to an Access database. However, I need to be able to check if the stored procedure of the same name already exists. Is there a way to do this other...
7
by: vovan | last post by:
I'm creating DataSet, then 2 DataTables, then DataRelation between those DataTables. I populate DataTables with DataAdapters. Data from each table is displayed in Grids. For display everything...
14
by: Dan | last post by:
Hello, we have an intranet application using Windows Integrated Authentification. When an user starts the application, he gets a form for inputting data. The first time he does that, the...
4
by: qwedster | last post by:
Howdy folks! I am using stored procedure to see if a value exists in database table and return 0 if exists or else -1, in the following SQL queries. However how to check if a value (that is...
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.