Stored Procedures - Return Value
-
Hi, This is my first time trying stored procedures on ASP.NET using C# but I am having some problem with the return value. Here is my stored procedures CREATE PROCEDURE IsValidLogin ( @username varchar(15) = NULL, @password varchar(12) = NULL , @num_of_user INT = 0 ) AS BEGIN SET @num_of_user = (SELECT COUNT(*) AS num_of_user FROM tb_user WHERE useremail = @username AND userpassword = @password) RETURN @num_of_user END GO Then here is my code (btw, this is for login) try { if (myConnection.State == ConnectionState.Closed) { myConnection.Open(); } myCommand = new SqlCommand("IsValidLogin", myConnection); myCommand.CommandType = CommandType.StoredProcedure; SqlParameter objParam1, objParam2, objParam3; objParam1 = myCommand.Parameters.Add("@username", SqlDbType.VarChar); objParam2 = myCommand.Parameters.Add("@password", SqlDbType.Char); objParam3 = myCommand.Parameters.Add("@num_of_user", SqlDbType.Int); objParam1.Direction = ParameterDirection.Input; objParam2.Direction = ParameterDirection.Input; objParam3.Direction = ParameterDirection.ReturnValue; objParam1.Value = this.TextBox1.Text; objParam2.Value = this.TextBox2.Text; myCommand.ExecuteNonQuery(); Response.Write(objParam3.Value); } catch(Exception exception) { throw exception; } finally { myConnection.Dispose(); myConnection.Close(); } It keep on returning '0' whiich I don't know where it gone wrong. Your help is much appreciated. Thank you. J Liang
-
Hi, This is my first time trying stored procedures on ASP.NET using C# but I am having some problem with the return value. Here is my stored procedures CREATE PROCEDURE IsValidLogin ( @username varchar(15) = NULL, @password varchar(12) = NULL , @num_of_user INT = 0 ) AS BEGIN SET @num_of_user = (SELECT COUNT(*) AS num_of_user FROM tb_user WHERE useremail = @username AND userpassword = @password) RETURN @num_of_user END GO Then here is my code (btw, this is for login) try { if (myConnection.State == ConnectionState.Closed) { myConnection.Open(); } myCommand = new SqlCommand("IsValidLogin", myConnection); myCommand.CommandType = CommandType.StoredProcedure; SqlParameter objParam1, objParam2, objParam3; objParam1 = myCommand.Parameters.Add("@username", SqlDbType.VarChar); objParam2 = myCommand.Parameters.Add("@password", SqlDbType.Char); objParam3 = myCommand.Parameters.Add("@num_of_user", SqlDbType.Int); objParam1.Direction = ParameterDirection.Input; objParam2.Direction = ParameterDirection.Input; objParam3.Direction = ParameterDirection.ReturnValue; objParam1.Value = this.TextBox1.Text; objParam2.Value = this.TextBox2.Text; myCommand.ExecuteNonQuery(); Response.Write(objParam3.Value); } catch(Exception exception) { throw exception; } finally { myConnection.Dispose(); myConnection.Close(); } It keep on returning '0' whiich I don't know where it gone wrong. Your help is much appreciated. Thank you. J Liang
-
Hi, Do you mean doing SELECT @num_of_user instead of SELECT COUNT(*) AS num_of_user? If it is, I don't think I could not select @num_of_user as it is suppose to capture the return value from the stored procedure and show it back to me. Sorry if I did not get what you mean. J Liang
-
Hi, This is my first time trying stored procedures on ASP.NET using C# but I am having some problem with the return value. Here is my stored procedures CREATE PROCEDURE IsValidLogin ( @username varchar(15) = NULL, @password varchar(12) = NULL , @num_of_user INT = 0 ) AS BEGIN SET @num_of_user = (SELECT COUNT(*) AS num_of_user FROM tb_user WHERE useremail = @username AND userpassword = @password) RETURN @num_of_user END GO Then here is my code (btw, this is for login) try { if (myConnection.State == ConnectionState.Closed) { myConnection.Open(); } myCommand = new SqlCommand("IsValidLogin", myConnection); myCommand.CommandType = CommandType.StoredProcedure; SqlParameter objParam1, objParam2, objParam3; objParam1 = myCommand.Parameters.Add("@username", SqlDbType.VarChar); objParam2 = myCommand.Parameters.Add("@password", SqlDbType.Char); objParam3 = myCommand.Parameters.Add("@num_of_user", SqlDbType.Int); objParam1.Direction = ParameterDirection.Input; objParam2.Direction = ParameterDirection.Input; objParam3.Direction = ParameterDirection.ReturnValue; objParam1.Value = this.TextBox1.Text; objParam2.Value = this.TextBox2.Text; myCommand.ExecuteNonQuery(); Response.Write(objParam3.Value); } catch(Exception exception) { throw exception; } finally { myConnection.Dispose(); myConnection.Close(); } It keep on returning '0' whiich I don't know where it gone wrong. Your help is much appreciated. Thank you. J Liang
Hi, try this way... SET @num_of_user = SELECT COUNT(*) FROM tb_user WHERE useremail = @username AND userpassword = @password) RETURN @num_of_user
Kiran Kumar.CH (MCP)
-
Hi, This is my first time trying stored procedures on ASP.NET using C# but I am having some problem with the return value. Here is my stored procedures CREATE PROCEDURE IsValidLogin ( @username varchar(15) = NULL, @password varchar(12) = NULL , @num_of_user INT = 0 ) AS BEGIN SET @num_of_user = (SELECT COUNT(*) AS num_of_user FROM tb_user WHERE useremail = @username AND userpassword = @password) RETURN @num_of_user END GO Then here is my code (btw, this is for login) try { if (myConnection.State == ConnectionState.Closed) { myConnection.Open(); } myCommand = new SqlCommand("IsValidLogin", myConnection); myCommand.CommandType = CommandType.StoredProcedure; SqlParameter objParam1, objParam2, objParam3; objParam1 = myCommand.Parameters.Add("@username", SqlDbType.VarChar); objParam2 = myCommand.Parameters.Add("@password", SqlDbType.Char); objParam3 = myCommand.Parameters.Add("@num_of_user", SqlDbType.Int); objParam1.Direction = ParameterDirection.Input; objParam2.Direction = ParameterDirection.Input; objParam3.Direction = ParameterDirection.ReturnValue; objParam1.Value = this.TextBox1.Text; objParam2.Value = this.TextBox2.Text; myCommand.ExecuteNonQuery(); Response.Write(objParam3.Value); } catch(Exception exception) { throw exception; } finally { myConnection.Dispose(); myConnection.Close(); } It keep on returning '0' whiich I don't know where it gone wrong. Your help is much appreciated. Thank you. J Liang
CREATE PROCEDURE IsValidLogin ( @username varchar(15) = NULL, @password varchar(12) = NULL , @num_of_user INT = 0 **--This means that it's an input parameter** )Try CREATE PROCEDURE IsValidLogin ( @username varchar(15) = NULL, @password varchar(12) = NULL , @num_of_user INT = 0 OUTPUT ) --Your SET statement here --No need for a RETURN statement (OR) In your C# codeint retVal; retVal = myCommand.ExecuteNonQuery();Remember: If you want any custom output from your SP, use an OUTPUT parameter. By default an SP returns 0 if it was successful and non-zero in case of failure. This must be used exclusively for error checking. SG -
CREATE PROCEDURE IsValidLogin ( @username varchar(15) = NULL, @password varchar(12) = NULL , @num_of_user INT = 0 **--This means that it's an input parameter** )Try CREATE PROCEDURE IsValidLogin ( @username varchar(15) = NULL, @password varchar(12) = NULL , @num_of_user INT = 0 OUTPUT ) --Your SET statement here --No need for a RETURN statement (OR) In your C# codeint retVal; retVal = myCommand.ExecuteNonQuery();Remember: If you want any custom output from your SP, use an OUTPUT parameter. By default an SP returns 0 if it was successful and non-zero in case of failure. This must be used exclusively for error checking. SGHi i_like_tintin, I tried your way and the retVal keep on returning -1 value. So I tried to display the objParam3.Value which I declare as objParam3.Direction = ParameterDirection.Output; and it return '0' to me no matter my username and password are correct.
-
Hi, try this way... SET @num_of_user = SELECT COUNT(*) FROM tb_user WHERE useremail = @username AND userpassword = @password) RETURN @num_of_user
Kiran Kumar.CH (MCP)
-
Hi i_like_tintin, I tried your way and the retVal keep on returning -1 value. So I tried to display the objParam3.Value which I declare as objParam3.Direction = ParameterDirection.Output; and it return '0' to me no matter my username and password are correct.
Execute your SP in Query Analyzer and check if it works without errors for different sets of input. SG