windows service storing the datetime in MySQL exception

Question

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 16-May-11 18:38pm

vrushali katkade

Updated 16-May-11 21:27pm

OriginalGriff

v7

Add a Solution

1 solution

Add a Solution

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

OriginalGriff · Answer 1 · 2011-05-16T20:32:00

Solution 1

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!

Posted 16-May-11 20:32pm

OriginalGriff

Updated 16-May-11 21:33pm

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

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

OriginalGriff 17-May-11 6:22am

If you get no update, then check the value of "e8.sts[counter8].HandleClient" - is it an ID which is in the database? Is it in "groupID" "9"?

vrushali katkade 17-May-11 6:33am

value is correct in the database

OriginalGriff 17-May-11 6:39am

Then log all the values to a text file, and see what you get: I suspect "e8.sts[counter8].HandleClient" is still the problem, but you need to get info on all the parameters to check.

vrushali katkade 17-May-11 6:53am

i will be check using the debugger it will be take the right value in the variable assign the ODBC command parameters value will be assigned right value there is no exception to execute the query

OriginalGriff 17-May-11 6:59am

If it executes the code without an exception and there is no update to the data, then the WHERE clause must be responsible!

windows service storing the datetime in MySQL exception

1 solution

Solution 1

Add your solution here

Preview 0

windows service storing the datetime in MySQL exception

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us