CODEWITHSUNDEEP
c#sql.netsql-serveroledb
Madam Zu Zu
Madam Zu Zu

Reputation: 6615

Error converting data type nvarchar to datetime in a Stored Procedure from code

I'm getting the following error when calling a stored procedure from C#.

the parameters are defined in the code as:

 if (repRIS.Length > 0)
    command.Parameters.AddWithValue("@repRIS", repRIS);
 else
    command.Parameters.AddWithValue("@repRIS", DBNull.Value);
  command.Parameters.Add("@invDt", OleDbType.Date).Value = invDate;

I have commented out everything out of the stored procedure and now only have the following:

ALTER PROCEDURE [dbo].[SearchDates]
    -- Add the parameters for the stored procedure here
     @invDt datetime,
     @repRIS varchar(10) ,

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

select this, that, and, the, other from myTableName ird
where
     ird.RIS = COALESCE(@repRIS, ird.RIS) 
     and ird.InventoryDate = @invDt
END

The InventoryDate is of type DateTime in the database.

when I run the SP from the SQL MS it produces results with no issues, however, when using the application to call it, I get the following error message

Error converting data type nvarchar to datetime

I've noticed that it started happening after I switched from SQLConnection to OLEDBConnection. I am yet to confirm this. (i had to switch to comply with how the rest of the application was written before me)

UPDATE: when I am putting in the value of 9/30/2018 in the textbox, it is getting passed into the stored procedure as: {9/30/2018 12:00:00 AM} after converting to datetime (code above)

Upvotes: 1

Views: 3965

Answers (2)

Steve
Steve

Reputation: 216342

When using OleDb one should always remember that the parameters are not passed according to their names but in the exact order in which they are added to the Parameters collection.

In your code you add first the @repRIS and this is the first parameter passed to the SP. But the SP expects a date for the first parameter and you get the exception

You need to change the order of the insertion in the Parameters collection or switch the declaration order of the parameters in the SP

command.Parameters.Add("@invDt", OleDbType.Date).Value = invDate;     
if (repRIS.Length > 0)
    command.Parameters.AddWithValue("@repRIS", repRIS);
else
    command.Parameters.AddWithValue("@repRIS", DBNull.Value);

And another thing to do is to look at this article Can we stop using AddWithValue already?

Upvotes: 5

gatsby
gatsby

Reputation: 1229

I would say that Min Date does not work in SQL from C# since min C# Datetime and the one supported by SQL are different.

Use : 

System.Data.SqlTypes.SqlDateTime.MinValue.Value

instead the Min offered by C# Datetime. This should work

Upvotes: 0

Related Questions