SQL Server Performance Forum – Threads Archive
Passing Null from C# to CLR Stored ProcedureHi, 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…
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? —
Microsoft SQL Server MVP
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
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
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
This is my CLR Stored Procedure Code:
public static void InsertSampleDateNull(DateTime dateField)
using (SqlConnection conn = new SqlConnection("context connection = true"))
SqlCommand cmd = new SqlCommand(@"INSERT INTO [T_SampleDateNull]
cmd.Parameters.AddWithValue("@dateField", dateField); // Add codes to execute here!!!
} 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)
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)
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!