October 30, 2006 at 11:12 am
I have an Access Project (2003) connected to SQL 2000. Part of the database entails an insert procedure being carried out after a file is audited. The problem I am having is that the sp will run fine some times and then for unkown reason it does not. What I am looking for is a way to capture the failure of the procedure (when it happens) and then alert the user about that failure.
here is the procedure:
INSERT INTO dbo.tblInvoice
(FolderID, NoOfPax, Branch, CompanyCode, Customer, IssueDate, ReferenceNo, CCNumber, ExpiryDate, FolderOwner, TermsRefListID, AppliedAmount,
Currency, InvoiceType, TourismFileNo, Memo, InvoiceClass)
SELECT FolderID, NoOfPax, Branch, CompanyCode, RTRIM(ISNULL(ReqByTitle, '')) + SPACE(1) + RTRIM(ISNULL(ReqByFirstName, '')) + SPACE(1)
+ RTRIM(ISNULL(ReqByLastName, '')) AS Customerreffullname, IssueDate, PONO AS PONO, CCNumber AS CCNumber, ExpiryDate AS ExpiryDate,
FolderOwner, IssueType, TourismValue, N'L.E' AS Currency, ServiceType AS InvoiceType, TourismFileNo, AirlineMemo, N'1' AS InvoiceClass
FROM dbo.tblTravelFolder
WHERE (FolderID = @FolderID)
this is the calling method:
Dim adocmd As ADODB.Command
Dim adoparam As Integer
Set adocmd = New ADODB.Command
With adocmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "udpTicketInvoice"
.Parameters.Append .CreateParameter("@TicketTxnID", adInteger, adParamInput, , TicketTxnID)
.CommandType = adCmdStoredProc
.Execute
End With
Set adocmd = Nothing
this code is attached to the after update event of IsAudited check box on the form
October 30, 2006 at 11:48 am
Are you telling us that the proc fails, or does not insert data without throwing an error??
You can check if the rowcount (@@rowcount) is greater than 0 in the proc. Then log the error in a table with the fileid for further analysis.
October 30, 2006 at 12:04 pm
Yes that is what is happening. I will try the @@rowcount and let you know
October 31, 2006 at 5:30 am
I think you have the wrong syntax for ISNULL. That function requires two parameters.
October 31, 2006 at 11:51 pm
Note the '' after the first argument in the ISNULL.
November 1, 2006 at 11:32 am
Your insert statement contains a parameter named @FolderID, but the VBA code executing the stored proc does not pass a parameter of that name, where does it come from ?
Have you tried error trapping in your VBA code, errors from SQL Server are returned from ado commmands executing stored procs
November 1, 2006 at 12:15 pm
The folderid is one of the inserted fields, I use it to open the invoice form after the update takes place. I do not get any errors from VB code. It is just that nothing happens. Can you help with an example of how to use the @@rowcount to return the message to the user?
November 1, 2006 at 12:23 pm
create procedure...
insert into ...
Select...
IF @@RowCount = 0
BEGIN
RAISERROR ('No rows were inserted (SpName)', 13, 1)
RETURN 1 --custom error message for that proc
END
November 2, 2006 at 12:08 am
thanks a lot
November 2, 2006 at 5:59 am
I forgot to mention this. It's safer to create a variable a save the value of @@rowcount.
That's because @@rowcount is evaluated after any t-sql statement... as demonstrated here :
Declare @RowCount as int
Select * from dbo.SysObjects
SET @Rowcount = @@ROWCOUNT
Select @RowCount, @@ROWCOUNT
--1205, 1
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply