-1

I am trying to execute following code for I have hardcoded the date in but it doesn't seem to delete it before I had data mismatch expection.

string myQuery = "DELETE FROM Class WHERE Date=#10/12/2015#;";
Jaskowaty
  • 17
  • 9
  • you must end each statement with a semicolon, the part in the quotes is your string – Jaskowaty Nov 17 '15 at 14:08
  • no I'm doing this in C# OLE DB – Jaskowaty Nov 17 '15 at 14:09
  • ANSI SQL-92 and other standards require to terminate SQL statements with semicolon. However, most current RDBS implementations do not require the use of this terminator. – vacsora Nov 17 '15 at 14:11
  • You wouldn't happen to be starting a transaction and not committing or anything along those lines? The query String looks ok at the surface. Perhaps some more of the code might shine some light. – Bearcat9425 Nov 17 '15 at 14:13
  • No I'm not, it doesn't throw any exceptions or anything, I really don't know why it's not deleting it – Jaskowaty Nov 17 '15 at 14:15
  • date could be confused for a Keyword, might need to make an alias to get at it or escape it. See this. http://stackoverflow.com/questions/7842262/date-as-a-column-name – Bearcat9425 Nov 17 '15 at 14:17
  • Alright, I'll try that Bearcat9425, thanks! – Jaskowaty Nov 17 '15 at 14:19
  • No problem hope it works. – Bearcat9425 Nov 17 '15 at 14:19
  • To debug things like this: run the SQL string `DELETE FROM Class WHERE Date=#10/12/2015#;` directly in Access as query. (assuming Access is available) – Andre Nov 17 '15 at 14:21

3 Answers3

1

I ran the SQL query in Access and it didn't work. So I generated it using the tools avaliable there and it looks like that:

DELETE Class.ClassDate FROM Class WHERE (((Class.ClassDate)=#12/10/2015#));
Jaskowaty
  • 17
  • 9
  • 1
    Then all that was wrong with your original SQL was the column name - `Date` instead of `ClassDate`. And the reason it didn't throw an error is: `Date` is also a function. Had you entered the today date instead of `#10/12/2015#`, it would have deleted all data in your table instead of nothing... You were lucky. :) – Andre Nov 17 '15 at 14:48
  • So does this query work for you in your C# application? – Gord Thompson Nov 17 '15 at 14:50
1

Instead of:

string myQuery = "DELETE FROM Class WHERE Date=#10/12/2015#;";

use

string myQuery = "DELETE FROM Class WHERE Date=#10/12/2015#";

Be sure not to put ; before " in Access SQL in C#

Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77
Fokuslux
  • 11
  • 1
0

A good reality check is to replace DELETE with SELECT *, if the WHERE clause doesn't return any rows in a SELECT query, it isn't going to delete any either.

Is it possible there is a time component stored in the field Date? The value #10/12/2015# is implicitly #10/12/2015 12:00 AM#, and a test for equality will only return rows that match exactly. If you're actually looking for all rows with any time of day on that date...

WHERE [Date] BETWEEN #10/12/2015# AND #10/12/2015 11:59 PM#

(Not sure if Date is a reserved word in Access -- life is too short to depend on Jet -- but even if it isn't the square brackets won't hurt anything.)

Mark McGinty
  • 756
  • 7
  • 13