Introduction
Hi all, I hope you are fine. We are all using SQL parameters in our applications, right? Yeah, it is a good way to prevent hijacks by injection. But still, if you pass those parameters in the right way, you will encounter many problems. In this article I will share you such an Issue. I hope you will like it.
Background
This morning I was working in a grid control that is editable and I selected a row to edit and began editing. In a date field I edited the date and clicked the update button. I was expecting an output result as "Successfully updated". But it didn't happen. Instead it throws an error. "SqlDateTime Overflow, Must be between"
Please see this article in my
blog.
Using the code
To resolve this issue, first you must understand the root cause for this. I was passing the parameters as follows.
- SqlParameter[] myParam = new SqlParameter[3];
- myParam[0] = new SqlParameter("@name", myObject.name);
- myParam[1] = new SqlParameter("@ValidFrom", myObject.hjcValidFrom);
- myParam[2] = new SqlParameter("@ValidTo", myObject.hjcValidTo);
When I run my application with the preceding code, I got the following error.
The fix/solution
I changed the parameter as follows.
- SqlParameter[] myParam = new SqlParameter[3];
- myParam[0] = new SqlParameter("@name", myObject.name);
- myParam[1] = new SqlParameter("@ValidFrom", myObject.hjcValidFrom.ToString("MM/dd/YYYY"));
- myParam[2] = new SqlParameter("@ValidTo", myObject.hjcValidTo.ToString("MM/dd/YYYY"));
That solved the issue. Simple, right?
Some other case
There are some other cases for which you may get this error. For examle if you use the code as follows.
- SqlParameter[] myParam = new SqlParameter[3];
- DateTime myDate=new DateTime();
- myParam[0] = new SqlParameter("@name", myObject.name);
- myParam[1] = new SqlParameter("@ValidFrom", myDate);
- myParam[2] = new SqlParameter("@ValidTo", myDate);
What you need to do to fix this issue is to just take the now property value from your date time object and pass it your parameter.
- SqlParameter[] myParam = new SqlParameter[3];
- DateTime myDate = DateTime.Now;
- myParam[0] = new SqlParameter("@name", myObject.name);
- myParam[1] = new SqlParameter("@ValidFrom", myDate);
- myParam[2] = new SqlParameter("@ValidTo", myDate);
Conclusion
I hope you enjoyed reading and found this useful. Please share your valuable feedback with me. For me it matters a lot.
Kindest Regards,
Sibeesh Venu