Problem in insert a datetime into SqlServer 2005 through Visual Studio 2005

I user Visual Studio 2005 64 bit ,windowxp 64 bit ,sqlserver 2005

The Sql statement : "insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (20001,'test',28/3/2006,4/5/2006,4/5/2006 ) "

and the result in Datebase (All the time become 1/1/1900 0:00:00 )

Although i change the datetype from datetime to smalldatetime the result is same

and i try input the date 28/3/2006 0:00:00 into server but

it show the error:Incorrect syntax near '0'.

What wrong help me please,Thank.

Dim conn As SqlConnection
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("FYP_Staff_MangementConnectionString").ConnectionString)
Dim sqlstr As String = "insert into sickleave (StaffID,sickLeaveReason,DateStart,DateEnd,RegistrationDate) values (" & User.Identity.Name & ",'" & Me.TbReason.Text & "'," & Calendar1.SelectedDates.Item(0).Month.ToString & "/" & Calendar1.SelectedDates.Item(0).Day.ToString & "/" & Calendar1.SelectedDates.Item(0).Year.ToString & "," & Calendar1.SelectedDates.Item(Calendar1.SelectedDates.Count - 1) & ",GETDATE() ) "
Dim command As New SqlCommand(sqlstr, conn)
conn.Open()
Try
command.ExecuteNonQuery()
Catch ex As Exception
Me.lbSuccess.Text = ex.Message
End Try



Answer this question

Problem in insert a datetime into SqlServer 2005 through Visual Studio 2005

  • Dattan

    Thank you it help me a lot
  • Obsolete

    Use parametrized queries instead of string concatenation to create your query.
    That will solve all your problems. (String concatenation to create queries also creates security leaks)
    See here:
    http://fgheysels.blogspot.com/2005/12/avoiding-sql-injection-and-date.html


  • Problem in insert a datetime into SqlServer 2005 through Visual Studio 2005