Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Glen Villar
  Where is Glen Villar?
 Palayan City
 Philippines
 Glen Villar
 Tags
Subject: RE: SPT Question
Thread ID: 365551 Message ID: 365554 # Views: 66 # Ratings: 5
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Friday, December 28, 2012 1:24:19 AM         
   


> Hi,
>
> I have created a query in SQL Server 2005 which executes fine in SSMS.
> However, when I try to run the query using SPT in VFP, it executes and returns
> records but viewing the records with BROWSE or with grid
> throws an error: "The data source for this object must be a variable reference."
>
> VFP status bar shows SPT returns records (see image).
>
>
> I was thinking that it maybe it was due to the length of the query inside the TEXT/ENDTEXT
> that is causing the problem so I converted the query to Stored Procedure
> and just call it from VFP but I still get the same result.
>
> Here's the query in stored procedure.
>
> USE [MYDATA]
> GO
> 
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> 
> ALTER PROCEDURE [dbo].[gtv_sp_GetAnySale] 
>     @AnyDesc nvarchar(50),
> 	@curyear integer 
> 	
> AS 
> 
>     select t1.prodcode,
> 	t1.proddesc1, t1.xmonth, t1.[2012], t2.sellingprice,
> 	t1.[2012]*t2.sellingprice as [total], t1.[Paid] 
> 	 from
> 	(select c.prodcode, t3.proddesc1, MONTH(c.transdate) as xmonth, sum(coalesce(c.qtyout,0))
> 		-sum(coalesce(c.qtyin,0)) as [2012],
> 		sum(c.paymentamt)-sum(c.DiscountAmt) as [Paid]
> 		from
> 		mydata.dbo.ytransstatic as c
> 	left outer join mydata.dbo.productmaster as t3
> 	on c.prodcode = t3.prodcode
> 		where exists
> 			(select a.prodcode, a.proddesc1 from mydata.dbo.productmaster as a
> 			where upper(a.proddesc1) like @AnyDesc
> 			and a.deptcode = '004' 
> 			and a.groupcode1 = '0402'
> 			and a.prodcode = c.prodcode) 
> 		and year(c.transdate) = @curyear
> 		and UPPER(c.transtype) in ('SAL','INV')
> 		group by c.prodcode, month(c.transdate), proddesc1
> 	) as t1
> 	left outer join mydata.dbo.sellpricemaster as t2
> 	on t1.prodcode = t2.prodcode
> 	where t2.pricelevel = 1
> 

>
>
> Here's how I called it in VFP:
>
> lcDesc = 'POM GRAM%'
> 
> 	TEXT TO lcQuery NoShow
> 		Execute mydata.dbo.gtv_sp_GetAnySale ?m.lcDesc, 2012
> 	ENDTEXT
> 
> 	norder	= SQLExec(m.lnConnect,m.lcQuery,"mytable",laError)
> 
> 	nError = AERROR(laError)
> 	DISPLAY MEMORY LIKE laError
> 

>
> The command DISPLAY MEMORY LIKE laError returns the same message: "The data source for this object must be a variable reference."
>
>
> As I mentioned, I can run this query in SMSS with no problem and it does run fine if I
> call it via VBA in MS Excel.
>
> At the moment, I just used excel sheet to call the stored procedure but I'm wondering
> what I could be missing with this in VFP.
>
> Thanks. :-)
>
>
> (coffee)
> It's never too late to be who you might have been.
> -George Eliot. English Novelist (1819-1880)
>

> http://thereportersassistant.blogspot.com
> http://fruitypc.blogspot.com


Foxpro doesn't allow a column name to be or even begin with a digit, like [2012] or 2012.

-Anders

ENTIRE THREAD

SPT Question Posted by Glen Villar @ 12/28/2012 1:08:36 AM
RE: SPT Question Posted by Anders Altberg @ 12/28/2012 1:24:19 AM
RE: SPT Question Posted by Glen Villar @ 12/28/2012 1:31:45 AM