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: -
USE [master]
-
GO
-
-
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
-
DROP DATABASE [ExampleDatabase];
-
GO
-
-
CREATE DATABASE [ExampleDatabase];
-
GO
-
-
USE [ExampleDatabase];
-
GO
-
-
IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
-
DROP TABLE dbo.ExampleTable;
-
GO
-
-
CREATE TABLE
-
dbo.ExampleTable
-
(
-
ID INT IDENTITY(1,1) NOT NULL,
-
UserID INT NULL,
-
Name NVARCHAR(50) NULL,
-
DateOfBirth DATETIME,
-
IsActive BIT,
-
Phone NVARCHAR(50) NULL,
-
Fax NVARCHAR(50) NULL,
-
CONSTRAINT PK_ID PRIMARY KEY(ID),
-
CONSTRAINT UNIQUE_Phone UNIQUE(Phone),
-
CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES ExampleTable(ID),
-
CONSTRAINT FK_Fax FOREIGN KEY(Fax) REFERENCES ExampleTable(Phone)
-
);
-
GO
-
-
INSERT INTO dbo.ExampleTable
-
(
-
UserID,
-
Name,
-
DateOfBirth,
-
IsActive,
-
Phone,
-
Fax
-
)
-
VALUES
-
(
-
1,
-
'Bill',
-
'12-31-2000',
-
'False',
-
'12345678',
-
'12345678'
-
);
-
GO
-
-
INSERT INTO dbo.ExampleTable
-
(
-
UserID,
-
Name,
-
DateOfBirth,
-
IsActive
-
)
-
VALUES
-
(
-
2,
-
'Larry',
-
'12-31-2005',
-
'True'
-
);
-
GO
-
-
SELECT * FROM ExampleTable;
-
GO
-
-
-
IF EXISTS(SELECT NAME FROM SYS.PROCEDURES WHERE NAME = N'CheckForeignKeyFax')
-
DROP PROCEDURE dbo.CheckForeignKeyFax;
-
GO
-
-
CREATE PROCEDURE dbo.CheckForeignKeyFax
-
(
-
@Fax NVARCHAR(50)
-
)
-
AS
-
DECLARE @ResultFax INT
-
IF EXISTS
-
(
-
SELECT
-
NULL
-
FROM
-
dbo.ExampleTable WITH (UPDLOCK)
-
WHERE
-
ISNULL(Phone, 'NULL') = ISNULL(@Fax, 'NULL')
-
--(Phone IS NULL AND @Fax IS NULL) OR (@Fax = Phone)
-
)
-
BEGIN SELECT @ResultFax = 0 END
-
ELSE BEGIN SELECT @ResultFax = -1 END
-
RETURN @ResultFax
-
GO
-
-
DECLARE @ReturnValue INT
-
EXEC @ReturnValue = CheckForeignKeyFax @Fax = '12345678'
-
SELECT ReturnValue=@ReturnValue;
-
GO
-
-
DECLARE @ReturnValue INT
-
EXEC @ReturnValue = CheckForeignKeyFax @Fax = NULL
-
SELECT ReturnValue=@ReturnValue;
-
GO
-
C# Code: -
using System;
-
using System.Collections.Generic;
-
using System.ComponentModel;
-
using System.Data;
-
using System.Drawing;
-
using System.Linq;
-
using System.Text;
-
using System.Windows.Forms;
-
-
using System.Data.SqlClient;
-
-
namespace WindowsFormsApplication1
-
{
-
public partial class Form1 : Form
-
{
-
public Form1()
-
{
-
InitializeComponent();
-
}
-
-
private string ConnectionString
-
{
-
get
-
{
-
return @"Persist Security Info=False;Data Source=.\SQLEXPRESS;User ID=sa;Password=asdfglkjh;Initial Catalog=master;";
-
}
-
}
-
-
private int CheckFax(string fax)
-
{
-
int Result = -1;
-
-
try
-
{
-
using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
-
{
-
using (SqlCommand CommandSql = new SqlCommand("ExampleDatabase.dbo.CheckForeignKeyFax"))
-
{
-
CommandSql.CommandType = CommandType.StoredProcedure;
-
CommandSql.Parameters.Add(new SqlParameter("@Fax", fax));
-
-
SqlParameter ParameterSql = new SqlParameter("@ReturnValue", DbType.Int32);
-
ParameterSql.Direction = ParameterDirection.ReturnValue;
-
-
CommandSql.Parameters.Add(ParameterSql);
-
-
ConnectionSql.Open();
-
CommandSql.Connection = ConnectionSql;
-
CommandSql.ExecuteScalar();
-
Result = Int32.Parse(CommandSql.Parameters["@ReturnValue"].Value.ToString());
-
ConnectionSql.Close();
-
}
-
}
-
}
-
catch (Exception ex)
-
{
-
MessageBox.Show(ex.ToString());
-
}
-
return Result;
-
}
-
-
private void button1_Click(object sender, EventArgs e)
-
{
-
MessageBox.Show(CheckFax(textBox1.Text).ToString());
-
}
-
}
-
}
-
Please help!
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
...
|
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...
|
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...
|
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...
|
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=()=>{
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
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...
| |