SQL Server Performance Forum – Threads Archive

Passing Null from C# to CLR Stored Procedure

Hi, I encountered a problem in passing a null to a datetime field in my CLR stored. Has anyone encountered this problem? How to solve this problem and what is the work around for this.
Thanks a lot in advance…

---

check this:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21761386.html

---

Hi, thanks for your reply. Maybe you got confused with my question. I’ve created a CLR stored procedure in SQL Server 2005 and one of the parameter is a datetime datatype. Now my problem is if I would pass a null to this field from my code in C#, I would get an exception. How could I solve this? Is there any work around on this one? Thanks a lot in advance.

---

What is the exact error message? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

Check what is the default value for that datetime field or whether NULL is accepted or not. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

---

Hi, thanks for your reply. I’m posting my code so that you could help me some more. This is the script of my table:
CREATE TABLE [dbo].[T_SampleDateNull](
[PK_Feild1] [int] IDENTITY(1,1) NOT NULL,
[DateField] [datetime] NULL,
CONSTRAINT [PK_T_SampleDateNull] PRIMARY KEY CLUSTERED
(
[PK_Feild1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
This is my CLR Stored Procedure Code:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertSampleDateNull(DateTime dateField)
{
using (SqlConnection conn = new SqlConnection("context connection = true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"INSERT INTO [T_SampleDateNull]
([DateField])
VALUES
(@dateField)", conn);
cmd.Parameters.AddWithValue("@dateField", dateField); // Add codes to execute here!!!
conn.Close();
}
} This is my C# code that throws an error("’InsertSampleDateNull’ failed because parameter 1 is not allowed to be null and is not an output parameter."):
private void button1_Click(object sender, EventArgs e)
{
try
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
{
conn.Open(); SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "InsertSampleDateNull";
cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@dateField", DBNull.Value); // Execute Non Query Here!!! conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Is there any workaround for passing null in a datetime field? Thanks a lot in advance.

---

Hi, I already solved my problem. For those having same problem as mine, this is the solution. In SqlStoredProcedure that I created the parameter data type is DateTime, I should have used SqlDateTime instead of DateTime.
Thanks a lot for those who tried to help me and more power to you all.

---

thanks, this tip was really useful!

---

]]>