Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-09-29
9
Medium Priority
?
1,174 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 66

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 600 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

618 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