Having issue with inserting datetime in mysql from C# on testing PC

Question

I am inserting data into MySql from C# code in Windows Forms application. This work fine on my development machine. when I move the code to other machine to test I get the error of

string was not recognized as valid datetime

I have tried with every solution TryParse, ParseExact, TryParseExact even Convert function. in last I tried to done with direct in query to skip further problems.

string dd = row["TIMESTAMP1"].ToString(); // 14-Nov-2018 02:25:00 AM
DateTime dt1 =  DateTime.ParseExact(dd, "dd-MMM-yy h:mm:ss tt", null);
//string date2 = dt1.ToString("yyyy-MM-dd hh:mm:ss");
string y = dt1.Year.ToString();
string m = dt1.Month.ToString();
string d = dt1.Day.ToString();
string h = dt1.Hour.ToString();
string i = dt1.Minute.ToString();
string s = dt1.Second.ToString();
string dte = y + "," + m + "," + d + "," + h + "," + i + "," + s;

//DateTime.TryParseExact(date2, "yyyy-MM-dd hh:mm:ss", new CultureInfo("en-US"), DateTimeStyles.None, out dateDb);
//DateTime date3 = DateTime.Parse(date2, "yyyy-MM-dd hh:mm:ss");
string query = "INSERT INTO transaction (transid, ticketid, storeid, TIMESTAMP, transtypeid, total) Values ('" + row["transid"] + "','" + row["ticketid"] + "','" + Convert.ToInt32(row["storeid"]) + "',STR_TO_DATE('"+dte+ "', '%Y,%m,%d,%h,%i,%s'),'" + Convert.ToInt32(row["transtypeid"]) + "','" + Convert.ToDecimal(row["total"]) + "')";

How do I make this possible to work on other machines without exceptions. It works fine on development machine.

Update

tried with parameters too

cmd.Parameters.Add(new MySqlParameter("@date", date2)).MySqlDbType = MySqlDbType.DateTime;

This looks like a formatting issue between locales. The way to avoid formatting issues is simple: **never concatenate input**. Instead, use *pararmeters*. https://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp - btw the bigger issue with concatenation is "SQL Injection", but it also solves locale issues — Marc Gravell, Mar 20 '18 at 22:42
Strings are not Dates. The NET DB Provider(s) are perfectly capable of passing a NET `DateTime` and storing it — Ňɏssa Pøngjǣrdenlarp, Mar 20 '18 at 22:43
The database doesnt matter - the NET Db Providers all provide the means for storing data (including DateTime) in their respective DBs. — Ňɏssa Pøngjǣrdenlarp, Mar 20 '18 at 22:54
@MarcGravell tried with paramters, still not working on second machine. it fine on my development machine, updated question too — Mangrio, Mar 20 '18 at 23:04
@Mangrio do you have code of what you tried? the parameter values should be `int`, `DateTime`, etc - not the strings — Marc Gravell, Mar 20 '18 at 23:05
@Mangrio and in the update: `date2` looks to be a `string`; it needs to be a `DateTime` — Marc Gravell, Mar 20 '18 at 23:11
You should use parameterized queries instead of generating query using string joining. That will not have this issue. — Chetan, Mar 21 '18 at 01:31
I did everything as told but not working my pakaged code on another machine — Mangrio, Mar 21 '18 at 19:05
@MarcGravell changes string to date, but not working. Actually working my PC, when I make package of it and install on other PC, it gives an error — Mangrio, Mar 21 '18 at 19:06

Having issue with inserting datetime in mysql from C# on testing PC

0 Answers0