CODEWITHSUNDEEP
c#sqlweb-servicesstored-procedures
Et Andrea
Et Andrea

Reputation: 273

How execute a stored procedure from the web method?

I want to execute a stored procedure inside a Web Method. It is a select statement in the stored procedure. I tried with the following code. However, the result not successful. The result should return 1 but it is always returning -1. Does anyone have any idea? Please help.

Here is the web service .asmx code:

  public class retrieveLoan : System.Web.Services.WebService
    {    
        string constring = "Data Source=DIT-NB1260382;Initial Catalog=Experiment;Integrated Security=True";
        SqlConnection myConn;

        [WebMethod(Description="Simple Example")]
        public int GetResult(int id, int age)
        {

            Int32 numberofRecords = 0;
            System.Data.DataSet workDS = new System.Data.DataSet();
            SqlCommand objCommand = default(SqlCommand);

            //Create a command object
            objCommand = new SqlCommand();

            //prepare the command for retreiving
            objCommand.CommandType = System.Data.CommandType.StoredProcedure;
            objCommand.CommandText = "myprocedure2";

            //open the connection
            myConn = new SqlConnection(constring);
            myConn.Open();
            objCommand.Connection = myConn;

            try
            {
                numberofRecords = (Int32)objCommand.ExecuteScalar();
                return numberofRecords;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                myConn.Close();
            }
        }
    }

and my store procedure:

ALTER PROCEDURE [dbo].[myprocedure2]
(
@puserid int,
@page int 
) 
AS
BEGIN
select * from userdet where userid = @puserid and age = @page
END

Upvotes: 1

Views: 2657

Answers (3)

Anupam Sharma
Anupam Sharma

Reputation: 368

Your query is "select * from userdet". What ExecuteScalar() does is pick the first cell value. Now you are type casting this to int. if your first cell value is a string type or some other type. you will definitely receive a error. And that will return -1. Please define the column name in your select query or count like this "select count(*) from userdet". Check ur query.

Upvotes: 0

Andrei Olaru
Andrei Olaru

Reputation: 304

I believe that executing this stored procedure without parameters would return an exception.

First of all, for you to see the Exception, in the catch declaration, you should try and declare the Exception explicitly, like this:

        try
        {
            numberofRecords = (Int32)objCommand.ExecuteScalar();
            return numberofRecords;
        }
        catch (Exception ex)
        {
            //here you can enter into debug mode and see the exception "ex"
            return -1;
        }
        finally
        {
            myConn.Close();
        }

When you see the exception, you can quickly solve the problem.

Next, you should add the parameters as NULL into your stored procedure (so they can accept null values), OR, if you do not, you must add these parameter in C# code, and send them some values.

Also, i would like to point the fact that if you want to retrieve a COUNT, you should modify your stored procedure as following:

 ALTER PROCEDURE [dbo].[myprocedure2] ( @puserid int, @page int  )  
 AS 
  BEGIN 
    select COUNT(userid) from userdet where userid = @puserid and age = @page 
  END

Hope this solves your issues here.

Upvotes: 1

Kjartan
Kjartan

Reputation: 19081

You're not providing a lot of info, so hard to answer, but here's a way forward:

Change catch (Exception) into catch (Exception ex), then see what that exception contains, either by returning it, or by analyzing it in debug mode.

If you publish your project in debug mode, you can connect to it and debug it using Tools > Attach to Process and connect to the process called w3wp.exe (if there are more than one of them, look for the one with the correct version of .Net under the Type-column).

Upvotes: 0

Related Questions