CODEWITHSUNDEEP
sqllinq-to-sql
CM.
CM.

Reputation: 49

LINQ Stored Procedure DATETIME NULL

I got this errormessage when I use Linq and and stored procedure as follow. I got this message when the DateTime parameter is NULL. How will I do to solve this?

Errormessage Serverfel i tillämpningsprogrammet. SqlDateTime-spill: Det måste vara mellan 1/1/1753 12:00:00 AM och 12/31/9999 11:59:59 PM.

Codeblock in C#

protected void ButtonSearch_Click(object sender, EventArgs e)
{
    Postit p = new Postit(); 
    DateTime? dt;

    if(TextBoxDate.Text=="")
    {
        dt=null;
    }
    else
    {
        dt=System.Convert.ToDateTime(TextBoxDate.Text);
    }

    GridView1.DataSource = p.SearchPostit(TextBoxMessage.Text, TextBoxWriter.Text, TextBoxMailto.Text, System.Convert.ToDateTime(dt));  
    GridView1.DataBind();
}



public ISingleResult<Entities.SearchPostitResult> SearchPostit([Parameter
(DbType="VarChar(1000)")] string message, [Parameter(DbType="VarChar(50)")] string writer, [Parameter(DbType="VarChar(100)")] string mailto, [Parameter(DbType="DateTime")] System.Nullable<System.DateTime> date)
{           
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), message, writer, mailto, date);

return ((ISingleResult<Entities.SearchPostitResult>)(result.ReturnValue));

Procedure
ALTER PROCEDURE [dbo].[SearchPostit]
    (
    @message varchar(1000)='', 
    @writer varchar(50)='',
    @mailto varchar(100)='',
    @date Datetime=null
    )
AS
    SELECT P.Message AS Postit, P.Mailto AS 'Mailad till', P.[Date] AS Datum , U.UserName AS Användare  
    FROM PostIt P   

    INNER JOIN [User] U ON P.UserId=U.UserId

        WHERE   P.message LIKE '%'+@message+'%' 
AND     P.Mailto LIKE '%'+@mailto+'%'  
AND     (
                @date IS NULL 
            OR  P.Date = @date
        )
AND     U.UserName LIKE '%'+@writer+'%'
    GROUP BY P.Message, P.Mailto, P.[Date], U.UserName
}

Upvotes: 0

Views: 1489

Answers (3)

Joakim
Joakim

Reputation: 11

You should use: 

SqlDateTime.MinValue.Value

Instead of:

new DateTime(1753,01,01).

Upvotes: 1

Neil
Neil

Reputation: 1922

By Assigning NULL to datetime

if(TextBoxDate.Text=="")
{
    dt=null;
}

your Convert statement will fail

System.Convert.ToDateTime(dt)

I'm not sure but i think the default value for datetime is used ("0001/01/01"), hence the query will fail since this date does not fall between 

1/1/1753 12:00:00 AM och 12/31/9999

i suggest you assign a minimum date to "dt" when there is no date entered from the UI

if(TextBoxDate.Text=="")
{
    dt= new DateTime(1753,01,01);
}

[EDIT]

All you have todo is remove the redundant Convert statement when calling the stored procedure

    GridView1.DataSource = p.SearchPostit(TextBoxMessage.Text, 
       TextBoxWriter.Text, TextBoxMailto.Text, dt);

its redundant since you declare dt as DateTime? already.

Upvotes: 0

Don
Don

Reputation: 9661

ALTER PROCEDURE [dbo].[SearchPostit]
    (
    @message varchar(1000)='', 
    @writer varchar(50)='',
    @mailto varchar(100)='',
    @date Datetime=null
    )
AS
    SELECT P.Message AS Postit, P.Mailto AS 'Mailad till', P.[Date] AS Datum , U.UserName AS Användare  
    FROM PostIt P   

    INNER JOIN [User] U ON P.UserId=U.UserId

        WHERE   P.message LIKE '%'+@message+'%' 
AND     P.Mailto LIKE '%'+@mailto+'%'  
AND     ISNULL(@Date, 0) IN (P.Date, 0)
AND     U.UserName LIKE '%'+@writer+'%'
    GROUP BY P.Message, P.Mailto, P.[Date], U.UserName
}

Upvotes: 0

Related Questions