Prev: Structure from OleAutoObject control
Next: Experience with simple bugtracking/change mgt tools
From: Kevin on 12 Jun 2010 09:26
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: E®!k /!$$E® on 13 Jun 2010 09:57
Keven,

You best use the the WHERE clause in the Sql statement.

Erik

"Kevin" <kdmurphy(a)eircom.net> schreef in bericht
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 14 Jun 2010 03:15
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: Kevin on 14 Jun 2010 07:54
Erik,

Using the WHERE clause would, if I understand it correctly, require the
entire data set to be read again everytime it is changed. Using the
filter I was hoping would enable the user to show/hide the records. The
two parts of the filter (checking the State and checking the Station)
work on their own. When combined using AND they fail and the Server
Filter access returns the previous filter, not what I'm trying to
achieve.

I did use it in the WHERE clause to see if it would work and it doesn't
there either.

Kevin

"ER!k \/!$$ER" <nospam(a)nospam.com> wrote in message
news:87kgqfF59iU1(a)mid.individual.net:

> Keven,
>
> You best use the the WHERE clause in the Sql statement.
>
> Erik
>
> "Kevin" <kdmurphy(a)eircom.net> schreef in bericht
> 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: Kevin on 14 Jun 2010 07:54
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

 |  Next  |  Last
Pages: 1 2 3 4 5 6
Prev: Structure from OleAutoObject control
Next: Experience with simple bugtracking/change mgt tools