1

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,
)
halfer
  • 19,824
  • 17
  • 99
  • 186
Karen Poscablo
  • 19
  • 1
  • 2
    You should be using parameterized queries. [Bobby Tables: A guide to preventing SQL injection](http://bobby-tables.com/) -- and `null` is not a string, so don't use single quotes around `null` like you do with `'null'`. – SqlZim Aug 31 '17 at 12:18
  • So what is the issue are you facing ? What's the error message ? – Pratik K Chatterjee Aug 31 '17 at 12:21
  • your problem is here: Date.Today & "','" & TimeOfDay should create a datetime im assuming? and you are setting this to date in your table - if you are creating a datetime then surely you need - Date.Today & "' '" & TimeOfDay - and your table creation should read checkdate datetime, – jimmy8ball Aug 31 '17 at 12:22
  • @SqlZim Thank you for the comment. I think the 'null' is accepted because the error is conversion failed when converting date and/or time from character string. – Karen Poscablo Aug 31 '17 at 12:29
  • @PratikKChatterjee Thank you for the comment. The error message is conversion failed when converting date and/or time from character string. – Karen Poscablo Aug 31 '17 at 12:30
  • @jimmy8ball Thank you for the comment. Should the datetime is combine with both the date and time? I just need a data for date and another for time. – Karen Poscablo Aug 31 '17 at 12:36
  • Please read [Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers?](//meta.stackoverflow.com/q/326569) - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. – halfer Aug 31 '17 at 16:06

1 Answers1

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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80