getting date conversion error while converting datetimepicker value to date in vb.net

Question

I am getting "conversion failed when converting date and or time from character string".I have a function which for converting datetimpicker value to specific date format. and I am calling that in a sql query . please correct me where i am wrong.this is my function for converting datetimepicker value to my specific format:

Public Function convertDateTimePickerToDate(ByVal Datevalue As DateTimePicker) As Date

    Dim convertedDate As Date
    Dim format = "dd-MMM-yyyy"
    Dim dateString As String = Datevalue.Value.ToString(format)
    convertedDate = Date.ParseExact(dateString, format, Nothing)
    Return convertedDate

End Function

and in my sql query i am calling it as:-

sql = "INSERT INTO SIM_SALES VALUES('" & txt_simNO.Text & "','" &txt_simMSISDN.Text & "','" & txt_simCustName.Text & "','" & convertDateTimePickerToDate(dtp_simSaleDate) & "')"

please correct me where I am wrong. or is there any other suggestions through which i can implement the same logic. thanks in advance

Answer 1

That format string only applies while it is a string. When you convert it back to a date structure that information is lost. When the date is re-converted back to a string when you append it to your sql string it uses the current culture's default format which looks like it might be dd/MM/yyyy for you. SQL Server expects MM/dd/yyyy if doing a blind string to date conversion.

However using SQL parameters will solve the two issues you have here, the first being the wrong date serialization format and the second is a SQL injection vulnerability. A user could enter sql commands into those textboxes which would get executed after joining them together.

Look into using command.Parameters.AddWithValue() if you are using ado.net and pass the date structure in as is.