Click here to Skip to main content
15,500,838 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I will be tested the application as windows application then it will be stored the datetime in MySQL data base.When I will be start this application using windows service it will be thrown this exception.

error [HY000][MySQL][ODBC 3.51 Driver] [MySqlid -6.0.11-alpha-community]incorrect datetime value " 5/6/2011 9:00:00 AM" for column column-name at row1

Windows application take the system format & my system format is yyyy-MM-dd hh:mm:ss
in windows service which format is used.


query18 += "select '" + obj8 + "' as DTvalue ,'" + date8 + "' as DTdatelogged1 ,'" + OpcGroup.QualityToString(e8.sts[counter8].Quality) + "' as DTquality ,'" + DateTime.FromFileTime(e8.sts[counter8].TimeStamp) + "' as DTtimestamp ,'" + e8.sts[counter8].HandleClient + "' as DTparamID Union " + Environment.NewLine;

UpdateQuery = Update parameter t Left join + Environment.NewLine;
                    UpdateQuery8 +=  (  + query18 +  ) Temp on" + Environment.NewLine;
                    UpdateQuery8 += t.itemID=Temp.DTparamID+ Environment.NewLine;
                    UpdateQuery8 += set paramvalue=DTvalue, date_logged1=DTdatelogged1,Quality=                         DTquality,date_logged=DTtimestamp   + Environment.NewLine;
                    UpdateQuery8 += where t.groupID=9 and t.itemID=Temp.DTparamID;




my query likethis timestamp value is 129500892576718750 it will be convert DateTime.FromFileTime() function converted value like '2011-05-17 12:30:57' in windows application it will be write into mysql database
but in windows service converted value like 2011/05/17 12:30:57 PM it will be not accepted by the MYSQL database same thing i will be used in the windows service



Please help me in this regard.

Thanks in Advance.

[edit]Code block added, "Ignore HTML..." option disabled - OriginalGriff[/edit]
Posted
Updated 16-May-11 22:27pm
v7

1 solution

SQL DateTime format is ISO: it is always yyyy-MM-dd HH:mm:ss.
Check where you are getting your date string from: the example in the error message is not in ISO format.

Keep DateTime information in DateTime variables: don't convert it to string, and especially, don't convert it to string to pass it to MySQL: use a Parametrized Query and pass it as a DateTime. Then you can't have any format conversion errors!


"please see my updates"


What did I say about Parametrized Queries?

Your code is the problem:
' as DTquality ,'" + DateTime.FromFileTime(e8.sts[counter8].TimeStamp) + "' as DTtimestamp ,'"

FromFileTime returns a DateTime, which your code causes to have an implicit ToString() method added in order to concatenate the strings.
This is where your problem is.

Change the way you build your query. Do not concatenate strings: it wastes memory and leave you wide open for anm SQL Injection attack which could accidentally or deliberately destroy your database, as well as cause your error.
If you convert this to Parametrized queries, your error will disappear!
 
Share this answer
 
v2
Comments
vrushali katkade 17-May-11 3:04am    
please see my updates
OriginalGriff 17-May-11 3:33am    
Answer updated
vrushali katkade 17-May-11 4:19am    
but this code worked properly in windows application
OriginalGriff 17-May-11 6:20am    
Possibly, yes. But what Locale is the PC set to? What locale is the Webserver?
vrushali katkade 17-May-11 6:08am     CRLF
UpdateQuery8 = "Update parameter " + Environment.NewLine; UpdateQuery8 += "set paramvalue=@paramvalue,date_logged1=@date_logged1,Quality=@Quality,date_logged=@date_logged" + Environment.NewLine; UpdateQuery8 += "where groupID=9 and itemID=@itemID"; cmd8 = new OdbcCommand(UpdateQuery8, con136); cmd8.Parameters.Add("@paramvalue", obj8.ToString()); cmd8.Parameters.Add("@date_logged1", date8); cmd8.Parameters.Add("@Quality", OpcGroup.QualityToString(e8.sts[counter8].Quality)); cmd8.Parameters.Add("@date_logged", dt); cmd8.Parameters.Add("@itemID",e8.sts[counter8].HandleClient); cmd8.ExecuteNonQuery(); it will be execute but there no updation in database

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900