Linq To SQL Stored Procedure Error When Date Is Empty

I have a SQL insert stored procedure :

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
      INSERT INTO Incidents
          (    OrderID, Email, Name, OriginalShipDate, IncidentReasonID, IncidentResolutionID, Notes, ReturnTagRequired, RefundRequired, RefundAmount,            DateRefundIssued, ShipperID, InventoryItem, InventoryQuantity, eBayCredit, eBayCreditFileDate, LostValue, RMARequested, IncidentCreatedBy


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));
Who is Participating?
kumar754Connect With a Mentor Commented:
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..


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...);

use ISNULL function to provide your replacement value, so that there is no erroring out
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.
frtoolsAuthor Commented:
Perfect works exactly like I want it to.

the tryparse statement needs an out result:

if (DateTime.TryParse(TextBox5.Text, out tempDate))
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.