CODEWITHSUNDEEP
c#sqlstored-procedurescountnullreferenceexception
Héctor
Héctor

Reputation: 23

How can I obtain a value from a stored procedure (SELECT COUNT) in c#

I'm using a stored procedure to count and validate if a username already exists in the database, but the problem isn't there, my problem is when I try to obtain the value of the SELECTION in C# in some blogs I found this code:

// Create ConnectionString
string connectionString = ConfigurationManager.ConnectionStrings["ProjectPractice"].ConnectionString;

// Check if the username is not in the DataBase
SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmo = new SqlCommand("spBuscaUsuarioMail", con);
cmo.CommandType = CommandType.StoredProcedure;
cmo.Parameters.AddWithValue("@Username", 1);

SqlParameter NroUserName = new SqlParameter("@Num_de_Usuarios", 0);
NroUserName.Direction = ParameterDirection.Output;
cmo.Parameters.Add(NroUserName);

con.Open();

int contarUsername = Int32.Parse(cmo.Parameters["@Num_de_Usuarios"].Value.ToString());

This seems to be great! And it looks like function very well but then in the last part int contarUsername = Int32.Parse... C# gives me an exception that is the next:

NullReferenceException was unhandled by user code.
Object reference not set o an instance of an object.

Then, in troubleshooting tips I read:

I tried a lot of things, but now I feel really tired and I can't think in a new solution.

This is the stored procedure I'm using:

CREATE PROCEDURE spBuscaUsuarioMail
   @Username CHAR (25),
   @Num_de_Usuarios INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    SELECT @Num_de_Usuarios = COUNT (UserName)
    FROM dbo.Usuarios
    WHERE UserName = @Username
END

EXEC spBuscaUsuarioMail '1', @Num_de_Usuarios OUTPUT
SELECT @Num_de_Usuarios

Upvotes: 2

Views: 431

Answers (3)

Héctor
Héctor

Reputation: 23

This is the solution. It works!

            string connectionString = connectionString = ConfigurationManager.ConnectionStrings["ProjectPractice"].ConnectionString;

            SqlConnection con = new SqlConnection(connectionString);

            SqlCommand cmo = new SqlCommand("spBuscaUsuario", con);
            cmo.CommandType = CommandType.StoredProcedure;
            cmo.Parameters.Add("@Username", SqlDbType.Char, 25).Value = txtUsername.Text;
            SqlParameter NroUserName = new SqlParameter("@Num_de_Usuarios", 0);
            NroUserName.Direction = ParameterDirection.Output;
            cmo.Parameters.Add(NroUserName);
            con.Open();
            cmo.ExecuteNonQuery();
            int contarUsername = Int32.Parse(cmo.Parameters["@Num_de_Usuarios"].Value.ToString());

And this is the Stored Procedure...

CREATE PROCEDURE spBuscaUsuario

@Username CHAR (25), @Num_de_Usuarios INT OUTPUT

AS

BEGIN

SET NOCOUNT ON
SELECT @Num_de_Usuarios = COUNT (UserName)
FROM dbo.Usuarios
WHERE UserName = @Username

END

I expect this code be useful for someone else.

Upvotes: 0

marc_s
marc_s

Reputation: 754518

There's something wrong with your parameters....

In the stored procedure, you have

@Username CHAR (25),
@Num_de_Usuarios INT OUTPUT

yet in your C# code, you set up both of them as int, it seems:

cmo.Parameters.AddWithValue("@Username", 1);

SqlParameter NroUserName = new SqlParameter("@Num_de_Usuarios", 0);
NroUserName.Direction = ParameterDirection.Output;
cmo.Parameters.Add(NroUserName);

I think you need to set up the @Username parameter as a string - and give it some meaningful value!

cmo.Parameters.Add("@Username", SqlDbType.Char, 25).Value = "Steve";  // or whatever

And of course, as @Steve already mentioned: you need to actually execute your stored procedure before you can read out the OUTPUT parameter!

Upvotes: 1

Steve
Steve

Reputation: 216293

If you don't execute the command it is really difficult to get the value of an output parameter

 cmo.Parameters.Add(NroUserName);
 con.Open();
 cmo.ExecuteNonQuery();
 int contarUsername = Convert.ToInt32(cmo.Parameters["@Num_de_Usuarios"].Value);

Also the last two lines of the Stored procedure, if they are really in the sp code are meaningless, you don't need them and you get a recursive call on the same stored procedure until somethink breaks . You should remove them

-- remove these two lines
EXEC spBuscaUsuarioMail '1', @Num_de_Usuarios OUTPUT
SELECT @Num_de_Usuarios

Also look at the answer from @marc_s, he points to another problem in your code.

Upvotes: 1

Related Questions