Conversion failed when converting date and/or time from character string vb.net

Question

Can't seem to find other way to convert this:

sql = "insert into Attendance values('" & Label1.Text & "','" & Button1.Text & "','" & Date.Today & "','" & TimeOfDay & "','null' )

Label1.Text is EmpID from Employee Database Button1.Text is status which is Time In or Time Out (remarks)

Code in SQL Server Management of Attendance is:

create table Attendance
(
 EmpID varchar(25) foreign key references Employee(EmpID),
 remarks varchar (25) primary key not null,
 checkdate date,
 tin time,
 tout time,
)

Answer 1

It's a terrible way to write/run a database query. Basically what is happening is that your date values are being turned into a string representation by visual basic when the SQL string is being assembled, but sql-server cannot turn the string generated back into a date

Fortunately, it doesn't have to, and you should write your query specifically so that it doesn't have to

It should look more like this:

Dim sqlC as New SqlCommand("INSERT INTO attendance VALUES(@emp, @rem, @dat, @tin, null)", myConnectionStringVariable)
sqlC.Parameters.AddWithValue("emp", Label1.Text)
sqlC.Parameters.AddWithValue("rem", Button1.Text)
sqlC.Parameters.AddWithValue("dat", Date.Today)
sqlC.Parameters.AddWithValue("tin", DateTime.Now)

Please at the very least look up a few tutorials on parameterised queries. Never ever (ever) again write an SQL where the value you want to include in your SQL, from some UI component, is string concatenated into the SQL command string

Even better, stop writing SQL code in your button click event handlers, and use an ORM library (datasets, entity framework, nhibernate etc)

Your code as is, is a massive security risk and is wholesale a pattern to be completely avoided