Solved

Linq To SQL Stored Procedure Error When Date Is Empty

Posted on 2011-02-28
4
658 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 500 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
C# Windows app updating JSON file error. 9 39
SQL Select Query help 1 38
How come this XML node is not read? 3 31
Using sample Autorize.net c# simple example 1 28
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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 a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

733 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