SQL Server Performance Forum – Threads Archive

Displaying errors from an SP into an ASP page

Hi, I am trying to display an error from a Stored Procedure in an asp page, below is the procedure and how I am trying to call it from the asp page, any help on what I am doing wrong would be great. CREATE PROCEDURE dbo.cnms_employee_dir_new @NETWORK_ID VARCHAR(3),
@CORP_ID VARCHAR(10),
@EMP_ID VARCHAR(10),
@DIR_ID VARCHAR(10),
@DIR_SEC_EMP_ID VARCHAR(10) = NULL,
@DIR_GROUP_CODE VARCHAR(4)= NULL,
@DIR_USER_1 VARCHAR(60)= NULL,
@DIR_USER_2 VARCHAR(60)= NULL,
@DIR_USER_3 VARCHAR(60)= NULL,
@DIR_USER_4 VARCHAR(60)= NULL,
@DIR_EMP_NICKNAME VARCHAR(32)= NULL,
@DIR_SECURITY_LEVEL VARCHAR(1)= NULL,
@DIR_EX_PAPER_DIR VARCHAR(1)= NULL,
@DIR_EX_ONLINE_DIR VARCHAR(1)= NULL,
@NODE_ID VARCHAR(5)= NULL,
@DIR_CIL_ID VARCHAR(20)= NULL,
@LOCATION_ID VARCHAR(10)= NULL,
@DIR_DIAL_CODE VARCHAR(10)= NULL,
@DIR_DDI_NUMBER VARCHAR(12)= NULL,
@DIR_FLAGS VARCHAR(10)= NULL,
@START_DATE DATETIME,
@END_DATE DATETIME= NULL AS
BEGIN
INSERT Directory
(
NETWORK_ID,
CORP_ID,
EMP_ID,
DIR_ID,
DIR_SEC_EMP_ID,
DIR_GROUP_CODE,
DIR_USER_1,
DIR_USER_2,
DIR_USER_3,
DIR_USER_4,
DIR_EMP_NICKNAME,
DIR_SECURITY_LEVEL,
DIR_EX_PAPER_DIR,
DIR_EX_ONLINE_DIR,
NODE_ID,
DIR_CIL_ID,
LOCATION_ID,
DIR_DIAL_CODE,
DIR_DDI_NUMBER,
DIR_FLAGS,
START_DATE,
END_DATE
)
VALUES
(
@NETWORK_ID,
@CORP_ID,
@EMP_ID,
@DIR_ID,
@DIR_SEC_EMP_ID,
@DIR_GROUP_CODE,
@DIR_USER_1,
@DIR_USER_2,
@DIR_USER_3,
@DIR_USER_4,
@DIR_EMP_NICKNAME,
@DIR_SECURITY_LEVEL,
@DIR_EX_PAPER_DIR,
@DIR_EX_ONLINE_DIR,
@NODE_ID,
@DIR_CIL_ID,
@LOCATION_ID,
@DIR_DIAL_CODE,
@DIR_DDI_NUMBER,
@DIR_FLAGS,
@START_DATE,
@END_DATE
)
IF @@ERROR<>0
BEGIN
SELECT ‘WRONG SEC’
END END
GO =========================================== objRS = Conn.Execute (szSQL) if objRS(0) = "WRONG SEC" Then
SEC_ERROR = 1
end if ============================================= Thanks for any help

---

You are missing SET NOCOUNT ON at the beginning of the stored procedure, right after AS CREATE PROCEDURE….
AS
SET NOCOUNT ON

---

Hi Bambola, I have since added NOCOUNT but it still has the same effect, I am wondering if I have used to right approach? am I trapping the error in the correct place? it is as if it is being bypassed

---

What about using the ADO Error Object or the ADO Connection Errors collection? Frank
http://www.insidesql.de
http://www.familienzirkus.de

---

]]>