|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
VS2008 : Error Using IIF in Select QueryUsing Visual Studio 2008: I have a tableadapter in my dataset connecting to a table in Microsoft Access Database, which forces me to use the IIF statement rather than the CASE statement in my query which Access doesn't allow (unfortunately....) Using this statement for example : Select IIF(true, 'yes', 'no') as Test, etc.... from .... works fine, as long as I don't use comparative signs as =, >, < ... It seems like a boolean test is not allowed there .... :-( ? So, if I'm writing Select IIF(Column1 = 0 , 'yes', 'no') as Test, etc.... from .... or Select IIF(Column1 = 'some value' , 'yes', 'no') as Test, etc.... from .... or Select IIF(Column1 > 0 , 'yes', 'no') as Test, etc.... from .... I'm putting myself in a freaky 'error' show, getting a message "Error in list of function arguments: '=' not recognized. Unable to parse query text." How can I perform that boolean test in the Select statement with IIF, and not getting the error from the Jet engine ? -- Filip http://www.ww2airborne.net/ Official Site of the 101st Airborne - 463rd PFA skype: airborne463pfa-fiwi [It's nice to be important, but it's more important to be nice!] ---------------------------------------------------------------- " Screaming Eagles 101" <see_my_site@online.please> a écrit dans le message de news: 4a41f66a$***@news4us.nl...Show quoteHide quote > Hi, I don't think it is the Jet Engine that is throwing the error.> > Using Visual Studio 2008: > > I have a tableadapter in my dataset connecting to a table in Microsoft > Access Database, > which forces me to use the IIF statement rather than the CASE statement in > my query > which Access doesn't allow (unfortunately....) > > Using this statement for example : Select IIF(true, 'yes', 'no') as Test, > etc.... from .... > works fine, as long as I don't use comparative signs as =, >, < ... > It seems like a boolean test is not allowed there .... :-( ? > > So, if I'm writing > > Select IIF(Column1 = 0 , 'yes', 'no') as Test, etc.... from .... > or > Select IIF(Column1 = 'some value' , 'yes', 'no') as Test, etc.... from > .... > or > Select IIF(Column1 > 0 , 'yes', 'no') as Test, etc.... from .... > > I'm putting myself in a freaky 'error' show, getting a message > "Error in list of function arguments: '=' not recognized. Unable to parse > query text." > > How can I perform that boolean test in the Select statement with IIF, > and not getting the error from the Jet engine ? > If you try using such an IIF statement in the Query builder it throws the error immediately. I ththrows it again if you use "Test Query", BUT the results are there. VBhelper shows an example (with VB6 unfortunately - no idea if it will work in VS2008) where the IIF part of the query is put in a string variable before building the SQL - this might trick the parser into accepting it? Good luck! " Screaming Eagles 101" <see_my_site@online.please> a écrit dans le message de news: 4a41f66a$***@news4us.nl...Show quoteHide quote > Hi, Sorry, forgot to ask...> > Using Visual Studio 2008: > > I have a tableadapter in my dataset connecting to a table in Microsoft > Access Database, > which forces me to use the IIF statement rather than the CASE statement in > my query > which Access doesn't allow (unfortunately....) > > Using this statement for example : Select IIF(true, 'yes', 'no') as Test, > etc.... from .... > works fine, as long as I don't use comparative signs as =, >, < ... > It seems like a boolean test is not allowed there .... :-( ? > > So, if I'm writing > > Select IIF(Column1 = 0 , 'yes', 'no') as Test, etc.... from .... > or > Select IIF(Column1 = 'some value' , 'yes', 'no') as Test, etc.... from > .... > or > Select IIF(Column1 > 0 , 'yes', 'no') as Test, etc.... from .... > > I'm putting myself in a freaky 'error' show, getting a message > "Error in list of function arguments: '=' not recognized. Unable to parse > query text." > > How can I perform that boolean test in the Select statement with IIF, > and not getting the error from the Jet engine ? Are the boolean test and yes/no results fixed? Or do they change from case to case. If they are fixed you could use a stored procedure in Access to do that part of the work for you (even passing a parameter or two).
Show quote
Hide quote
"Clive Lumb" <clumb2@gratuit_en_anglais.fr.invalid> schreef in bericht Actualy the real thing is calculated as followsnews:O%23RJjYM9JHA.1340@TK2MSFTNGP05.phx.gbl... > > " Screaming Eagles 101" <see_my_site@online.please> a écrit dans le > message de news: 4a41f66a$***@news4us.nl... >> Hi, >> >> Using Visual Studio 2008: >> >> I have a tableadapter in my dataset connecting to a table in Microsoft >> Access Database, >> which forces me to use the IIF statement rather than the CASE statement >> in my query >> which Access doesn't allow (unfortunately....) >> >> Using this statement for example : Select IIF(true, 'yes', 'no') as Test, >> etc.... from .... >> works fine, as long as I don't use comparative signs as =, >, < ... >> It seems like a boolean test is not allowed there .... :-( ? >> >> So, if I'm writing >> >> Select IIF(Column1 = 0 , 'yes', 'no') as Test, etc.... from .... >> or >> Select IIF(Column1 = 'some value' , 'yes', 'no') as Test, etc.... from >> .... >> or >> Select IIF(Column1 > 0 , 'yes', 'no') as Test, etc.... from .... >> >> I'm putting myself in a freaky 'error' show, getting a message >> "Error in list of function arguments: '=' not recognized. Unable to parse >> query text." >> >> How can I perform that boolean test in the Select statement with IIF, >> and not getting the error from the Jet engine ? > > Sorry, forgot to ask... > Are the boolean test and yes/no results fixed? Or do they change from case > to case. > If they are fixed you could use a stored procedure in Access to do that > part of the work for you (even passing a parameter or two). SELECT colDevice, colAggregation, IIF(colAggregation = 'SUM', SUM(colValue), AVG(colValue)) AS Amount FROM ... GROUP BY... My example was to simplify things... -- Filip http://www.ww2airborne.net/ Official Site of the 101st Airborne - 463rd PFA skype: airborne463pfa-fiwi [It's nice to be important, but it's more important to be nice!] ---------------------------------------------------------------- Be aware that it is very confusing sending messages to newsgroups that keep
the same instructions, but than start talking about languages which are handled by another language, Your question has nowhere to do with whatever VB, AdoNet, or vb.data You are busy with the Query editor which is a part of the SQL server solution in Visual Studio, as that is working for Access as well, be then not surprised that it gives other results than you would expect. Cor
Show quote
Hide quote
"Cor Ligthert[MVP]" <Notmyfirstn***@planet.nl> schreef in bericht Hi Cor,news:eQfjHkM9JHA.1336@TK2MSFTNGP05.phx.gbl... > Be aware that it is very confusing sending messages to newsgroups that > keep the same instructions, but than start talking about languages which > are handled by another language, > > Your question has nowhere to do with whatever VB, AdoNet, or vb.data > > You are busy with the Query editor which is a part of the SQL server > solution in Visual Studio, as that is working for Access as well, be then > not surprised that it gives other results than you would expect. > > Cor > I'm not considering myself as a VB guru, but merely a medium VB program developer. Are you saying that in VB2008 in the dataset (xsd), the Query Builder for a table adpater, after launching the "Configure" command, or the TableAdapter Configuration Wizard which allows you to connect to all kinds of databasesas Access, Oracle, whatever ODBC, SQL, etc .... is made in the first place for SQL Server (only?) That looks kinda strange to me, but this would explain a lot of course... why it gives an error ... I thougth this problem was in the first place a VB/ADO thing, but in that perspective this changes quite a bit. I am more confused than ever... -- Filip http://www.ww2airborne.net/ Official Site of the 101st Airborne - 463rd PFA skype: airborne463pfa-fiwi [It's nice to be important, but it's more important to be nice!] ---------------------------------------------------------------- You use the query explorer in my idea is that a part of Visual Studio
Database edition and is in my perception more and more around SQL Server It does work for Access as well, but I would be surprised as it was able to do all versions from Access and in all SQL code about that, SQL transact code has endless dialects you know. Cor Show quoteHide quote " Screaming Eagles 101" <see_my_site@online.please> wrote in message news:4a422a79$1_5@news4us.nl... > > "Cor Ligthert[MVP]" <Notmyfirstn***@planet.nl> schreef in bericht > news:eQfjHkM9JHA.1336@TK2MSFTNGP05.phx.gbl... >> Be aware that it is very confusing sending messages to newsgroups that >> keep the same instructions, but than start talking about languages which >> are handled by another language, >> >> Your question has nowhere to do with whatever VB, AdoNet, or vb.data >> >> You are busy with the Query editor which is a part of the SQL server >> solution in Visual Studio, as that is working for Access as well, be then >> not surprised that it gives other results than you would expect. >> >> Cor >> > > Hi Cor, > > I'm not considering myself as a VB guru, but merely a medium VB program > developer. > > Are you saying that in VB2008 in the dataset (xsd), the Query Builder for > a table adpater, after launching > the "Configure" command, or the TableAdapter Configuration Wizard which > allows > you to connect to all kinds of databasesas Access, Oracle, whatever ODBC, > SQL, etc .... > is made in the first place for SQL Server (only?) > > That looks kinda strange to me, but this would explain a lot of course... > why it > gives an error ... > I thougth this problem was in the first place a VB/ADO thing, but in that > perspective > this changes quite a bit. > > I am more confused than ever... > > -- > Filip > http://www.ww2airborne.net/ > Official Site of the 101st Airborne - 463rd PFA > skype: airborne463pfa-fiwi > [It's nice to be important, but it's more important to be nice!] > ---------------------------------------------------------------- > > Filip
Access does not fully conform to the ansi-sql standards. Some functions in Access would not work on other sql-based DBMS The function is not supported on Sql. So I think this is the reason why it wouldn't also work on TableAdapter Query Configuration Wizard to avoid incompatibilities with other database systems. I guess what you should do is to just use the functions available in ansi-sql to mimic the behavior of the functions in Access. Other solutions might be to use a command object or call a Access query in a stored procedure kind of way hth Michel Posseth Show quoteHide quote " Screaming Eagles 101" <see_my_site@online.please> schreef in bericht news:4a41f66a$1_2@news4us.nl... > Hi, > > Using Visual Studio 2008: > > I have a tableadapter in my dataset connecting to a table in Microsoft > Access Database, > which forces me to use the IIF statement rather than the CASE statement in > my query > which Access doesn't allow (unfortunately....) > > Using this statement for example : Select IIF(true, 'yes', 'no') as Test, > etc.... from .... > works fine, as long as I don't use comparative signs as =, >, < ... > It seems like a boolean test is not allowed there .... :-( ? > > So, if I'm writing > > Select IIF(Column1 = 0 , 'yes', 'no') as Test, etc.... from .... > or > Select IIF(Column1 = 'some value' , 'yes', 'no') as Test, etc.... from > .... > or > Select IIF(Column1 > 0 , 'yes', 'no') as Test, etc.... from .... > > I'm putting myself in a freaky 'error' show, getting a message > "Error in list of function arguments: '=' not recognized. Unable to parse > query text." > > How can I perform that boolean test in the Select statement with IIF, > and not getting the error from the Jet engine ? > > -- > Filip > http://www.ww2airborne.net/ > Official Site of the 101st Airborne - 463rd PFA > skype: airborne463pfa-fiwi > [It's nice to be important, but it's more important to be nice!] > ---------------------------------------------------------------- > >  
Other interesting topics
What are HashTables good for?
.net 3.5 with vs 2005 Need some help from VB Programmers... distributing software Alternative to strongly typed datasets winmm book? writing to file from web service Uisng ADO.Net The Entity Framework "Random" numbers cause pattern? Need help in finding error in SOAP web service call |
|||||||||||||||||||||||
