Link to home
Start Free TrialLog in
Avatar of saumathur
saumathur

asked on

DateTime format problem when passing in as Sql Parameter.

OK, I have a stored procedure that accepts parameters and one of which is of SqlTYpe DateTime (default and unchangable size of 8). Now, I am passing in the value, via a function that I created and I have to do it like this as it is a requirement.
THe problem is that when i pass in the datetime as a string, when it converts it to string and executes the query, i get the following error:

String was not recognized as a valid DateTime.

I debugged the code and the value for the string is exactly what it shud be, i.e. 14/9/2004
For your convenience, here is the function that i use...!!

public void UpdateHasTaken(int moduleID, string PackDescription, string StudentNo, string ReservedOn)
            {
                  // Create Instance of Connection and Command Object
                  SqlConnection myConnection = PortalSettings.SqlConnectionString;
                  SqlCommand myCommand = new
                        SqlCommand("RMIT_DesignStore_UpdateHasTaken", myConnection);

                  // Mark the Command as a SPROC
                  myCommand.CommandType = CommandType.StoredProcedure;
                  // Add Parameters to SPROC
                  SqlParameter parameterModuleID = new SqlParameter("@ModuleID",
                                                                                                                 SqlDbType.Int, 4);
                  parameterModuleID.Value = moduleID;
                  myCommand.Parameters.Add(parameterModuleID);

                  SqlParameter parameterPackDescription = new SqlParameter("@PackDescription",
                                                                                               SqlDbType.NVarChar, 50);
                  parameterPackDescription.Value = PackDescription;
                  myCommand.Parameters.Add(parameterPackDescription);

                  SqlParameter parameterStudentNo = new SqlParameter("@StudentNo",
                                                                                                  SqlDbType.NVarChar, 8);
                  parameterStudentNo.Value = StudentNo;
                  myCommand.Parameters.Add(parameterStudentNo);

                  SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn",                    
                                                                                                  SqlDbType.DateTime, 8);
                  parameterReservedOn.Value = ReservedOn;
                  myCommand.Parameters.Add(parameterReservedOn);

                  myConnection.Open();
                  myCommand.ExecuteNonQuery();
                  myConnection.Close();
            }

Is there a formatting problem ? The date passed in as a string is of format dd/mm/yyyy.
If you could help me with this, that would be great.

Thanks
Avatar of bramsquad
bramsquad
Flag of United States of America image

i think all SQL date data types have to be passed inbetween a set of pound signs

for example, instead of passing the string

14/9/2004

pass

#14/9/2004#

~b
it also could be that your system is expecting a date in the format mm/dd/yyyy
Avatar of saumathur
saumathur

ASKER

so how do i make the system accept the date i nthe format dd/mm/yyyy?
Avatar of YZlat
bramsquad # # will work with Access, not sql server.

saumathur , why don't you convert your date to mm/dd/yyyy format before the insertion?
myDate.ToString("mm/dd/yyyy")
With the following formattings SQL Server would take the date in correct format.
 
parameterReservedOn.ToString("yyyyMMdd")
or
parameterReservedOn.ToString("yyyy-MM-dd")
or
parameterReservedOn.ToString("u")     // u equal to yyyy-MM-dd hh:mm:ss
I want them to bein the database in the format dd/mm/yyyy. That is a requirement of the project.
In the code, where i go
 SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn",                    
                                                                                                  SqlDbType.DateTime, 8);
               parameterReservedOn.Value = ReservedOn;
               myCommand.Parameters.Add(parameterReservedOn);

I tried to say parameterReservedOn.Value = Convert.ToDateTime(ReservedOn);
THis gave me the same error.
Any thoughts ?
Thanks
pass the data to sql server in the locale independent format- yyyy-mm-dd as was mentioned by ihenry
Use Globalization. Start a new thread with new culture. (this is in VB format, change it to C#)

Imports System.Globalization
Imports System.Threading

Thread.CurrentThread.CurrentCulture = New CultureInfo("en-GB")
TextBox1.Text = Now.ToString("dd/mm/yyyy hh:mm tt")

Try this link:   http://www.microsoft.com/globaldev/getWR/steps/wrg_date.mspx
I think the problem u are facing is since the default dateTime is 8, u can not give a 10 chars input like - dd/mm/yyyy

So change the statement to

SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn",                    
                                                                                                  SqlDbType.DateTime); //Don't give the length


 parameterReservedOn.Value =(DateTime) ReservedOn.ToString("dd/mm/yyyy");


