Access SQL - Alternate for !=

Question

I'm working in a C# program. I'm using Access 2010. I have some SQL Queries which are confusing.

OleDbCommand cmd = new OleDbCommand("SELECT * FROM Daybook WHERE [Entry Date]<=#" +
       dateTimePicker13.Value.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture) + 
       "# AND [Entry Date]>=#" + 
       dateTimePicker12.Value.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture) + 
       "# AND Credit<>0 AND Debit<>0", conn);

It is not working. I have also tried with NOT Credit=0... But that is also not working. When I tried `Credit!=0' it gave me a syntax error.

Also : When I replace 0 with 1, NOT Credit=1 Or Credit<>1 it responses back and shows all values other than 1, which is correct for the second case.

Is this the correct method to do this? I have to select values where Credit and Debit are not 0. Is there a possible alternate or is that a syntax mistake I made?

Thanks in Advance!

You should use parameters to prevent from SQL Injections. Also it will make the code way more readable. — Sybren, Jan 05 '15 at 13:37
But will `OR` return values where both **Credit and Debit** are not 0; — Harshan01, Jan 05 '15 at 13:37
`Harshan01` Do a google search if you need more explanation on what SQL Injection is and how to prevent it.. come on now.. !!! [How To: Protect From SQL Injection in ASP.NET](http://msdn.microsoft.com/en-us/library/ff648339.aspx) — MethodMan, Jan 05 '15 at 13:38
[SQL Tutorial how to use "AND && OR"](http://www.w3schools.com/sql/sql_and_or.asp) — MethodMan, Jan 05 '15 at 13:40
And, it is just select keyword which uses just dates. Can you please explain what type of injection would occur. Moreover, the dates are culture invariant to prevent UK/US issue. — Harshan01, Jan 05 '15 at 13:41
Are you trying to select records where BOTH Credit and Debit are nonzero, or records where EITHER Credit or Debit (or both) are nonzero? Regarding injection, it doesn't matter whether it seems like injection is impossible in this particular case. The best practice is to _always_ use parameters, not just use them when you think there might be a risk. — JLRishe, Jan 05 '15 at 13:41
Are you sure there are records available for your params? Note that `[Entry Date]<=#2015/01/05#` will select records up to 0:00 of this date, not records later in that day. — Hans Keﬆing, Jan 05 '15 at 13:45

score 0 · Answer 1 · edited May 23 '17 at 12:12

You should be able to access an Access database with LinqToSql. It must be in the 2002 or newer. It should look something in lines of:

using (OleDbConnection connection = new OleDbConnection(DbConnString))
using (DataRepositoryDataContext ctx = new DataRepositoryDataContext(connection))
{
    var books = ctx.Daybook.Where(o=>
      o.EntryDate <= dateTimePicker13.Value &&
      o.EntryDate >= dateTimePicker12.Value &&
      o.Credit != 0 &&
      o.Debit != 0
    );
    //do something with books ex: print list of names to console
    Console.WriteLine(string.Join(",",books.Select(o=>o.Name).ToList()));
}

Correct way to note an operator Not Equal To is <>.

Most implementations support the ANSI standard operator <> as well as the != operator that is familiar to users of most popular programming languages.

MySQL 5.1: supports both != and <>
PostgreSQL 8.3: supports both != and <>
SQLite: supports both != and <>
Oracle 10g: supports both != and <>
Microsoft SQL Server 2000/2005/2008: supports both != and <>
IBM Informix Dynamic Server 10: supports both != and <>
InterBase/Firebird: supports both != and <>
Apache Derby 10.6: supports both != and <>
Sybase Adaptive Server Enterprise 11.0: supports both != and <>

These support only the ANSI standard operator:

IBM DB2 UDB 9.5: supports only <>
Microsoft Access 2010: supports only <>

ref : Should I use != or <> for not equal in TSQL?

To limit results to be inside a timespan you should use BETWEEN.

Note, that Linq never executes "SELECT * FROM", but rather replaces "*" with all the parameters. It is true that http://xkcd.com/327/ no longer would work, but it's alternatives do. Therefore punchline remains : "sanitize your database inputs" — Margus, Jan 05 '15 at 14:33
In short **<>** is correct and **!=** is considered as an alias, but not implemented for every widely available database. — Margus, Jan 05 '15 at 14:46

Access SQL - Alternate for !=

1 Answers1