working with date/time on sql in vb.net

Question

_{Wednesday, February 15, 2012 1:36 PM}

Hi,

I need help in saving datetime to sql database.

this is my code

 Dim SQLServer As String = My.Settings.SQLServer
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cudate As DateTime = DateTime.Now


        conn = New SqlConnection(SQLServer)
        conn.Open()

        cmd = New SqlCommand("INSERT INTO SMSKoder([SMSKode],[GyldigTil]) VALUES('" + kode + "', '" + cudate + "') ", conn)
        cmd.ExecuteNonQuery()
        


        conn.Close()
        conn = Nothing
        cmd = Nothing

when i execute my program, i get this error

Let me know whats the problem..

Thanks, Ed

All replies (8)

_{Wednesday, February 15, 2012 2:10 PM ✅Answered | 1 vote}

You need to use Command Parameters to avoid this issue:

cmd = New SqlCommand("INSERT INTO SMSKoder([SMSKode],[GyldigTil]) VALUES(@kode, @cudate)", conn)cmd.Parameters.AddWithValue("@kode", kode)cmd.Parameters.AddWithValue("@cudate", cudate)cmd.ExecuteNonQuery()

Paul ~~~~ Microsoft MVP (Visual Basic)

_{Wednesday, February 15, 2012 1:44 PM | 1 vote}

Use 

cudate.ToString("yyyy-MM-dd")

Amit Govil | Email

"Weeks of coding can save you hours of planning"

_{Wednesday, February 15, 2012 1:50 PM}

thanks amit.

i tried it on this format dd.MM.yyyy HH:mm:ss but im still getting the same error.. :(

Dim cudate As DateTime = DateTime.Now
 Dim cudatestr = cudate.ToString("dd.MM.yyyy HH:mm:ss")

Thanks, Ed

_{Wednesday, February 15, 2012 1:51 PM | 1 vote}

What is the datatype for that column in database.

Amit Govil | Email

"Weeks of coding can save you hours of planning"

_{Wednesday, February 15, 2012 1:56 PM}

the date type is datetime

here

Thanks, Ed

_{Wednesday, February 15, 2012 1:57 PM | 1 vote}

Proble is due to datetime format.

So better use parameterized query and add your datetime as a Datetime Parameter instead of a string.

 cmd = New SqlCommand("INSERT INTO SMSKoder([SMSKode],[GyldigTil]) VALUES(@code,@date) ", conn)

cmd.Parameters.Add(@Code,SqlDbtypes.Varchar).Values=kode cmd.Parameters.Add(@date, SqlDbtypes.Datetime).Values=cudatecmd.ExecuteNonQuery()Hope it will help you

Want to add MVP with my name.

_{Wednesday, February 15, 2012 2:12 PM}

hi bahushekh,

i get an error.. :( im sorry.. im new to vb

Thanks, Ed

_{Wednesday, February 15, 2012 3:25 PM}

sqldbtype is an enum in System.Data Namespace so you can either import it or directly use it.

I am sorry again i forgot to remove ; at the end of the statements. 

cmd = New SqlCommand("INSERT INTO SMSKoder([SMSKode],[GyldigTil]) VALUES(@code,@date) ", conn)
cmd.Parameters.Add(@Code,System.Data.SqlDbtypes.Varchar).Values=kode 

cmd.Parameters.Add(@date, System.Data.SqlDbtypes.Datetime).Values=cudate

cmd.ExecuteNonQuery()

Want to add MVP with my name.