I am not sure where the error is here, and would appreciate any additional thoughts anyone has...
I am running my Blazor app against a local SQL Server 2019 instance. In my code I am calling a stored procedure.The page that supplies the parameters to the sproc uses 8 different DatePicker controls. Overall, the sproc takes 40 (yes, 40 - that's what the customer wants) input parameters.
None of the input parameters are any type of date/datetime. They are either NVARCHAR or INT. In the case of the DatePickers, I have backing variables assigned to each picker and a custom method which converts the date(s) (if the user has picked one) to properly formatted strings which are converted to dates inside the sproc - mainly because I have had endless problems with the singular date data type and can't convert the necessary columns to datetime2 or similar.
OK, so now the problem: every time I run this I get this message:
Microsoft.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
I know this is coming from EF, but I've checked the model and all of the code, and nowhere does any sort of date enter the picture anywhere except within the sproc itself.
Here's the weird part: EF is executing this as raw SQL. If I put in a breakpoint and grab the formatted string it is going to execute, put it in SQL Server Management Studio as-is and execute, it runs perfectly. No problems at all. It is just when EF tries to run the string it blows up because I think there is an inherent conversion to date when it thinks it recognizes a date.
More info: all nvarchar values (including the "dates") are all properly quoted, something the generator did NOT do when it created the code to execute this sproc. In other words, @FirstName=david,@LastName=ferreira, ... is how it generated the statement to execute. I built an extension method to quote all the strings just to stop it from initially blowing up.
Anyone have any ideas? Does EF try to recognize the the format as a date and cause this problem? Or am I missing something basic?