CODEWITHSUNDEEP
mysqlvb.net
Ismail.ethio
Ismail.ethio

Reputation: 35

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

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

What I have to do to insert as I entered correctly?

This is the code:

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: 1989

Answers (1)

Steve
Steve

Reputation: 3703

OK your problem is that you are setting dtr to the minimum datetime:

 dtr = DateTimePicker1.MinDate

I think you want to use the .value property as per the MSDN documentation here.

You are then using this variable in your insert statement, which is why all the dates are zeroed.

If this isn't simplified code for the purpose of the question, please search the web for "SQL Injection" and understand what it is and why it is important to prevent it. You should at the very least sanitise your input, but it would be preferable use database parameters.

On another note, the minimum DateTime for .net is 0000-00-00 however the minimum 'supported' date in MySQL is 1000-01-01. MySQL will allow 0000-00-00 but it isn't supported and therefore can't be recommended. Please see this page in the manual for more information. The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

Upvotes: 1

Related Questions