MS SQL backend, MS Access frontend, Inserting and updating problem

(Just a wild stab here)  Check and make sure that the record that youre grabbing has not been deleted, either by another block of code behind your form, or through another process.  

<< Key value for this row >>  How is the prmary keys for this table being created?  If your INSERT is attempting to add a record that violates primary key rules of the SQL Server table, this error message would make sense.

Hope this helps.
-Jim

Unfortunately, this happens with an Access front end, I'm not sure when but I think I know why, and it is usually when trying to update a JOINed view or sp.

When SQL updates the record, I'm sure its due to the way Triggers are implemented, it actually deletes and re-creates internal references to the original record.  Even though the record maintains PK and other info it is no longer visible through the recordset as the same record.

I'd suggest not using the JOINed sp. What is it your actually trying to do with the sp?

Cheers

#deleted is almost alwys due to the lack a a timestamp field in the server table. Jet uses approximate timings and mssql and mysql and oracle all use precise timings. You can get all sort of strange error messages and see #deleted in forms and tables

Add the timestamp and see what happens...Also make sure that every table has a primary key created.

Basically I have table called tblProject which has one to many relationship with other tables such as tblProjectCategories, tblProjectStages... I want to display the most of the information about projects. So I have to create JOINed query to include column from other tables. Also I want the the records to be updatable at the same time.

ALTER PROCEDURE dbo.spProjectsDir
(@proType int,
@proStage int,
@proArchived bit,
@proDetails varchar(100),
@clientCompany int)
AS
BEGIN
         DECLARE @detailsWildCard varchar(100)
         IF @proDetails = ' '  
            SELECT @detailsWildcard = '%'
         ELSE
            SELECT @detailsWildCard = '%' + @proDetails + '%'

SELECT        dbo.tblProjects.ProjectName, dbo.tblProjects.ProjectTypeID, dbo.tblProjects.ProjectStageID, dbo.tblProjects.ClientCompanyID,
                      dbo.tblProjects.ProjectLocation, dbo.tblProjects.ProjectDetails, dbo.tblProjectTypes.ProjectType, dbo.tblProjectStage.Stage,
                      dbo.tblCompanies.Company, dbo.tblProjects.ProjectID
FROM         dbo.tblCompanies RIGHT OUTER JOIN
                      dbo.tblProjects ON dbo.tblCompanies.CompanyID = dbo.tblProjects.ClientCompanyID LEFT OUTER JOIN
                      dbo.tblProjectStage ON dbo.tblProjects.ProjectStageID = dbo.tblProjectStage.StageID LEFT OUTER JOIN
                      dbo.tblProjectTypes ON dbo.tblProjects.ProjectTypeID = dbo.tblProjectTypes.ProjectTypeID
WHERE     (dbo.tblProjects.ProjectTypeID = @proType OR
                      @proType IS NULL) AND (dbo.tblProjects.ProjectStageID = @proStage OR
                      @proStage IS NULL) AND (dbo.tblProjects.ProjectArchived = @proArchived OR
                      @proArchived IS NULL) AND (dbo.tblProjects.ProjectDetails LIKE @detailsWildCard) AND (dbo.tblProjects.ClientCompanyID = @clientCompany OR
                      @clientCompany IS NULL)
END



If it's impossible to fix the query, what would be a good way to use to update the form?

ok...well...i'll suggest something which i use...unbound forms...by not binding your form to a table/view/stp you can have way more control over things...i really prefer this method as you avoid all sorts of locking and collision problems


I would create a sub called SetForm

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
    strsql = "EXEC dbo.spProjectsDir " & proType "," & proStage & "," & proArchived & ",'" & proDetails & "'" & clientCompany
 'note the use of double and single quotes for text
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "your connection string"
    cnn.Open
    Set rst = New ADODB.Recordset
        With rst
            .Open Source:=strsql, _
                  ActiveConnection:=cnn, _
                  CursorType:=adOpenKeyset, _
                  LockType:=adLockOptimistic
me!ProjectName = !ProjectName
me!ProjectTypeID = !ProjectTypeID
me!ProjectLocation = !ProjectLocation
'etc
            .Close
        End With
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

now we have a form filled with data but not bound to any underlying data

some then when a user updates a field, presses an update button or whatever you call the action stp

 Dim cnn As ADODB.Connection
        Set cnn = New ADODB.Connection
       
        strSQL = "EXEC dbo.stpUpdateProjects " & me!ProjectId & ", '" & me!ProjectName & "'"
        cnn.ConnectionString = pubstrConnect
        cnn.Open
        cnn.Execute strSQL
        cnn.Close

then to refresh the form we just call setForm() again

make any sense?

SidFishes can you please elaborate on the updating process? Should I create a stp to update all the fields on the form? If possible can you give me an example of how the stpUpdateProjects looks like?

Thanks for answering!

the stp would be something like

create procedure dbo.stpUpdateProjects
@ProjectID INT,
@ProjectName varchar(100),
@ClientCompanyID INT
/*etc*/
AS
Update tblProjects
set ProjectName = @projectname,
     ClientCompanyID = @ClientCompanyID
/*etc*/
 where ProjectID = @projectID
/*if you need to update the company table as well you could either add another update sql statement to this procedure, create a trigger or call another stp from with this procedure*/
go

this would be the code to use on the click of a button


Private sub btnUpdate_Click()
Dim cnn As ADODB.Connection
        Set cnn = New ADODB.Connection
       
        strSQL = "EXEC dbo.stpUpdateProjects " & me!ProjectId & ", '" & me!ProjectName & "'," & me!ClientCompanyID
        cnn.ConnectionString = pubstrConnect
        cnn.Open
        cnn.Execute strSQL
        cnn.Close

me!lblUpdated.visible = true 'a label which provides feedback to user that update occured...could use a msgbox too
CALL SetForm 'refresh the form data

end sub

of course you need to handle nulls, empty strings and add error checking and such but that's the bones of it...

It seems that this method works! However, the form just display the first record retrieved. I try to set Recordset of the form to rst then the old problem appears again! How do I fix it now?