Using VO2ADO Filters
in [Visual Objects]
|
From: Ginny Caughey on 15 Jun 2010 08:02 It's not obvious to me what's different. I use LIKE rather than = for substring comparisons. Do you get the same result with Management Studio? Another tip - I only ever allow Date columns to be null. It makes life a lot simpler if non-Date columns have a default value instead of permitted them to be null. -- Ginny Caughey www.wasteworks.com
From: Geoff Schaller on 15 Jun 2010 08:27 Kevin, Your connection information is wrong regardless. You cannot combine a dynamic cursor with a client side recordset. I don't know what else might be wrong - try the SQL in the SSMS and see if it works. Geoff "Kevin" <kdmurphy(a)eircom.net> wrote in message news:HLoRn.168$K4.139(a)news.indigo.ie: > Geoff, > > The method I use to open the connection is below. This is based on one > of Robert's samples. If the connection was the problem surely it would > affect all filters and not just the second one? The first filter I apply > works. When I add to the filter string and set it again and the new one > does not work. If I checking the Filter access it just returns the > original filter. Even clearing the filter first does not work. > > The two parts of the filter (checking the State and checking the > Station) both work on their own, it is when they are combined they won't > work, and as stated above the Filter access just returns the previous > setting. > > Kevin > > > method ConnectionOpenConnection() class StandardShellWindow > // > // Open ADOConnection > // > local oConn as ADOConnection > local cbErr as codeblock > local uError as usual > local cConnStr as string > local cMsg as string > local cError as string > local oWB as WarningBox > local lOpen as logic > > // Set error handler > cbErr := ErrorBlock( {|oErr| _Break( oErr )}) > > // Connect using NT Login > cConnStr := [Provider=SQLOLEDB;Integrated Security=SSPI;] + ; > [Persist Security Info=False;Initial Catalog=Testing;Data > Source=LOCAL-PC\SQLEXPRESS] > > begin sequence > oConn := AdoConnection{} > oConn:ConnectionTimeout := 5 > oConn:ConnectionString := cConnStr > // setup prompt > oConn:Properties:[item, "Prompt"]:Value := AdPromptComplete > > // Set cursor location > oConn:CursorLocation := AdUseClient > > lOpen := true > > recover using uError > cMsg := "Failed to create connection." + CRLF > cMsg += uError:description > > // Show message > oWB := WarningBox{ self, "Open ADOConnection", cMsg } > oWB:Show() > > lOpen := false > > end sequence > > if lOpen > // Open connectin > begin sequence > oConn:Open(nil,nil,nil,nil) > > recover using uError > end > > if oConn:State <> adStateOpen > cError := "Failed to open connection." + CRLF > > // Error occurred > if oConn:Errors:Count > 0 > // ADO Error > cError := oConn:Errors:[item,1]:description > elseif IsInstanceOf(uError, #Error) > cError := uError:description > > else > cError := "Unknown error" > endif > > oWB := WarningBox{ self, "Open ADOConnection", cError } > oWB:Show() > > else > self:Menu:EnableItem( IDM_mnuFuel_Connection_Create_Table_ID ) > AdoSetConnection( oConn ) > > endif > > endif > > // Restore handler > ErrorBlock( cbErr ) > > return nil > > > "Geoff Schaller" <geoffx(a)softxwareobjectives.com.au> wrote in message > news:6GkRn.2699$Ls1.681(a)news-server.bigpond.net.au: > > > > Kevin, > > > > Why are you using adOpenDynamic? I cannot see what your connection > > string is so it is too hard to say whether this filter will be ignored > > or not. However, if you follow Robert's example on the matter, it should > > work. Your alternate is to do as Erik suggests. > > > > Geoff > > > > > > > > "Kevin" <kdmurphy(a)eircom.net> wrote in message > > news:%VLQn.161$K4.159(a)news.indigo.ie: > > > > > > I have an ADOServer created from an MS SqL SERVER table using the > > > following statement: > > > SELECT ClaimNo, PurchaseID, PurDate, Station, FuelType, Litres, Price, > > > Cost, Notes, State FROM Fuel > > > oSrv := AdoServer{ cSQL, oConn, adOpenDynamic, adLockOptimistic, > > > adCmdText } > > > > > > There are some items which I want hidden by default, but the user can > > > select to view them, so I apply the following filter before showing the > > > window: > > > oSrv:SetFilter( [State = null or State <> 'R'] ) > > > > > > and this works fine. > > > > > > I now want to add another condition to the filter so build it as > > > follows: > > > cFilter := [(State = null or State <> 'R') AND Station LIKE 'Topaz%'] > > > oSrv:SetFilter( cFilter ) > > > oSrv:Requery() > > > > > > When I apply this filter using oSrv:SetFilter it does not work. No > > > change is seen in the ADOServer. Checking oSrv:Filter before assigning > > > the new filter returns the correct string. After assigning the new > > > filter it returns 0. > > > > > > I have tried assigning the filter using the Filter assign but it never > > > seems to work, the access always returns 0 afterwards. > > > > > > Both the initial Filter and the additional one will work on their own > > > but not when combined as above. > > > > > > What am I doing wrong or missing? > > > > > > VO28 SP3 > > > Current version of VO2ADO > > > Connection cursor location: AdUseClient > > > > > > Thanks in advance. > > > > > > Kevin
From: Geoff Schaller on 15 Jun 2010 08:29 I cannot see any difference. What does SSMS give you? "Kevin" <kdmurphy(a)eircom.net> wrote in message news:EHJRn.176$K4.168(a)news.indigo.ie: > Ginny, > > I used SQL Master to test the statement and it was returning nothing. > The statement looked like this: > Select * from Fuel where (State = null or State <> 'R') and Station = > 'Topaz%' > > But when I changed it to: > Select * from Fuel where Station = 'Topaz%' and (State = null or State > <> 'R') > > It worked. Any idea why the second would work but the first wouldn't? > Surely they should have the same result. > > Thanks. > > Kevin > > "Ginny Caughey" <ginny.caughey.online(a)wasteworks.com> wrote in message > news:4c167664$0$6408$c3e8da3(a)news.astraweb.com: > > > > Sounds like your SQL was incorrect then if WHERE didn't find a row that > > should have been there. What did you get with SQL Management Studio? > > > > -- > > > > Ginny Caughey > > www.wasteworks.com
From: Kevin on 15 Jun 2010 08:36 I don't have SQL Management Studio at the moment but will shortly. When I created the table originally the State column would default to null but I created the table again giving it a default value and that did not work. I had tried LIKE but it did not seem to work. Just tried it again and it seems to work, must have got something wrong the first time around. Thanks for the pointers. I have a look at setting the other columns to default values and do it without having to recreate the table. Being a small table it's not a major headache but it show me how to do it for the future. Kevin "Ginny Caughey" <ginny.caughey.online(a)wasteworks.com> wrote in message news:4c176b33$0$8905$c3e8da3(a)news.astraweb.com: > It's not obvious to me what's different. I use LIKE rather than = for > substring comparisons. Do you get the same result with Management Studio? > Another tip - I only ever allow Date columns to be null. It makes life a lot > simpler if non-Date columns have a default value instead of permitted them > to be null. > > -- > > Ginny Caughey > www.wasteworks.com
From: Kevin on 15 Jun 2010 09:07
Geoff, At the moment I don't have SSMS but will try it when I do. What cursor should I be using? I do not fully understand them as yet. Kevin "Geoff Schaller" <geoffx(a)softxwareobjectives.com.au> wrote in message news:flKRn.2853$Ls1.2580(a)news-server.bigpond.net.au: > Kevin, > > Your connection information is wrong regardless. You cannot combine a > dynamic cursor with a client side recordset. I don't know what else > might be wrong - try the SQL in the SSMS and see if it works. > > Geoff > > > > "Kevin" <kdmurphy(a)eircom.net> wrote in message > news:HLoRn.168$K4.139(a)news.indigo.ie: > > > Geoff, > > > > The method I use to open the connection is below. This is based on one > > of Robert's samples. If the connection was the problem surely it would > > affect all filters and not just the second one? The first filter I apply > > works. When I add to the filter string and set it again and the new one > > does not work. If I checking the Filter access it just returns the > > original filter. Even clearing the filter first does not work. > > > > The two parts of the filter (checking the State and checking the > > Station) both work on their own, it is when they are combined they won't > > work, and as stated above the Filter access just returns the previous > > setting. > > > > Kevin > > > > > > method ConnectionOpenConnection() class StandardShellWindow > > // > > // Open ADOConnection > > // > > local oConn as ADOConnection > > local cbErr as codeblock > > local uError as usual > > local cConnStr as string > > local cMsg as string > > local cError as string > > local oWB as WarningBox > > local lOpen as logic > > > > // Set error handler > > cbErr := ErrorBlock( {|oErr| _Break( oErr )}) > > > > // Connect using NT Login > > cConnStr := [Provider=SQLOLEDB;Integrated Security=SSPI;] + ; > > [Persist Security Info=False;Initial Catalog=Testing;Data > > Source=LOCAL-PC\SQLEXPRESS] > > > > begin sequence > > oConn := AdoConnection{} > > oConn:ConnectionTimeout := 5 > > oConn:ConnectionString := cConnStr > > // setup prompt > > oConn:Properties:[item, "Prompt"]:Value := AdPromptComplete > > > > // Set cursor location > > oConn:CursorLocation := AdUseClient > > > > lOpen := true > > > > recover using uError > > cMsg := "Failed to create connection." + CRLF > > cMsg += uError:description > > > > // Show message > > oWB := WarningBox{ self, "Open ADOConnection", cMsg } > > oWB:Show() > > > > lOpen := false > > > > end sequence > > > > if lOpen > > // Open connectin > > begin sequence > > oConn:Open(nil,nil,nil,nil) > > > > recover using uError > > end > > > > if oConn:State <> adStateOpen > > cError := "Failed to open connection." + CRLF > > > > // Error occurred > > if oConn:Errors:Count > 0 > > // ADO Error > > cError := oConn:Errors:[item,1]:description > > elseif IsInstanceOf(uError, #Error) > > cError := uError:description > > > > else > > cError := "Unknown error" > > endif > > > > oWB := WarningBox{ self, "Open ADOConnection", cError } > > oWB:Show() > > > > else > > self:Menu:EnableItem( IDM_mnuFuel_Connection_Create_Table_ID ) > > AdoSetConnection( oConn ) > > > > endif > > > > endif > > > > // Restore handler > > ErrorBlock( cbErr ) > > > > return nil > > > > > > "Geoff Schaller" <geoffx(a)softxwareobjectives.com.au> wrote in message > > news:6GkRn.2699$Ls1.681(a)news-server.bigpond.net.au: > > > > > > > Kevin, > > > > > > Why are you using adOpenDynamic? I cannot see what your connection > > > string is so it is too hard to say whether this filter will be ignored > > > or not. However, if you follow Robert's example on the matter, it should > > > work. Your alternate is to do as Erik suggests. > > > > > > Geoff > > > > > > > > > > > > "Kevin" <kdmurphy(a)eircom.net> wrote in message > > > news:%VLQn.161$K4.159(a)news.indigo.ie: > > > > > > > > > I have an ADOServer created from an MS SqL SERVER table using the > > > > following statement: > > > > SELECT ClaimNo, PurchaseID, PurDate, Station, FuelType, Litres, Price, > > > > Cost, Notes, State FROM Fuel > > > > oSrv := AdoServer{ cSQL, oConn, adOpenDynamic, adLockOptimistic, > > > > adCmdText } > > > > > > > > There are some items which I want hidden by default, but the user can > > > > select to view them, so I apply the following filter before showing the > > > > window: > > > > oSrv:SetFilter( [State = null or State <> 'R'] ) > > > > > > > > and this works fine. > > > > > > > > I now want to add another condition to the filter so build it as > > > > follows: > > > > cFilter := [(State = null or State <> 'R') AND Station LIKE 'Topaz%'] > > > > oSrv:SetFilter( cFilter ) > > > > oSrv:Requery() > > > > > > > > When I apply this filter using oSrv:SetFilter it does not work. No > > > > change is seen in the ADOServer. Checking oSrv:Filter before assigning > > > > the new filter returns the correct string. After assigning the new > > > > filter it returns 0. > > > > > > > > I have tried assigning the filter using the Filter assign but it never > > > > seems to work, the access always returns 0 afterwards. > > > > > > > > Both the initial Filter and the additional one will work on their own > > > > but not when combined as above. > > > > > > > > What am I doing wrong or missing? > > > > > > > > VO28 SP3 > > > > Current version of VO2ADO > > > > Connection cursor location: AdUseClient > > > > > > > > Thanks in advance. > > > > > > > > Kevin |