CODEWITHSUNDEEP
mysqlvb.net
usminuru
usminuru

Reputation: 341

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

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

Upvotes: 0

Views: 18717

Answers (4)

Ayushman Mishra
Ayushman Mishra

Reputation: 11

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.

Upvotes: 1

Steve
Steve

Reputation: 216253

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.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460048

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

Upvotes: 2

Ohlin
Ohlin

Reputation: 4178

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.

Upvotes: 0

Related Questions