inconsistent report problems

[Keith G Hicks]

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

 

[Keith G Hicks]

By the way, when I run the reports like this they run totally right without
any problems:

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

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim sSQL As String

sSQL = "EXEC dbo.RptSales '" & Format(dteGetDate, "mm/dd/yyyy") & "',
'" & strGetCounties & "'"

rs.Open sSQL, cnnCurrProj, adOpenStatic

Me.RecordSource = rs.Source

DoCmd.Maximize

rs.Close

Exit_Report_Open:
Set rs = Nothing
Exit Sub

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

End Sub

I want to run them the other way so I can handle backend errors in the front
end.

Keith

 

[Sylvain Lafontaine]

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]

Then store the values (or use the SQL-Server Profiler) that you are
receiving at the beginning of the SP in a table somewhere to make sure that
your parameters have been correctly sent/received. You can also use the
..Refresh method :

Dim p As ADODB.Parameter
cmd.Parameters.Refresh

For Each p In cmd.Parameters
Debug.Print "name = " & p.name
Debug.Print "Direction = " & p.Direction
Debug.Print "Type = " & p.Type
Debug.Print "Size = " & p.Size
Debug.Print "Precision = " & p.Precision
Debug.Print "NumericScale = " & p.NumericScale
Debug.Print
Next

For a description of the constant values for the Type field, see the file
adovbs.inc (there is also a version for javascript) that you will find on
your machine or at the following reference:

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=122

 

[Keith G Hicks]

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]

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.

 

[Keith G Hicks]

There is no return value in this SP, so you shouldn't have any
adParamReturnValue parameter. This is likely the source of your problem.

The return value is 0 because I don't set it explicity. I plan to add some
error handling to some reporting stored procedures so I will need that in
the front end. Anyway, I commented that front end code out to see if it
would help but it didn't.

When it's to be used from ADP, it's also better to put parenthesis around
your parameters in the SP.

Not sure what you are talking about here. Do you mean this (if so, I tried
that too and it didn't solve my problem)?

CREATE PROCEDURE dbo.sp_Sales
(
@SaleID INT
)
AS

SET NOCOUNT ON

To make sure, write the values that you are
receiving in a table somewhere to make sure that they are OK.

I ran a trace. The values and types are fine.

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).

I tried that for kicks. It did nothing.

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.

It's displayed on the report in several places. CompName is not the only
field that's a problem. All of them give me an errror. I used that as just
an example.

You can also try to reduce the size of
@Counties VARCHAR (8000) to something smaller.

Chanigng this not help either and it's never caused me problems in the
hundreds of stored procedures I've written before. I have another report
that only has an INT input variable and I have the same problems with that
one. Like I said earlier, there are a couple of similar reports that are
fine and a few that are not.

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

This was a mistake on my part in my earlier post. tblNotices is not in the
actual SP. It's tblSales. I was testing somethign and grabbed the wrong code
for my post below. This is not the problem.

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.

I am using Access 2003. It's only using A2k format.


Something interesting that I noticed. If create a new report with the same
code and I put a single text anywhere on the report for CompName (again,
this field is used just as an example) it displays correctly. However, if I
put it in the ReportHeader section I get the error that it cannot be found.
I've NEVER had this type of thing occur in over 10 years of mdb programming
and 5 years working with SQL and Delphi. If I take out all the code and set
the RecordSource property in the properties window I can get the value to
appear correctly in the ReportHeader.

This is a temporary solution until we get a browser based one completed.
It's getting to the point that this problem is not worth the trouble. I
think I'm going to revert back to the simpler way I was running reports (as
described in one of the posts related to this entry).

Keith

 

[Sylvain Lafontaine]

It's possible that you have some sort of corruption here: decompile the file
to see if it's change anything and if not, try to import the report into an
empty ADP project file.

Also, make sure that you are closing the report before viewing it instead of
just switching from the design mode.

And finally, personally, I never directly assign a recordset to a report. I
always use the RecordSource and the RecordSourceQualifier to bind the report
to a SP and I use the InputParameters to send my parameters and I never had
any problem with any of my reports. (Of course, when you do this, you must
make a frequent use of the Refresh (F5) function from the View menu for both
the tables and the Views/SP/UDF database windows.).

 

[Keith G Hicks]

I've decompiled till I'm blue in the face and have tried several times to
run all this from a brand new adp file. I learned a long long time ago that
mdb containers can get corrupt. I can't even remember how many times I've
had to copy objects to a new mdb file to fix a problem! That was the very
first thing I tried here. I also rebooted a few times and have taken several
breaks to clear my own head.

I'm currently changing the reports taht I've converted so far back to my
other strategy and it's fine. I'm not going to spend any more time on this
issue. It's not worht it. I think I'll never do any ADP work again. THere
are better ways to do things. Thanks for attempting to help.

Keith

 

[Sylvain Lafontaine]

I don't know what is that other strategy, so I cannot tell you anything
about it. Beside using the RecordSource with the Input Parameters, you
could also set the RecordSource to a full sql string in the Report_Open
event:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "select * from MyTable where Id=1"
End Sub


or:

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "EXEC dbo.MySP 1"
End Sub

This use the fact that contrary to forms, the record source for a report is
queried after the Report_Open event and not before it.