Insert date from VB to SQL Server

Question

I am trying to insert a date with time and minute into my SQL Server which has a column of type date.

But I get the following :

 There was an error parsing the query. [ Token line number = 1,Token line offset = 65,Token in error = 10 ]

Here is my code:

    Dim dateComplete As Date = CDate(Me.TB_Date.Text & " " & TXT_timeInHour.Text & ":" & TXT_timeInMin.Text)
    MsgBox(dateComplete)


    Dim MyConnexion As SqlCeConnection = New SqlCeConnection("Data Source=C:\Users\W8\Documents\aa.sdf;")
    Dim Requete As String = "Insert into toto(titre, chien, dateIn) values (99," & swimTotal & ", " _
                            & dateComplete & ")"

    Dim Commande As New SqlCeCommand(Requete, MyConnexion)
    MyConnexion.Open()
    Commande.ExecuteNonQuery()

I tried with format(dateComplete, "yyyy-DD-MM") but it doesnt give me the corresponding date into SQL Server Management.

Thank you.

score 3 · Accepted Answer · edited May 23 '17 at 12:28

This is one of the many reasons to use a parameterized query

Dim Requete As String = "Insert into toto(titre, chien, dateIn) " & _ 
                        "values (99, @swim, @dtcom)"
Using MyConnexion = New SqlCeConnection("....")
Using Commande = New SqlCeCommand(Requete, MyConnexion)
    Comande.Parameters.AddWithValue("@swim", swimTotal)
    Comande.Parameters.AddWithValue("@dtcom", dateComplete)
    MyConnexion.Open()
    Commande.ExecuteNonQuery()
End Using
End Using

In this way, the job to insert the date inside your database table, is executed by the database engine that knows better how to treat dates, numbers and strings. Not to forget, using a parameterized query, avoids any possibility to create an Sql Injection attack from a malicious user.

Alright, it works perfectly. Thank you very much for you fast and impressive response! — supertoto, Dec 30 '14 at 17:25

Insert date from VB to SQL Server

1 Answers1