Problem with stored procedure/C# code
-
Hi, i have created a stored procedure which returns number of un-read memo(s)..... stored procedure's code : ---------------------------- CREATE PROCEDURE spNumOfUnReadMemos @EmpTo char(6), @NumOfUnReadMemos int OUTPUT AS SELECT COUNT(EmpTo) FROM tblMemoManagement WHERE EmpTo=@EmpTo AND ReadYesNo='NO' RETURN; GO --------------------------------------- Then i have a C# Class which access this stored procedure as public int getNumUnReadMemos() { int retVal=0; try { //create the sql sql = "spNumOfUnReadMemos"; //open the connection gICVars.scInCtrl.Open(); //create the command cmdMemo = new SqlCommand(sql,gICVars.scInCtrl); cmdMemo.CommandType=CommandType.StoredProcedure; //create the parameters cmdMemo.Parameters.Add("@EmpTo", SqlDbType.Char,6); cmdMemo.Parameters[0].Value=this.getToEmp(); cmdMemo.Parameters.Add("@NumOfUnReadMemos", SqlDbType.Int,4); cmdMemo.Parameters[1].Direction=ParameterDirection.Output; //execute the non-query cmdMemo.ExecuteNonQuery(); //retrieve the value of NumOfUnReadMemos Console.WriteLine(cmdMemo.Parameters[1].Value); //retVal = int.Parse(cmdMemo.Parameters[1].Value.ToString()); Console.WriteLine(cmdMemo.Parameters[1].Value); //clean up code cmdMemo.Dispose(); sql=null; gICVars.scInCtrl.Close(); return retVal; } catch(Exception ex) { Console.WriteLine(ex.ToString()); return -1; } } ---------------------------------------------- whenever i tried to execute this code .. i got following error.... System.FormatException: Input string was not in a correct format. at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.Int32.Parse(String s, NumberStyles style, IFormatProvider provider) at System.Int32.Parse(String s) at InventoryControl.cMemo.getNumUnReadMemos() in d:\shirtuniverse\inventorycontrol\cmemo.cs:line 380 ------------------------ any idea(s)... y i am getting this error THANX in advance
-
Hi, i have created a stored procedure which returns number of un-read memo(s)..... stored procedure's code : ---------------------------- CREATE PROCEDURE spNumOfUnReadMemos @EmpTo char(6), @NumOfUnReadMemos int OUTPUT AS SELECT COUNT(EmpTo) FROM tblMemoManagement WHERE EmpTo=@EmpTo AND ReadYesNo='NO' RETURN; GO --------------------------------------- Then i have a C# Class which access this stored procedure as public int getNumUnReadMemos() { int retVal=0; try { //create the sql sql = "spNumOfUnReadMemos"; //open the connection gICVars.scInCtrl.Open(); //create the command cmdMemo = new SqlCommand(sql,gICVars.scInCtrl); cmdMemo.CommandType=CommandType.StoredProcedure; //create the parameters cmdMemo.Parameters.Add("@EmpTo", SqlDbType.Char,6); cmdMemo.Parameters[0].Value=this.getToEmp(); cmdMemo.Parameters.Add("@NumOfUnReadMemos", SqlDbType.Int,4); cmdMemo.Parameters[1].Direction=ParameterDirection.Output; //execute the non-query cmdMemo.ExecuteNonQuery(); //retrieve the value of NumOfUnReadMemos Console.WriteLine(cmdMemo.Parameters[1].Value); //retVal = int.Parse(cmdMemo.Parameters[1].Value.ToString()); Console.WriteLine(cmdMemo.Parameters[1].Value); //clean up code cmdMemo.Dispose(); sql=null; gICVars.scInCtrl.Close(); return retVal; } catch(Exception ex) { Console.WriteLine(ex.ToString()); return -1; } } ---------------------------------------------- whenever i tried to execute this code .. i got following error.... System.FormatException: Input string was not in a correct format. at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.Int32.Parse(String s, NumberStyles style, IFormatProvider provider) at System.Int32.Parse(String s) at InventoryControl.cMemo.getNumUnReadMemos() in d:\shirtuniverse\inventorycontrol\cmemo.cs:line 380 ------------------------ any idea(s)... y i am getting this error THANX in advance
ronin1770 wrote: CREATE PROCEDURE spNumOfUnReadMemos @EmpTo char(6), @NumOfUnReadMemos int OUTPUT AS SELECT COUNT(EmpTo) FROM tblMemoManagement WHERE EmpTo=@EmpTo AND ReadYesNo='NO' RETURN; GO No where in this stored procedure is the @NumOfUnReadMemos assigned so the output parameter will not contain anything. You have two choices. 1. Assign the value to the output parameter 2. Access the Stored Procedure in .NET using ExecuteScalar() ronin1770 wrote: //retVal = int.Parse(cmdMemo.Parameters[1].Value.ToString()); I'm guessing that this is where the parser error occurred. The reason is that the value, when converted to a string, is not a number, so the parser throws an exception. It isn't a number because the stored procedure never assigned anything to the output parameter.
Do you want to know more? WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
-
ronin1770 wrote: CREATE PROCEDURE spNumOfUnReadMemos @EmpTo char(6), @NumOfUnReadMemos int OUTPUT AS SELECT COUNT(EmpTo) FROM tblMemoManagement WHERE EmpTo=@EmpTo AND ReadYesNo='NO' RETURN; GO No where in this stored procedure is the @NumOfUnReadMemos assigned so the output parameter will not contain anything. You have two choices. 1. Assign the value to the output parameter 2. Access the Stored Procedure in .NET using ExecuteScalar() ronin1770 wrote: //retVal = int.Parse(cmdMemo.Parameters[1].Value.ToString()); I'm guessing that this is where the parser error occurred. The reason is that the value, when converted to a string, is not a number, so the parser throws an exception. It isn't a number because the stored procedure never assigned anything to the output parameter.
Do you want to know more? WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums