Saving Date&Time from VB.Net to mysql Database Date?

Question

I'm inserting client information into Clients table found in MySql database from VB.Net 2010. The problem is when it insert Date values, the MySql Date column shows: "0000-00-00".

What I have to do to insert the compatible date format into MysQl Database?

This is the code that i have tried to do that:

Dim dtb, dtr As DateTime
DateTimePicker1.Format = DateTimePickerFormat.Custom
        DateTimePicker1.CustomFormat = "yyyy-mm-dd"
        dtb = DateTimePicker1.MinDate
        txtdtb.AppendText(dtb)

        DateTimePicker2.Format = DateTimePickerFormat.Custom
        DateTimePicker2.CustomFormat = "yyyy-mm-dd"
        dtr = DateTimePicker1.MinDate

            ExecSQL("INSERT INTO clients VALUES('" & clid.Text & "','" & clname.Text & "','" & clgen.Text & "','" & dtb & "','" & claddress.Text & "','" & clemail.Text & "','" & clphone.Text & "','" & clocp.Text & "','" & dtr & "')")
            MsgBox("Record Saved", MsgBoxStyle.Information, "Save")

            FillList(frmMember.lvMember, GetData("SELECT * FROM clients"))

        End If

Please use a parameterized query.... of course after rewriting that ExecSQL — Steve
– Steve, Commented Feb 13, 2014 at 14:43

Tim Schmelter · Accepted Answer · 2014-02-13 15:01:50Z

2

You are inserting DateTimePicker1.MinDate instead of the actual values the user has chosen.

Also, use uppercase months otherwise these are the minutes mm:

DateTimePicker1.CustomFormat = "yyyy-MM-dd"
DateTimePicker2.CustomFormat = "yyyy-MM-dd"

Finally, use sql-parameters instead of string concatenation to prevent sql-injection and localization or conversion issues.

Here's an example:

Using con As New MySqlConnection(My.Settings.ConnectionString)
    Using cmd = New MySqlCommand("INSERT INTO clients VALUES(@clid,@clname,@clgen,@dtb,@claddress,@clemail,@clphone,@clocp,@dtr)", con)
        cmd.Parameters.AddWithValue("@clid", Int32.Parse(clid.Text))
        ' .... '
        cmd.Parameters.AddWithValue("@dtr", dtr.Value)
        con.Open()
        Dim insertedClientCount = cmd.ExecuteNonQuery()
    End Using
End Using

edited Feb 13, 2014 at 15:01

answered Feb 13, 2014 at 14:41

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

usminuru Over a year ago

Thanks, I'm beginner please help me how to do that?

Tim Schmelter Over a year ago

@ensberiyu: i've added an example. I've assumed that clid actually is an int in the database. Always use the correct type. Therefore i've used Int32.Parse. You can use Int32.TryParse to validate the input.

Bugs · Accepted Answer · 2017-03-29 10:11:51Z

1

In short you need DateTimePicker1.value.tostring("yyyy-M-d").

The CustomFormat property of DateTimePicker only changes the way the component is displayed on the form, the actual date value of the DateTimePicker is stored in its Value property:

DateTimePicker1.Value

To change the format of the date you can just use the ToString(format as string) method:

DateTimePicker1.Value.ToString("yyyy-M-d")

In MySQL the "date" data type stores date only and accepts date in the format "2017-03-20" hence we use the format "yyyy-M-d" in the ToString function.

edited Mar 29, 2017 at 10:11

Bugs

4,4799 gold badges34 silver badges41 bronze badges

answered Mar 20, 2017 at 10:16

Ayushman Mishra

111 silver badge4 bronze badges

1 Comment

Venu GoPal Over a year ago

Nice and simple (Y)

Ohlin · Accepted Answer · 2014-02-13 14:44:59Z

0

I had problems like that as well and I solved it by specifying ToString("s") to all my DateTime variables

dtb.ToString('s')
dtr.ToString('s')

By writing this way the date has the correct format (2006-08-22T06:30:07) when inserting into MySQL.

answered Feb 13, 2014 at 14:44

Ohlin

4,2082 gold badges31 silver badges35 bronze badges

1 Comment

usminuru Over a year ago

When I check the position of (yyyy/mm/dd) using Msgbox: it shows (dd/mm/yyyy). I think the position of the date format not changed. what is the problem?

Steve · Accepted Answer · 2014-02-13 15:13:51Z

Probably the problem of the zero date is due to the fact that you read the wrong property of the DateTimePicker (MinDate instead of Value), but you have a big problem here.

If you use string concatenation to form your Sql commands, you are writing a very weak code. First you are open to Sql Injections, second you cannot be sure to format correctly your input values to the likes of the database engine and third parsing error could arise if your user types a single quote in a string field.

To fix all of these problems require a rewrite of your ExecSQL to receive a collection of parameters

For Example

 Dim pms As List(Of MySqlParameter) = new List(Of MySqlParameter)()
 pms.Add(new MySqlParameter("@id",clid.Text) ' Convert.ToInt32(clid.Text) if this is numeric'
 .... and so on for the other parameters ....

 ExecSql("Insert into clients " & _
         "VALUES(@id,@name,@gen,@dtb,@addr,@email,@phone,@clop,@dtr)",pms)


 Public Sub ExecSQL(command As String, pms As List(Of MySqlParameter))
     .. open the connection with using
     Dim cmd = new MySqlCommand(command, connection)
     cmd.Parameters.AddRange(pms.ToArray())
     cmd.ExecuteNonQuery();
 End Sub

As you can see, the command text is now more readable and there are no more string concatenations with embedded single quotes.

Collectives™ on Stack Overflow

Saving Date&Time from VB.Net to mysql Database Date?

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related