Problem with Stored Procedure's Return value
-
Hi, I am using a Stored Procedure to validate Login Process: Stored Procedure is defined as follows : ---------------------------------------------------------- CREATE PROCEDURE spCheckLogin @LoginID char(20), @Pwd char(8), @Response int OUTPUT AS IF EXISTS (SELECT * FROM tEmployees WHERE LoginID=@LoginID AND Pwd=@Pwd) Return 1 ELSE Return 0 GO ---------------------------------------------------------- In C#, I am accessing this Stored Procedure as follows: try { string temp=""; //open the connection gvDatabase.scInventoryControl.Open(); //create the sql sql = "spCheckLogin"; //create command cmdLogin = new SqlCommand(sql,gvDatabase.scInventoryControl); cmdLogin.CommandType=CommandType.StoredProcedure; cmdLogin.Parameters.Add("@LoginID", SqlDbType.Char,20); cmdLogin.Parameters["@LoginID"].Value=log; cmdLogin.Parameters.Add("@Pwd", SqlDbType.Char,8); cmdLogin.Parameters["@Pwd"].Value=p; cmdLogin.Parameters.Add("@Response",SqlDbType.Int,4); cmdLogin.Parameters["@Response"].Direction=ParameterDirection.Output; Console.WriteLine("Data Access started"); cmdLogin.ExecuteNonQuery(); temp = cmdLogin.Parameters["@Response"].Value.ToString(); Console.WriteLine(temp); //clean up code cmdLogin.Dispose(); gvDatabase.scInventoryControl.Close(); Console.WriteLine("Data Access Closed"); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } ------------------------------------------------------------ However It doesn't print any values, although I values do exists in the table What am i doing wrong? ThANX in ADvance
-
Hi, I am using a Stored Procedure to validate Login Process: Stored Procedure is defined as follows : ---------------------------------------------------------- CREATE PROCEDURE spCheckLogin @LoginID char(20), @Pwd char(8), @Response int OUTPUT AS IF EXISTS (SELECT * FROM tEmployees WHERE LoginID=@LoginID AND Pwd=@Pwd) Return 1 ELSE Return 0 GO ---------------------------------------------------------- In C#, I am accessing this Stored Procedure as follows: try { string temp=""; //open the connection gvDatabase.scInventoryControl.Open(); //create the sql sql = "spCheckLogin"; //create command cmdLogin = new SqlCommand(sql,gvDatabase.scInventoryControl); cmdLogin.CommandType=CommandType.StoredProcedure; cmdLogin.Parameters.Add("@LoginID", SqlDbType.Char,20); cmdLogin.Parameters["@LoginID"].Value=log; cmdLogin.Parameters.Add("@Pwd", SqlDbType.Char,8); cmdLogin.Parameters["@Pwd"].Value=p; cmdLogin.Parameters.Add("@Response",SqlDbType.Int,4); cmdLogin.Parameters["@Response"].Direction=ParameterDirection.Output; Console.WriteLine("Data Access started"); cmdLogin.ExecuteNonQuery(); temp = cmdLogin.Parameters["@Response"].Value.ToString(); Console.WriteLine(temp); //clean up code cmdLogin.Dispose(); gvDatabase.scInventoryControl.Close(); Console.WriteLine("Data Access Closed"); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } ------------------------------------------------------------ However It doesn't print any values, although I values do exists in the table What am i doing wrong? ThANX in ADvance
looks to me you got two thing mixed up. you should eighter change youre sql code to ======================= CREATE PROCEDURE spCheckLogin @LoginID char(20), @Pwd char(8), @Response int OUTPUT AS IF EXISTS (SELECT * FROM tEmployees WHERE LoginID=@LoginID AND Pwd=@Pwd) SET @Response=1 ELSE SET @Response=0 GO ======================== OR you should use something like this in youre code: ======================== string temp=""; //create the sql string sql = "spCheckLogin"; //create command cmdLogin = new SqlCommand(sql,gvDatabase.scInventoryControl); cmdLogin.CommandType=CommandType.StoredProcedure; cmdLogin.Parameters.Add("@LoginID", SqlDbType.Char,20); cmdLogin.Parameters["@LoginID"].Value=log; cmdLogin.Parameters.Add("@Pwd", SqlDbType.Char,8); cmdLogin.Parameters["@Pwd"].Value=p; cmdLogin.Parameters.Add("@Response",SqlDbType.Int,4); cmdLogin.Parameters["@Response"].Direction=ParameterDirection.Output; Console.WriteLine("Data Access started"); try { //open the connection gvDatabase.scInventoryControl.Open(); //use the connection temp=(string)cmdLogin.ExecuteScaler(); Console.WriteLine(temp); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } finally { //clean up code gvDatabase.scInventoryControl.Close(); Console.WriteLine("Data Access Closed"); } cmdLogin.Dispose(); ======================== i hope this helps. (p.s. i did not test above code) cookies are for eating, not for websites.
-
Hi, I am using a Stored Procedure to validate Login Process: Stored Procedure is defined as follows : ---------------------------------------------------------- CREATE PROCEDURE spCheckLogin @LoginID char(20), @Pwd char(8), @Response int OUTPUT AS IF EXISTS (SELECT * FROM tEmployees WHERE LoginID=@LoginID AND Pwd=@Pwd) Return 1 ELSE Return 0 GO ---------------------------------------------------------- In C#, I am accessing this Stored Procedure as follows: try { string temp=""; //open the connection gvDatabase.scInventoryControl.Open(); //create the sql sql = "spCheckLogin"; //create command cmdLogin = new SqlCommand(sql,gvDatabase.scInventoryControl); cmdLogin.CommandType=CommandType.StoredProcedure; cmdLogin.Parameters.Add("@LoginID", SqlDbType.Char,20); cmdLogin.Parameters["@LoginID"].Value=log; cmdLogin.Parameters.Add("@Pwd", SqlDbType.Char,8); cmdLogin.Parameters["@Pwd"].Value=p; cmdLogin.Parameters.Add("@Response",SqlDbType.Int,4); cmdLogin.Parameters["@Response"].Direction=ParameterDirection.Output; Console.WriteLine("Data Access started"); cmdLogin.ExecuteNonQuery(); temp = cmdLogin.Parameters["@Response"].Value.ToString(); Console.WriteLine(temp); //clean up code cmdLogin.Dispose(); gvDatabase.scInventoryControl.Close(); Console.WriteLine("Data Access Closed"); } catch(Exception ex) { Console.WriteLine(ex.ToString() ); } ------------------------------------------------------------ However It doesn't print any values, although I values do exists in the table What am i doing wrong? ThANX in ADvance