There are too many problems with this.
wrote:
@Num_of_User INT = 0
You have a parameter clash. You've defined this as a return value, yet here you are specifying it as an input parameter with a default value of zero.
wrote:
objParam1.Value = "txtuser"; objParam2.Value = "txtPass";
This passes a string literal to the parameter, so the stored procedure will never get the value passed in to the method, so it will never find anything because it does not match what you inserted into the database.
wrote:
if(myConnection.State.Equals(ConnectionState.Closed)) { myConnection.Open();
This is redundant. The connection will always be closed as it is newly created. Here is a simpler solution: For something this simple, I'd use ExecuteScalar and get the value from the SELECT directly SQL:
alter PROCEDURE ValidateUser
(
@UserName VARCHAR(50) = Null,
@Password VARCHAR(50) = Null
)
AS
SELECT COUNT(*)
FROM NorthWindUsers
WHERE UserName = @UserName AND Password = @Password
GO
The sp prefix was removed because in SQL Server this may clash with system stored procedures. If SQL Server sees a stored procedure call starting with sp it goes to the master database first. C#
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connstr"]);
SqlCommand myCommand = new SqlCommand("ValidateUser", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
SqlParameter objParam1 = myCommand.Parameters.Add("@UserName", SqlDbType.VarChar);
SqlParameter objParam2 = myCommand.Parameters.Add("@Password",SqlDbType.VarChar);
objParam1.Value = txtuser;
objParam2.Value = txtPass;
myConnection.Open();
int returnParam = (int)myCommand.ExecuteScalar();
myConnection.Close();
.... // Do the rest of your stuff.
The direction was dropped, as all parameters are Input by default.
Upcoming events: * Glasgow: Geek Dinner (5th March) * Edinburgh: Web Security Conference Day for Windows Developers (12th April) My: Website | Blog |
