| Groups | Blog | Home |
sql server clients : Newbie, True and False
[quoted text, click to view]
"tclancey" <tull@idcodeware.co.uk> wrote in message news:%23z5at5vcHHA.4820@TK2MSFTNGP06.phx.gbl... > Hi All. > > First time working on an SQL database. I'm able to create all my tables > and fields without errors. I can work eith the back end pretty well, but > I can't work out True or False Fields/Columns. > > If I try: > Insert Into MyTable (value1, value2) values (true, false); > > I'm told that true (or false) cannot be used as they are column names or > keywords. Brace yourself. What does "I'm told" mean? A rhetorical question - this time I think I know what is happening. In the future, you should quote any errors exactly and completely and tell us exactly how you came to encounter the error. Usually this means providing some clue about the application you are using to execute queries. In addition, you should always specify which version of sql server you are using alsong with the SP level. [quoted text, click to view] > I've searched and searched for the answer, all the info I've found pretty > much contradicts itself! Make a note of the "sources" that told you that you could use TRUE or FALSE. Ignore them for all future information needs. [quoted text, click to view] > I'm using a Bit field, which surely should mean 1=True, 0=False. Does > with any other database. > > What am I doing wrong? !! Assuming that 1 = TRUE and that 0 = FALSE. They aren't. TSQL is not C/C++/VB, there is no boolean datatype, and you cannot evaluate a number (by itself) as a boolean expression. If you want to use a bit value of 1, then you must specify 1 (and not TRUE or "TRUE" or 'TRUE'). If you have a background in Access, then there are many other things that you will need to relearn when using sql server. I recommend you search (and post to, if needed) the .programming NG since it is more focused on sql server programming and experiences much higher volume.
Hi All.
First time working on an SQL database. I'm able to create all my tables and fields without errors. I can work eith the back end pretty well, but I can't work out True or False Fields/Columns. If I try: Insert Into MyTable (value1, value2) values (true, false); I'm told that true (or false) cannot be used as they are column names or keywords. I've searched and searched for the answer, all the info I've found pretty much contradicts itself! I'm using a Bit field, which surely should mean 1=True, 0=False. Does with any other database. What am I doing wrong? !! Cheers, Tull.
Thanks for your reply.
I understand my expression of the problem was somewhat limited, I thought there would be an easy answer that I've missed. If I explain the following it may make more sense, or at least give a better idea of what I want to achieve. I have an application that I need to work with Access (currently it does, very well) MySql and MsSql. I can get all True/False queries to work with MySql without any problems using exactly the same syntax as Access. MsSql is the problem. There simply isn't a Boolean return field available. There must be a simple answer somewhere as I'm sure other people have faced the same problem. The 'bit' field in MySql handles True and False correctly. Why can't MS? [quoted text, click to view] "Scott Morris" <bogus@bogus.com> wrote in message news:OaQm7LwcHHA.1508@TK2MSFTNGP06.phx.gbl... > "tclancey" <tull@idcodeware.co.uk> wrote in message > news:%23z5at5vcHHA.4820@TK2MSFTNGP06.phx.gbl... >> Hi All. >> >> First time working on an SQL database. I'm able to create all my tables >> and fields without errors. I can work eith the back end pretty well, but >> I can't work out True or False Fields/Columns. >> >> If I try: >> Insert Into MyTable (value1, value2) values (true, false); >> >> I'm told that true (or false) cannot be used as they are column names or >> keywords. > > Brace yourself. What does "I'm told" mean? A rhetorical question - this > time I think I know what is happening. In the future, you should quote > any errors exactly and completely and tell us exactly how you came to > encounter the error. Usually this means providing some clue about the > application you are using to execute queries. In addition, you should > always specify which version of sql server you are using alsong with the > SP level. > >> I've searched and searched for the answer, all the info I've found pretty >> much contradicts itself! > > Make a note of the "sources" that told you that you could use TRUE or > FALSE. Ignore them for all future information needs. > >> I'm using a Bit field, which surely should mean 1=True, 0=False. Does >> with any other database. >> >> What am I doing wrong? !! > > Assuming that 1 = TRUE and that 0 = FALSE. They aren't. TSQL is not > C/C++/VB, there is no boolean datatype, and you cannot evaluate a number > (by itself) as a boolean expression. If you want to use a bit value of 1, > then you must specify 1 (and not TRUE or "TRUE" or 'TRUE'). If you have a > background in Access, then there are many other things that you will need > to relearn when using sql server. I recommend you search (and post to, if > needed) the .programming NG since it is more focused on sql server > programming and experiences much higher volume. >
Sorry, tired when I wrote this last night. Let me explain a little better.
The application I'm working on at the moment needs to connect to Access, MsSql or MySql, this will be decided by the customer and set in the software by a hardware key. I have started conversion of the code that was originaly written for Access. All commands, Insert, Update etc work fine with MySql, but not with MsSql. The only real problem that I can see is the True and False fields. I really don't want to have to write completely seperate routines for each database back end. Also, pulling in a field and setting a CheckBox.Checked state to True or False is easy if the return value is True or False. I have thought about writing some kind of parser to check for certain keywords and replace them, but I cannot guarantee that the users are not going to use the words True, Flase or even 1 or 0. Any help would be greatfully recevied as I currently have no idea how to get around this! Thanks, Tull. [quoted text, click to view] "tclancey" <tull@idcodeware.co.uk> wrote in message news:OlVQoJxcHHA.3484@TK2MSFTNGP04.phx.gbl... > Thanks for your reply. > > I understand my expression of the problem was somewhat limited, I thought > there would be an easy answer that I've missed. > > If I explain the following it may make more sense, or at least give a > better idea of what I want to achieve. > > I have an application that I need to work with Access (currently it does, > very well) MySql and MsSql. > > I can get all True/False queries to work with MySql without any problems > using exactly the same syntax as Access. MsSql is the problem. There > simply isn't a Boolean return field available. > > There must be a simple answer somewhere as I'm sure other people have > faced the same problem. > > The 'bit' field in MySql handles True and False correctly. Why can't MS? > > > "Scott Morris" <bogus@bogus.com> wrote in message > news:OaQm7LwcHHA.1508@TK2MSFTNGP06.phx.gbl... >> "tclancey" <tull@idcodeware.co.uk> wrote in message >> news:%23z5at5vcHHA.4820@TK2MSFTNGP06.phx.gbl... >>> Hi All. >>> >>> First time working on an SQL database. I'm able to create all my tables >>> and fields without errors. I can work eith the back end pretty well, >>> but I can't work out True or False Fields/Columns. >>> >>> If I try: >>> Insert Into MyTable (value1, value2) values (true, false); >>> >>> I'm told that true (or false) cannot be used as they are column names or >>> keywords. >> >> Brace yourself. What does "I'm told" mean? A rhetorical question - this >> time I think I know what is happening. In the future, you should quote >> any errors exactly and completely and tell us exactly how you came to >> encounter the error. Usually this means providing some clue about the >> application you are using to execute queries. In addition, you should >> always specify which version of sql server you are using alsong with the >> SP level. >> >>> I've searched and searched for the answer, all the info I've found >>> pretty much contradicts itself! >> >> Make a note of the "sources" that told you that you could use TRUE or >> FALSE. Ignore them for all future information needs. >> >>> I'm using a Bit field, which surely should mean 1=True, 0=False. Does >>> with any other database. >>> >>> What am I doing wrong? !! >> >> Assuming that 1 = TRUE and that 0 = FALSE. They aren't. TSQL is not >> C/C++/VB, there is no boolean datatype, and you cannot evaluate a number >> (by itself) as a boolean expression. If you want to use a bit value of >> 1, then you must specify 1 (and not TRUE or "TRUE" or 'TRUE'). If you >> have a background in Access, then there are many other things that you >> will need to relearn when using sql server. I recommend you search (and >> post to, if needed) the .programming NG since it is more focused on sql >> server programming and experiences much higher volume. >> > >
[quoted text, click to view]
> I have started conversion of the code that was originaly written for > Access. All commands, Insert, Update etc work fine with MySql, but not > with MsSql. The only real problem that I can see is the True and False > fields. I really don't want to have to write completely seperate routines > for each database back end. Laudable - but highly improbable (IMO). [quoted text, click to view] > Also, pulling in a field and setting a CheckBox.Checked state to True or > False is easy if the return value is True or False. > > I have thought about writing some kind of parser to check for certain > keywords and replace them, but I cannot guarantee that the users are not > going to use the words True, Flase or even 1 or 0. > > Any help would be greatfully recevied as I currently have no idea how to > get around this! Neither do I as I don't use Access. I suggest you post to an Access-specific NG since that is the crux of the issue here. As I said earlier, there is no boolean datatype in sql server. Since moving an Access system to sql server appears to be a common activity, I expect that this topic has been covered in an Access NG.
Many thanks for your input. I'll give this a go.
Cheers, Tull. [quoted text, click to view] "Scott Morris" <bogus@bogus.com> wrote in message news:%23$t6iPSdHHA.4172@TK2MSFTNGP05.phx.gbl... >> I have started conversion of the code that was originaly written for >> Access. All commands, Insert, Update etc work fine with MySql, but not >> with MsSql. The only real problem that I can see is the True and False >> fields. I really don't want to have to write completely seperate >> routines for each database back end. > > Laudable - but highly improbable (IMO). > >> Also, pulling in a field and setting a CheckBox.Checked state to True or >> False is easy if the return value is True or False. >> >> I have thought about writing some kind of parser to check for certain >> keywords and replace them, but I cannot guarantee that the users are not >> going to use the words True, Flase or even 1 or 0. >> >> Any help would be greatfully recevied as I currently have no idea how to >> get around this! > > Neither do I as I don't use Access. I suggest you post to an > Access-specific NG since that is the crux of the issue here. As I said > earlier, there is no boolean datatype in sql server. Since moving an > Access system to sql server appears to be a common activity, I expect that > this topic has been covered in an Access NG. >
[quoted text, click to view]
"tclancey" <tull@idcodeware.co.uk> wrote in message news:eNWAwTUdHHA.4684@TK2MSFTNGP06.phx.gbl... > Many thanks for your input. I'll give this a go. > Cheers, > Tull. As Scott pointed out, MS SQL Server doesn't have a "Boolean" data type (there are some specific reasons why this is the case, but we won't dive into them here). MS SQL does have a "Bit" data type, which you can use to "simulate" Boolean data types to some degree, much like Access does. You can even write insert and update statements that accept 'True'/'False' and convert it to 1/0 for storage, and vice versa, by using SQL CASE expressions. As Scott said, you should post to the MS SQL Programming newsgroup for more detailed responses. In the meantime, here's a Q&D example of converting 'True' and 'False' to and from '1' and '0' in a Bit data type column: CREATE TABLE #test (code INT NOT NULL PRIMARY KEY, bitflag BIT) DECLARE @flag VARCHAR(10) SET @flag = 'True' INSERT INTO #test (code, bitflag) SELECT 1, 0 INSERT INTO #test (code, bitflag) SELECT 2, 0 INSERT INTO #test (code, bitflag) SELECT 3, CASE @flag WHEN 'True' THEN 1 WHEN 'False' THEN 0 ELSE NULL END UPDATE #test SET bitflag = CASE @flag WHEN 'True' THEN 1 WHEN 'False' THEN 0 ELSE NULL END WHERE code = 2 SELECT code, bitflag, CASE bitflag WHEN 1 THEN 'True' WHEN 0 THEN 'False' END AS boolean FROM #test DROP TABLE #test
[quoted text, click to view]
On Sat, 31 Mar 2007 15:39:27 +0100, tclancey wrote: >The application I'm working on at the moment needs to connect to Access, >MsSql or MySql, this will be decided by the customer and set in the software >by a hardware key. (snip) >Any help would be greatfully recevied as I currently have no idea how to get >around this! Hi Tull, Since Access, SQL Server, and MySQL all have different proprietary ways to handle boolean and/or bit values, the best way to get the same code working against all databases would be to avoid these proprietary types altogether. Just use (for isntance) a CHAR(1) datatype, with a CHECK constraint to only alllow the values 'T' and 'F' (for true and false). It might be a bit more work for the front end since you can't use the standard boolean types, but that's easily offset by the fact that you only need one code base for the three databases. -- Hugo Kornelis, SQL Server MVP
Don't see what you're looking for? Try a search.
|
March 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |