Author: Dan Neuman
Posted: 2002-11-07 at 21:14:33
Mike,
Thanks for the reply.
tried that. same error message.
-----Original Message-----
From: profox@leafe.com [mailto:profox@leafe.com]On Behalf Of Mike McCann
Sent: Thursday, November 07, 2002 8:11 PM
To: Multiple recipients of ProFox
Subject: Re: Remote View Inlist Parameterized where clause
Dan Neuman <dneuman@pclv.com> wrote:
[("SP","SC","CA")]
try
[('SP','SC','CA')]
SQL Server does not like double quotes
--
Mike
|| Paul,
|| Thanks for your reply, I have already tried it, and I get the
|| following Error message when I try to open the view.
||
|| "Connectivity error: [Microsoft][ODBC SQL Server Driver]
|| [SQL Server]Line 1:Incorrect syntax near '@P1'."
||
|| Does this make sense to anyone?
||
||
|| Here is the actual Remote View generation script.
|| *********** View Definition
|| CREATE SQL VIEW "PCVPRJTemp" ;
|| REMOTE CONNECTION "pcadata" ;
|| AS select pcaprj.* ;
|| from dbo.pcaprj ;
|| where pcaprj.cprjid in ?vp_clist
||
|| =DBSetProp('PCVPRJTemp', 'View', 'UpdateType', 1)
|| =DBSetProp('PCVPRJTemp', 'View', 'WhereType', 4)
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchMemo', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'SendUpdates', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'UseMemoSize', 255)
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchSize', -1)
|| =DBSetProp('PCVPRJTemp', 'View', 'MaxRecords', -1)
|| =DBSetProp('PCVPRJTemp', 'View', 'Tables', 'dbo.pcaprj')
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchSize', -1)
|| =DBSetProp('PCVPRJTemp', 'View', 'Comment', "")
|| =DBSetProp('PCVPRJTemp', 'View', 'BatchUpdateCount', 1)
|| =DBSetProp('PCVPRJTemp', 'View', 'ShareConnection', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'Prepared', .F.)
|| =dbsetprop('pcvprjtemp', 'view', 'parameterlist', "vp_clist,'c'")
|| =DBSetProp('PCVPRJTemp', 'View', 'CompareMemo', .T.)
|| =DBSetProp('PCVPRJTemp', 'View', 'FetchAsNeeded', .F.)
|| **************** End of View Definition
||
|| Then I try to use the view with:
|| Public vp_cList
|| vp_cList = [("SP","SC","CA")] && Which I know are in the data.
|| USE PCAView!PcvPrjTemp && This is where I get the connectivity
|| error. ***************************
||
|| Thanks for your help.
|| Dan Neuman
||
|| -----Original Message-----
|| From: profox@leafe.com [mailto:profox@leafe.com]On Behalf Of Paul
|| McNett Sent: Thursday, November 07, 2002 4:57 PM
|| To: Multiple recipients of ProFox
|| Subject: Re: Remote View Inlist Parameterized where clause
||
||
|| On Thursday 07 November 2002 04:51 pm, Dan Neuman wrote:
||
||| I have a question for the SQL Server remote view gurus.
||
|| Only if someone tells me why my combobox isn't working <g>...
||
||
||| I need to create a remote view to a SQL database, and in the where
||| clause I would like to provide a list of values. And if the field
||| matches one of those values, then return the record.
|||
|||
||| Is there a way to do that?
|||
|||
||| Something like:
||| Local vp_List
||| vp_List = ["A","B","C"]
||| Use RemoteViewName
|||
|||
|||
||| Remote View SQL Statement:
||| Select Table.* from dbo.Tablename where inlist(Tablename.Field,
||| ?vp_List)
||
|| try this (works with spt, not sure about remote views):
||
|| private vp_List
|| vp_list = [("A","B","C")]
||
|| * remote view:
|| select table.* from dbo.tablename where tablename.field in ?vp_list
||
|| --
|| Paul McNett - p@ulmcnett.com
|| Hollister, California, USA
||