cannot change data in stored procedure

[bradmaunsell]

I'm new with SQL 2005 Express and I'm stuck - again
Windows XP
Access 2003
Access Project (on my laptop)
I am re-writing a fairly large application.
I have a problem with stored procedures. More specifically, I cannot change data when I run a stored procedure (used as RecordSource). The form has a header and multiple record detail section (continuous forms). For example, a policyholder header and a detail section with all of the policyholder's endorsements.

My form displays all data corretly but I cannot change any data.

After thrashing around for several hours, I have discovered the problem has nothing to do with my front end. If I run the stored procedure in the SQL Manager, I still cannot edit data. I can edit table data and edit view data but not any data returned from running a stored procedure. At first I looked for a non-updatable select statment but that is not the problem either.

Here is a very simple example. If I run this in the SQL Manager, the data returned is correct but I cannot change anything. It can't get much simpler than this sp.

----------------------------------------------------------

USE [MGA]
GO
/****** Object: StoredProcedure [dbo].[sp_cbo_Staff] Script Date: 12/04/2007 16:32:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Brad Maunsell
-- Create date: 11/06/2007
-- Description: Used for drop downs on underwriters form
-- =============================================

ALTER PROCEDURE [dbo].[sp_cbo_Staff]
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (100) PERCENT tblStaff.StaffID, tblStaff.Initials,
tblStaff.FirstName, tblStaff.LastName,tblStaff.IsUnderwriter
FROM tblStaff
ORDER BY LastName
END

----------------------------------------------------------

Any help will be greatly appreciated.

Thanks,
Brad
Burlingon, Vermont

 

[cmmrfrds]

There needs to be something unique in the select list from the table you are updating, such as, the primary key. Another way to establish uniqueness on the table is to add a timestamp field which sql server maintains to be unique. This will be returned as a hidden field on the recordset and the recordset will be updateable. Of course, it could still be not updateable if the recordset properties are read only which is the default. Also, aggregate recordsets are not updateable - on this I am not sure about the 100 percent.

 

[bradmaunsell]

Thanks for the response.

This stored proceedure does have a unique filed - StaffID. I checked the table and that field is definded as the primary key.

I tried adding dbo. to each but it makes no difference.

 

[cmmrfrds]

When you say the SP is the recordsource on the Form. Is this on the data tab of the Main Form?

 

[bradmaunsell]

Actually, all.

I have a main form that has a tab object. Each tab has a form on it. Some of the tab forms are linked to the main form and some are unbound. Some are single form and others are continuous forms. Some allow data editing and others are unbound data display only.

Most are not populated until the user clicks the tab. Obviously the reason for not populating is my attempt to improve performance. So, if that tab is not always used, it does not have to be populated. For example, one tab form contains a transaction history. This is not always used but is important to offer the user as it is a real time saver when that info is required.

I have also eliminated some tab forms and replaced them with a command button to open a popup form. For example, I have a policy ancestry from that is seldom used but again, quite a time saver when the underwriter needs that info.

 

[BSman]

Have you tried using a view or even the table as your data source, rather than a stored procedure?

Bob