"I want them to bein the database in the format dd/mm/yyyy. That is a requirement of the project."

Note that the dates are always stored in binary format in the database, which is completely independent of the actual textual format you may be using. You can format a datetime to just any format you need after retrieving them from the database.
User  123654789987 i tried what u said with no size given to the argument and this statement

 parameterReservedOn.Value =(DateTime) ReservedOn.ToString("dd/mm/yyyy");

this did not even compile, as it says that this dd/mm/yyyy is not a valid System.IFormatProvider provider.

i tried to put in .ToString("d") and (d) still got the same compile error
Try the following

parameterReservedOn.Value = Convert.ToDateTime(ReservedOn.ToString("d"));

I am not getting any compile error
The thing is, that in the Database Table, the value is stored in the format
 dd/mm/yyyy

on the DataGrid, that is displayed on the webusercontrol it is in the form
9/13/2004 12:00:00 AM, which is mm/dd/yy hh:mm:ss AM/PM

The value extracted from the datagrid cell after getting rid og the hours and secnds is
string res which is "9/13/2004"

Now, this is the value that is being fed into the function mentioned above as ReservedOn

So, maybe this is creating havoc somewhere...!!! I tried to use the statement
parameterReservedOn.Value = (DateTIme)ReservedOn.Tostring("culture"); where culture is of the  CultureInfo and has been specified to "en-AU" format
THis gives me an error saying can convert string to type datetime.

Can someone tell me how to fix this..its eating my brain.!!!
Thanks
" Try the following

parameterReservedOn.Value = Convert.ToDateTime(ReservedOn.ToString("d"));

I am not getting any compile error"

I tried that, no compile error, but still the same error on the webpage
String was not recognised as valid DateTime
"The thing is, that in the Database Table, the value is stored in the format
 dd/mm/yyyy"

No it is NOT. It's just DISPLAYED like that. It's stored as binary if it is a DateTime field in the database.
oh ok...fair enough..!!

But how do i get round this .... i still cant figure it out.!
SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn",                    
                                                                                                  SqlDbType.DateTime, 8);
               parameterReservedOn.Value = DateTime.Parse(ReservedOn);
               myCommand.Parameters.Add(parameterReservedOn);

(Assuming that ReservedOn is a string containing the date)
Still the same error:
THis is in the debug window:

      ReservedOn      "9/13/2004 "      string
and the error is:
String was not recognized as a valid DateTime.
error on this line: Line 454:      parameterReservedOn.Value = DateTime.Parse(ReservedOn);

earlier it was in the myCOmmand.ExecuteNonQuery();

Okay. In this case, try this:
parameterReservedOn.Value = DateTime.Parse(ReservedOn, "MM/dd/yyyy", DateTimeFormatInfo.InvariantInfo);
(note that System.Globalization must be in the using clauses)
Note that this differs from what you wrote in the original question:
14/9/2004 must be dd/MM/yyyy
but
9/13/2004 must be MM/dd/yyyy
C:\Share\rmit01\Rainbow\DesktopModules\RMIT\Four\DesignStore\CDesignStoreDB.cs(454): The best overloaded method match for 'System.DateTime.Parse(string, System.IFormatProvider, System.Globalization.DateTimeStyles)' has some invalid arguments

THis is the error im getting.

Regardin ur previous comment, yes ur right, but the thing is that in the database table, when i view it, it is like 13/09/2004 but when it is displayed on the datagrid, its of the form 9/13/2004 12.00.00 AM Thats what i meant.
Um, try ParseExact() instead of Parse()
Damn..now i get the error
String was not recognized as valid DateTime in the line
DateTime.ParseExact(ReservedOn, "MM/dd/yyyy", DateTimeFormatInfo.InvariantInfo);
So. What is the format you're trying to parse here? Set a breakpoint and tell us: is the first part the day or the month?
i set a break point, and the value in reservedOn before this line is executed is
      ReservedOn      "9/13/2004 "      string
Now, I want to get this into the database as type Sql type datetime. i would like to get it to be like dd/mm/yyyy and not mm/dd/yyyy

