Solved

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

Posted on 2004-09-29
9
1,142 Views
Last Modified: 2012-08-13
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
Comment
Question by:climbingrose
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12186882
(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
 
LVL 5

Expert Comment

by:kemp_a
ID: 12187751
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 12190760
#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
Industry Leaders: 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!

 

Author Comment

by:climbingrose
ID: 12190781
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 12191150
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
 

Author Comment

by:climbingrose
ID: 12191416
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
 
LVL 36

Expert Comment

by:SidFishes
ID: 12191634
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
 

Author Comment

by:climbingrose
ID: 12197498
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
 
LVL 36

Accepted Solution

by:
SidFishes earned 200 total points
ID: 12201166
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question