locked
Retrieving @ReturnValue parameter using SqlHelper RRS feed

  • Question

  • User-1469591355 posted
    I have some SP that looks like the following:

    CREATE PROCEDURE [dbo].[Users_CreateUser]
        @UserName    NVARCHAR(64),
        @Password    NVARCHAR(128),
        @PasswordFormat INT,
        @PasswordSalt NVARCHAR(128),
        @Email        NVARCHAR(256),
        @UserId        INT OUTPUT
    AS
    BEGIN
        IF ( EXISTS ( SELECT UserId
                      FROM   dbo.Users
                      WHERE  Username = @Username ) )
        BEGIN
            RETURN 6
        END

        INSERT INTO [dbo].[Users] (UserName, Password, PasswordFormat, PasswordSalt, Email)
        VALUES (@UserName, @Password, @PasswordFormat, @PasswordSalt, @Email)

        IF (@@ERROR <> 0)
            RETURN -1
        ELSE
            SET @UserId = SCOPE_IDENTITY()

        RETURN 0
    END

    I am using .Net 2.0 and MS DAAB 2.0

                SqlParameter [] arParms = new SqlParameter[7];

                arParms[0] = new SqlParameter("@Username", SqlDbType.NVarChar, 64 );
                arParms[0].Value = username;
                arParms[1] = new SqlParameter("@Password", SqlDbType.NVarChar, 128 );
                arParms[1].Value = password;
                arParms[2] = new SqlParameter("@PasswordFormat", SqlDbType.Int );
                arParms[2].Value = (int) this.PasswordFormat;
                arParms[3] = new SqlParameter("@PasswordSalt", SqlDbType.NVarChar, 128 );
                arParms[3].Value = salt;
                arParms[4] = new SqlParameter("@Email", SqlDbType.NVarChar, 256 );
                arParms[4].Value = email;
                // Output UserId
                arParms[5] = new SqlParameter("@UserId", SqlDbType.Int);
                arParms[5].Direction = ParameterDirection.Output;
                // Return Value
                arParms[6] = new SqlParameter("@Return_Value", SqlDbType.Int);
                arParms[6].Direction = ParameterDirection.ReturnValue;

                SqlHelper.ExecuteNonQuery(this._sqlConnectionString, "Users_CreateUser", arParms);

    When I execute this code I get the following exception:

    Exception Details: System.ArgumentException: Parameter count does not match Parameter Value count.

     I am assuming this error is popping up because my SP only takes 6 parameters but I am sending a 7th parameter for the return value as well. How can I get around this? Should I just do a SELECT before my RETURNs and then perform an ExecuteScalar?

    Thanks

    Thursday, August 25, 2005 4:45 PM

All replies

  • User-1469591355 posted
    Well I have figured out the problem.

    Line 338 of SqlHelper.cs inside ExecuteNonQuery:


                // Detach the SqlParameters from the command object, so they can be used again
                cmd.Parameters.Clear();


    What's interesting is that in the ExecuteReader method there is a safety check:

    Line 807 of SqlHelper.cs ExecuteReader:


                    // Detach the SqlParameters from the command object, so they can be used again.
                    // HACK: There is a problem here, the output parameter values are fletched
                    // when the reader is closed, so if the parameters are detached from the command
                    // then the SqlReader can´t set its values.
                    // When this happen, the parameters can´t be used again in other command.
                    bool canClear = true;
                    foreach(SqlParameter commandParameter in cmd.Parameters)
                    {
                        if (commandParameter.Direction != ParameterDirection.Input)
                            canClear = false;
                    }
               
                    if (canClear)
                    {
                        cmd.Parameters.Clear();
                    }


    Does anyone know if this is fixed in the Enterprise Library, or would it be easier to patch up the current MS DAAB v2.0 code?

    Thanks




    Thursday, August 25, 2005 7:52 PM
  • User-1469591355 posted
    Bah! Sorry about the formatting in the previous post... not sure what the heck happened there. Anyways my problem is fixed and it was my own darn fault. I feel better knowing that this stumped others as well :)

    Here's a blog post with the solution in Bill Selznick's comment http://weblogs.asp.net/tgraham/archive/2003/05/14/7031.aspx

    Thursday, August 25, 2005 10:09 PM
  • User-2133032323 posted

    Hi..... I'm a vb.net programmer learning C# ..... I'm re-doing my business logic layer for my site in C# and I'm having some problems.. this is my code

    public class states

    {

    #region "Private Members"

    private string _ConnectionString;

    #endregion

    public states(string ConnectionString)

    {

    _ConnectionString = ConnectionString;

    }

    public DataTable @Get(string state_id)

    {

    SqlParameter[] @params = new SqlParameter[1];

    DataSet da;

    try

    {

    @params[0] = new SqlParameter("@state_id", state_id);

    return da = SqlHelper.ExecuteDataset(_ConnectionString, CommandType.StoredProcedure, "SelectStates", @params).Tables(0);

    }

    catch (SqlException exSql)

    {

    throw (new ApplicationException(exSql.Message, exSql));

    }

    catch (Exception ex)

    {

    throw (new ApplicationException("Error Getting User", ex));

    }

    My problem is at --> return da = SqlHelper.Executedataset() <-- Its saying the namespace SQLHelper isn't valid, but I've been searching around forums and that seems to work for everyone else.... I am using data.SqlClient

    Saturday, August 27, 2005 10:16 PM
  • User-1469591355 posted
    You need to download and compile the MS Data Access Application Block (aka MS DAAB). The reason you are getting that error is because you don't have the line: using Microsoft.ApplicationBlocks.Data; in your code. If you don't have the MS DAAB dll referenced then you will need to download and compile the DAAB code.

    You can download the code at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp

    If you need help compiling the code just shoot me a message and I can send you the compiled DLL.

    Sunday, August 28, 2005 12:29 AM
  • User1584761184 posted

    You shoud use this Namespace for sqlhelper

     

    using Microsoft.ApplicationBlocks.Data;

     

    for solve your problem.

     

    Bye

    Babji Raju G.

    gbraju2001@gmail.com

    Friday, September 2, 2005 2:32 AM