Controls disappear in form using stored procedure as data source

[Bernd Schneider]

Hi Experts,

I would like to use the sp "sp_routeStat_CTY_Total_TestForm" as data
source for the form "Ufo_Test" with the input paramter "@LAND".

But after I did so, all controls of the form desappeared during run
time.

The sp delivers a ranking of flight destinations. @LAND should limit
the result to one single country (filed CC in the tables):

data source of form properties: sp_routeStat_CTY_Total_TestForm

import parameter of form properties (T_CC is a text field):
@LAND nvarchar(2) = forms!Ufo_Test!T_CC

The sp works fine if I enter the parameter manually.

I'm using MS SQL 2000, Access 2002 project (ADP). Both libraries ADO
and DAO are enabled, but I don't want to use a recordset. I need to
solve this without VBA.

This is the stored procedure:
CREATE PROCEDURE dbo.sp_routeStat_CTY_Total_TestForm(@LAND
nvarchar(2))
AS SELECT dbo.T_Route_St_CTY.CTY, dbo.T_Route_St_CTY.CITY,
dbo.T_Route_St_CTY.CC, COUNT(dbo.T_cum_TICKET.TICKETNR) AS Tickets,
SUM(dbo.T_cum_TICKET.Remittance_TOT) AS TOTAL,
SUM(dbo.T_cum_TICKET.Fare_TOT) AS FARE
FROM dbo.T_Route_St_CTY INNER JOIN
dbo.T_Route_St_APT ON dbo.T_Route_St_CTY.CTY =
dbo.T_Route_St_APT.CTY INNER JOIN
dbo.T_cum_TICKET INNER JOIN
dbo.T_Routing ON dbo.T_cum_TICKET.TICKETNR =
dbo.T_Routing.TICKETNR ON dbo.T_Route_St_APT.APT =
dbo.T_Routing.DEST_AIRP
WHERE (dbo.T_cum_TICKET.REMITTANCE_PERIOD_ENDING_DATE BETWEEN
CONVERT(DATETIME, '2005-01-01 00:00:00', 102) AND CONVERT(DATETIME,
'2005-06-30 00:00:00', 102))
GROUP BY dbo.T_Route_St_CTY.CTY, dbo.T_Route_St_CTY.CITY,
dbo.T_Route_St_CTY.CC
HAVING (dbo.T_Route_St_CTY.CC = @LAND)
ORDER BY COUNT(dbo.T_cum_TICKET.TICKETNR) DESC
GO

Any advice would be appreciated.
Thanks in advance
Bernd

 

[Sylvain Lafontaine]

First, don't use the prefix sp_ ; as this has a special meaning for
SQL-Server.

Try adding a SET NOCOUNT ON at the beginning of your SP. If this doesn't
work, take a look with the SQL-Profiler to see what happens.

 

[Sylvain Lafontaine]

As far as I can remember, controls will disappears if the returned recordset
is empty and the Allow Additions is No.

 

[Bernd Schneider]

I will rename my procedures in order to distinguish them from the
system procedures. Thanks for that.

I will try the SET NOCOUNT ON.

The Allow Additions property of the form could be a reason. I will
assure myself that it's set to yes. So thanks for this as well.

If I start the procedure within access, but without the form, the sp
retunrs a result set which is not empty, after entering the input
parameter @LAND manually at run time. So my first guess was that
Ididn't comply with conventions regarding the parameter (see line
below):

import parameter of form properties (T_CC is a text field):
@LAND nvarchar(2) = forms!Ufo_Test!T_CC

But as we both didn't find any mistakes in this regard, I will
concentrate on your issues tomorrow.

Thank you for your help.
Bernd

 

[Sylvain Lafontaine]

Personally, all the parameters that I'm used between a bound form and
SQL-Server are of type integer or datetime, so I don't know if the line «
@LAND nvarchar(2) = forms!Ufo_Test!T_CC » is OK or not. Maybe we must write
"string" instead of "nvarchar(2)", I don't know; this is why I made you the
suggestion of using SQL-Profiler to see if everything was right.

For the « SET NOCOUNT ON », I'm now thinking that this is not probably the
cause of your problem.

The last time that I've seen this happening, it was because I made some
changes to the SP without refreshing the queries window but there are other
possibilities, too. I've seen this problem often but usually, it's very
easy to see why it's happening.

Finally, make sure that you have set the Record Source Qualifier to dbo (and
don't write it directly in the Record Source property). Check also the
other properties like Filter and open the Input Parameters in Zoom mode
(Shift-F2) to make sure that there is nothing else hidding there.

Finally, inside the SP, you could write the parameter somewhere in a debug
table to make sure that it has been correctly transmitted.