• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1182
  • Last Modified:

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

Hi,

I'm developing a client/server application which has MS SQL Backend and MS Access frontend. I created a form based on a stored procedure which is in turn based on a JOIN query.

When I insert new record I got this error:
"Key value for this row was changed or deleted at the data store. The local row is now deleted".

After that all the fields in the form change into "#DELETE"!!!
0
climbingrose
Asked:
climbingrose
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(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
0
 
kemp_aCommented:
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
0
 
SidFishesCommented:
#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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
climbingroseAuthor Commented:
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?
0
 
SidFishesCommented:
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?


0
 
climbingroseAuthor Commented:
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!
0
 
SidFishesCommented:
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...

0
 
climbingroseAuthor Commented:
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?
0
 
SidFishesCommented:
depending what the key for the form is maybe something like this
adding  back/forward buttons to the form

Public Sub BackBtn_Click()
      pubProjectID = Me!ProjectID - 1 'obviously this only works if your proect id's are sequential integers
      SetForm
End Sub
Private Sub FwdBtn_Click()
    If Not Me.NewRecord Then
        pubProjectID= Me!ProjectID + 1
        SetForm
    End If
End Sub

then your sql for setform would be

    strsql = "EXEC dbo.spProjectsDir " & proType "," & proStage & "," & proArchived & ",'" & proDetails & "'" & clientCompany & "," pubProjectID

and you'd add a where clause to your stp to get this project

another option (and simpler if you don't have thousands of records) might be to create a combo box which lists the Projects or companies...and use the key of that to create your key param for the stp...just call setForm in the afterupdate of the combo box with the key ID





0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now