[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 791
  • Last Modified:

Retrieving a Return value from a Sproc with C#

the Sproc:

CREATE PROC dbo.[spx_SvcReq_Login]
   @email  varchar(100)
AS
  SET NOCOUNT ON
  If Exists (
      Select      Top 1 *
      From        [staff_main] with (nolock)
      Where       ([email_link] = @email)
   )
      Return 1
   Else
      Return 0
                 
  SET NOCOUNT OFF
GO

the C#:

public static void userLoginValidation(string user_email)
    {
        SqlConnection SQLConn = new SqlConnection(OrrwebConnectionString());

        SqlCommand sqlCmd = new SqlCommand("spx_SvcReq_Login", SQLConn);
        sqlCmd.CommandType = CommandType.StoredProcedure;

        sqlCmd.Parameters.Add("@email", SqlDbType.VarChar);
        sqlCmd.Parameters["@email"].Value = user_email;
        sqlCmd.Parameters.Add("@retVal", SqlDbType.Int, 0);


        try
        {
            SQLConn.Open();
            sqlCmd.ExecuteNonQuery();
        }
        catch (Exception objError)
        {

        }
        finally
        {
            SQLConn.Close();
        }
        //The next line is pseudo-code...
        return sqlReturnParam;
    }


The problem here is that I'm just learning C#, so there's a lotta things I really don't know. One of those things is syntax, which I'm gradually learning. In ASP classic, I would have no problem obtaining the return value from the sproc, but I'm sorta lost at sea with this one. How do I go about getting the return value from the sproc, so that this function outputs that value for use in my main page code-behind?
0
Paul Kahl
Asked:
Paul Kahl
  • 5
  • 4
3 Solutions
 
Bob LearnedCommented:
Try this:

   sqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

Bob
0
 
Paul KahlAuthor Commented:
Ok... but how to I make that the output of the function? As it stands now, the function is as follows:

public static void userLoginValidation(string user_email)
    {
        /* Validate User */
        SqlConnection SQLConn = new SqlConnection(OrrwebConnectionString());

        SqlCommand sqlCmd = new SqlCommand("spx_SvcReq_Login", SQLConn);
        sqlCmd.CommandType = CommandType.StoredProcedure;

        sqlCmd.Parameters.Add("@email", SqlDbType.VarChar);
        sqlCmd.Parameters["@email"].Value = user_email;

        sqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

        try
        {
            SQLConn.Open();
            sqlCmd.ExecuteNonQuery();
        }
        finally
        {
            SQLConn.Close();
        }
    }
0
 
Bob LearnedCommented:
sqlCmd.ExecuteNonQuery()

int returnValue = Convert.ToInt32(sqlCmd.Parameters("@ReturnValue").Value);

return returnValue;

I am not sure if you need the Convert.ToInt32, but I believe that the Value property returns an 'object' type, so you would need it in that case.  No IDE to test right now.

Bob
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Paul KahlAuthor Commented:
public static void userLoginValidation(string user_email)
    {
        /* Validate User */
        SqlConnection SQLConn = new SqlConnection(OrrwebConnectionString());
        SqlCommand sqlCmd = new SqlCommand("spx_SvcReq_Login", SQLConn);
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.Parameters.Add("@email", SqlDbType.VarChar);
        sqlCmd.Parameters["@email"].Value = user_email;

        sqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

        try
        {
            SQLConn.Open();
            sqlCmd.ExecuteNonQuery();
            int returnValue = Convert.ToInt32(sqlCmd.Parameters("@ReturnValue").Value);

            return returnValue;
        }
        finally { SQLConn.Close(); }
    }

running that code gives me these beautiful errors:
'System.Data.SqlClient.SqlCommand.Parameters' is a 'property' but is used like a 'method'.
Since 'Orrweb.userLoginValidation(string)' returns void, a return keyword must not be followed by an object expression.

So I change the int returnValue line to read as:
int returnValue = Convert.ToInt32(sqlCmd.Parameters[1].Value);

and instead, I get just this error:
Since 'Orrweb.userLoginValidation(string)' returns void, a return keyword must not be followed by an object expression.
0
 
Paul KahlAuthor Commented:
Sorry this is such a pain. I have searched Google output for days, and hardly anyone seems to be doing their database work from an external class like this (which I'm doing to attempt to achieve true separation of DB from Code from UI). Added to my dismally tiny knowledge of C#, I feel like a giant blundering through a crystal shop, trying to get a good look at a shiny teacup.
0
 
Bob LearnedCommented:
Sorry, I that was a VB.NET-type burp:

    int returnValue = Convert.ToInt32(sqlCmd.Parameters["@ReturnValue"].Value);

Bob
0
 
Paul KahlAuthor Commented:
changed the Try section to:
try
        {
            SQLConn.Open();
            sqlCmd.ExecuteNonQuery();
            int returnValue = Convert.ToInt32(sqlCmd.Parameters["@ReturnValue"].Value);
            return returnValue;
        }

This still returns an error on the "return" line:
Since 'Orrweb.userLoginValidation(string)' returns void, a return keyword must not be followed by an object expression.
0
 
Paul KahlAuthor Commented:
I guess I should change the overall function, since it's a void return, but I don't know the syntax to get it to be a parameterized return.

The whole function is:

    public static void userLoginValidation(string user_email)
    {
        /* Validate User */
        SqlConnection SQLConn = new SqlConnection(OrrwebConnectionString());

        SqlCommand sqlCmd = new SqlCommand("spx_SvcReq_Login", SQLConn);
        sqlCmd.CommandType = CommandType.StoredProcedure;

        sqlCmd.Parameters.Add("@email", SqlDbType.VarChar);
        sqlCmd.Parameters["@email"].Value = user_email;

        sqlCmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

        try
        {
            SQLConn.Open();
            sqlCmd.ExecuteNonQuery();
            int returnValue = Convert.ToInt32(sqlCmd.Parameters["@ReturnValue"].Value);
            return returnValue;
        }
        finally
        {
            SQLConn.Close();
        }
    }

changing it to public static int userLoginValidation might do the trick. That seems to return no errors.

That said, the final approach to the finish line is the validation function on the front end.

I have a call on button click (this is a login form, don'tcha know) to this:
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        if (!isEmail(this.txtEmailAddress.Text))
        {
            lblErrorMessage.Text = this.txtEmailAddress.Text + " is not a valid email address.";
        }
        else
        {
            int returnValue = Orrweb.userLoginValidation(this.txtEmailAddress.Text);
            Response.Write(returnValue.ToString());
           
           
            lblErrorMessage.Text = "Email Address is valid.";
            Session.Add("UserEmail",this.txtEmailAddress.Text);
            //Response.Redirect("Default.aspx");
        }
    }

It seems to work beautifully, and that said, thanks a TON for all your help!
0
 
Bob LearnedCommented:
Man, I missed the 'void' too :(

Bob
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now