1

I am been creating a desktop form in vb.net in which I used datetime-picker tool. I want a format of dd/MM/yyyy so I kept that format from the datetimepicker properties.

But when I am trying to insert the records from vb.net to MSSQL, it will obvious shows me SQL Exception:

conversion failed when converting date and/or time from character string.

because MSSQL supports some ISO FORMAT and all.(MM/dd/yyyy, accepted and ran properly,I tried this).

So I thought to convert the date into vb.net, store it in a variable declared as 'date' or 'datetime' (tried both), into accepted format and inserted the variable into the Sql Command.

The following command I used to convert date.

1) Fdt = Date.ParseExact(From_Dt.Text, "MM/dd/yyyy", Globalization.CultureInfo.InvariantCulture)-------- Searched the internet and got this stuff.

2) Fdt = Convert.ToDateTime(From_Dt.Text)

3) #" & format(DateTimePicker1.Value.Date) & "# ----directly inside the sqlcommand in vb.net.

and many other functions I am not posting now.

I am not getting one thing, all above worked and gave me result in MM/dd/yyyy (checked by adding watch on it during runtime) but it still gives me same exception but if I change the date format from datetimepicker properties to MM/dd/yyyy then the sqlquery is accepted without exception..

Even I tried datatype of the field to date/datetime/datetime2(7) one by one.

And even tried to convert directly into SQLQuery by some Convert/Cast function but the same exception I got.

But none of them worked, I had referred many links and this question may be similar to other but I didn't found any solution so I at last had to create this question.

The Code in which query is fired : 

Try
        'Dim Fdt As DateTime, Tdt As DateTime
        'Fdt = Date.ParseExact(From_Dt.Text, "yyyy-MM-dd", Globalization.CultureInfo.InvariantCulture)
        'Tdt = Date.ParseExact(To_Dt.Text, "yyyy-MM-dd", Globalization.CultureInfo.InvariantCulture)
        'Fdt = Convert.ToDateTime(From_Dt.Text)
        'Tdt = Convert.ToDateTime(To_Dt.Text)
        If txtCompName.Text <> nothing And cmbCompType.Text <> nothing Then
            If CheckEof("select * from Company where Comp_Year = '" & Year1 & "' and Comp_Code = '" & txtCompCode.Text & "'", con) Then
                cmd = New SqlCommand(("insert into Company (Comp_Year, Comp_Code, Comp_Name, Comp_Prop, Comp_Add1, Comp_Add2, Comp_City, Comp_Phone, Comp_FAX, Comp_GST, Comp_CST, Comp_PNR, Comp_TDSNo, Comp_DrugLIC1, Comp_DrugLIC2, Comp_Mess1, Comp_Mess2, Comp_FDT, Comp_TDT, Comp_Distribution, Comp_Juridiction, Comp_Type) values('" &
                Year1 & "','" & txtCompCode.Text & "','" & txtCompName.Text & "','" & txtCompShtName.Text & "','" &
                txtCompAdd1.Text & "','" & txtCompAdd2.Text & "','" & txtCompCity.Text & "','" & txtCompPhno.Text & "','" &
                txtCompMobile.Text & "','" & txtCompGST.Text & "','" & txtCompCST.Text & "','" & txtCompPAN.Text & "','" &
                txtCompTDS.Text & "','" & txtCompDrg1.Text & "','" & txtCompDrg2.Text & "','" & txtCompMsg1.Text & "','" &
                txtCompMsg2.Text & "','" & From_Dt.Value & "','" & To_Dt.Value & "','" & txtCompDist.Text & "','" &
                txtCompJuri.Text & "','" & cmbCompType.Text & "')"), con)
            End If
            ExecuteQuery()
            MsgBox("Company Created SuccessFully,", MessageBoxIcon.Information)
            CompCreation_Reset()
        Else
            MsgBox("Mandatory fields cannot be Blank", MessageBoxIcon.Stop)
            txtCompName.Focus()
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
halfer
  • 19,824
  • 17
  • 99
  • 186
bonny
  • 688
  • 1
  • 14
  • 33
  • dd/MM/yyyy I want so by datetimepicker.text I get that format, later I converted to MM/dd/yyyy by the functions I wrote above but not working for me – bonny May 18 '16 at 06:02
  • One of the unambiguous ISO standard date fromats is yyyy-mm-dd hh:mm:ss. Additionally you should not be passing dates as strings. See http://stackoverflow.com/questions/34358886/string-sql-is-not-in-correct-format-insertion-fails-using-vb-nethow?rq=1 – Alex May 18 '16 at 06:03
  • okie I try yyyy-mm-dd hh:mm and say you @Alex, I guess I did it before but try and say you again.... – bonny May 18 '16 at 06:07

1 Answers1

6

Winforms DateTimePicker has a datetime type property called Value

.Net Datetime maps directly to sql server datetime, and since datetime stores no display format, you don't need to worry about the presentation layer's format at all.

simply pass the value property as a parameter to your sql statement. Further reading: How do I create a parameterized SQL query? Why Should I?

Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Got this now "the conversion of varchar datatype to a datetime data type resulted in an out of range value" Please explain me what is this??? and any solution on this. @Zohar Peled.. – bonny May 18 '16 at 06:24
  • You are sending a varchar value to your sql statement instead of a parameter with datetime datatype. Please edit your question to show the sql statement and vb.net code that executes it. – Zohar Peled May 18 '16 at 06:30
  • Check the code @Zohar Peled, I tried From_dt.value as you said but gave me different thing "the conversion of varchar datatype to a datetime data type resulted in an out of range value" ... lines below Try in the code are commented as I tried those things tooo...! – bonny May 18 '16 at 06:42
  • @bonny - Zohar's telling you to [use parameters](https://msdn.microsoft.com/en-us/library/ms254953(v=vs.110).aspx). I.e. don't try to construct your SQL as one big *string*. It's the conversion to *strings* that is giving you formatting issues. – Damien_The_Unbeliever May 18 '16 at 06:44
  • okieee, so I use paramater for the date instead of string in sqlcommand, is it like that @Damien_The_Unbeliever – bonny May 18 '16 at 06:47
  • Okie wait, I think I got it.... @Damien_The_Unbeliever Just Tell you in a short time... – bonny May 18 '16 at 06:49
  • @bonny - your Code should look something like `cmd = New SqlCommand(("insert into Company (...columns...) values(@Comp_Year, @Comp_Code,....)");` and then additional lines as you add each parameter to the command - `cmd.Parameters.Add("@Comp_Year", SqlDbType.Int).Value = Year1`, etc. – Damien_The_Unbeliever May 18 '16 at 06:49
  • Okie so I learnt two things from you, 1) How to pass sql parameters and 2) a new way to write sqlquery in a proper order... Thanx alot, Its done @Damien_The_Unbeliever – bonny May 18 '16 at 07:04
  • Also big thanx to @ZoharPeled – bonny May 18 '16 at 07:04
  • Thanks @Damien_The_Unbeliever! – Zohar Peled May 18 '16 at 07:11
  • @bonny Glad to help :-) – Zohar Peled May 18 '16 at 07:11