troubleshooting Question

getting the value returned by stored procedure from TableAdapter

Avatar of coolmind26
coolmind26 asked on
.NET ProgrammingASP.NETMicrosoft SQL Server 2005
6 Comments1 Solution1595 ViewsLast Modified:
Dear All,

I am using Strongly typed DataSet to retrieve data from a database.I have created several TableAdapters using the Dataset Designer.
In one of the TableAdapters I have a method to insert data into that particular table which uses a stored procedure.
The stored procedure returns different values like -1 if record already exists, 0 if inserted , others if error.

The code of stored procedure is:

USE [C:\DOCUMENTS AND SETTINGS\SUBHRANIL\MY DOCUMENTS\VISUAL STUDIO 2005\WEBSITES\VIEWSERVER\VIEWSERVER\APP_DATA\SERVERLIST1.MDF]
GO
/****** Object:  StoredProcedure [dbo].[spServer_UsingExists]    Script Date: 10/30/2007 20:54:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spServer_UsingExists]
(
      @Server_Name nvarchar(50),
      @Server_Desc nvarchar(50),
      @Server_Foldername nvarchar(50),
      @Server_Folderpath nvarchar(50),
      @Server_Deptsp int,
      @Server_Deptname nvarchar(50)
)
AS
DECLARE @Result int
BEGIN TRANSACTION
IF EXISTS
(
      SELECT
            NULL
      FROM
            ServerDetails WITH (UPDLOCK)
      WHERE
           
            [SERVER FOLDER PATH] = @Server_Folderpath
)
      BEGIN
            SELECT @Result = -1
      END
ELSE
      BEGIN
            INSERT INTO
                 ServerDetails
            (
                  [SERVER NAME],
                  [SERVER DESC],
                  [SERVER FOLDER NAME],
                  [SERVER FOLDER PATH],
                  [DEPARTMENT SP],
                  [DEPARTMENT NAME]
            )
            VALUES
            (
                  @Server_Name,
                  @Server_Desc,
                  @Server_Foldername,
                  @Server_Folderpath,
                  @Server_Deptsp,
                  @Server_Deptname
            )
            SELECT @Result = @@ERROR
      END
IF @Result <> 0
      BEGIN
            ROLLBACK
      END
ELSE
      BEGIN
            COMMIT
      END
RETURN @Result

Now my question is" How to get the value returned by the stored procedure?
I am trying to get it the normal way but I am either getting the primary Id value of the record inserted (when choosing ExecuteScalar) or 1( when using ExecuteNonQuery).
But I am not getting it to return -1 i.e when a record already exists.I am trying to get this value in code behind file of web form.

Thanks in advance for your time.

coolmind26.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros