store datetimepicker value of c# into mysql database

Question

Hello I want to store datetimepicker value into mysql database my code is given below

dtpDate = datetimepicker1.value.date;
dtpTime = datetimepicker2.value.Timeofday;
MySqlCommand cmd = new MySqlCommand("INSERT INTO schedule_days(schedule_name,start_time,status,days,start_date,connector_id) VALUES ('" + name + "','" + dtpTime + "','" + s + "','" + day + "','"+dtpDate+"','" + chkArray[i].Tag + "')", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

but no value is being stored at database and at that place there is unable to read data comes. what may be the problem?

Solution 1

The Value is not being entered at MySQL database because there is mistake in your query at dtpTime and dtpDate fields.

you shout replace it whith dtpTime.Value.TimeofDay and dtpDate.Value.Date ane new query will be like this

dtpDate = datetimepicker1.value.date;
dtpTime = datetimepicker2.value.Timeofday;
MySqlCommand cmd = new MySqlCommand("INSERT INTO schedule_days(schedule_name,start_time,status,days,start_date,connector_id) VALUES ('" + name + "','" + dtpTime.Value.TimeofDay + "','" + s + "','" + day + "','"+dtpDate.Value.Date.ToString("yyyy-MM-dd HH:mm")+"','" + chkArray[i].Tag + "')", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

Solution 2

Well, it may not be the cause of the problem (are there any exceptions? What does ExecuteNonQuery return?) but you should definitely not be building up your SQL like this. It leads to SQL injection attacks, as well as data conversion problems.

Instead, you should use parameterized SQL:

using (MySqlConnection conn = new MySqlConnection(...))
{
    conn.Open();
    using (MySqlCommand cmd = new MySqlCommand(
       "INSERT INTO schedule_days(schedule_name,start_time,status,days,start_date,connector_id) " +
       "VALUES (@name, @time, @status, @days, @date, @connector)", conn))
    {
        cmd.Parameters.Add("@name", MySqlDbType.VarChar).Value = name;
        cmd.Parameters.Add("@time", MySqlDbType.Time).Value = dtpTime;
        cmd.Parameters.Add("@status", MySqlDbType.VarChar).Value = s;
        cmd.Parameters.Add("@days", MySqlDbType.Int32).Value = day;
        cmd.Parameters.Add("@date", MySqlDbType.Date).Value = dtpDate;
        cmd.Parameters.Add("@connector", MySqlDbType.VarChar).Value = chkArray[i].Tag;

        int insertedRows = cmd.ExecuteNonQuery();
        // TODO: Validate that insertedRows is 1?
    }
}

I've guessed at the data types - please check them against your actual database.

Comments

[Dustin Kingen]

I believe you will want AddWithValue since Add is marked as obsolete unless MySqlConnection is different than SqlConenction.

[Jon Skeet]

@Romoku: That's the problem with being on a flaky wifi connection with only intermittent docs access :) Thanks!

[Mike Perrenoud]

Actually that won't work - MySQL parameters must be prefaced with a :.

[Dustin Kingen]

@MichaelPerrenoud Wrong you can use : or @. Unless this is the wrong documentation. Which is actually possible if you're not using dotconnect.

[Jon Skeet]

@Romoku: Have found the overload allowing you to specify the SqlDbType and then set the property value.

[Dustin Kingen]

Yes that is correct. Although I'm not sure if you need to use object initializer with Value.

[Tejas Virpariya]

@JonSkeet:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':name, :time, :status, :days, :date, :connector)' at line 1 this kind of error I am getting.

[Mike Perrenoud]

I stand corrected, but I believe the correct documentation would be this link here, which then has a link to the MySqlParameter class which does in fact show the @ being used.

[Dustin Kingen]

@MichaelPerrenoud I just checked that documentation and it looks like there are no unnamed parameters and named parameters must start with ?

[Mike Perrenoud]

Did you look at the MySqlParameter linked documentation?

[Jon Skeet]

@TejasVirpariya: It should be fine according to the documentation - but try the version with @ prefixes instead (I've edited my answer).

[Dustin Kingen]

@MichaelPerrenoud Well it's confusing the documentation says to use ?, but apparently it has a switch to use the old style. There are also no updated examples of creating MySqlParameter. See MySqlCommand for the named parameters. Look at the top of the same page for the switch.

[Dustin Kingen]

@MichaelPerrenoud I guess unless I setup a MySql database and try it then there's no confirmation.

[Dustin Kingen]

@MichaelPerrenoud Alright I confirmed it. The syntax in my post is correct.

[Kevan]

@Romoku: Hi Romoku it I agree with your answer but it would be better if u use datetimepicker1.Value.Date.ToString("yyyy-MM-dd HH:mm") at the place of dtpdate and dtpTime.Value.TimeOfDay at the place of dtpTime. otherwise it would not be accepted in mysql again at the place of date field. To give value directly will also save your two space variable too.

[Dustin Kingen]

Your solution has vulnerabilities, so I cannot support it.

[Kevan]

@Romoku:ok no problem but please give me detail how it has vulnerabilities!

[Dustin Kingen]

See my answer. You're not using a parameterized query which creates vulnerabilities. If someone were to enter "';DROP TABLE schedule_days--" for the name variable bad stuff would happen.

[Kevan]

@Romoku:may be you are right but I have understood what use's problem is and give solution which user has accepted because it is working as he desired. May be your solution is secure but you has done the same mistake in filling of dtpDate and dtpTime filled which asker has done in his question.your value will not be entered in MySQL database if you will not use dtpDate.Value.Date.ToString("yyyy-MM-dd HH:mm") and dtpTime.Value.TimeofDay instead of dtpDate and dtpTime .

[Dustin Kingen]

I'm just saying fix your solution and I'll undo my downvote. Your solution is not safe.