Linq To SQL Stored Procedure Error When Date Is Empty

I have a SQL insert stored procedure :

USE [RMA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Incident_insertTicket]
      -- Add the parameters for the stored procedure here
      @orderid varchar(50) = null,
      @email varchar(50) = null,
      @name varchar(50) = null,
      @origshipdate date = null,
      @reasonid int = null,
      @resolutionid int = null,
      @notes text = null,
      @returntagoption char(10) = null,
      @refundrequired char(10) = null,
      @refundamount float = null,
      @daterefundissued date = null,
      @shipperid int = null,
      @item varchar(50)= null,
      @itemqty int = null,
      @ebaycredit char(10) = null,
      @ebaycreditdate date = null,
      @lostvalue float = null,
      @rmarequested char(10) = null,
      @incidentcreatedby int = null
      
      
      
AS
BEGIN
      SET NOCOUNT ON;
      
      INSERT INTO Incidents
          (    OrderID, Email, Name, OriginalShipDate, IncidentReasonID, IncidentResolutionID, Notes, ReturnTagRequired, RefundRequired, RefundAmount,            DateRefundIssued, ShipperID, InventoryItem, InventoryQuantity, eBayCredit, eBayCreditFileDate, LostValue, RMARequested, IncidentCreatedBy
          )
     VALUES
          (
            @orderid,@email,@name,@origshipdate,@reasonid,@resolutionid,@notes,@returntagoption,@refundrequired,@refundamount,
            @daterefundissued,@shipperid,@item,@itemqty,@ebaycredit,@ebaycreditdate,@lostvalue,@rmarequested,@incidentcreatedby                
          )


      
      
END

Now I've set all the variables in the stored procedure to null and all values in the table can be null. This will work if I fill all the controls in with a value. The problem is if one of the options to, lets say i don't want to create a refund, there would not be a refund date. So that date field would be empty, and when exexuted it throws an error. How can I pass in the linq statement with null date values.

RMADataClassDataContext CreateTicket = new RMADataClassDataContext();

           CreateTicket.sp_Incident_insertTicket(TextBox2.Text, TextBox3.Text, TextBox4.Text, Convert.ToDateTime(TextBox5.Text), int.Parse(DropDownList1.SelectedValue), int.Parse(DropDownList2.SelectedValue), TextBox6.Text, DropDownList3.SelectedValue, DropDownList4.SelectedValue, float.Parse(TextBox7.Text), Convert.ToDateTime(TextBox8.Text), int.Parse(DropDownList5.SelectedValue),  DropDownList6.SelectedValue, int.Parse(TextBox9.Text), DropDownList7.SelectedValue, Convert.ToDateTime(TextBox10.Text), float.Parse(TextBox1.Text), DropDownList8.SelectedValue, int.Parse(DropDownList9.SelectedValue));
frtoolsAsked:
Who is Participating?
 
kumar754Commented:
This might be because you are not using Nullable Types, Linq is expecting a nullable type as a parameter of the stored procedure.

Here in the "Convert.ToDateTime(TextBox5.Text)" if the date is null. it will not convert to a nullable datetime
so you shld rather convert it to similar type and then pass the variable. Same goes for int, decimal data types..

you shld rather use like this:

DateTime? _DateRefundIssued= null;

DateTime tempDate;
if (DateTime.TryParse(TextBox5.Text, tempDate))
  _DateRefundIssued = tempDate; // assign only if the date value exist or in proper format

// else it will be null..

Now pass you _DateRefundIssued variable to the Store Procedure as a parameter.. it shld work.

same goes with int and decimal too..

so:

int? _OrderID = null;

int tempOrderID = 0;
if (int.TryParse(TextBox2.Text, tempOrderID))
   _OrderID = tempOrderID;

Now, pass this to your stored procedure:

RMADataClassDataContext CreateTicket = new RMADataClassDataContext();

CreateTicket.sp_Incident_insertTicket( _OrderID , <nullable_type> , <nullable_type>, _DateRefundIssued, <nullable_type>, <nullable_type>, etc...);



0
 
anushahannaCommented:
use ISNULL function to provide your replacement value, so that there is no erroring out
http://msdn.microsoft.com/en-us/library/ms184325.aspx
0
 
frtoolsAuthor Commented:
I'm sure that the SQL S_P is not the problem when I execute the S_P and do not apply any values and do not check the pass null value it works with no errors. The problem seems to be in the Linq statement itself.
0
 
frtoolsAuthor Commented:
Perfect works exactly like I want it to.

the tryparse statement needs an out result:

if (DateTime.TryParse(TextBox5.Text, out tempDate))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.