Calling a stored procedure with exit

Question

I am trying to get some information from my database, and also get the return value. I know that the stored procedure is working fine.

The code I use is a modified part that I use to register a user. This happens incorrectly in the cmd.ExecuteReader part of my code.

protected void btn_login_Click(object sender, ImageClickEventArgs e) { //Actions after Submit button is clicked Page.Validate(((ImageButton)sender).ValidationGroup); if (Page.IsValid) { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnectString"].ConnectionString)) { SqlCommand cmd = new SqlCommand("usp_validateUsers", conn); //Input Values cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("username", Uname.Text); cmd.Parameters.AddWithValue("password", pwd.Text); //Return Values SqlParameter retParam = cmd.Parameters.Add("@RetVal", SqlDbType.Int); retParam.Direction = ParameterDirection.ReturnValue; SqlParameter acsParam = cmd.Parameters.Add("@ac_status", SqlDbType.Int); acsParam.Direction = ParameterDirection.Output; SqlParameter nikParam = cmd.Parameters.Add("@memb_name", SqlDbType.VarChar); nikParam.Direction = ParameterDirection.Output; try { // Open Connection and execute Stored Proc conn.Open(); ///////////SOMETHING GOES WRONG HERE/////////////// cmd.ExecuteReader(); //Retrieve Data int retVal = (int)retParam.Value; string nickname = nikParam.Value.ToString(); string ac_stats = acsParam.Value.ToString(); if (retVal != 0) { //Invalid Username or password } else { //Login User } } catch (Exception Error) { lbl_login.Text = "An error occured, please try again later"; debug.Text = Error.Message; } finally { debug.Text = "\n Clossing Connection"; if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } } } } 

When I just want to get the return value, I just use cmd.ExecuteScalar(); I know how to get data when I pass an SQL query to an SQL database, but when using stored procedures it seems like this is different.

EDIT This code may probably improve, but it really does what it should do.

 ALTER PROCEDURE dbo.usp_validateUsers @username varchar(10), @password varchar(10), @ac_status char(1) OUTPUT, @memb_name varchar(15) OUTPUT AS IF EXISTS(SELECT * FROM MEMB_INFO WHERE (memb___id = @username)) BEGIN SELECT @ac_status = ac_status, @memb_name = memb_name FROM MEMB_INFO WHERE (memb___id = @username) AND (memb__pwd = @password) RETURN 0 END ELSE BEGIN return 1 END 

When I use breakpoints to catch possible exceptions in Visual Studio, it gives me: String [4]: ​​Size property has an invalid size of 0

Answer 1

The error you indicated may be caused by the fact that you did not specify the size of your VarChar parameters. Instead of these lines:

 SqlParameter nikParam = cmd.Parameters.Add("@memb_name", SqlDbType.VarChar); 

Try the following:

 SqlParameter nikParam = cmd.Parameters.Add("@memb_name", SqlDbType.VarChar, 15); 

Answer 2

You need to create SqlDataReader.

From turning on and off using stored procedures in C #

The SqlDataReader class is used to read only the direct stream of records returned from the database. The SqlDataReader object is not created directly through the constructor (hence the lack of a keyword), but rather through the ExecuteReader Method for the SqlCommand object. Before calling the ExecuteReader, the database connection method is created using the Public method of the SqlConnection object.

Try

 SqlDataReader drLogins; Conn.Open(); drLogins = cmd.ExecuteReader(); 

Answer 3

Your @ac_status defined as an integer parameter. change its character or string.

 SqlParameter acsParam = cmd.Parameters.Add("@ac_status", SqlDbType.Int);