SQL Server Performance

Passing Null from C# to CLR Stored Procedure

Discussion in 'SQL Server 2005 CLR' started by whteshadow, Jun 23, 2006.

  1. whteshadow New Member

    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...
  2. ranjitjain New Member

  3. whteshadow New Member

    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.
  4. FrankKalis Moderator

  5. satya Moderator

    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.
  6. whteshadow New Member

    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.
  7. whteshadow New Member

    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.
  8. keith5000 New Member

    thanks, this tip was really useful!

Share This Page