Solved

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

Posted on 2004-09-29
9
1,113 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
9 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
(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
Comment Utility
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
Comment Utility
#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
 

Author Comment

by:climbingrose
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 36

Expert Comment

by:SidFishes
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now