[2005] Access YES/NO problem or Data Adapter problem?

[Desolator144]

I'm using an SQL statement to try and run an Update statement to an Access database. Unfortunately I have to mess with a YES/NO field aka boolean. Here's my current statement:

UPDATE [table1] SET field1 = 'blah',field2 = 'blahblah',TorF = no WHERE num = 289

now I've tried yes, no, 0, 1, True, and False and every one of them result in a "data mismatch type." Now I looked this up extensively online and some sources say 0 and 1 should work and others say True and False out of single quotes should work. So is there some sort of VB data adapter or other ADO problem going on here that's interfering with it? I thought the data adapter will send anything even if I sent it an SQL string of "hey go do some databasey stuff, alright?" and let the DB handle it but then again it it specifically an OLEDB adapter. So yeah I'm sure someone here has at some point had to deal with the yes/no field so what worked for you?

[techgnome]

It's 0.....
Post your code... it maybe the way you are setting it up or calling it... sometimes the answer isn't where it seems.

-tg

[Desolator144]

well I put in 0 and read the error a little more carefully and it turns out that the value for the WHERE test is actually text. I put it in single quotes and now I'm having another nightmare of a problem that's unrelated :P can't really tell if the 0 works but I think like 1/3 of the loop worked without failing so maybe it does

[jmcilhinney]

A Yes/No column contains boolean values. You should using True and False without single quotes. I'm assuming that when you used True and False your data type mismatch was coming from the WHERE clause.

INSERT table1 SET field1= 'blah', filed2 = 'blahblah', TorF = False WHERE num = '289'

SQL Server bit columns use 0 and 1.

[nick2k3]

A Yes/No column contains boolean values. You should using True and False without single quotes. I'm assuming that when you used True and False your data type mismatch was coming from the WHERE clause.

INSERT table1 SET field1= 'blah', filed2 = 'blahblah', TorF = False WHERE num = '289'

SQL Server bit columns use 0 and 1.

use paramaters in your update command. it will better parse a boolean value

[jmcilhinney]

use paramaters in your update command. it will better parse a boolean value

If you're inserting a variable into the SQL statement then you should use parameters regardless of the type. If you're using a literal value then you would use a boolean literal.