0

This is my SQL statement: 

query = "Update(tbl_shifts) SET StartTime=" & starttime & ", Endtime =" & endtime & ", TotalHours =" & totalhours & ", TotalPay=" & pay & ", Completed =" & True & "WHERE ShiftDate=" & DateTime.Today And "EmployeeName =" & EmployeeLogin.usersname

Upon the click of the button, it is supposed to add the variables (starttime, endtime, totalhours, pay) and tick the yes/no 'Completed' field into the row of tbl_shifts where the entry in the column 'EmployeeName' matches the global variable 'EmployeeLogin.usersname' and the entry in the column ShiftDate (which is currently stored as DD/MM/YYYY HH:MM:SS) matches DateTime.Today (which is only DD/MM/YYYY (I think)). What do I do to make my sql work as intended?

molnarm
  • 9,856
  • 2
  • 42
  • 60
Charlie Forbes
  • 1
  • 2
  • What is your problem? What is the intended behavior? Also *please* read [this](http://stackoverflow.com/questions/601300/what-is-sql-injection) question and its answers. – molnarm May 06 '15 at 12:53
  • 2
    you need an space before WHERE – verhie May 06 '15 at 12:54
  • 1
    Also: which DBMS are you using? –  May 06 '15 at 12:54
  • 3
    [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection – marc_s May 06 '15 at 12:54
  • I'm using access. I need the variables detailed above to be inserted into the blank fields in tblwhere EmployeeName = employeelogin.usersname and ShiftDate = datetime.today. Although the entries into ShiftDate are DD/MM/YYYY HH:MM:SS whereas Datetime.today is only DD/MM/YYYY so i need to know how to: 1) make the sql work and 2) how to match a DD/MM/YYYY value to a DD/MM/YYYY HH:MM:SS value – Charlie Forbes May 06 '15 at 13:06

1 Answers1

0

There are a few typo's in your query

This:

query = "Update(tbl_shifts) SET StartTime=" & starttime & ", Endtime =" & endtime & ", TotalHours =" & totalhours & ", TotalPay=" & pay & ", Completed =" & True & "WHERE ShiftDate=" & DateTime.Today And "EmployeeName =" & EmployeeLogin.usersname

should be:

query = "Update tbl_shifts SET StartTime=" & starttime & ", Endtime =" & endtime & ", TotalHours =" & totalhours & ", TotalPay=" & pay & ", Completed =" & True &  " WHERE ShiftDate=" & DateTime.Today & " And EmployeeName =" & EmployeeLogin.usersname

So

  • Update tbl_shifts
  • a space key before WHERE
  • move the double quote before " And EmployeeName
verhie
  • 1,298
  • 1
  • 7
  • 7