[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simple Update code

Posted on 2005-05-05
5
Medium Priority
?
257 Views
Last Modified: 2006-11-18
I'm having trouble with some update code, heres my code:

------------------------------------------------
'Open the connection the backend DB
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = MM_RMbackend_STRING
objConn.Open

'Open the table where the records are to be inserted using the key passed from the form
DIM getAppt, Query1, FormKeyVar
FormKeyVar = Request.Form("AppointmentID")
Set getAppt = Server.CreateObject("ADODB.Recordset")
Query1 = "SELECT * FROM Appointments WHERE AppointmentID = " + Replace(FormKeyVar, "'", "''") + ""
getAppt.Open Query1, objConn, , adLockOptimistic, adCmdTable
Const AdOpenDynamic = 1
Const adCmdText = 1
Const adLockOptimistic = 3

'check if last app within 90 days of new app
dim lastAppDate, thisAppDate, statBook

If getOtherApps.EOF And getOtherApps.BOF Then
      lastAppDate = #01/01/01#
      thisAppDate = (getAppt.Fields.Item("AppointmentDate").Value)
else
      lastAppDate = #01/05/05#
      thisAppDate = (getAppt.Fields.Item("AppointmentDate").Value)
end if

If DateDiff("d", lastAppDate, thisAppDate) >= 90 Then
      
      'Update the selected record
      getAppt("AppointmentRep") = Request.Form("RepID")
      getAppt("AppointmentSubject") = Request.Form("AppointmentSubject")
      getAppt("Booked") = 1
      getAppt.Update

end if
--------------------

When i run the page i get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

Query-based update failed because the row to update could not be found.

/repeatprescribe/regenthouse/repbook2.asp, line 94 [this is the getAppt.Update line]

When i run the SQL query Query1  it returns the record. whats wring with my code.
0
Comment
Question by:alex_wareing
  • 3
5 Comments
 
LVL 3

Expert Comment

by:xiong8086
ID: 13933481
change this :
If getOtherApps.EOF And getOtherApps.BOF Then
     lastAppDate = #01/01/01#
     thisAppDate = (getAppt.Fields.Item("AppointmentDate").Value)
else
     lastAppDate = #01/05/05#
     thisAppDate = (getAppt.Fields.Item("AppointmentDate").Value)
end if

To
If getOtherApps.EOF OR getOtherApps.BOF Then
     lastAppDate = #01/01/01#
     thisAppDate = (getAppt.Fields.Item("AppointmentDate").Value)
else
     lastAppDate = #01/05/05#
     thisAppDate = (getAppt.Fields.Item("AppointmentDate").Value)
end if

why not use a update statement directly?

best regards,
Xiong
0
 
LVL 13

Accepted Solution

by:
nurbek earned 2000 total points
ID: 13933485
try this


'Open the table where the records are to be inserted using the key passed from the form
DIM getAppt, Query1, FormKeyVar
FormKeyVar = Request.Form("AppointmentID")
Set getAppt = Server.CreateObject("ADODB.Recordset")
Query1 = "SELECT * FROM Appointments WHERE AppointmentID = " + Replace(FormKeyVar, "'", "''") + ""
getAppt.Open Query1, objConn, 3, 3


'check if last app within 90 days of new app
dim lastAppDate, thisAppDate, statBook

If getOtherApps.EOF And getOtherApps.BOF Then
     lastAppDate = #01/01/01#
     'thisAppDate = (getAppt.Fields.Item("AppointmentDate").Value)
      thisAppDate = no records found, assing default value

else
     lastAppDate = #01/05/05#
     thisAppDate = (getAppt.Fields.Item("AppointmentDate").Value)
end if

If DateDiff("d", lastAppDate, thisAppDate) >= 90 Then
     
     'Update the selected record
     getAppt("AppointmentRep") = Request.Form("RepID")
     getAppt("AppointmentSubject") = Request.Form("AppointmentSubject")
     getAppt("Booked") = 1
     getAppt.Update

end if
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13933488
you are assinging

Const AdOpenDynamic = 1
Const adCmdText = 1
Const adLockOptimistic = 3

after opening the recordset
0
 

Author Comment

by:alex_wareing
ID: 13940579
Works great, is there any chance you could explain this problem a little more
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13943003
I usually use
getAppt.Open Query1, objConn, 3, 3  ------- for inserting, updating, deleting

getAppt.Open Query1, objConn, 1, 1 -------  i open it just for reading

3,3 or 1,1, defines the recordset object for read/write and  movenext/moveprev/requery etc..

hope this makes a sense :)

regards,
Nurbek


0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

825 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