UpdateRow error in SQL

HCL Domino Domino Forum
1

Hi,

I am writing an agent to update a SQL db from a lotus notes database. If the record is already in SQL, then I need to update it else create a new record.

I am getting error when I am trying to update an existing record. Creating a new record works fine. Below is my code. I searched the forum and followed some of the suggestions but nothing resolved the issue.

Set doc = view.GetFirstDocument

	While Not doc Is Nothing

		'Get employee id and fetch corresponding record

		query.SQL="select * from AD_Users where PID = "+Cstr(doc.PersonID(0))+""

		Call result.Execute

		

		If  result.IsResultSetAvailable Then 'Update Row

			result.LastRow

			Call result.SetValue("PID", doc.PersonID(0))

			Call result.SetValue("FullName", doc.AltFullName(0))

			Call result.SetValue("LastUpdated", Now())

			status = result.UpdateRow '-----HERE is the error

		Else 'Add new Row

			result.AddRow

			Call result.SetValue("PID", doc.PersonID(0))

			Call result.SetValue("FullName", doc.AltFullName(0))

			Call result.SetValue("LastUpdated", Now())

			status = result.UpdateRow '-----WORKS fine

		End If

		result.Close(DB_CLOSE)

		Set doc = view.GetNextDocument(doc)

	Wend

Error: LS: DO- ODBC could not complete the requested operation

Please help…thanks in advance

2

Subject: Try error handler with Print result.GetExtendedErrorMessage

The LotusScript ODBC classes have a method “GetExtendedErrorMessage” which
should give you more details of the error.
Use an error handler (On Error …) to catch the error then print
result.GetExtendedErrorMessage.
The detailed error message should then appear either in your Notes status bar
(if the code runs in the client) or in the server log (if the code runs on the
server).

Here’s a version of the code that includes such an error handler.
Note that if you already had an “On Error GoTo …” statement before this
section of code to create a custom error handler, you should replace the “On
Error GoTo 0” line below with a copy of your previous error handler. “On Error
GoTo 0” actually removes all custom error handlers for whatever Sub or Function
the code runs in.

Set doc = view.GetFirstDocument
While Not doc Is Nothing
'Get employee id and fetch corresponding record
query.SQL=“select * from AD_Users where PID = “+Cstr(doc.PersonID(0))+””
Call result.Execute

If result.IsResultSetAvailable Then 'Update Row
	result.LastRow
	Call result.SetValue("PID", doc.PersonID(0))
	Call result.SetValue("FullName", doc.AltFullName(0))
	Call result.SetValue("LastUpdated", Now())
	On Error Resume Next
	status = result.UpdateRow '-----HERE is the error
	If Err<>0 Then
		Print result.GetErrorMessage+" -

"+result.GetExtendedErrorMessage
Err=0 'Clear the error.
End If
On Error GoTo 0 'Reset the error handler to default behaviour.
Else 'Add new Row
result.AddRow
Call result.SetValue(“PID”, doc.PersonID(0))
Call result.SetValue(“FullName”, doc.AltFullName(0))
Call result.SetValue(“LastUpdated”, Now())
status = result.UpdateRow '-----WORKS fine
End If
result.Close(DB_CLOSE)
Set doc = view.GetNextDocument(doc)
Wend

3

Subject: UpdateRow error in SQL

I would drop the following line:Call result.SetValue(“PID”, doc.PersonID(0))

You already know that the PID of the SQL record that you’ve retrieved matches the PersonID of the current document - otherwise, you wouldn’t be executing this section of code. Also, updating the key value in SQL databases (assuming that PID is a key value) is generally not a good idea, even if you are updating it to the value that it currently has!

4

Subject: RE: UpdateRow error in SQL

Thanks Mark for your response. In my SQL table, PID is not a key value. I have another key value column. But i still followed your suggestion and am getting the same error. Any other issue you see?

5

Subject: RE: UpdateRow error in SQL

Additional info. The error number is Error 720