Trapping error when updating a record - Urgent -Help needed 1

[janrussell]

Maybe I'm just burned out on this, but I can't seem to catch an update error - at least there should be an error. I am trying to read in Excel data and update a SQL table with the values from Excel. That all works fine. But what I want to do is flag an entry in the Excel spreadsheet by writing to a specific cell if the update didn't occur for that element (i.e., the project code did not exist). The SQL is:
Call Db.Execute("Update PROJECT set user_cost1 = '" & ProjTotal1 & "', user_cost2 = '" & ProjTotal2 & "', user_text5 = '" & Monthtag & "', user_cost3 = '" & ProjTotal3 & "' where proj_short_name = '" & ProjCodeName & " '&quot
I have a ProjCodeName value in the Excel spreadsheet that doesn't exist in the SQL table. How do I catch the fact that it didn't find a particular value so the update failed? I've tried using ON ERROR and other variations to trap something....
Please can anyone tell me what to do? The following code snippet is my latest attempt

Call Db.Execute("Update PROJECT set user_cost1 = '" & ProjTotal1 & "', user_cost2 = '" & ProjTotal2 & "', user_text5 = '" & Monthtag & "', user_cost3 = '" & ProjTotal3 & "' where proj_short_name = '" & ProjCodeName & " '&quot

MyConErr = Err.Number
If (MyConErr <> 0) Then
MsgBox (&quot;Update error. Review spreadsheet for failed record.&quot
myxlsheet.Cells(iRow, 5).Value = &quot;**&quot;
Err.Clear
End If

Thanks!

 

[jebenson]

You can use the RecordsAffected parameter of the Execute method to determine if no records were updated. The Execute method has this structure:

connection.Execute CommandText, RecordsAffected, Options

Here are the definitions of the parameters

CommandText A String value that contains the SQL statement, table name, stored procedure, a URL, or provider-specific text to execute.

RecordsAffected Optional. A Long variable to which the provider returns the number of records that the operation affected.

Options Optional. A Long value that indicates how the provider should evaluate the CommandText argument. Can be one or more CommandTypeEnum or ExecuteOptionEnum values.

So, pass a Long var to the Execute command in the RecordsAffected position, like so:

Dim RecsAff as Long

RecsAff = 0

Call Db.Execute(&quot;Update PROJECT set user_cost1 = '&quot; & ProjTotal1 & &quot;', user_cost2 = '&quot; & ProjTotal2 & &quot;', user_text5 = '&quot; & Monthtag & &quot;', user_cost3 = '&quot; & ProjTotal3 & &quot;' where proj_short_name = '&quot; & ProjCodeName & &quot; '&quot;, RecsAff)

and change your error catching code to this:

If (RecsAff = 0) Then
MsgBox (&quot;Update error. Review spreadsheet for failed record.&quot
myxlsheet.Cells(iRow, 5).Value = &quot;**&quot;
Err.Clear
End If

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[777Ziggorat]

Hi, another way is to return a parameter and make use of a stored procedure.

EG:

CREATE PROCEDURE spUpd_TestScore

@pName varchar(60)
,@pAbbreviation varchar(10)
,@pError int OUTPUT

AS
BEGIN

--update
UPDATE Activity
SET Name = RTRIM (@pName)
,Abbreviation = RTRIM(@pAbbreviation)

--return any errors.
SET @pError = @@ERROR

END


Basically in this SP, the parameter @pError will return your error, so you can get it using something like

cmdExecute.Execute
lRetVal = cmdExecute(&quot;pError&quot

lRetVal will return your SQL error.

hope it helps.

 

[janrussell]

Thanks jebenson! That worked perfectly! Problem solved! Thanks to other replies as well!