Store value in Yes/No field in Access using C#

by

I can see a number of issues with your statement.

string sQuery = @"UPDATE ch1 set ch1 = " + true ;

I don’t have MS-Access handy, but it seems that it uses -1 for “Yes”. So you could try:

string sQuery = @"UPDATE ch1 set ch1 = -1" ;

or if the boolean value is in a variable, say, foo:

string sQuery = @"UPDATE ch1 set ch1 = " + (foo ? -1 : 0) ;

This is not advised however, as commenter @Gord Thompson points out. A literal 

string sQuery = @"UPDATE ch1 set ch1 = True" ;

or

string sQuery = @"UPDATE ch1 set ch1 = " + foo;

should work.

I notice that your error message reads

Syntax error in query expression ”True’;’.

with True between quotes, but also with a semicolon in there. It’s possible that the line that causes the error is different from what you gave us, perhaps it was

string sQuery = @"UPDATE ch1 set ch1 = " + true + ";";

or something like that? With a semicolon where SQL wasn’t expecting one?

There are some more issues. I notice that both the table name and the column name are ch1. While it is allowed that a table and a column have the same name, it doesn’t happen often. Are these really the right names?

Also, there is no WHERE clause here. In it’s current form, this statement will fill the entire column of the table with the same value, which may not be what you want.

Finally, the format

string sQuery = @"UPDATE ch1 set ch1 = " + something ;

is open to a well-known attack called SQL injection. You should read up on so-called “prepared statements” and use these. As a side benefit, this will also prevent some syntax errors in your SQL statements.

More Related Contents:

  1. SQL connection string for microsoft access 2010 .accdb
  2. Do we have transactions in MS-Access?
  3. Search SQL by date ASP.NET
  4. Sql: Incorrect syntax near ‘(‘ [closed]
  5. What are good ways to prevent SQL injection? [duplicate]
  6. NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
  7. Validation failed for one or more entities while saving changes to SQL Server Database using Entity Framework
  8. What represents a double in sql server?
  9. Distinct in Linq based on only one field of the table
  10. Query Microsoft Access MDB Database using LINQ and C#
  11. SQL Insert Query Using C#
  12. Map category parent id self referencing table structure to EF Core entity
  13. Entity Framework error: Cannot insert explicit value for identity column in table
  14. Get an IDataReader from a typed List
  15. Does using parameterized SqlCommand make my program immune to SQL injection?
  16. Executing query with parameters
  17. Entity Framework – stored procedure return value
  18. What’s the fastest way to bulk insert a lot of data in SQL Server (C# client)
  19. insert datetime value in sql database with c#
  20. Getting the Last Insert ID with SQLite.NET in C#
  21. Join and Include in Entity Framework
  22. Access SQL syntax error when using OleDbCommandBuilder
  23. Get affected rows on ExecuteNonQuery
  24. Add WHERE clauses to SQL dynamically / programmatically
  25. How to pass string array in SQL parameter to IN clause in SQL
  26. Check if a SQL table exists
  27. String field with single quotation mark is causing an error when inserting record in table
  28. Is it possible to convert VBA to C#?
  29. Linq To Sql compare Time only
  30. Update-Database command is not working in ASP.Net Core / Entity Framework Core because object in database already exists

Leave a Comment