So should i try to make it DateTime.ParseExact(ReservedOn, "dd/mm/yyyy",....etc.
Tried to make it dd/mm/yyyy, still the same problem
Note: mm stays for minutes, MM is months!
yeah i made that change, still the same error..!!! DUnno how to get rid of this..!!
Try "M/d/yyyy".
And if that doesn't help, maybe try ReservedOn.Trim() (this will get rid of any whitespace before or after the date in the string).
You wont believe this...NONE of those worked..!!!! boohoo!!

This is the debug window for ParameterReservedOn:

+      _value      "9/13/2004"      System.Object
      string      "9/13/2004"      string
      _version      Current      System.Data.DataRowVersion
      ActualSize      <error: an exception of type: {System.FormatException} occurred>      int
      CoercedValue      <error: an exception of type: {System.FormatException} occurred>      System.Object

So, the string inside is alright, but why does it not get changed to proper format
Did you already try "d/M/yyyy"?
yes i did. i tried d/M/yyyy and M/d/yyyy
     public static void Main() {
            string reservedOn="9/13/2004";
            DateTime dt=DateTime.ParseExact(reservedOn, "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);
            Console.WriteLine(dt);
      }

This does work for me (compiles and outputs the wanted date).
I did the following:

SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn", SqlDbType.DateTime, 8);
      string resOn = ReservedOn;
      DateTime dt = DateTime.ParseExact(resOn, "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);
      parameterReservedOn.Value = dt;
      myCommand.Parameters.Add(parameterReservedOn);

And got this:
String was not recognized as a valid DateTime.
in line 453:
 DateTime dt = DateTime.ParseExact(resOn, "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);

when the value goes into the database, thats when something is happening to it i reckon, if only i knew what
oh and i tried M/d/yyyy, d/M/yyyy, dd/MM/yyyy, MM/dd/yyyy, same result
Well, I'm pretty certain that your string contents is not correct. Does it work if you substitute it with a string literal just as I did?
string resOn = "9/13/2004";
parameterReservedOn.Value = resOn;
myCommand.Parameters.Add(parameterReservedOn);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

String was not recognized as a valid DateTime.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: String was not recognized as a valid DateTime.

Source Error:

Line 460:
Line 461:                  myConnection.Open();
Line 462:                  myCommand.ExecuteNonQuery(); //here is the error
Line 463:                  myConnection.Close();
Line 464:            }
 
beats the hell out of me..!!!!
Yeah, of course, you're not longer parsing the DateTime yourself. What happens if you ***ONLY*** replace the resOn with the listeral as I asked?

string resOn = "9/13/2004";
DateTime dt = DateTime.ParseExact(resOn, "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);
parameterReservedOn.Value = dt;
myCommand.Parameters.Add(parameterReservedOn);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
Ok, I did what u said and it worked..!!!!

So, why wasnt it working before..!!! I was chekcing the watches and the values before were exactly the same, then why didnt it work..?? and how do i make it work now..!!
ASKER CERTIFIED SOLUTION
Avatar of AvonWyss
AvonWyss
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ths function is being called so:
oDB.UpdateHasTaken(10239, PackDescr, StudNo, res); //res is the value fed in as ReservedOn

just to let u know where the value is coming there from

String ReservOn = dgi.Cells[4].Text; //this is the value in the datagrid and dgi is DataGridItems
string res = ReservOn.Substring(0, 10); //this i did to get rid of the hh:mm:ss from the data in the datagrid, cos as mentioned before, in the datagrid on the page it is displayed as MM/dd/yyyy hh:mmm:ss
Well, the problem is, Substring(0,10) will only work if the month an day both use two digits. But in this case, the month only has one digit, right? So this breaks your code.

Try this:
string res = ReservOn.Substring(0, ReservOn.IndexOf(' '));
YES YES YES YES !!!!!!! It works now....
parameterReservedOn.Value=DateTime.ParseExact(ReservedOn.Trim(), "M/d/yyyy", DateTimeFormatInfo.InvariantInfo);  worked perfectly

Thank you sooo much AvonWyss..!!! seriously..thanks a lot, will give u excellent grade dude.. thanks a lot....thanks a lot..

really appreciate you helping me for the past soooo many hours..!!
thanks so much
Good if it works....
But PLEASE also change the Substring() like I suggested in my previous comment, because if you don't do this, it will break on dates where the day and the month are just one digit (like "9/1/2004")!
ok...thanks