Re: inconsistent report problems

There is no return value in this SP, so you shouldn't have any
adParamReturnValue parameter. This is likely the source of your problem.
When it's to be used from ADP, it's also better to put parenthesis around
your parameters in the SP. To make sure, write the values that you are
receiving in a table somewhere to make sure that they are OK.

It's also better that you don't use the sp_ prefix as it has a special
meaning for SQL-Server and can lead to subtil bugs hard to find (but I don't
think that's your problem here).

Another possibility would be that the field CompName that is giving you
trouble is used in a Cross Join, so maybe this is related to your problem.
Is this field associated with a control on the report or with the Sorting
and Grouping dialog window for this report? If it's only associated with
Sorting and Grouping, then you should add a hidden control on the report to
associate it. When you set dynamically a recordset to a report, Access is
more sensible to such missing things. You can also try to reduce the size of
@Counties VARCHAR (8000) to something smaller.

The join between tblNotices and #TempCounties also looks suspicious because
the tblNotices doesn't seem to be used anywhere else.

Solution: set the record source of the report to the stored procedure, set
the record source qualifier to dbo and use the InputParameters property to
pass your parameters and set the Error Trapping (in the General Options for
the VBA window) to Break on All Errors: if there is any error, Access will
probably show you an error message.

Finally, some bugs have been corrected in A2003; so maybe you should
upgrade.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Keith G Hicks" <krh@xxxxxxxxxxx> wrote in message
news:u3H2yylIIHA.3916@xxxxxxxxxxxxxxxxxxxxxxx
Sylvain,

I ALWAYS use SET NOCOUNT ON. That's not the problem.
ALL my SP's (& views, functions, triggers) always have dbo in the CREATE
statement.
Adding dbo to the Access calling code makes no difference in my results.
Like I said below, when I'm in debug mode, I tested the values of the
columns in rs and they were as expected. Of course that means that
rs.RecordCount > 0 and I also tested for EOF and it was false.

The stored procedure is very simple and like I said it works fine outside
of
this situation:

CREATE PROCEDURE dbo.sp_RptSales
@GetSaleDate VARCHAR(10),
@Counties VARCHAR(8000)

AS

SET NOCOUNT ON

DECLARE
@Sql VARCHAR(8000),
@spot SMALLINT,
@str VARCHAR(8000)

CREATE TABLE #TempCounties (County VARCHAR(30))

--create the temp table to store the counties from the county selection

WHILE @Counties <> ''
BEGIN
SET @spot = CHARINDEX(',', @Counties)
IF @spot>0
BEGIN
SET @str = LEFT(@Counties, @spot-1)
SET @Counties = RIGHT(@Counties, LEN(@Counties)-@spot)
END
ELSE
BEGIN
SET @str = @Counties
SET @Counties = ''
END

SET @sql = 'INSERT INTO #TempCounties
VALUES('''+CONVERT(VARCHAR(30),@str)+''')'
EXEC(@sql)
END

CREATE INDEX County ON #TempCounties (County)

SELECT
dbo.tblSales.SaleID,
dbo.tblCompanyInfo.CompName,
dbo.tblSales.County,
dbo.tblSales.BidPrice,
dbo.tblSales.PropAddress1,
dbo.tblSales.PropAddress2,
dbo.tblSales.PropAddress3,
dbo.tblSales.PropAddress4,
dbo.tblSales.PropCity,
dbo.tblSales.PropState,
dbo.tblSales.PropZip,
dbo.tblSales.OverBid,
dbo.tblSales.Bidder
FROM
dbo.tblSales
INNER JOIN #TempCounties A ON dbo.tblNotices.County = A.County
CROSS JOIN dbo.tblCompanyInfo
WHERE
dbo.tblSales.DateWentToSale = @GetSaleDate

-- end of sp_RptSales




"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OznU7glIIHA.3672@xxxxxxxxxxxxxxxxxxxxxxx
Add « SET NOCOUNT ON » at the beginning of your SP and 50% of chance that
you will be OK. It's also possible that you have a problem with the
schema
of the SP, so make sure that you have used dbo everywhere - including in
the
create statement - and add dbo. at the beginning of RptSales when you set
the CommandText. If nothing work, then come back with a description of
your
SP.

Finally, you should test for .EOF before setting the rs to the report.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Keith G Hicks" <krh@xxxxxxxxxxx> wrote in message
news:eoiSaXlIIHA.6108@xxxxxxxxxxxxxxxxxxxxxxx
This is for a report in A2002 (A2k format). It's an ADP. The backend is
SQL
2k.

I'm using virtually the same code for 4 reports so far. The only
difference
is the name of the stored procedure and the list of params. The code
below
runs without any errors in each of the 4 situation. Each of the stored
procedures in question when run in QA and using the same parameter
values
I'm using in my front end runs without errors and returns rows. There
are
no
problems with the stored procedures. They all test out fine.
Additionally,
as far as I can see, there are no differences in the properties of each
of
the 4 reports that would cause me any problems. I would expect all 4
reports
to preview the data I see in QA. However, that is not the case. 2 of
the
reports seem to work fine while the other 2 do not. The 2 that do not
work
correctly display either #Name? or #Error in the text boxes where I'd
expect
correct data to show up. I also get errors that fields "Access can't
find
the field 'CompName' referred to in your expression." But it's clearly
there
because when I run in debug and type ?rs!CompName in the immediate
window
I
get the value I'd expect. It's as if the ADP is screwed up and doesn't
know
what to do sometimes. It gets confused. I also get no data errors on
one
of
them when that should clearly not be happening.

Here's my code:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

With cmd
Set .ActiveConnection = cnnCurrProj
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.CommandText = "RptSales"

.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("@GetSaleDate", adVarChar,
adParamInput, 10, Format(dteGetDate, "mm/dd/yyyy"))
.Parameters.Append .CreateParameter("@Counties", adVarChar,
adParamInput, 8000, strGetCounties)

End With

rs.Open cmd, , adOpenStatic, adLockReadOnly

If cmd.Parameters.Item("@RETURN_VALUE").Value <> 0 Then 'error
handling
MsgBox "Error getting report data (" &
Trim(str(cmd.Parameters.Item("@RETURN_VALUE").Value)) & ")"
Cancel = True
Call Report_Close
GoTo Exit_Report_Open
End If

Set Me.Recordset = rs

DoCmd.Maximize

Exit_Report_Open:
Exit Sub

Err_Report_Open:
MsgBox Err.Description & ", # " & str(Err.Number)
Resume Exit_Report_Open

End Sub


Any ideas? Are ADP's so screwed up that this sort of unpredictable
behavior
occur?

Thanks,

Keith








.

Loading