not getting the proper return value from stored procedure

Question

Hi,

 

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 check whether a certain
data is present in a column of a database table.The table has 7 fields one
of which one is a Server Name field.I am accepting the Server Name from the user
and checking it against the database table.
If the server name is present I am displaying a message' NOT AVAILABLE' to the user
on the web form and vice - versa.

The TableAdapter method uses a stored procedure which accesses the table
and returns appropriate values.

The code for the stored procedure is as follows:
[code]
ALTER PROCEDURE spcheck_sname
 
 (
   @Server_Name nvarchar(50)
  
 )
 
AS
  SET NOCOUNT ON 
DECLARE @Result int
SELECT NULL FROM ServerDetails where [SERVER NAME] =  @Server_Name

IF @@ROWCOUNT < 1
   SELECT @Result = -1
  
else
   SELECT @Result = 1      
 
 RETURN @Result

[/code]
I am checking if there are any rows
 with the particular server name, if count is < 1 then it shud return -1 else 1.

I am calling this SP with tableAdapter method as given below:

private int chkServerName(string sname1)
        {
            try
            {
                Serverlist1TableAdapters.SERVERDETAILSTableAdapter nwAdapter = new Serverlist1TableAdapters.SERVERDETAILSTableAdapter();
                int snval = (int)nwAdapter.spcheck(sname1); // spcheck([server name])                

               return snval;  //  is TableAdapter method which uses the above SP

            }
            catch (Exception ex)
            {
                return ex.GetHashCode();
            }

        }

Thus the value returned by the SP is obtained by me in this function i.e chkServerName(string sname1) which is in the code behind page of the web form.

The problem is that I am always getting a value of -1 even if a certain
server name is present in the table.This is when ExecuteNonQuery mode is used for the TA method. If executeScalar mode is used I get some junk value.

Now, where is the problem? Is it with the stored procedure or the TableAdapter.The SP seems to be ok
when executed alone. I had a different SP earlier for the same purpose
but I still had the same problem.

So how do I solve this problem? Why is it happening in the first place?

Any help that solves this problem will be highly appreciated.

Thanks in advance for your time.