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

Question

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

Ah! A good opportunity of SQL Injection here.

ɐsɹǝʌ ǝɔıʌ
– ɐsɹǝʌ ǝɔıʌ

2014-02-13 15:42:39 +00:00
Commented Feb 13, 2014 at 15:42 — ɐsɹǝʌ ǝɔıʌ
– ɐsɹǝʌ ǝɔıʌ, Commented Feb 13, 2014 at 15:42

Steve · Accepted Answer · 2014-02-13 16:04:47Z

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'.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related