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.SqlConnecti onString;
SqlCommand myCommand = new
SqlCommand("RMIT_DesignSto re_UpdateH asTaken", myConnection);
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedur e;
// Add Parameters to SPROC
SqlParameter parameterModuleID = new SqlParameter("@ModuleID",
SqlDbType.Int, 4);
parameterModuleID.Value = moduleID;
myCommand.Parameters.Add(p arameterMo duleID);
SqlParameter parameterPackDescription = new SqlParameter("@PackDescrip tion",
SqlDbType.NVarChar, 50);
parameterPackDescription.V alue = PackDescription;
myCommand.Parameters.Add(p arameterPa ckDescript ion);
SqlParameter parameterStudentNo = new SqlParameter("@StudentNo",
SqlDbType.NVarChar, 8);
parameterStudentNo.Value = StudentNo;
myCommand.Parameters.Add(p arameterSt udentNo);
SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn" ,
SqlDbType.DateTime, 8);
parameterReservedOn.Value = ReservedOn;
myCommand.Parameters.Add(p arameterRe servedOn);
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
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.SqlConnecti
SqlCommand myCommand = new
SqlCommand("RMIT_DesignSto
// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedur
// Add Parameters to SPROC
SqlParameter parameterModuleID = new SqlParameter("@ModuleID",
SqlDbType.Int, 4);
parameterModuleID.Value = moduleID;
myCommand.Parameters.Add(p
SqlParameter parameterPackDescription = new SqlParameter("@PackDescrip
SqlDbType.NVarChar, 50);
parameterPackDescription.V
myCommand.Parameters.Add(p
SqlParameter parameterStudentNo = new SqlParameter("@StudentNo",
SqlDbType.NVarChar, 8);
parameterStudentNo.Value = StudentNo;
myCommand.Parameters.Add(p
SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn"
SqlDbType.DateTime, 8);
parameterReservedOn.Value = ReservedOn;
myCommand.Parameters.Add(p
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
it also could be that your system is expecting a date in the format mm/dd/yyyy
ASKER
so how do i make the system accept the date i nthe format dd/mm/yyyy?
bramsquad # # will work with Access, not sql server.
saumathur , why don't you convert your date to mm/dd/yyyy format before the insertion?
saumathur , why don't you convert your date to mm/dd/yyyy format before the insertion?
myDate.ToString("mm/dd/yyy y")
With the following formattings SQL Server would take the date in correct format.
parameterReservedOn.ToStri ng("yyyyMM dd")
or
parameterReservedOn.ToStri ng("yyyy-M M-dd")
or
parameterReservedOn.ToStri ng("u") // u equal to yyyy-MM-dd hh:mm:ss
parameterReservedOn.ToStri
or
parameterReservedOn.ToStri
or
parameterReservedOn.ToStri
ASKER
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(p arameterRe servedOn);
I tried to say parameterReservedOn.Value = Convert.ToDateTime(Reserve dOn);
THis gave me the same error.
Any thoughts ?
Thanks
In the code, where i go
SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn"
SqlDbType.DateTime, 8);
parameterReservedOn.Value = ReservedOn;
myCommand.Parameters.Add(p
I tried to say parameterReservedOn.Value = Convert.ToDateTime(Reserve
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.Curre ntCulture = 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
Imports System.Globalization
Imports System.Threading
Thread.CurrentThread.Curre
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");
So change the statement to
SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn"
SqlDbType.DateTime); //Don't give the length
parameterReservedOn.Value =(DateTime) ReservedOn.ToString("dd/mm
"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.
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.
ASKER
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
parameterReservedOn.Value =(DateTime) ReservedOn.ToString("dd/mm
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(Reserve dOn.ToStri ng("d"));
I am not getting any compile error
parameterReservedOn.Value = Convert.ToDateTime(Reserve
I am not getting any compile error
ASKER
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.Tostr ing("cultu re"); 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
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.Tostr
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
ASKER
" Try the following
parameterReservedOn.Value = Convert.ToDateTime(Reserve dOn.ToStri ng("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
parameterReservedOn.Value = Convert.ToDateTime(Reserve
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.
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.
ASKER
oh ok...fair enough..!!
But how do i get round this .... i still cant figure it out.!
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(p arameterRe servedOn);
(Assuming that ReservedOn is a string containing the date)
SqlDbType.DateTime, 8);
parameterReservedOn.Value = DateTime.Parse(ReservedOn)
myCommand.Parameters.Add(p
(Assuming that ReservedOn is a string containing the date)
ASKER
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( );
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.Invaria ntInfo);
(note that System.Globalization must be in the using clauses)
parameterReservedOn.Value = DateTime.Parse(ReservedOn,
(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
14/9/2004 must be dd/MM/yyyy
but
9/13/2004 must be MM/dd/yyyy
ASKER
C:\Share\rmit01\Rainbow\De sktopModul es\RMIT\Fo ur\DesignS tore\CDesi gnStoreDB. cs(454): The best overloaded method match for 'System.DateTime.Parse(str ing, System.IFormatProvider, System.Globalization.DateT imeStyles) ' 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.
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()
ASKER
Damn..now i get the error
String was not recognized as valid DateTime in the line
DateTime.ParseExact(Reserv edOn, "MM/dd/yyyy", DateTimeFormatInfo.Invaria ntInfo);
String was not recognized as valid DateTime in the line
DateTime.ParseExact(Reserv
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?
ASKER
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(Reserv edOn, "dd/mm/yyyy",....etc.
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(Reserv
ASKER
Tried to make it dd/mm/yyyy, still the same problem
Note: mm stays for minutes, MM is months!
ASKER
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).
ASKER
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
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"?
ASKER
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(res ervedOn, "M/d/yyyy", DateTimeFormatInfo.Invaria ntInfo);
Console.WriteLine(dt);
}
This does work for me (compiles and outputs the wanted date).
string reservedOn="9/13/2004";
DateTime dt=DateTime.ParseExact(res
Console.WriteLine(dt);
}
This does work for me (compiles and outputs the wanted date).
ASKER
I did the following:
SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn" , SqlDbType.DateTime, 8);
string resOn = ReservedOn;
DateTime dt = DateTime.ParseExact(resOn, "M/d/yyyy", DateTimeFormatInfo.Invaria ntInfo);
parameterReservedOn.Value = dt;
myCommand.Parameters.Add(p arameterRe servedOn);
And got this:
String was not recognized as a valid DateTime.
in line 453:
DateTime dt = DateTime.ParseExact(resOn, "M/d/yyyy", DateTimeFormatInfo.Invaria ntInfo);
when the value goes into the database, thats when something is happening to it i reckon, if only i knew what
SqlParameter parameterReservedOn = new SqlParameter("@ReservedOn"
string resOn = ReservedOn;
DateTime dt = DateTime.ParseExact(resOn,
parameterReservedOn.Value = dt;
myCommand.Parameters.Add(p
And got this:
String was not recognized as a valid DateTime.
in line 453:
DateTime dt = DateTime.ParseExact(resOn,
when the value goes into the database, thats when something is happening to it i reckon, if only i knew what
ASKER
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?
ASKER
string resOn = "9/13/2004";
parameterReservedOn.Value = resOn;
myCommand.Parameters.Add(p arameterRe servedOn);
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..!!!!
parameterReservedOn.Value = resOn;
myCommand.Parameters.Add(p
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(
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.Invaria ntInfo);
parameterReservedOn.Value = dt;
myCommand.Parameters.Add(p arameterRe servedOn);
myConnection.Open();
myCommand.ExecuteNonQuery( );
myConnection.Close();
string resOn = "9/13/2004";
DateTime dt = DateTime.ParseExact(resOn,
parameterReservedOn.Value = dt;
myCommand.Parameters.Add(p
myConnection.Open();
myCommand.ExecuteNonQuery(
myConnection.Close();
ASKER
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..!!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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(' '));
Try this:
string res = ReservOn.Substring(0, ReservOn.IndexOf(' '));
ASKER
YES YES YES YES !!!!!!! It works now....
parameterReservedOn.Value= DateTime.P arseExact( ReservedOn .Trim(), "M/d/yyyy", DateTimeFormatInfo.Invaria ntInfo); 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
parameterReservedOn.Value=
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")!
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")!
ASKER
ok...thanks
for example, instead of passing the string
14/9/2004
pass
#14/9/2004#
~b