?
Solved

Linq To SQL Stored Procedure Error When Date Is Empty

Posted on 2011-02-28
4
Medium Priority
?
666 Views
Last Modified: 2013-11-11
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));
0
Comment
Question by:frtools
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 6

Expert Comment

by:anushahanna
ID: 35003633
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
 

Author Comment

by:frtools
ID: 35007670
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
 
LVL 12

Accepted Solution

by:
kumar754 earned 2000 total points
ID: 35021025
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
 

Author Comment

by:frtools
ID: 35027546
Perfect works exactly like I want it to.

the tryparse statement needs an out result:

if (DateTime.TryParse(TextBox5.Text, out tempDate))
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question